转载

C#中对Excel进行操作

工作中要处理一批数据，主要是处理从别处导出来的Excel表格（大概有一千多行，三十多列），拿到表格对Excel表格进行分析，按照一定的规则进行拆分成为一万多行的数据；首先这个需求要用程序进行处理的背景是人工进行拆分已经耗费了一周人天的资源，所以要用C#对数据进行处理，来提高工作效率。开始的想法是把Excel中的数据导入到C#的一个容器中，对容器进行操作，但是拿到规则之后，发现规则太过于复杂，最后的方案变成把Excel数据导入到数据库中，然后，在数据库中进行数据处理，然后再把数据库中的数据导出到Excel表格中。

阅读目录

把Excel中的数据导入到数据库中
处理Excel中的数据
导出数据库中的数据到Excel中
总结

回到顶部

把Excel中的数据导入到数据库中

在Excel数据到数据库中时，首先要上传这个Excel表格，然后找到数据的sheet页，然后把需要的列数据导入到数据库中，其中要注意Excel版本兼容性的问题。其具体的代码如下：

 1 if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件  2             {  3                 Response.Write("<script>alert('请您选择Excel文件')</script> ");  4                 return;//当无文件时,返回  5             }  6             string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名  7             if (IsXls != ".xls" && IsXls != ".xlsx")  8             {  9                 Response.Write("<script>alert('只可以选择Excel文件')</script>"); 10                 return;//当选择的不是Excel文件时,返回 11             } 12             SqlConnection cn = new SqlConnection(strConn); 13             cn.Open(); 14             string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数 15             string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径 16             FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上 17             DataSet ds = ExecleDs(savePath, filename);           //调用自定义方法 18             DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组 19             int rowsnum = ds.Tables[0].Rows.Count; 20             if (rowsnum == 0) 21             { 22                 Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示 23             } 24             else 25             { 26                 for (int i = 3; i < dr.Length; i++) 27                 { 28                     //获取Excel中的内容并把他们导入到SQL Server数据库中 29                     string hhaspx_A = dr[i].ItemArray[0].ToString().Replace("'",""); 30                     string hhaspx_B = dr[i].ItemArray[1].ToString(); 31                     string hhaspx_C = dr[i].ItemArray[2].ToString(); 32                     string hhaspx_D = dr[i].ItemArray[3].ToString(); 33                     string hhaspx_E = dr[i].ItemArray[4].ToString().Replace("'", ""); 34                     string hhaspx_H = dr[i].ItemArray[7].ToString(); 35                     string hhaspx_I = dr[i].ItemArray[8].ToString(); 36                     string hhaspx_J = dr[i].ItemArray[9].ToString(); 37                     string hhaspx_N = dr[i].ItemArray[13].ToString().Replace("'", ""); 38                     string hhaspx_P = dr[i].ItemArray[15].ToString().Replace("'", ""); 39                     string hhaspx_S = dr[i].ItemArray[18].ToString(); 40                     string hhaspx_X = dr[i].ItemArray[23].ToString().Replace("'", ""); 41                     string hhaspx_AB = dr[i].ItemArray[27].ToString(); 42                     string insertstr = "INSERT INTO dbo.S_DataToExcel ( hhaspx_A , hhaspx_B , hhaspx_C , hhaspx_D , hhaspx_E , hhaspx_H , hhaspx_I , Hhaspx_J , hhaspx_N , hhaspx_P ,  hhaspx_S , hhaspx_X ,  hhaspx_AB)"; 43                         insertstr += "values ( '" + hhaspx_A + "' , cast('" + hhaspx_B + "' as int) ,cast( '" + hhaspx_C + "' as int) ,cast( '" + hhaspx_D + "' as int),'" + hhaspx_E + "', cast('" + hhaspx_H + "' as int) ,cast('" + hhaspx_I + "' as int),cast( '" + hhaspx_J + "' as int), '" + hhaspx_N + "', '" + hhaspx_P + "' ,  cast('" + hhaspx_S + "' as int), '" + hhaspx_X + "' ,  '" + hhaspx_AB + "')"; 44                         SqlCommand cmd = new SqlCommand(insertstr, cn); 45                         try 46                         { 47                             cmd.ExecuteNonQuery(); 48                         } 49                         catch (MembershipCreateUserException ex)       //捕捉异常 50                         { 51                             Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>"); 52                         } 53                     54                 } 55             }

从上传路径的Excel中获取Excel中的数据并装载到相应的容器之中：

 1 public DataSet ExecleDs(string filenameurl, string table)  2         {  3             //"Provider=Microsoft.ACE.OLEDB.12.0;"因为Excel存在版本的问题，我们这里选用了“Microsoft.ACE.OLEDB.12.0”用来兼容其他类型的Excel  4             string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";  5             OleDbConnection conn = new OleDbConnection(strConn);  6             conn.Open();  7             DataSet ds = new DataSet();  8             OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);  9             odda.Fill(ds, table); 10             return ds; 11         }

如果我们本地的系统中没有安装“Microsoft.ACE.OLEDB.12.0”相关的组件，建议在微软官网进行安装并下载；

回到顶部

处理Excel中的数据

调用数据库的存储过程，把数据按照规则进行转换，并输出转换的结果,存储过程处理数据的部分就不再赘述。

回到顶部

导出数据库中的数据到Excel中

把存储过程转换过后的数据进行获取并放到打他table中导出到Excel表格中。

1  string getDataSql = "EXEC sp_Tools_ExcelTrans"; 2  3                 SqlCommand cmd1 = new SqlCommand(getDataSql, cn); 4                 SqlDataAdapter sda = new SqlDataAdapter(cmd1); 5                 DataTable Dt = new DataTable(); 6                 sda.Fill(Dt); 7  8                 //然后把数据进行导出来 9                 DataChangeExcel.DataSetToExcel(Dt, Server.MapPath("~//upfiles//outputFormDataBase.xls"));

其中把Datatable数据导出到Excel中的方法:

 1 using System;  2 using System.Collections.Generic;  3 using System.Data;  4 using System.Web;  5 using Microsoft.Office.Interop;  6   7 /// <summary>  8 /// DataChangeExcel 的摘要说明  9 /// </summary> 10  11     public class DataChangeExcel 12     { 13         /// <summary> 14         /// 数据库转为excel表格 15         /// </summary> 16         /// <param name="dataTable">数据库数据</param> 17         /// <param name="SaveFile">导出的excel文件</param> 18         public static void DataSetToExcel(DataTable dataTable, string SaveFile) 19         { 20             Microsoft.Office.Interop.Excel.Application excel; 21             Microsoft.Office.Interop.Excel._Workbook workBook; 22             Microsoft.Office.Interop.Excel._Worksheet workSheet; 23             object misValue = System.Reflection.Missing.Value; 24             excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); 25             workBook = excel.Workbooks.Add(misValue); 26             workSheet = (Microsoft.Office.Interop.Excel._Worksheet)workBook.ActiveSheet; 27             int rowIndex = 1; 28             int colIndex = 0; 29             //取得标题 30             foreach (DataColumn col in dataTable.Columns) 31             { 32                 colIndex++; 33                 excel.Cells[1, colIndex] = col.ColumnName; 34             } 35             //取得表格中的数据 36             foreach (DataRow row in dataTable.Rows) 37             { 38                 rowIndex++; 39                 colIndex = 0; 40                 foreach (DataColumn col in dataTable.Columns) 41                 { 42                     colIndex++; 43                     excel.Cells[rowIndex, colIndex] = 44                          row[col.ColumnName].ToString().Trim(); 45                     //设置表格内容居中对齐 46                       excel.Cells[rowIndex, colIndex]).HorizontalAlignment = 47                       Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; 48                 } 49             } 50             excel.Visible = false; 51             workBook.SaveAs(SaveFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, 52                 misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, 53                 misValue, misValue, misValue, misValue, misValue); 54             dataTable = null; 55             workBook.Close(true, misValue, misValue); 56             excel.Quit(); 57             PublicMethod.Kill(excel);//调用kill当前excel进程 58             releaseObject(workSheet); 59             releaseObject(workBook); 60             releaseObject(excel); 61         } 62  63         private static void releaseObject(object obj) 64         { 65             try 66             { 67                 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); 68                 obj = null; 69             } 70             catch 71             { 72                 obj = null; 73             } 74             finally 75             { 76                 GC.Collect(); 77             } 78         } 79     }