工作中要处理一批数据，主要是处理从别处导出来的Excel表格（大概有一千多行，三十多列），拿到表格对Excel表格进行分析，按照一定的规则进行拆分成为一万多行的数据；首先这个需求要用程序进行处理的背景是人工进行拆分已经耗费了一周人天的资源，所以要用C#对数据进行处理，来提高工作效率。开始的想法是把Excel中的数据导入到C#的一个容器中，对容器进行操作，但是拿到规则之后，发现规则太过于复杂，最后的方案变成把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 else25 {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 try46 {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 DataChangeExcel12 {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 try66 {67 System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);68 obj = null;69 }70 catch71 {72 obj = null;73 }74 finally75 {76 GC.Collect();77 }78 }79 }
其中”using Microsoft.Office.Interop;“要引入微软Excel的类库：”Microsoft Excel 15.0 Object Library“,并修改其引用库的属性”嵌入互操作类型“为”False“。
总结
有些时候对于Excel表格进行处理，Excel提供了编程在Excel内部对数据列进行处理，但是大部分人对于Excel中的编程不是很熟悉，导致操作起来有一些的困难，如果有一个程序猿（媛）的话，通过程序进行处理那就游刃有余了。
- 2楼最爱晴天
- 楼主可以看下npoi~~~
- Re: 进击的Jary
- @最爱晴天，引用楼主可以看下npoi~~~，NPOI很方便不用配一大堆东西就可以使用。
- 1楼大萝莉控
- EXCEL内置的数据处理功能已经足够应付日常90%的数据处理场合，只要不是太奇葩的，见过有人写一个统计小工具，其实用EXCEL内置的数据透视分分钟搞定。。。