.NET中导入导出Excel总结
前一段时间，做了Excle的导入和导出，在此记录开发思路及技术要点，以便在今后开发中参考。 ——我不是高手，只是善于总结；我也不是大神，只是善于发现你们经常忽略的东西。
一、导入Excle：开发中我们会遇到这样的需求把excle中的数据导入到数据库。不符合要求的数据过滤出来显示到页面给出错误信息提示，符合要求的数据导入到数据库。
开发思路：把要导入的excle数据转化成DataTable，然后操作转化后的DataTable。
a.读取excle转化为DataTabel：
public DataTable ExcleToDataTable(string filePath,string name) { string xlsConnStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"", filePath);//连接字符串 string sql = string.Format("select * from [{0}$] where 1=1", name);//可是更改Sheet名称，比如sheet1，等等 OleDbConnection oleConn = new OleDbConnection(xlsConnStr); OleDbCommand oleComm = new OleDbCommand(sql, oleConn); OleDbDataAdapter oleDataAdapter = new OleDbDataAdapter(sql, oleConn); DataTable dt = null; try { oleConn.Open(); DataSet ds = new DataSet(); oleDataAdapter.Fill(ds); dt = ds.Tables[0]; } catch (Exception ex) { //数据绑定错误 自定义错误提示 } finally { oleConn.Close(); } return dt; }
b.excle数据转化成DataTabel后，就可以操纵DataTable.这样开发就容易多了。
二、导出Excle：开发中我们会遇到这样的需求把页面上Grid或者其他存放数据控件中数据用Excle导出。
项目中自己做的例子：
private void ExportExcel() { //需要添加 Microsoft.Office.Interop.Excel引用 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); if (app == null) { //提示:服务器上缺少Excel组件，需要安装Office软件 ApplicationRun.GetInfoForm().Add(new UserControl.Message(MessageType.Error, "$CS_Lack_of_Excel_components_on_the_server_you_need_to_install_Office_software")); return; } app.Visible = false; app.UserControl = true; Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks; Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(System.Windows.Forms.Application.StartupPath + "\\template\\FTensionTSMachQuery.xlsx"); //加载模板 Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets; Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作薄。 if (worksheet == null) { //提示:工作薄中没有工作表 ApplicationRun.GetInfoForm().Add(new UserControl.Message(MessageType.Error, "$CS_There_is_no_worksheet_in_work_sheet")); return; } string tCard = this.tCardEdit.Value.Trim(); FengHuoFacade _fhFacade = new FengHuoFacade(this.DataProvider); //获取数据 object[] objZLSim = _fhFacade.QuerySimulationByTcard(tCard, "ZL"); int r = 3;//定义行标（从第三行开始赋值） #region 固定行填充数据 if (objZLSim != null) { SimulationForQuery zlSim = objZLSim[0] as SimulationForQuery; object objShift = _fhFacade.GetShift(tCard); //模板固定行赋值 worksheet.Cells[r, 3] = tCard;//抽丝编号 worksheet.Cells[r, 7] = zlSim.CSLength;//抽丝长度 worksheet.Cells[r, 11] = zlSim.CSEquipmentNo;//抽丝机台号 worksheet.Cells[r + 1, 3] = FormatHelper.ToDateString(zlSim.Mdate);//生产时间 worksheet.Cells[r + 1, 7] = zlSim.Muser;//操作人员 worksheet.Cells[r + 3, 7] = zlSim.UVMark;//UV胶识 worksheet.Cells[r + 4, 3] = FormatHelper.ToDateString(zlSim.Mdate);//张力生产日期 worksheet.Cells[r + 4, 7] = objShift ==null ? "" : ((Shift)objShift).ShiftDescription;//班次 worksheet.Cells[r + 4, 11] = zlSim.ZLEquipmentNo;//设备代码 worksheet.Cells[r + 5, 3] = zlSim.Muser;//作业人员 } #endregion int row_ = 16; #region 设计表头 worksheet.Cells[row_ - 2, 1] = "ABS-2"; worksheet.Cells[row_ - 1, 1] = "ABS-5"; worksheet.Cells[row_ - 2, 2] = "盘状态"; Microsoft.Office.Interop.Excel.Range rgPState = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 2], worksheet.Cells[row_ - 1, 2]); rgPState.Merge(rgPState.MergeCells);//合并单元格 worksheet.Cells[row_ - 2, 3] = "张力光纤编号"; Microsoft.Office.Interop.Excel.Range rgTcrd = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 3], worksheet.Cells[row_ - 1, 4]); rgTcrd.Merge(rgTcrd.MergeCells);//合并单元格 worksheet.Cells[row_ - 2, 5] = "张力长度（米）"; Microsoft.Office.Interop.Excel.Range rgZLLength = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 5], worksheet.Cells[row_ - 1, 6]); rgZLLength.Merge(rgZLLength.MergeCells);//合并单元格 worksheet.Cells[row_ - 2, 7] = "光纤外观"; Microsoft.Office.Interop.Excel.Range rgLooks = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 7], worksheet.Cells[row_ - 1, 8]); rgLooks.Merge(rgLooks.MergeCells);//合并单元格 worksheet.Cells[row_ - 2, 9] = "光纤盘绕状态"; Microsoft.Office.Interop.Excel.Range rgCoilingState = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 9], worksheet.Cells[row_ - 1, 10]); rgCoilingState.Merge(rgCoilingState.MergeCells);//合并单元格 worksheet.Cells[row_ - 2, 11] = "备注"; Microsoft.Office.Interop.Excel.Range rgMark = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 11], worksheet.Cells[row_ - 1, 12]); rgMark.Merge(rgMark.MergeCells);//合并单元格 #endregion #region 填充数据 double zlLengthTotal = 0; if (objZLSim != null && objZLSim.Length > 0) { for (int i = 0; i < objZLSim.Length; i++) { worksheet.Cells[row_ + i, 1] = "良/不良"; Microsoft.Office.Interop.Excel.Range rgGoodOrBad1 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, 1], worksheet.Cells[row_ + i, 2]); rgGoodOrBad1.Merge(rgGoodOrBad1.MergeCells);//合并单元格 worksheet.Cells[row_ + i, 3] = ((SimulationForQuery)objZLSim[i]).Rcard;//张力编号 worksheet.Cells[row_ + i, 5] = ((SimulationForQuery)objZLSim[i]).ZLLength;//张力长度 zlLengthTotal += double.Parse(string.IsNullOrEmpty(((SimulationForQuery)objZLSim[i]).ZLLength) ? "0" : ((SimulationForQuery)objZLSim[i]).ZLLength); worksheet.Cells[row_ + i, 7] = "良/不良"; Microsoft.Office.Interop.Excel.Range rgGoodOrBad2 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, 7], worksheet.Cells[row_ + i, 8]); rgGoodOrBad2.Merge(rgGoodOrBad2.MergeCells);//合并单元格 worksheet.Cells[row_ + i, 9] = "良/不良"; Microsoft.Office.Interop.Excel.Range rgGoodOrBad3 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, 9], worksheet.Cells[row_ + i, 10]); rgGoodOrBad3.Merge(rgGoodOrBad3.MergeCells);//合并单元格 worksheet.Cells[row_ + i, 11] = "";//备注 Microsoft.Office.Interop.Excel.Range rgMarkValue = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, 11], worksheet.Cells[row_ + i, 12]); rgMarkValue.Merge(rgMarkValue.MergeCells);//合并单元格 } } #endregion int row = row_ + objZLSim.Length + 4;//空四行 #region 合计 worksheet.Cells[row, 1] = "张力良品总长度"; Microsoft.Office.Interop.Excel.Range rgZLLengthTotal = worksheet.Cells.get_Range(worksheet.Cells[row, 1], worksheet.Cells[row + 1, 4]); rgZLLengthTotal.Merge(rgZLLengthTotal.MergeCells);//合并单元格 worksheet.Cells[row, 5] = zlLengthTotal.ToString("0"); Microsoft.Office.Interop.Excel.Range rgZLLengthTotalValue = worksheet.Cells.get_Range(worksheet.Cells[row, 5], worksheet.Cells[row + 1, 6]); rgZLLengthTotalValue.Merge(rgZLLengthTotalValue.MergeCells);//合并单元格 worksheet.Cells[row, 7] = "张力良率（%）"; Microsoft.Office.Interop.Excel.Range rgZLYield = worksheet.Cells.get_Range(worksheet.Cells[row, 7], worksheet.Cells[row + 1, 9]); rgZLYield.Merge(rgZLYield.MergeCells);//合并单元格 try { worksheet.Cells[row, 10] = (zlLengthTotal / double.Parse(((SimulationForQuery)objZLSim[0]).CSLength)).ToString("0.000"); } catch (Exception) { worksheet.Cells[row, 10] = ""; } Microsoft.Office.Interop.Excel.Range rgZLYieldValue = worksheet.Cells.get_Range(worksheet.Cells[row, 10], worksheet.Cells[row + 1, 12]); rgZLYieldValue.Merge(rgZLYieldValue.MergeCells);//合并单元格 worksheet.Cells[row + 2, 1] = "张力断线次数合计"; Microsoft.Office.Interop.Excel.Range rgZLBreakTimesTotal = worksheet.Cells.get_Range(worksheet.Cells[row + 2, 1], worksheet.Cells[row + 3, 4]); rgZLBreakTimesTotal.Merge(rgZLBreakTimesTotal.MergeCells);//合并单元格 worksheet.Cells[row + 2, 5] = ((SimulationForQuery)objZLSim[0]).BreakTimes; Microsoft.Office.Interop.Excel.Range rgZLBreakTimesTotalValue = worksheet.Cells.get_Range(worksheet.Cells[row + 2, 5], worksheet.Cells[row + 3, 6]); rgZLBreakTimesTotalValue.Merge(rgZLBreakTimesTotalValue.MergeCells);//合并单元格 worksheet.Cells[row + 2, 7] = "PT生存长"; Microsoft.Office.Interop.Excel.Range rgPT = worksheet.Cells.get_Range(worksheet.Cells[row + 2, 7], worksheet.Cells[row + 3, 9]); rgPT.Merge(rgPT.MergeCells);//合并单元格 try { worksheet.Cells[row + 2, 10] = (zlLengthTotal / double.Parse(((SimulationForQuery)objZLSim[0]).PTTimes)).ToString("0.000"); } catch (Exception) { worksheet.Cells[row + 2, 10] = ""; } Microsoft.Office.Interop.Excel.Range rgPTValue = worksheet.Cells.get_Range(worksheet.Cells[row + 2, 10], worksheet.Cells[row + 3, 12]); rgPTValue.Merge(rgPTValue.MergeCells);//合并单元格 #endregion //调整Excel的样式。 Microsoft.Office.Interop.Excel.Range rgStytle = worksheet.Cells.get_Range(worksheet.Cells[row_ - 2, 1], worksheet.Cells[row + 3, 12]);//定义范围 rgStytle.Borders.LineStyle = 1;//添加边框 rgStytle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//文本水平居中对齐 rgStytle.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//文本垂直居中对齐 worksheet.Columns.AutoFit(); //自动调整列宽。 #region 预览Excle worksheet.DisplayAutomaticPageBreaks = true;//显示分页线 worksheet.PageSetup.CenterHorizontally = true;//水平居中 worksheet.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; worksheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;//A4纸张大小 worksheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;//纸张方向.纵向 app.Visible = true; worksheet.PrintPreview(null); app.Visible = false; #endregion #region 保存到指定位置 SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "导出Excel (*.xlsx)|*.xlsx|(*.xls)|*.xls"; saveFileDialog.FilterIndex = 0; saveFileDialog.Title = "导出Excel保存路径"; saveFileDialog.FileName = "FTensionTSMachQuery_" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx"; saveFileDialog.RestoreDirectory = true; if (saveFileDialog.ShowDialog() == DialogResult.OK) { string savePath = saveFileDialog.FileName; //Missing 在System.Reflection命名空间下。保存到指定位置 workbook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); workbook.Close(Missing.Value, Missing.Value, Missing.Value); app.Quit(); KillExcel(app); //提示:导出成功 ApplicationRun.GetInfoForm().Add(new UserControl.Message(MessageType.Success, "$CS_Export_Success")); return; } app.DisplayAlerts = false; workbook.Close(Missing.Value, Missing.Value, Missing.Value); app.Quit(); KillExcel(app); //直接打开excel表至打印预览 // PrintPriviewExcelFile(savePath); #endregion } //调用底层函数获取进程标示 ,杀掉excle进程释放内存 [DllImport("User32.dll")] public static extern int GetWindowThreadProcessId(IntPtr hWnd, out int ProcessId); private static void KillExcel(Microsoft.Office.Interop.Excel.Application theApp) { int id = 0; IntPtr intptr = new IntPtr(theApp.Hwnd); System.Diagnostics.Process p = null; try { GetWindowThreadProcessId(intptr, out id); p = System.Diagnostics.Process.GetProcessById(id); if (p != null) { p.Kill(); p.Dispose(); } } catch (Exception ex) { } }
其中关键就实例化 Microsoft.Office.Interop.Excel.Application，以及操作Microsoft.Office.Interop.Excel.Application
对excle属性的使用可以参考：http://www.cnblogs.com/herbert/archive/2010/06/30/1768271.html
还有就是释放EXCLE.exe进程方法：如果不释放EXCLE.exe进程，那么每次导出exlec内存中会出现一个EXCLE.exe进程，多次导出后很占用内存。
//调用底层函数获取进程标示 ,杀掉excle进程释放内存 [DllImport("User32.dll")] public static extern int GetWindowThreadProcessId(IntPtr hWnd, out int ProcessId); private static void KillExcel(Microsoft.Office.Interop.Excel.Application theApp) { int id = 0; IntPtr intptr = new IntPtr(theApp.Hwnd); System.Diagnostics.Process p = null; try { GetWindowThreadProcessId(intptr, out id); p = System.Diagnostics.Process.GetProcessById(id); if (p != null) { p.Kill(); p.Dispose(); } } catch (Exception ex) { } }
