C# 读取Excel文件代码的几种方法Word文件下载.docx
《C# 读取Excel文件代码的几种方法Word文件下载.docx》由会员分享,可在线阅读,更多相关《C# 读取Excel文件代码的几种方法Word文件下载.docx(7页珍藏版)》请在冰豆网上搜索。
//源的定义
strConn
=
"
Provider=Microsoft.Jet.OLEDB.4.0;
+
Data
Source="
strExcelFileName
;
Extended
Properties='
Excel
8.0;
HDR=NO;
IMEX=1'
//Sql语句
//string
strExcel
string.Format("
select
*
from
[{0}$]"
strSheetName);
这是一种方法
[sheet1$]"
//定义存放的数据表
DataSet
ds
new
DataSet();
//连接数据源
OleDbConnection
conn
OleDbConnection(strConn);
conn.Open();
//适配到数据源
OleDbDataAdapter
adapter
OleDbDataAdapter(strExcel,
strConn);
adapter.Fill(ds,
conn.Close();
return
ds.Tables[strSheetName];
很简单的代码,但是问题就出在连接字符串上面,后面一定要加上ExtendedProperties='
Excel8.0;
,HDR和IMEX也一定要配合使用,哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...
片段2
提供两种方法:
一个是直接打开excel文件,然后逐行读取,速度较慢;
还有一种方法是通过OleDb连接,把excel文件作为数据源来读取
方法一:
这种直接读取单元格的方法释放很重要。
Excel.Application
excel
null;
Excel.Workbooks
wbs
Excel.Workbook
wb
Excel.Worksheet
ws
Excel.Range
range1
object
Nothing
System.Reflection.Missing.Value;
try
Excel.Application();
excel.UserControl
true;
excel.DisplayAlerts
false;
excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing
)
excel.Workbooks;
wbs[1];
(Excel.Worksheet)wb.Worksheets["
Sheet2"
];
int
rowCount
ws.UsedRange.Rows.Count;
colCount
ws.UsedRange.Columns.Count;
if
(rowCount
<
0)
throw
InvalidFormatException("
文件中没有数据记录"
(colCount
4
)
字段个数不对"
for
(int
i
0;
this.rowNo
1;
object[]
row
object[4];
j
j<
4;
j++)
ws.get_Range(ws.Cells[i+2,j+1],ws.Cells[i+2,j+1]);
row[j]
range1.Value;
(row[0]
==
null)
this.isNullRecord++;
break;
(this.isNullRecord
>
continue;
DataRow
dataRow
this.readExcel(row);
1)
(this.verifyData(dataRow)
false)
errFlag++;
this.updateTableCurr(dataRow);
finally
(excel
!
(wbs
(wb
(ws
(range1
System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
wb.Close(false,Nothing,Nothing);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
wbs.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
excel.Application.Workbooks.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
方法二:
这里是fill进dataset,也可以返回OleDbDataReader来逐行读,数据较快
注:
这种方法容易把混合型的字段作为null值读取进来,解决办法是改造连接字符串
strConn="
DataSource=C:
\\Erp1912.xls;
ExtendedProperties='
HDR=Yes;
通过Imex=1来把混合型作为文本型读取,避免null值,详细请见
importExcelToDataSet(string
FilePath)
strConn;
FilePath
Properties=Excel
myCommand
OleDbDataAdapter("
SELECT
FROM
[Sheet1$]"
strConn);
myDataSet
myCommand.Fill(myDataSet);
catch(Exception
ex)
该Excel文件的工作表的名字不正确,"
ex.Message);
myDataSet;
片段3
用c#读取excel文件,写到datagridview控件中
strconn
Source=C:
\MyExcel.xls;
Properties="
'
OleDbConnection(strconn);
(bo
comboBox1.Items.Clear();
dt
conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
{
null,
TABLE"
});
foreach
(DataRow
dr
in
dt.Rows)
comboBox1.Items.Add((String)dr["
TABLE_NAME"
]);
//MessageBox.Show((String)dr["
//comboBox1.Text
comboBox1.Items[0].ToString();
else
sql
comboBox1.Text;
aper
OleDbDataAdapter(sql,
conn);
myset
aper.Fill(myset,
comboBox1.Text);
dataGridView1.DataSource
myset.Tables[comboBox1.Text];
备注:
@"
ExtendedProperties="
IMEX=1"
indicatesthatthefirstrowcontainscolumnnames,notdata
IMEX=1;
tellsthedrivertoalwaysread"
intermixed"
datacolumnsastext
TIP!
SQLsyntax:
"
SELECT*FROM[sheet1$]"
-i.e.worksheetnamefollowedbya"
$"
andwrappedin"
["
]"
brackets.
如果第一行是数据而不是标题的话,应该写:
HDR=No;
片段4
C#读取Excel文件数据
相当简单,Excel就像数据库,每个Sheet就是一个Table.Microsoft.Jet.OLEDB驱动.
之后是DataReader循环,或DataSet处理都非常简单.
HTTP:
//BLOG.CSDN.NET/CRABO/
注意:
数据类型的转换!
#region
set
connection
Source=
+this.txtPath.Text+"
myDataReader
craboDbConnection
OleDbCommand
myOleDbCommand
OleDbCommand("
myOleDbConnection);
#endregion
myOleDbConnection.Open();
myOleDbCommand.ExecuteReader();
while
(myDataReader.Read())
this.txtSeq.Text=Convert.ToString(myDataReader.GetValue(0));
//列1
this.txtName.Text=Convert.ToString(myDataReader.GetValue
(1));
//列2
this.txtPIN.Text=Convert.ToString(myDataReader.GetValue
(2));
//列3
Catch
catch(System.Threading.ThreadAbortException
System.Threading.Thread.ResetAbort();
this.lblResult.Text
线程被中断..."
+e.Message;
System.Windows.Forms.MessageBox.Show(ex.ToString());
//
Always
call
Close
when
done
reading.
(myDataReader
myDataReader.Close();
the
connection
with
it.
(craboDbConnection!
=null
&
craboDbConnection.State
ConnectionState.Open)
craboDbConnection.Close();
if(webResponse!
=null)
webResponse.Close();