学步园

        这几天要对一些数据格式为Excel表格的词典文进行统计，并且一些过滤。本来并不太复杂的事，由于操作Excel的关系，浪费了很长时间，下定决心要写一个操作Excel表格的类，方便以后重用。下面为代码：要添加.NET引用“using Microsoft.Office.Interop.Excel”，和对应的COM引用“Microsoft Excel 11.0 Object Library”

       由于Excel的读写有较大的不同，所有把读取Excel和写入Excel分开，一以下为写入数据。

using System;<br /> using System.Collections.Generic;<br /> using System.Linq;<br /> using System.Text;<br /> using System.Reflection; // For Missing.Value and BindingFlags<br /> using System.Runtime.InteropServices; // For COMException<br /> using Microsoft.Office.Interop.Excel;<br /> using Excel = Microsoft.Office.Interop.Excel;<br /> namespace MyExcelWriter<br /> {<br /> public class MyExcelWriter<br /> {<br /> private string filePath;<br /> /// <summary><br /> /// Excel主程序<br /> /// </summary><br /> private Excel.Application app;<br /> private Excel.Workbooks workbooks;<br /> private Excel.Sheets sheets;<br /> // private _Workbook workbook;<br /> private _Worksheet worksheet;<br /> private object nothing = System.Reflection.Missing.Value;<br /> private string FilePath<br /> {<br /> get<br /> {<br /> return this.filePath;<br /> }<br /> set<br /> {<br /> this.filePath = value;<br /> }<br /> }<br /> /// <summary><br /> /// 要写入的文件路径<br /> /// </summary><br /> /// <param name="path"></param><br /> public MyExcelWriter(string path)<br /> {<br /> this.FilePath = path;<br /> }<br /> ~MyExcelWriter()<br /> {<br /> if (app != null)<br /> {<br /> app.Workbooks.Close();<br /> app.Quit();<br /> }<br /> System.GC.Collect();<br /> }<br /> /// <summary><br /> /// 写入前，打开文件，这一步必须<br /> /// </summary><br /> /// <returns>1:成功，1:失败</returns><br /> public int OpenExcelToRead()<br /> {<br /> int val = 0;<br /> app = (Excel.Application)new Application();<br /> if (app == null)<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> app.Visible = false;</p> <p> workbooks = (Excel.Workbooks)app.Workbooks;<br /> try<br /> {<br /> workbooks.Open(this.FilePath, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing);</p> <p> }<br /> catch (Exception)<br /> {<br /> if (app != null)<br /> {<br /> app.Quit();<br /> val = 1;<br /> return val;<br /> }<br /> }<br /> sheets =app.Worksheets;<br /> worksheet = (_Worksheet)sheets.get_Item(1);<br /> if (worksheet == null)<br /> {<br /> if (app != null)<br /> {<br /> app.Quit();</p> <p> }<br /> val = 1;<br /> return val;<br /> }<br /> return val;<br /> }<br /> /// <summary><br /> /// 写入某个具体行列单元格数据<br /> /// </summary><br /> /// <param name="row">行号，1开始</param><br /> /// <param name="colums">列号，1开始</param><br /> /// <param name="value">写入的数据</param><br /> /// <returns>0成功,1失败</returns><br /> public int WriteExcelCell(int row, int colum, string value)<br /> {<br /> int val = 0;<br /> if ((row <= 0) || (colum <= 0))<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> try<br /> {<br /> worksheet.Cells[row, colum] = value;<br /> }<br /> catch(Exception )<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> return val;<br /> }<br /> /// <summary><br /> /// 清空某个单元格<br /> /// </summary><br /> /// <param name="row">行</param><br /> /// <param name="colum">列</param><br /> /// <returns>0成功,1失败</returns><br /> public int ClearExcelCell(int row,int colum)<br /> {<br /> int val = 0;<br /> if ((row <= 0) || (colum <= 0))<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> try<br /> {<br /> worksheet.Cells[row, colum] =string.Empty;<br /> }<br /> catch (Exception ex)<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> return val;<br /> }<br /> /// <summary><br /> /// 保存写入 的数据<br /> /// </summary><br /> /// <returns>0成功,1失败</returns><br /> public int CloseAndSaveExcel()<br /> {<br /> int val = 0;<br /> try<br /> {<br /> if (app != null)<br /> {<br /> app.DisplayAlerts = false;<br /> app.UserControl = false;<br /> }<br /> if (worksheet != null)<br /> {<br /> worksheet.SaveAs(this.FilePath, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing, nothing);<br /> }</p> <p> }<br /> catch (COMException)<br /> {<br /> val = 1;<br /> return val;<br /> }<br /> finally<br /> {<br /> if (app != null)<br /> {<br /> app.Quit();<br /> }<br /> }<br /> return val;<br /> }<br /> }<br /> }<br />