用c#读取Excel的三种方法Word下载.docx
《用c#读取Excel的三种方法Word下载.docx》由会员分享,可在线阅读,更多相关《用c#读取Excel的三种方法Word下载.docx(15页珍藏版)》请在冰豆网上搜索。
objConn=newOleDbConnection("
Provider=Microsoft.Jet.OLEDB.4.0;
DataSource="
+strExcelPath+"
;
"
+"
ExtendedProperties=\"
Excel8.0;
HDR=NO;
IMEX=1;
\"
);
17:
break;
18:
.xlsx"
19:
Provider=Microsoft.ACE.OLEDB.12.0;
Excel12.0;
20:
21:
default:
22:
objConn=null;
23:
24:
}
25:
if(objConn==null)
26:
27:
returnnull;
28:
29:
objConn.Open();
30:
//获取Excel中所有Sheet表的信息
31:
//System.Data.DataTableschemaTable=objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
32:
//获取Excel的第一个Sheet表名
33:
//stringtableName=schemaTable.Rows[0][2].ToString().Trim();
34:
stringstrSql="
select*from["
+tableName+"
]"
35:
//获取Excel指定Sheet表中的信息
36:
OleDbCommandobjCmd=newOleDbCommand(strSql,objConn);
37:
OleDbDataAdaptermyData=newOleDbDataAdapter(strSql,objConn);
38:
myData.Fill(ds,tableName);
//填充数据
39:
objConn.Close();
40:
//dtExcel即为excel文件中指定表中存储的信息
41:
dtExcel=ds.Tables[tableName];
42:
returndtExcel;
43:
44:
catch
45:
46:
47:
48:
下面说明一下连接字符串
HDR=Yes,这代表第一行是标题,不做为数据使用(但是我在实际使用中,如果第一行存在复杂数值,那么读取得到的Datatable列标题会自动设置为F1、F2等方式命名,与实际应用不符,所以当时是通过HDR=No方式将所有内容读取到Datatable中,然后手动将第一行设置成标题的);
IMEX(IMportEXportmode)设置
IMEX有三种模式:
0isExportmode
1isImportmode
2isLinkedmode(fullupdatecapabilities)
我这里特别要说明的就是IMEX参数了,因为不同的模式代表著不同的读写行为:
当IMEX=0时为“汇出模式”,这个模式开启的Excel档案只能用来做“写入”用途。
当IMEX=1时为“汇入模式”,这个模式开启的Excel档案只能用来做“读取”用途。
当IMEX=2时为“链接模式”,这个模式开启的Excel档案可同时支援“读取”与“写入”用途。
---------------------------------
另外,读取Excel2007版本的文件时,版本应该从8.0改为12.0,同时驱动不能再用Jet,而应该用ACE。
负责会造成“找不到可安装的ISAM”的错误。
在网上还发现采用这种方式存在取出的Sheet表的个数多于实际Excel表中的Sheet表个数的情况,其原因有二:
1.取出的名称中,包括了XL命名管理器中的名称(参见XL2007的公式--命名管理器,快捷键Crtl+F3);
2.取出的名称中,包括了FilterDatabase后缀的,这是XL用来记录Filter范围的。
对于第一点比较简单,删除已有命名管理器中的内容即可;
第二点处理起来比较麻烦,Filter删除后这些名称依然保留着,简单的做法是新增Sheet然后将原SheetCopy进去。
但实际情况并不能为每个Excel做以上检查。
下面给出了过滤的方案。
(此问题我们有验证过,大家自己验证一下吧)
//objConn为读取Excel的链接,下面通过过滤来获取有效的Sheet页名称集合
System.Data.DataTableschemaTable=objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
List<
string>
lstSheetNames=newList<
();
for(inti=0;
i<
schemaTable.Rows.Count;
i++)
stringstrSheetName=(string)dtSheetName.Rows[i]["
TABLE_NAME"
];
if(strSheetName.Contains("
$"
)&
&
!
strSheetName.Replace("
'
"
).EndsWith("
))
//过滤无效SheetName完毕....
continue;
if(lstSheetNames!
=null&
lstSheetNames.Contains(strSheetName))
lstSheetNames.Add(strSheetName);
因为读取出来无效SheetName一般情况最后一个字符都不会是$。
如果SheetName有一些特殊符号,读取出来的SheetName会自动加上单引号。
比如在Excel中将SheetName编辑成MySheet
(1),此时读取出来的SheetName就为:
MySheet
(1)$'
,所以判断最后一个字符是不是$之前最好过滤一下单引号。
(2)Com组件的方式(通过添加Microsoft.Office.Interop.Excel引用实现)
能够非常灵活的读取Excel中的数据,用户可以灵活的调用各种函数进行处理。
基于单元格的处理,读取速度较慢,对于数据量较大的文件最好不要使用此种方式读取。
需要添加相应的DLL引用,必须存在此引用才可使用,如果是Web站点部署在IIS上时,还需要服务器机子已安装了Excel,有时候还需要为配置IIS权限。
privateStopwatchwath=newStopwatch();
///<
summary>
///使用COM读取Excel
/summary>
paramname="
excelFilePath"
>
路径<
/param>
returns>
DataTabel<
/returns>
publicSystem.Data.DataTableGetExcelData(stringexcelFilePath)
Excel.Applicationapp=newExcel.Application();
Excel.Sheetssheets;
Excel.Workbookworkbook=null;
objectoMissiong=System.Reflection.Missing.Value;
System.Data.DataTabledt=newSystem.Data.DataTable();
wath.Start();
if(app==null)
workbook=app.Workbooks.Open(excelFilePath,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
//将数据读入到DataTable中——Start
sheets=workbook.Worksheets;
Excel.Worksheetworksheet=(Excel.Worksheet)sheets.get_Item
(1);
//读取第一张表
if(worksheet==null)
stringcellContent;
intiRowCount=worksheet.UsedRange.Rows.Count;
intiColCount=worksheet.UsedRange.Columns.Count;
Excel.Rangerange;
//负责列头Start
DataColumndc;
intColumnID=1;
range=(Excel.Range)worksheet.Cells[1,1];
while(range.Text.ToString().Trim()!
="
)
dc=newDataColumn();
dc.DataType=System.Type.GetType("
System.String"
dc.ColumnName=range.Text.ToString().Trim();
dt.Columns.Add(dc);
range=(Excel.Range)worksheet.Cells[1,++ColumnID];
//End
for(intiRow=2;
iRow<
=iRowCount;
iRow++)
DataRowdr=dt.NewRow();
49:
for(intiCol=1;
iCol<
=iColCount;
iCol++)
50:
51:
range=(Excel.Range)worksheet.Cells[iRow,iCol];
52:
cellContent=(range.Value2==null)?
"
:
range.Text.ToString();
53:
dr[iCol-1]=cellContent;
54:
55:
dt.Rows.Add(dr);
56:
57:
wath.Stop();
58:
TimeSpants=wath.Elapsed;
59:
//将数据读入到DataTable中——End
60:
returndt;
61:
62:
63:
64:
65:
66:
finally
67:
68:
workbook.Close(false,oMissiong,oMissiong);
69:
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
70:
workbook=null;
71:
app.Workbooks.Close();
72:
app.Quit();
73:
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
74:
app=null;
75:
GC.Collect();
76:
GC.WaitForPendingFinalizers();
77:
78:
79:
80:
///使用COM,多线程读取Excel(1主线程、4副线程)
81:
82:
83:
84:
publicSystem.Data.DataTableThreadReadExcel(stringexcelFilePath)
85:
86:
87:
Excel.Sheetssheets=null;
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
workbook=app.Workbooks.Open(excelFilePath,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,
99:
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
while(iColCount>
=ColumnID)
114:
115:
116:
117:
stringstrNewColumnName=range.Text.ToString().Trim();
118:
if(strNewColumnName.Length==0)strNewColumnName="
_1"
119:
//判断列名是否重复
120:
for(inti=1;
ColumnID;
121:
122:
if(dt.Columns[i-1].ColumnName==strNewColumnName)
123:
strNewColumnName=strNewColumnName+"
124:
125:
dc.ColumnName=strNewColumnName;
126:
127:
128:
129:
130:
//数据大于500条,使用多进程进行读取数据
131:
if(iRowCount-1>
500)
132:
133:
//开始多线程读取数据
134:
//新建线程
135:
intb2=(iRowCount-1)/10;
136:
DataTabledt1=newDataTable("
dt1"
137:
dt1=dt.Clone();
138:
SheetOptionssheet1thread=newSheetOptions(worksheet,iColCount,2,b2+1,dt1);
139:
Threadothread1=newThread(newThreadStart(sheet1thread.SheetToDataTable));
140:
othread1.Start();
141:
//阻塞1毫秒,保证第一个读取dt1
142:
Thread.Sleep
(1);
143:
DataTabledt2=newDataTable("
dt2"
144:
dt2=dt.Clone();
145:
SheetOptionssheet2thread=newSheetOptions(worksheet,iColCount,b2+2,b2*2+1,dt2);
146:
Threadothread2=newThread(newThreadStart(sheet2thread.SheetToDataTable));
147:
othread2.Start();
148:
DataTabledt3=newDataTable("
dt3"
149:
dt3=dt.Clone();
150:
SheetOptionssheet3thread=newSheetOptions(worksheet,iColCount,b2*2+2,b2*3+1,dt3);
151:
Threadothread3=newThread(newThreadStart(sheet3thread.SheetToDataTable));
152:
othread3.Start();
153:
DataTabledt4=newDataTable("
dt4"
154:
dt4=dt.Clone();
155:
SheetOptionssheet4thread=newSheetOptions(worksheet,iColCount,b2*3+2,b2*4+1,dt4);
156:
Threadothread4=newThread(newThreadStart(sheet4thread.SheetToDataTable));
157:
othread4.Start();
158:
//主线程读取剩余数据
159:
for(intiRow=b2*4+2;
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
othread1.Join();
1