ASP.Net操作Excel(下)_上传读取文件
继上篇讲过操作Excel导出数据，这节将介绍如何上传Excel文件并读取内容，并保存到本地数据库。
1.首先介绍一个上传工具:Brettle.Web.NeatUpload.dll。
可能有部分人用过，网上也有很多资料，这里就不做太多解释。
(a).NeatUpload类库下载:
(b).用法简介：
Web.config中在<httpMoudles></httpMoudles>中加入节点：
<add name="UploadHttpModule" type="Brettle.Web.NeatUpload.UploadHttpModule, Brettle.Web.NeatUpload"/>
页面上写入<%@ Register assembly="Brettle.Web.NeatUpload" namespace="Brettle.Web.NeatUpload" tagprefix="Upload" %>
调用方法：<Upload:InputFile ID="" runat="Server"></Upload:InputFile>
2.如何对Excel数据进行处理
注意点：对Excel中的数据必须按照模板进行填写。具体代码如下：
View Code
1 string fileName = this.upload_File.FileName;
2 if (null == fileName || ("").Equals(fileName))
3 {
4 Response.Write("<script type=\"text/javascript\">alert(\"没有上传文件！\")</script>");
5 Response.Redirect("Export_Demo.aspx");
6 Response.End();
7 }
8 //生成新的文件名，保证不重复
9 string uploadFileName = string.Format("{0}-{1}{2}{3}", "订单",DateTime.Now.ToString("yyyyMMdd"),GetRndNum(3),fileName.Substring((fileName.LastIndexOf('.'))));
10 //文件路径
11 string uploadFilePath = Server.MapPath("Temp_Down/"+uploadFileName);
12 //保存至服务器指定路径
13 this.upload_File.MoveTo(uploadFilePath, Brettle.Web.NeatUpload.MoveToOptions.Overwrite);
14
15 //应用程序类
16 ApplicationClass excelAC;
17 //Workbook类
18 WorkbookClass excelWbC;
19 Worksheet excelWs;
20 object missing = System.Reflection.Missing.Value;
21 excelAC = new ApplicationClass();
22 excelAC.Visible = false;
23 excelWbC = (WorkbookClass)excelAC.Workbooks.Open(Server.MapPath("Temp_Down/" + uploadFileName), missing,
24 missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
25 excelWs = (Worksheet)excelWbC.Worksheets[1];
26
27 //获取有效列数
28 int excel_Cols_Count = excelWs.UsedRange.Columns.Count;
29 //获取有效行数
30 int excel_Rows_Count = excelWs.UsedRange.Rows.Count;
31
32 //获取需要读取的Excel数据并转换为可变数组
33 //注意：上传的Excel必须按照规定模板填写，否则有效数据无法确定
34 Range excel_Range = excelWs.get_Range("A1", "C" + excel_Rows_Count.ToString());
35 Array excel_Range_Arr = (System.Array)excel_Range.Formula;
36
37 //此测试模板中规定只有三列
38 if (excel_Cols_Count >= 3)
39 {
40 System.Data.DataTable dt = new System.Data.DataTable();
41 dt.Columns.Add("Id", typeof(int));
42 dt.Columns.Add("name", typeof(string));
43 dt.Columns.Add("sex", typeof(int));
44 dt.Columns.Add("age", typeof(int));
45 for (int i = 2; i <= excel_Rows_Count; i++)
46 {
47 DataRow dr = dt.NewRow();
48 dr["name"] = excel_Range_Arr.GetValue(i, 1).ToString();
49 dr["sex"] = excel_Range_Arr.GetValue(i, 2);
50 dr["age"] = excel_Range_Arr.GetValue(i, 3);
51 dt.Rows.Add(dr);
52 }
53
54 //批量保存
55 SqlBulkCopy bulkCopy = new SqlBulkCopy(_conn);
56 bulkCopy.BatchSize = 1000;
57 bulkCopy.DestinationTableName = "UserInfo";
58 bulkCopy.WriteToServer(dt);
59
60 //释放资源
61 dt.Dispose();
62 bulkCopy.Close();
63 excelWbC.Close(XlSaveAction.xlDoNotSaveChanges, Server.MapPath("Temp_Down/" + uploadFileName), missing);
64 excelAC.Quit();
65 excelAC = null;
思路比较简单，不再做介绍，另外这里并没有使用上篇用到的类库，而是直接引用了Microsoft自带的类库。引用：Microsoft.Office.Interop.Excel.dll。
最后一定要记得释放资源，不然Excel进程会一直占用资源。
关于释放资源（补充代码）：
View Code
1 Exl_Wc.Close(XlSaveAction.xlDoNotSaveChanges, Server.MapPath("../upload/Import_Pro_Kuc/" + str_Pro_Kuc_File), Missing);
2 Exl_Ac.Quit();
3 //.Net 回收Com组件
4 if (Exl_Range != null)
5 System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl_Range);
6 System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl_Ws);
7 System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl_Wc);
8 System.Runtime.InteropServices.Marshal.ReleaseComObject(Exl_Ac);
9 Exl_Range = null;
10 Exl_Ws = null;
11 Exl_Wc = null;
12 Exl_Ac = null;
13 GC.Collect();
PS:补充一个方法，这个方法可以把Excel作为数据源，并返回一个Datable。参数为服务器上的文件完全路径。
此方法在64位操作系统的电脑上无法运行，由于64位操作系统不支持Microsoft OLE DB Provider for Jet驱动程序。
View Code
1 ///<summary>
2 /// 读取excel的方法。不管sheet名字叫什么。
3 ///</summary>
4 ///<param name="excelFilename"></param>
5 ///<returns></returns>
6 public static System.Data.DataTable GetExcelTable(string excelFilename)
7 {
8 string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=35;Extended Properties=Excel 8.0;Persist Security Info=False;HDR=NO;IMEX=1;", excelFilename);
9 DataSet ds = new DataSet();
10 string tableName;
11 using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(connectionString))
12 {
13 connection.Open();
14 System.Data.DataTable table = connection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
15 tableName = table.Rows[0]["Table_Name"].ToString();
16 string strExcel = "select * from " + "[" + tableName + "]";
17 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, connectionString);
18 adapter.Fill(ds, tableName);
19 connection.Close();
20 }
21 return ds.Tables[tableName];
22 }
---------------------------------------------------欢迎交流----------------------------------------------------------------------
