前一段时间，做了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[];
}
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(); //第一个工作薄。
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 = ;//定义行标（从第三行开始赋值）
#region 固定行填充数据
if (objZLSim != null)
{
SimulationForQuery zlSim = objZLSim[] as SimulationForQuery;
object objShift = _fhFacade.GetShift(tCard);
//模板固定行赋值
worksheet.Cells[r, ] = tCard;//抽丝编号
worksheet.Cells[r, ] = zlSim.CSLength;//抽丝长度
worksheet.Cells[r, ] = zlSim.CSEquipmentNo;//抽丝机台号
worksheet.Cells[r + , ] = FormatHelper.ToDateString(zlSim.Mdate);//生产时间
worksheet.Cells[r + , ] = zlSim.Muser;//操作人员
worksheet.Cells[r + , ] = zlSim.UVMark;//UV胶识
worksheet.Cells[r + , ] = FormatHelper.ToDateString(zlSim.Mdate);//张力生产日期
worksheet.Cells[r + , ] = objShift ==null ? "" : ((Shift)objShift).ShiftDescription;//班次
worksheet.Cells[r + , ] = zlSim.ZLEquipmentNo;//设备代码
worksheet.Cells[r + , ] = zlSim.Muser;//作业人员 }
#endregion int row_ = ;
#region 设计表头
worksheet.Cells[row_ - , ] = "ABS-2";
worksheet.Cells[row_ - , ] = "ABS-5";
worksheet.Cells[row_ - , ] = "盘状态";
Microsoft.Office.Interop.Excel.Range rgPState = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgPState.Merge(rgPState.MergeCells);//合并单元格 worksheet.Cells[row_ - , ] = "张力光纤编号";
Microsoft.Office.Interop.Excel.Range rgTcrd = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgTcrd.Merge(rgTcrd.MergeCells);//合并单元格 worksheet.Cells[row_ - , ] = "张力长度（米）";
Microsoft.Office.Interop.Excel.Range rgZLLength = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgZLLength.Merge(rgZLLength.MergeCells);//合并单元格 worksheet.Cells[row_ - , ] = "光纤外观";
Microsoft.Office.Interop.Excel.Range rgLooks = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgLooks.Merge(rgLooks.MergeCells);//合并单元格 worksheet.Cells[row_ - , ] = "光纤盘绕状态";
Microsoft.Office.Interop.Excel.Range rgCoilingState = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgCoilingState.Merge(rgCoilingState.MergeCells);//合并单元格 worksheet.Cells[row_ - , ] = "备注";
Microsoft.Office.Interop.Excel.Range rgMark = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row_ - , ]);
rgMark.Merge(rgMark.MergeCells);//合并单元格
#endregion #region 填充数据
double zlLengthTotal = ;
if (objZLSim != null && objZLSim.Length > )
{
for (int i = ; i < objZLSim.Length; i++)
{
worksheet.Cells[row_ + i, ] = "良/不良";
Microsoft.Office.Interop.Excel.Range rgGoodOrBad1 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, ], worksheet.Cells[row_ + i, ]);
rgGoodOrBad1.Merge(rgGoodOrBad1.MergeCells);//合并单元格
worksheet.Cells[row_ + i, ] = ((SimulationForQuery)objZLSim[i]).Rcard;//张力编号
worksheet.Cells[row_ + i, ] = ((SimulationForQuery)objZLSim[i]).ZLLength;//张力长度
zlLengthTotal += double.Parse(string.IsNullOrEmpty(((SimulationForQuery)objZLSim[i]).ZLLength) ? "" : ((SimulationForQuery)objZLSim[i]).ZLLength); worksheet.Cells[row_ + i, ] = "良/不良";
Microsoft.Office.Interop.Excel.Range rgGoodOrBad2 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, ], worksheet.Cells[row_ + i, ]);
rgGoodOrBad2.Merge(rgGoodOrBad2.MergeCells);//合并单元格 worksheet.Cells[row_ + i, ] = "良/不良";
Microsoft.Office.Interop.Excel.Range rgGoodOrBad3 = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, ], worksheet.Cells[row_ + i, ]);
rgGoodOrBad3.Merge(rgGoodOrBad3.MergeCells);//合并单元格 worksheet.Cells[row_ + i, ] = "";//备注
Microsoft.Office.Interop.Excel.Range rgMarkValue = worksheet.Cells.get_Range(worksheet.Cells[row_ + i, ], worksheet.Cells[row_ + i, ]);
rgMarkValue.Merge(rgMarkValue.MergeCells);//合并单元格 }
}
#endregion int row = row_ + objZLSim.Length + ;//空四行
#region 合计
worksheet.Cells[row, ] = "张力良品总长度";
Microsoft.Office.Interop.Excel.Range rgZLLengthTotal = worksheet.Cells.get_Range(worksheet.Cells[row, ], worksheet.Cells[row + , ]);
rgZLLengthTotal.Merge(rgZLLengthTotal.MergeCells);//合并单元格 worksheet.Cells[row, ] = zlLengthTotal.ToString("");
Microsoft.Office.Interop.Excel.Range rgZLLengthTotalValue = worksheet.Cells.get_Range(worksheet.Cells[row, ], worksheet.Cells[row + , ]);
rgZLLengthTotalValue.Merge(rgZLLengthTotalValue.MergeCells);//合并单元格 worksheet.Cells[row, ] = "张力良率（%）";
Microsoft.Office.Interop.Excel.Range rgZLYield = worksheet.Cells.get_Range(worksheet.Cells[row, ], worksheet.Cells[row + , ]);
rgZLYield.Merge(rgZLYield.MergeCells);//合并单元格 try
{
worksheet.Cells[row, ] = (zlLengthTotal / double.Parse(((SimulationForQuery)objZLSim[]).CSLength)).ToString("0.000");
}
catch (Exception)
{ worksheet.Cells[row, ] = "";
}
Microsoft.Office.Interop.Excel.Range rgZLYieldValue = worksheet.Cells.get_Range(worksheet.Cells[row, ], worksheet.Cells[row + , ]);
rgZLYieldValue.Merge(rgZLYieldValue.MergeCells);//合并单元格 worksheet.Cells[row + , ] = "张力断线次数合计";
Microsoft.Office.Interop.Excel.Range rgZLBreakTimesTotal = worksheet.Cells.get_Range(worksheet.Cells[row + , ], worksheet.Cells[row + , ]);
rgZLBreakTimesTotal.Merge(rgZLBreakTimesTotal.MergeCells);//合并单元格 worksheet.Cells[row + , ] = ((SimulationForQuery)objZLSim[]).BreakTimes;
Microsoft.Office.Interop.Excel.Range rgZLBreakTimesTotalValue = worksheet.Cells.get_Range(worksheet.Cells[row + , ], worksheet.Cells[row + , ]);
rgZLBreakTimesTotalValue.Merge(rgZLBreakTimesTotalValue.MergeCells);//合并单元格 worksheet.Cells[row + , ] = "PT生存长";
Microsoft.Office.Interop.Excel.Range rgPT = worksheet.Cells.get_Range(worksheet.Cells[row + , ], worksheet.Cells[row + , ]);
rgPT.Merge(rgPT.MergeCells);//合并单元格 try
{
worksheet.Cells[row + , ] = (zlLengthTotal / double.Parse(((SimulationForQuery)objZLSim[]).PTTimes)).ToString("0.000");
}
catch (Exception)
{ worksheet.Cells[row + , ] = "";
} Microsoft.Office.Interop.Excel.Range rgPTValue = worksheet.Cells.get_Range(worksheet.Cells[row + , ], worksheet.Cells[row + , ]);
rgPTValue.Merge(rgPTValue.MergeCells);//合并单元格 #endregion //调整Excel的样式。
Microsoft.Office.Interop.Excel.Range rgStytle = worksheet.Cells.get_Range(worksheet.Cells[row_ - , ], worksheet.Cells[row + , ]);//定义范围
rgStytle.Borders.LineStyle = ;//添加边框
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 = ;
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 = ;
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 = ;
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)
{ }
}