先说说题外话，前段时间近一个月，我一直在做单据导入功能，其中就涉及到Excel操作，接触Excel后发现他的api说明并不多，好在网上有很多朋友贴出了一些代码，我在不断的挫折中吸取了很多教训，现共享出来，给大家参考。
1. 最好在客户端使用，不要在B/S服务端使用，因为会受到IIS权限和占用内存影响，多人并发操作必然完蛋
2. 需要引入两个DLL,Microsoft.Office.Interop.Excel.dll和office.dll,在加上项目的时候，会报错“类型“Microsoft.Office.Interop.Excel.ApplicationClass” 未定义构造函数无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。”，这时只需要将将引用的DLL：Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了
3. 注意Excel中sheetindex, rowindex,columnindex都是从1开始的
4. 理清Excel里面的对象（Application、Workbook、Worksheet、Range），其中Range包含行和列还有单元格，很多方法都是弱类型object，需要拆箱和装箱操作，循环读取效率并不高，所以我在读取大批量数据Excel时，采用的方式是将Excel调用另存为csv文件，再通过文本操作字符串的方式解析csv文件，读取每一行，实践证明，这样的效率比较高。解析csv文件在附件可以供大家下载
5. 客户端必须正确安装office2003或office2007，如果是安装的wps系列的，需卸载后再重新安装office
这里我贴出我封装Excel的操作类，希望能对大家有所帮助，欢迎大家指正：
ExcelHelper。cs
/// <summary> /// author by :ljun /// Excel工具类，目前仅支持一个工作薄的操作 /// </summary> public class ExcelHelper : IDisposable { #region 构造函数 /// <summary> /// 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径 /// </summary> /// <param name="templetFilePath">Excel模板文件路径</param> /// <param name="outputFilePath">输出Excel文件路径</param> public ExcelHelper(string templetFilePath, string outputFilePath) { if (templetFilePath == null) throw new Exception("Excel模板文件路径不能为空！"); if (outputFilePath == null) throw new Exception("输出Excel文件路径不能为空！"); if (!File.Exists(templetFilePath)) throw new Exception("指定路径的Excel模板文件不存在！"); this.templetFile = templetFilePath; this.outputFile = outputFilePath; excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; excelApp.DisplayAlerts = false; //是否需要显示提示 excelApp.AlertBeforeOverwriting = false; //是否弹出提示覆盖 //打开模板文件，得到WorkBook对象 workBook = excelApp.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, Type.Missing, Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } /// <summary> /// 构造函数，新建一个工作簿 /// </summary> public ExcelHelper() { excelApp = new Excel.ApplicationClass(); excelApp.Visible = false; //设置禁止弹出保存和覆盖的询问提示框 excelApp.DisplayAlerts = false; excelApp.AlertBeforeOverwriting = false; //新建一个WorkBook workBook = excelApp.Workbooks.Add(Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion #region 私有变量 private string templetFile = null; private string outputFile = null; private object missing = System.Reflection.Missing.Value; private Excel.Application excelApp; private Excel.Workbook workBook; private Excel.Worksheet workSheet; private Excel.Range range; private Excel.Range range1; private Excel.Range range2; #endregion #region 公共属性 /// <summary> /// WorkSheet数量 /// </summary> public int WorkSheetCount { get { return workBook.Sheets.Count; } } /// <summary> /// Excel模板文件路径 /// </summary> public string TempletFilePath { set { this.templetFile = value; } } /// <summary> /// 输出Excel文件路径 /// </summary> public string OutputFilePath { set { this.outputFile = value; } } #endregion #region 批量写入Excel内容 /// <summary> /// 将二维数组数据写入Excel文件 /// </summary> /// <param name="arr">二维数组</param> /// <param name="top">行索引</param> /// <param name="left">列索引</param> public void ArrayToExcel(object[,] arr, int top, int left) { int rowCount = arr.GetLength(0); //二维数组行数（一维长度） int colCount = arr.GetLength(1); //二维数据列数（二维长度） range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.FormulaArray = arr; } #endregion #region 行操作 /// <summary> /// 插行（在指定行上面插入指定数量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void InsertRows(int rowIndex, int count) { try { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 复制行（在指定行下面复制指定数量行） /// </summary> /// <param name="rowIndex"></param> /// <param name="count"></param> public void CopyRows(int rowIndex, int count) { try { range1 = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; for (int i = 1; i <= count; i++) { range2 = (Excel.Range)workSheet.Rows[rowIndex + i, this.missing]; range1.Copy(range2); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 删除行 /// </summary> /// <param name="sheetIndex"></param> /// <param name="rowIndex"></param> /// <param name="count"></param> public void DeleteRows(int rowIndex, int count) { try { for (int i = 0; i < count; i++) { range = (Excel.Range)workSheet.Rows[rowIndex, this.missing]; range.Delete(Excel.XlDirection.xlDown); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 列操作 /// <summary> /// 插列（在指定列右边插入指定数量列） /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void InsertColumns(int columnIndex, int count) { try { range = (Excel.Range)(workSheet.Columns[columnIndex, this.missing]); //注意：这里和VS的智能提示不一样，第一个参数是columnindex for (int i = 0; i < count; i++) { range.Insert(Excel.XlDirection.xlDown, missing); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } /// <summary> /// 删除列 /// </summary> /// <param name="columnIndex"></param> /// <param name="count"></param> public void DeleteColumns(int columnIndex, int count) { try { for (int i = columnIndex + count-1; i >= columnIndex; i--) { ((Excel.Range)workSheet.Cells[1, i]).EntireColumn.Delete(0); } } catch (Exception e) { this.KillExcelProcess(false); throw e; } } #endregion #region 单元格操作 /// <summary> /// 合并单元格，并赋值，对指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="beginRowIndex">开始行索引</param> /// <param name="beginColumnIndex">开始列索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除，合并才不会出错 range.MergeCells = true; range.Value2 = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } /// <summary> /// 向单元格写入数据，对当前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要写入的文本值</param> public void SetCells(int rowIndex, int columnIndex, string text) { try { workSheet.Cells[rowIndex, columnIndex] = text; } catch { this.KillExcelProcess(false); throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！"); } } /// <summary> /// 向单元格写入数据，对当前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要写入的文本值</param> public void SetCells(int rowIndex, int columnIndex, string text, string comment) { try { workSheet.Cells[rowIndex, columnIndex] = text; SetCellComment(rowIndex, columnIndex, comment); } catch { this.KillExcelProcess(false); throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！"); } } /// <summary> /// 向单元格写入数据，对当前WorkSheet操作 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="text">要写入的文本值</param> public void SetCellComment(int rowIndex, int columnIndex, string comment) { try { Excel.Range range = workSheet.Cells[rowIndex, columnIndex] as Excel.Range; range.AddComment(comment); } catch { this.KillExcelProcess(false); throw new Exception("向单元格[" + rowIndex + "," + columnIndex + "]写数据出错！"); } } /// <summary> /// 单元格背景色及填充方式 /// </summary> /// <param name="startRow">起始行</param> /// <param name="startColumn">起始列</param> /// <param name="endRow">结束行</param> /// <param name="endColumn">结束列</param> /// <param name="color">颜色索引</param> public void SetCellsBackColor(int startRow, int startColumn, int endRow, int endColumn, ColorIndex color) { Excel.Range range = excelApp.get_Range(excelApp.Cells[startRow, startColumn], excelApp.Cells[endRow, endColumn]); range.Interior.ColorIndex = color; } #endregion #region 保存文件 /// <summary> /// 另存文件 /// </summary> public void SaveAsFile() { if (this.outputFile == null) throw new Exception("没有指定输出文件路径！"); try { workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); } catch (Exception e) { throw e; } finally { this.Quit(); } } /// <summary> /// 将Excel文件另存为指定格式 /// </summary> /// <param name="format">HTML，CSV，TEXT，EXCEL，XML</param> public void SaveAsFile(SaveAsFileFormat format) { if (this.outputFile == null) throw new Exception("没有指定输出文件路径！"); try { switch (format) { case SaveAsFileFormat.HTML: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.CSV: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.TEXT: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlUnicodeText, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } case SaveAsFileFormat.XML: { workBook.SaveAs(outputFile, Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); break; } default: { workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing); break; } } } catch (Exception e) { throw e; } finally { this.Quit(); } } #endregion #region 杀进程释放资源 /// <summary> /// 结束Excel进程 /// </summary> public void KillExcelProcess(bool bAll) { if (bAll) { KillAllExcelProcess(); } else { KillSpecialExcel(); } } [DllImport("user32.dll", SetLastError = true)] static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId); /// <summary> /// 杀特殊进程的Excel /// </summary> public void KillSpecialExcel() { try { if (excelApp != null) { int lpdwProcessId; GetWindowThreadProcessId((IntPtr)excelApp.Hwnd, out lpdwProcessId); if (lpdwProcessId > 0) //c-s方式 { System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill(); } else { Quit(); } } } catch { } } /// <summary> /// 释放资源 /// </summary> public void Quit() { if (workBook != null) workBook.Close(null, null, null); if (excelApp != null) { excelApp.Workbooks.Close(); excelApp.Quit(); } if (range != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range); range = null; } if (range1 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range1); range1 = null; } if (range2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(range2); range2 = null; } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); workSheet = null; } if (workBook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook); workBook = null; } if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; } GC.Collect(); } /// <summary> /// 接口方法 释放资源 /// </summary> public void Dispose() { Quit(); } #endregion #region 静态方法 /// <summary> /// 杀Excel进程 /// </summary> public static void KillAllExcelProcess() { Process[] myProcesses; myProcesses = Process.GetProcessesByName("Excel"); //得不到Excel进程ID，暂时只能判断进程启动时间 foreach (Process myProcess in myProcesses) { myProcess.Kill(); } } /// <summary> /// 打开相应的excel /// </summary> /// <param name="filepath"></param> public static void OpenExcel(string filepath) { Excel.Application xlsApp = new Excel.Application(); xlsApp.Workbooks.Open(filepath); xlsApp.Visible = true; } #endregion } /// <summary> /// 常用颜色定义,对就Excel中颜色名 /// </summary> public enum ColorIndex { 无色 = -4142, 自动 = -4105, 黑色 = 1, 褐色 = 53, 橄榄 = 52, 深绿 = 51, 深青 = 49, 深蓝 = 11, 靛蓝 = 55, 灰色80 = 56, 深红 = 9, 橙色 = 46, 深黄 = 12, 绿色 = 10, 青色 = 14, 蓝色 = 5, 蓝灰 = 47, 灰色50 = 16, 红色 = 3, 浅橙色 = 45, 酸橙色 = 43, 海绿 = 50, 水绿色 = 42, 浅蓝 = 41, 紫罗兰 = 13, 灰色40 = 48, 粉红 = 7, 金色 = 44, 黄色 = 6, 鲜绿 = 4, 青绿 = 8, 天蓝 = 33, 梅红 = 54, 灰色25 = 15, 玫瑰红 = 38, 茶色 = 40, 浅黄 = 36, 浅绿 = 35, 浅青绿 = 34, 淡蓝 = 37, 淡紫 = 39, 白色 = 2 } /// <summary> /// HTML，CSV，TEXT，EXCEL，XML /// </summary> public enum SaveAsFileFormat { HTML, CSV, TEXT, EXCEL, XML }
杂种类。什么都有
using System; //using System.Data; using System.Configuration; using System.Web; //using Microsoft.Office.Interop; //using Excel = Microsoft.Office.Interop.Excel; //using Excel = Microsoft.Office.Interop.Excel; using Excel = Microsoft.Office.Interop.Excel; using System.Data; using System.Windows.Forms; using System.Data.OleDb; using System.Reflection; // 引用这个才能使用Missing字段 using System.Collections.Specialized; using System.Runtime.InteropServices; namespace ExcelEditClass { /// <SUMMARY> /// ExcelEdit 的摘要说明 /// </SUMMARY> public class ExcelEdit //public class ExcelEdit:Excel.ApplicationClass //liuxfu { //to kill excel [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); public string mFilename; public Excel.Application app; public Excel.Workbooks wbs; public Excel.Workbook wb; public Excel.Worksheets wss; public Excel.Worksheet ws; /// <summary> /// 构造函数，不创建Excel工作薄 /// </summary> public ExcelEdit() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 创建Excel工作薄 /// </summary> public void Create()//创建一个Excel对象 { try { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(true); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// <summary> /// 显示Excel /// </summary> public void ShowExcel() { app.Visible = true; } /// <summary> /// 打开一个存在的Excel文件 /// </summary> /// <param name="FileName">Excel完整路径加文件名</param> public void Open(string FileName)//打开一个Excel文件 { try { app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing); //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; app.UserControl = true;//如果只想用程序控制该excel而不想让用户操作时候，可以设置为false } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } public Excel.Worksheet GetSheet(string SheetName) //获取一个工作表 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName]; return s; } public Excel.Worksheet AddSheet(string SheetName) //添加一个工作表 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); try { s.Name = SheetName; return s; } catch { return null; } } public void DelSheet(string SheetName)//删除一个工作表 { try { ((Excel.Worksheet)wb.Worksheets[SheetName]).Delete(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一 { Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[OldSheetName]; try { s.Name = NewSheetName; return s; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); return null; } } public Excel.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二 { try { Sheet.Name = NewSheetName; return Sheet; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); return null; } } //_____________________________ole自定义函数___________________________________________________________________________ //______________________________excel和datagridview之间互操作函数_______________________________________________________ ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //把EXCEl中的某工作表显示到datagridview中 /// <summary> /// ExcelEdit myExcel = new ExcelEdit(); /// myExcel.Open("d:\\数据库表格20071217.xls"); /// myExcel.Excel2DBView("908", this.dataGridView1); /// myExcel.Close(); /// </summary> /// <param name="tablename"></param> /// <param name="dataGridView1"></param> public void Excel2DBView(string xlsFilaName, string tablename, DataGridView dataGridView1) { try { string sExcelFile = xlsFilaName; //string strExcelFileName = @""+ myPath +""; //string strString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties = 'Excel 8.0;HDR=NO;IMEX=1 '"; //string sConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + sExcelFile + ";Extended Properties=Excel 8.0;"; string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\""; OleDbConnection connection = new OleDbConnection(sConnectionString); string sql_select_commands = "Select * from [" + tablename + "$]"; OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection); DataSet ds = new DataSet(); adp.Fill(ds, tablename); dataGridView1.Rows.Clear(); dataGridView1.Columns.Clear(); //写入dataGridView控件标题 for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++) { dataGridView1.Columns.Add(ds.Tables[tablename].Rows[0][j].ToString(), ds.Tables[tablename].Rows[0][j].ToString()); } for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++) { dataGridView1.Rows.Add(); } //写入dataGridView控件行数据 for (int i = 1; i < ds.Tables[tablename].Rows.Count; i++) for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++) { dataGridView1.Rows[i - 1].Cells[j].Value = Convert.ToString(ds.Tables[tablename].Rows[i][j]); } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } /* for (int i = 0; i < ds.Tables["Book1"].Rows.Count; i++) { sum1 += Convert.ToInt32(ds.Tables["Book1"].Rows[i]["字段A"]); } for (int j = 0; j < ds.Tables["Book1"].Rows.Count; j++) { sum2 += Convert.ToInt32(ds.Tables["Book1"].Rows[j]["字段B"]); } MessageBox.Show(sum1.ToString() + " and " + sum2.ToString()); * */ /* *备注： * 用OLEDB进行Excel文件数据的读取，并返回DataSet数据集。其中有几点需要注意的： 1.连接字符串中参数IMEX 的值： 0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities) IMEX有3个值：当IMEX=2 时，EXCEL文档中同时含有字符型和数字型时，比如第C列有3个值，2个为数值型 123，1个为字符型 ABC，当导入时， 页面不报错了，但库里只显示数值型的123，而字符型的ABC则呈现为空值。当IMEX=1时，无上述情况发生，库里可正确呈现 123 和 ABC. 2.参数HDR的值： HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES 3.参数Excel 8.0 对于Excel 97以上版本都用Excel 8.0 @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" "HDR=Yes;" indicates that the first row contains columnnames, not data "IMEX=1;" tells the driver to always read "intermixed" data columns as text TIP! SQL syntax: "SELECT * FROM [sheet1$]" - i.e. worksheet name followed by a "$" and wrapped in "[" "]" brackets. 如果第一行是数据而不是标题的话, 应该写: "HDR=No;" "IMEX=1;" tells the driver to always read "intermixed" data columns as text * */ /* 你可以先用代码打开xls文件: Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Open("d:\text2.xls") for i=0 to xlBook.Worksheets.Count-1 set xlSheet = xlBook.Worksheets(i) xlSheet.Name //这就是你需要的每个sheet的名字,保存起来,备后用 next i 这里使用的VB写的范例,变成c#即可. */ } ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //把EXCEl中的某工作表中字段值为FieldNameStr的行显示到datagridview中 /// <summary> /// </summary> /// <param name="tablename"></param> /// <param name="FieldValueStr"></param> /// <param name="dataGridView1"></param> public void Excel2DBView_SelectFieldValue(string tablename, string FieldName, string FieldNameStr, DataGridView dataGridView1) { string sExcelFile = mFilename; try { string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; OleDbConnection connection = new OleDbConnection(sConnectionString); string sql_select_commands = "select * from [" + tablename + "$] where " + FieldName + "=" + FieldNameStr; //MessageBox.Show(sql_select_commands); OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection); DataSet ds = new DataSet(); adp.Fill(ds, tablename); //设置DataGridView控件的字段标题 DataSet ds2 = new DataSet(); ds2 = GetNewDataSet(tablename); for (int j = 0; j < ds2.Tables[tablename].Columns.Count; j++) { dataGridView1.Columns.Add(ds2.Tables[tablename].Rows[0][j].ToString(), ds2.Tables[tablename].Rows[0][j].ToString()); } for (int i = 0; i < ds.Tables[tablename].Rows.Count; i++) { dataGridView1.Rows.Add(); } for (int i = 0; i < ds.Tables[tablename].Rows.Count; i++) for (int j = 0; j < ds.Tables[tablename].Columns.Count; j++) { dataGridView1.Rows[i].Cells[j].Value = Convert.ToString(ds.Tables[tablename].Rows[i][j]); } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } //返回excel中不把第一行当做标题看待的数据集 public DataSet GetNewDataSet(string tablename) { string sExcelFile = mFilename; try { string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\""; OleDbConnection connection = new OleDbConnection(sConnectionString); string sql_select_commands = "Select * from [" + tablename + "$]"; OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection); DataSet ds = new DataSet(); adp.Fill(ds, tablename); return ds; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); return null; } } ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// //根据字段名，删除EXCEl中的某工作表中的记录(SQL语句未成功执行) /// <summary> /// </summary> /// <param name="tablename"></param> /// <param name="FieldValueStr"></param> /// <param name="dataGridView1"></param> public void DeleteExcelFieldValue(string tablename, string FieldName, string FieldNameStr) { string sExcelFile = mFilename; try { string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sExcelFile + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; OleDbConnection connection = new OleDbConnection(sConnectionString); string sql_select_commands = "delete from [" + tablename + "$] where " + FieldName + "=" + FieldNameStr; //string sql_select_commands = "delete from [" + tablename + "$]"; //string sql_select_commands = "drop table [" + tablename + "$]"; MessageBox.Show(sql_select_commands); OleDbDataAdapter adp = new OleDbDataAdapter(sql_select_commands, connection); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// 在工作表中插入行，并调整其他行以留出空间 ///当前工作表 /// 欲插入的行索引 public void InsertRows(Excel.Worksheet sheet, int rowIndex) { Excel.Range range = (Excel.Range)sheet.Rows[rowIndex, Type.Missing]; //object Range.Insert(object shift, object copyorigin); //shift: Variant类型，可选。指定单元格的调整方式。可以为下列 XlInsertShiftDirection 常量之一： //xlShiftToRight 或 xlShiftDown。如果省略该参数，Microsoft Excel 将根据区域形状确定调整方式。 range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Type.Missing); range = null; } /// 在工作表中删除行 ///当前工作表 ///欲删除的行索引 public void DeleteRows(Excel.Worksheet sheet, int rowIndex) { try { Excel.Range range = (Excel.Range)sheet.Rows[rowIndex, Type.Missing]; range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp); range = null; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// 在工作表中删除所有行名为rowText的行 ///当前工作表 ///欲删除的行索引 public void DeleteRows(string sheetName, string FieldName, string rowText) { Excel.Worksheet worksheet = GetSheet(sheetName); try { if (worksheet == null) { throw new Exception("Worksheet error "); return; } //获取工作表行列数 int rowCounts = rowcount(sheetName); int colCounts = colcount(sheetName); // MessageBox.Show(rowCounts.ToString() +"|" +colCounts.ToString()); int colIndex = 0; //定位字段索引 for (int i = 1; i <= colCounts; i++) { if (GetCellStr(sheetName, 1, i) == FieldName) { colIndex = i; break; } } if (colIndex == 0) { MessageBox.Show("找不到字段名：" + FieldName); return; } //int jj = 0; //int jjj = 0; for (int i = 1; i <= rowCounts; i++) { //jj++; if (GetCellStr(sheetName, i, colIndex) == rowText) { DeleteRows(worksheet, i); i--; rowCounts = rowCounts - 1; //jjj++; } } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// 根据字段名，追加记录 public void AddRecords(string sheetName, string FieldName, string recordText) { Excel.Worksheet worksheet = GetSheet(sheetName); try { if (worksheet == null) { throw new Exception("Worksheet error "); return; } //获取工作表行列数 int rowCounts = rowcount(sheetName); int colCounts = colcount(sheetName); // MessageBox.Show(rowCounts.ToString() +"|" +colCounts.ToString()); int colIndex = 0; //定位字段索引 for (int i = 1; i <= colCounts; i++) { if (GetCellStr(sheetName, 1, i) == FieldName) { colIndex = i; break; } } if (colIndex == 0) { MessageBox.Show("找不到字段名：" + FieldName); return; } SetCellStr(sheetName, rowCounts + 1, colIndex, recordText); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } //获取字段中的字段名称集 public StringCollection GetFieldValues(string sheetName, string FieldName) { Excel.Worksheet worksheet = GetSheet(sheetName); if (worksheet == null) { throw new Exception("Worksheet error "); return null; } //获取工作表行列数 int rowCounts = rowcount(sheetName); int colCounts = colcount(sheetName); // MessageBox.Show(rowCounts.ToString() +"|" +colCounts.ToString()); int colIndex = 0; //定位字段索引 for (int i = 1; i <= colCounts; i++) { if (GetCellStr(sheetName, 1, i) == FieldName) { colIndex = i; break; } } if (colIndex == 0) { MessageBox.Show("找不到字段名：" + FieldName); return null; } StringCollection fieldStrs = new StringCollection(); string cellStr; for (int i = 2; i <= rowCounts; i++) { cellStr = GetCellStr(sheetName, i, colIndex); if (!fieldStrs.Contains(cellStr)) { fieldStrs.Add(cellStr); } } return fieldStrs; } /// DataGridView导出到Excel /// ExcelEdit myExcel = new ExcelEdit(); /// myExcel.Create(); /// myExcel.DGView2Excel(this.dataGridView1, "d:\\xiao.xls", "xiaobiao"); /// myExcel.Save(); /// myExcel.Close(); public void DGView2Excel(DataGridView dgv, string sheetName) { try { int rowCount = dgv.RowCount; int columnCount = dgv.ColumnCount; //Excel.Workbooks workbook=(Excel.Workbook)app //Open(xlsFileName); Excel.Worksheet worksheet = GetSheet(sheetName); int sheet_rowCounts = rowcount(sheetName); int sheet_colCounts = colcount(sheetName); //int rowIndex = 0; if (worksheet == null) { throw new Exception("Worksheet error "); } // Excel.Range r = worksheet.get_Range("A1 ", Missing.Value); if (r == null) { MessageBox.Show("Range无法启动 "); throw new Exception("Range error "); } //以上是一些例行的初始化工作,下面进行具体的信息填充 //填充标题 int ColIndex = 1; foreach (DataGridViewColumn dHeader in dgv.Columns) { worksheet.Cells[1, ColIndex++] = dHeader.HeaderText; //worksheet.Cells[21, ColIndex++] = dHeader.HeaderText; } ColIndex = 1; //获取DataGridView中的所有行和列的数值,填充到一个二维数组中. object[,] myData = new object[rowCount + 1, columnCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { myData[i, j] = dgv[j, i].Value; //这里的获取注意行列次序 } } //将填充好的二维数组填充到Excel对象中. r = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 1, columnCount]); r.Value2 = myData; r = null; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// DataGridView追加到Excel指定表格中 /// ExcelEdit myExcel = new ExcelEdit(); /// myExcel.Create(); /// myExcel.DGViewAdd2Excel(this.dataGridView1, "d:\\xiao.xls", "xiaobiao"); /// myExcel.Save(); /// myExcel.Close(); public void DGViewAdd2Excel(DataGridView dgv, string sheetName) { try { //DataGridView控件行列数 int rowCount = dgv.RowCount; int columnCount = dgv.ColumnCount; //工作表行列数 int sheet_row_count = rowcount(sheetName); int sheet_column_count = colcount(sheetName); // MessageBox.Show(sheet_row_count.ToString() + " | "+sheet_column_count.ToString()); //Excel.Workbooks workbook=(Excel.Workbook)app //Open(xlsFileName); Excel.Worksheet worksheet = GetSheet(sheetName); if (worksheet == null) { throw new Exception("Worksheet error "); } // Excel.Range r = worksheet.get_Range("A1 ", Missing.Value); if (r == null) { MessageBox.Show("Range无法启动 "); throw new Exception("Range error "); } //以上是一些例行的初始化工作,下面进行具体的信息填充 //填充标题 int ColIndex = 1; foreach (DataGridViewColumn dHeader in dgv.Columns) { worksheet.Cells[1, ColIndex++] = dHeader.HeaderText; } ColIndex = 1; //获取DataGridView中的所有行和列的数值,填充到一个二维数组中. object[,] myData = new object[rowCount + 1, columnCount]; for (int i = 1; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { myData[i - 1, j] = dgv[j, i - 1].Value; //这里的获取注意行列次序 } } //将填充好的二维数组填充到Excel对象中. //r = worksheet.get_Range(worksheet.Cells[sheet_row_count + 1, 1], worksheet.Cells[sheet_row_count + rowCount, columnCount]); r = worksheet.get_Range(worksheet.Cells[sheet_row_count + 1, 1], worksheet.Cells[sheet_row_count + rowCount, columnCount]); r.Value2 = myData; r = null; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } //_________________________________________________________________________________ public StringCollection countexcel(string _filename) //返回工作表名 { if (System.IO.File.Exists(_filename)) { Excel.ApplicationClass myExcelApp = new Excel.ApplicationClass(); Excel.Workbook excel_wb; excel_wb = myExcelApp.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); StringCollection a = new StringCollection(); for (int i = 1; i <= excel_wb.Worksheets.Count; i++) { a.Add(((Excel.Worksheet)excel_wb.Worksheets[i]).Name); } myExcelApp.Workbooks.Close(); myExcelApp.Quit(); myExcelApp = null; return a; } else { return null; } } public DataSet proces(string _filename) //用datset返回整个excel { string strConn = "Provider=Microsoft.Jet.OleDb.4.0;"; strConn += "data source=" + _filename + ";"; strConn += "Extended Properties=Excel 8.0;"; strConn += "HDR=Yes;IMEX=1"; OleDbConnection objConn = new OleDbConnection(strConn); DataSet ds = new DataSet(); OleDbDataAdapter oldda = new OleDbDataAdapter(); foreach (string sheetname in countexcel(_filename)) { string a = "select * from "; a += sheetname; oldda.SelectCommand.CommandText = a; oldda.Fill(ds, sheetname); } return ds; } public int rowcount(string _filename, string sheetname) //行数 { Excel.ApplicationClass myExcelApp = new Excel.ApplicationClass(); Excel.Workbook excel_wb; excel_wb = myExcelApp.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); int counts = ((Excel.Worksheet)excel_wb.Worksheets.get_Item(sheetname)).UsedRange.Rows.Count; myExcelApp.Workbooks.Close(); myExcelApp.Quit(); myExcelApp = null; return counts; } public int rowcount(string sheetname) //行数 { Excel.Worksheet worksheet = GetSheet(sheetname); if (worksheet == null) { throw new Exception("Worksheet error "); } int counts = worksheet.UsedRange.Rows.Count; worksheet = null; return counts; } public int colcount(string _filename, string sheetname) //列数 { Excel.ApplicationClass myExcelApp = new Excel.ApplicationClass(); Excel.Workbook excel_wb; excel_wb = myExcelApp.Workbooks.Open(_filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); int counts = ((Excel.Worksheet)excel_wb.Worksheets.get_Item(sheetname)).UsedRange.Columns.Count; myExcelApp.Workbooks.Close(); myExcelApp.Quit(); myExcelApp = null; return counts; } public int colcount(string sheetname) //列数 { Excel.Worksheet worksheet = GetSheet(sheetname); if (worksheet == null) { throw new Exception("Worksheet error "); } int counts = worksheet.UsedRange.Columns.Count; worksheet = null; return counts; } public string GetCellStr(string sheetName, int row, int col) //返回指定单元格的文本 { string str = ""; try { //Open(xlsFileName); Excel.Worksheet worksheet = GetSheet(sheetName); Excel.Range r = (Excel.Range)(worksheet.Cells[row, col]); str = r.Text.ToString(); r = null; worksheet = null; //Close(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } return str; //Excel.Range rng3 = xSheet.get_Range("C6", System.Reflection.Missing.Value); //rng3.Cells.FormulaR1C1 = txtCellText.Text; // rng3.Interior.ColorIndex = 6; //设置Range的背景色 } public void SetCellStr(string sheetName, int row, int col, string writeStr) //将字符串写入指定单元格 { try { //Open(xlsFileName); Excel.Worksheet worksheet = GetSheet(sheetName); Excel.Range r = (Excel.Range)(worksheet.Cells[row, col]); //r.Text.FormulaR1C1; r.Value2 = writeStr; worksheet = null; r = null; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } //Save(); // Close(); //Excel.Range rng3 = xSheet.get_Range("C6", System.Reflection.Missing.Value); //rng3.Cells.FormulaR1C1 = txtCellText.Text; // rng3.Interior.ColorIndex = 6; //设置Range的背景色 } //_______________________________________添加自定义函数_________________________________________________________________ /// <summary> /// 将数据写入Excel /// </summary> /// <param name="data">要写入的二维数组数据</param> /// <param name="startRow">Excel中的起始行</param> /// <param name="startColumn">Excel中的起始列</param> public void WriteData(string[,] data, string fileName, string sheetName, int startRow, int startColumn) { try { Excel.Application myExcel; Excel.Workbook myWorkBook; myExcel = new Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Excel.Worksheet worksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); int rowNumber = data.GetLength(0); int columnNumber = data.GetLength(1); for (int i = 0; i < rowNumber; i++) { for (int j = 0; j < columnNumber; j++) { //在Excel中，如果某单元格以单引号“'”开头，表示该单元格为纯文本，因此，我们在每个单元格前面加单引号。 myExcel.Cells[startRow + i, startColumn + j] = "'" + data[i, j]; } } myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } /// <summary> /// 将数据写入Excel /// </summary> /// <param name="data">要写入的字符串</param> /// <param name="starRow">写入的行</param> /// <param name="startColumn">写入的列</param> public void WriteData(string dataStr, string fileName, string sheetName, int row, int column) { try { Excel.Application myExcel; Excel.Workbook myWorkBook; myExcel = new Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Excel.Worksheet worksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); myExcel.Cells[row, column] = dataStr; myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } //Excel.Range rng3 = xSheet.get_Range("C6", System.Reflection.Missing.Value); //rng3.Cells.FormulaR1C1 = txtCellText.Text; // rng3.Interior.ColorIndex = 6; //设置Range的背景色 } /// <summary> /// 读取指定单元格数据 /// </summary> /// <param name="row">行序号</param> /// <param name="column">列序号</param> /// <returns>该格的数据</returns> public string ReadData(string fileName, string sheetName, int row, int column) { Excel.Application myExcel; Excel.Workbook myWorkBook; myExcel = new Excel.Application(); //myWorkBook = myExcel.Application.Workbooks.Add(true); myWorkBook = myExcel.Workbooks.Add(fileName); Excel.Worksheet worksheet = (Excel.Worksheet)myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing); worksheet.Name = sheetName; //Excel.Worksheet worksheet = (Excel.Worksheet)myExcel.Worksheets[sheetName]; worksheet.Activate(); Excel.Range range = myExcel.get_Range(myExcel.Cells[row, column], myExcel.Cells[row, column]); string str = range.Text.ToString(); myExcel.Quit(); myWorkBook = null; myExcel = null; GC.Collect(); return str; } public void UniteCells(Excel.Worksheet ws, int x1, int y1, int x2, int y2) //合并单元格 { ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing); } public void UniteCells(string ws, int x1, int y1, int x2, int y2) //合并单元格 { GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing); } public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY) //将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString(); } } } public void InsertTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) //将内存中数据表格插入到Excel指定工作表的指定位置二 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { ws.Cells[startX + i, j + startY] = dt.Rows[i][j]; } } } public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY) //将内存中数据表格添加到Excel指定工作表的指定位置一 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } public void AddTable(System.Data.DataTable dt, Excel.Worksheet ws, int startX, int startY) //将内存中数据表格添加到Excel指定工作表的指定位置二 { for (int i = 0; i <= dt.Rows.Count - 1; i++) { for (int j = 0; j <= dt.Columns.Count - 1; j++) { ws.Cells[i + startX, j + startY] = dt.Rows[i][j]; } } } /// <summary> /// 保存Excel /// </summary> /// <returns>保存成功返回True</returns> public bool Save() //保存文档 { if (mFilename == "") { return false; } else { try { wb.Save();//似乎实效，所以加一下一个语句 wb.SaveAs(mFilename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return true; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); return false; } } } /// <summary> /// Excel文档另存为 /// </summary> /// <param name="fileName">保存完整路径加文件名</param> /// <returns>保存成功返回True</returns> public bool SaveAs(object FileName) //文档另存为 { try { wb.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); return true; } catch (Exception e) { MessageBox.Show(e.Message.ToString()); return false; } } //去掉文件后缀 public void RemoveExefilter(string FileName) { string filename = FileName; string substr; if (System.IO.File.Exists(FileName)) { System.IO.FileInfo myfile = new System.IO.FileInfo(FileName); //filename = FileName.Trim(myfile.Extension.ToCharArray()); substr = myfile.Extension; filename = filename.Substring(0, filename.Length - substr.Length); //MessageBox.Show(substr); //MessageBox.Show(filename); if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } myfile.MoveTo(filename.ToString()); } } /// <summary> /// 关闭Excel /// </summary> /// /// <summary> /// winapi 用于找到句柄线程 /// </summary> /// <param name="hwnd"></param> /// <param name="ID"></param> /// <returns></returns> public void Close() //关闭一个Excel对象，销毁对象 { /* app.Quit(); //release_xlsObj(); //release_xlsObj(); //wb.Save(); wb.Close(Type.Missing,Type.Missing,Type.Missing); wbs.Close(); app.Quit(); wb = null; wbs = null; app = null; //KillProcess("EXCEL"); GC.Collect(); */ app.Quit(); release_xlsObj(); //调用window api查找Excel进程,并用关闭 IntPtr t = new IntPtr(app.Hwnd); int ProcessById; GetWindowThreadProcessId(t, out ProcessById); System.Diagnostics.Process ExcelProcess = System.Diagnostics.Process.GetProcessById(ProcessById); ExcelProcess.Kill(); app = null; wbs = null; wb = null; wss = null; ws = null; } public void release_xlsObj() { //if (app != null) //System.Runtime.InteropServices.Marshal.ReleaseComObject(app); //app = null; if (wbs != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs); wbs = null; if (wb != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb); wb = null; if (wss != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(wss); wss = null; if (ws != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(ws); ws = null; //System.Runtime.InteropServices.Marshal.ReleaseComObject(Range); // Range=null; GC.Collect(); } //////如何杀死word,excel等进程,下面的方法可以直接调用 public void KillProcess(string processName) { System.Diagnostics.Process myproc = new System.Diagnostics.Process(); //得到所有打开的进程 try { foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName)) { if (!thisproc.CloseMainWindow()) { thisproc.Kill(); } } } catch (Exception e) { MessageBox.Show(e.Message.ToString()); } } } }
同时分享调用方，我用的WinForm给大家写得示例，以下是窗口截图：
有兴趣的朋友可以 下载附件
无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口
解决办法是将引用的DLL：Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了。