C#操作Excel文件

使用:Microsoft.Office.Interop.Excel 组件对Excel文档进行操作

操作手记:

 

具体操作方法封装:

using System; using System.Data; using System.Data.OleDb; using System.Reflection; using Microsoft.Office.Interop.Excel; namespace ConsoleApplication { public class ExcelDemo { /// <summary> /// 返回Excel Application对象 /// </summary> /// <returns></returns> public static Application CreateApplication() { Application app = new ApplicationClass(); return app; } /// <summary> /// 使用Application对象创建新工作簿并返回 /// </summary> /// <param name="app">Excel对象</param> /// <returns></returns> public static Workbook GetWorkbook(Application app) { Workbook book = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); return book; } /// <summary> /// 使用Application对象打开已有工作簿并返回 /// </summary> /// <param name="app">Excel对象</param> /// <param name="fullPath">完整路径</param> /// <returns></returns> public static Workbook GetWorkbook(Application app, string fullPath) { Workbook book = app.Workbooks.Open(fullPath, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false); return book; } /// <summary> /// 使用工作簿对象创建工作表并返回 /// </summary> /// <param name="book">工作簿对象</param> /// <returns></returns> public static Worksheet GetWorksheet(Workbook book) { Worksheet sheet = book.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value) as Worksheet; return sheet; } /// <summary> /// 返回工作簿中指定下标的工作表 /// </summary> /// <param name="book">工作簿对象</param> /// <param name="index">工作表下标[下标从1开始]</param> /// <returns></returns> public static Worksheet GetWorksheet(Workbook book, int index) { Worksheet sheet = book.Worksheets[index] as Worksheet; return sheet; } /// <summary> /// 把DataTable中的数据,填充到工作表中 /// </summary> /// <param name="table">DataTable对象</param> /// <param name="sheet">Worksheet对象</param> /// <returns></returns> public static Worksheet SetWorkSheetValue(System.Data.DataTable table, Worksheet sheet) { return SetWorkSheetValue(table, sheet, 1, 1); } /// <summary> /// 把DataTable中的数据,填充到工作表中 /// </summary> /// <param name="table">DataTable对象</param> /// <param name="sheet">Worksheet对象</param> /// <param name="beginRow">起始行标</param> /// <param name="beginCol">起始列标</param> /// <returns></returns> public static Worksheet SetWorkSheetValue(System.Data.DataTable table, Worksheet sheet, int beginRow, int beginCol) { if (table == null || sheet == null) { return sheet; } int rows = beginRow; int cols = beginCol; foreach (DataColumn col in table.Columns) { sheet.Cells[rows, cols] = col.ColumnName; cols++; } rows++; foreach (DataRow row in table.Rows) { cols = beginCol; foreach (DataColumn col in table.Columns) { sheet.Cells[rows, cols] = row[col]; cols++; } rows++; } return sheet; } /// <summary> /// 查询指定Excel中的特定工作表 /// </summary> /// <param name="fullPath">Excel文件完整路径</param> /// <param name="sheetName">工作表名称</param> /// <returns></returns> public static System.Data.DataTable GetExcelWorkSheetValue(string fullPath, string sheetName) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullPath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; OleDbConnection oleCon = new OleDbConnection(strCon); OleDbDataAdapter oleAdp = new OleDbDataAdapter("Select * From [" + sheetName + "$]", oleCon); System.Data.DataTable table = new System.Data.DataTable(); try { oleAdp.Fill(table); } catch (OleDbException e) { throw new Exception("运行时错误：" + e.Message); } finally { if (oleCon.State != ConnectionState.Closed) { oleCon.Close(); } } return table; } } }

 

对操作类进行调用:

using System; using System.Text; using System.Data; using Microsoft.Office.Interop.Excel; namespace ConsoleApplication { class Program { static void Main(string[] args) { System.Data.DataTable table = new System.Data.DataTable(); table.Columns.Add("ID"); table.Columns.Add("Name"); table.Columns.Add("Code"); table.Columns.Add("Time"); for (int i = 0; i < 10; i++) { DataRow row = table.NewRow(); row["ID"] = i; row["Name"] = "Name_" + i; row["Code"] = "Code_" + i; row["Time"] = DateTime.Now; table.Rows.Add(row); } // 获取应该程序对象 Microsoft.Office.Interop.Excel.Application app = ExcelDemo.CreateApplication(); // 获取工作簿对象 Workbook book = ExcelDemo.GetWorkbook(app); // 获取工作表对象 Worksheet sheet = ExcelDemo.GetWorksheet(book, 1); // 为工作表填充值 sheet = ExcelDemo.SetWorkSheetValue(table, sheet); // 保存设置 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; app.ActiveWorkbook.SaveCopyAs(@"D:/Xls.xls");// 操作已存在工作簿应使用:app.ActiveWorkbook.Save();进行保存 // 退出 app.Quit(); // 查询 StringBuilder sb_Msg = new StringBuilder(); table = ExcelDemo.GetExcelWorkSheetValue(@"D:/Xls.xls", "sheet2"); foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { sb_Msg.Append(row[col]); } sb_Msg.Append("/n"); } Console.Write(sb_Msg.ToString()); } } }