关于在WinForm下对Excel进行操作的总结

关于在WinForm下对Excel进行操作的总结

【转】http://hi.baidu.com/dearfather/blog/item/8911302426071039c9955929.html

---by weily @ 2005-8-10

                魏雷远

一、首先在WinForm下,对Excel操作与Web有些地方不同；

二、创建Excel

在引用中添加

通过：.NET IDE 菜单 项目(P)-à添加引用(R)-à添加引用 COM 中选择 Microsoft Excel 11.0.0 Object Library（根据Office版本不同有相应的COM组件）,选择后添加  

或者采用其他方式

然后在.cs文件前添加如下项：

using System.Reflection;

using System.Runtime.InteropServices;

using Excel = Microsoft.Office.Interop.Excel;

    

创建Excel对象

private Excel.Application m_objExcel = null;

private Excel.Workbooks m_objBooks = null;

private Excel._Workbook m_objBook = null;

private Excel.Sheets m_objSheets = null;

private Excel._Worksheet m_objSheet = null;

private Excel.Range m_objRange = null;

private Excel.Font m_objFont = null;

private object m_objOpt = System.Reflection.Missing.Value;

    

m_objExcel = new Excel.Application();

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));

…………………………………

以下对Excel中的区域进行操作

m_objRange = m_objSheet.get_Range("A1",m_objOpt);

m_objRange = m_objRange.get_Resize(1,iFields);

m_objRange.set_Value(m_objOpt,objHeaders);

m_objFont = m_objRange.Font;

m_objFont.Bold = true;

m_objBook.SaveAs(saveFileDialog.FileName,m_objOpt,m_objOpt,m_objOpt,m_objOpt,m_objOpt,Excel.XlSaveAsAccessMode.xlNoChange,m_objOpt,

m_objOpt,m_objOpt,m_objOpt,m_objOpt);                  

m_objBook.Close(false,m_objOpt,m_objOpt);

m_objExcel.Quit();

……………………………….

根据以上方法创建Excel文件后,每次调用以上的过程,会产生一个Excel进程,即使上面有m_objExcel.Quit()等方法存在,也没有终止Excel进程。

三、销毁Excel进程

因为创建Excel后,对Excel文件中的任何区域(即任何Cell)操作(Excel中,Cell被看作对象),都会生成一个对象,所以必须进行销毁相应的Excel生成的对象：

m_objExcel.Quit();

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);

m_objSheet = null;

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);

m_objSheets = null;

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);

m_objBook = null;

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);

m_objBooks = null;

System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);

m_objExcel = null;

GC.Collect();                         //强制进行垃圾回收

GC.WaitForPendingFinalizers();        //挂起当前线程,直到以上线程队列清空为止

四、经测试,以上程序到目前为止有效。请大家提供更好的处理方式。Thanks.