5:
{
6:
stringstrSheetName=(string)dtSheetName.Rows[i]["TABLE_NAME"];
7:
if(strSheetName.Contains("$")&&!
strSheetName.Replace("'","").EndsWith("$"))
8:
{
9:
//过滤无效SheetName完毕....
10:
continue;
11:
}
12:
if(lstSheetNames!
=null&&!
lstSheetNames.Contains(strSheetName))
13:
lstSheetNames.Add(strSheetName);
14:
}
因为读取出来无效SheetName一般情况最后一个字符都不会是$。
如果SheetName有一些特殊符号,读取出来的SheetName会自动加上单引号。
比如在Excel中将SheetName编辑成MySheet
(1),此时读取出来的SheetName就为:
'MySheet
(1)$',所以判断最后一个字符是不是$之前最好过滤一下单引号。
---------------------------------
(2)Com组件的方式(通过添加Microsoft.Office.Interop.Excel引用实现)
优点:
能够非常灵活的读取Excel中的数据,用户可以灵活的调用各种函数进行处理。
缺点:
基于单元格的处理,读取速度较慢,对于数据量较大的文件最好不要使用此种方式读取。
需要添加相应的DLL引用,必须存在此引用才可使用,如果是Web站点部署在IIS上时,还需要服务器机子已安装了Excel,有时候还需要为配置IIS权限。
读取代码如下:
1:
privateStopwatchwath=newStopwatch();
2:
///
3:
///使用COM读取Excel
4:
///
5:
///路径
6:
///DataTabel
7:
publicSystem.Data.DataTableGetExcelData(stringexcelFilePath)
8:
{
9:
Excel.Applicationapp=newExcel.Application();
10:
Excel.Sheetssheets;
11:
Excel.Workbookworkbook=null;
12:
objectoMissiong=System.Reflection.Missing.Value;
13:
System.Data.DataTabledt=newSystem.Data.DataTable();
14:
wath.Start();
15:
try
16:
{
17:
if(app==null)
18:
{
19:
returnnull;
20:
}
21:
workbook=app.Workbooks.Open(excelFilePath,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,
22:
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
23:
//将数据读入到DataTable中——Start
24:
sheets=workbook.Worksheets;
25:
Excel.Worksheetworksheet=(Excel.Worksheet)sheets.get_Item
(1);//读取第一表
26:
if(worksheet==null)
27:
returnnull;
28:
stringcellContent;
29:
intiRowCount=worksheet.UsedRange.Rows.Count;
30:
intiColCount=worksheet.UsedRange.Columns.Count;
31:
Excel.Rangerange;
32:
//负责列头Start
33:
DataColumndc;
34:
intColumnID=1;
35:
range=(Excel.Range)worksheet.Cells[1,1];
36:
while(range.Text.ToString().Trim()!
="")
37:
{
38:
dc=newDataColumn();
39:
dc.DataType=System.Type.GetType("System.String");
40:
dc.ColumnName=range.Text.ToString().Trim();
41:
dt.Columns.Add(dc);
42:
43:
range=(Excel.Range)worksheet.Cells[1,++ColumnID];
44:
}
45:
//End
46:
for(intiRow=2;iRow<=iRowCount;iRow++)
47:
{
48:
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:
catch
63:
{
64:
returnnull;
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:
///DataTabel
84:
publicSystem.Data.DataTableThreadReadExcel(stringexcelFilePath)
85:
{
86:
Excel.Applicationapp=newExcel.Application();
87:
Excel.Sheetssheets=null;
88:
Excel.Workbookworkbook=null;
89:
objectoMissiong=System.Reflection.Missing.Value;
90:
System.Data.DataTabledt=newSystem.Data.DataTable();
91:
wath.Start();
92:
try
93:
{
94:
if(app==null)
95:
{
96:
returnnull;
97:
}
98:
workbook=app.Workbooks.Open(excelFilePath,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,
99:
oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);
100:
//将数据读入到DataTable中——Start
101:
sheets=workbook.Worksheets;
102:
Excel.Worksheetworksheet=(Excel.Worksheet)sheets.get_Item
(1);//读取第一表
103:
if(worksheet==null)
104:
returnnull;
105:
stringcellContent;
106:
intiRowCount=worksheet.UsedRange.Rows.Count;
107:
intiColCount=worksheet.UsedRange.Columns.Count;
108:
Excel.Rangerange;
109:
//负责列头Start
110:
DataColumndc;
111:
intColumnID=1;
112:
range=(Excel.Range)worksheet.Cells[1,1];
113:
while(iColCount>=ColumnID)
114:
{
115:
dc=newDataColumn();
116:
dc.DataType=System.Type.GetType("System.String");
117:
stringstrNewColumnName=range.Text.ToString().Trim();
118:
if(strNewColumnName.Length==0)strNewColumnName="_1";
119:
//判断列名是否重复
120:
for(inti=1;i121:
{
122:
if(dt.Columns[i-1].ColumnName==strNewColumnName)
123:
strNewColumnName=strNewColumnName+"_1";
124:
}
125:
dc.ColumnName=strNewColumnName;
:
dt.Columns.Add(dc);
127:
range=(Excel.Range)worksheet.Cells[1,++ColumnID];
128:
}
129:
//End
130:
//数据大于500条,使用多进程进行读取数据
131:
if(iRowCount-1>500)
132:
{
:
//开始多线程读取数据
134:
//新建线程
:
intb2=(iRowCount-1)/10;
136:
DataTabledt1=newDataTable("dt1");
137:
dt1=dt.Clone();
:
SheetOptionssheet1thread=newSheetOptions(worksheet,iColCount,2,b2+1,dt1);
:
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;iRow<=iRowCount;iRow++)
160:
{
161:
DataRowdr=dt.NewRow();
162:
for(intiCol=1;iCol<=iColCount;iCol++)
:
{
164:
range=(Excel.Range)worksheet.Cells[iRow,iCol];
165:
cellContent=(range.Value2==null)?
"":
range.Text.ToString();
166:
dr[iCol-1]=cellContent;
167:
}
168:
dt.Rows.Add(dr);
169:
}
170:
othread1.Join();
171:
othread2.Join();
172: