C#创建Excel(.xls和.xlsx)文件的三种方法

生成EXCEL文件是经常需要用到的功能，我们利用一些开源库可以很容易实现这个功能。

方法一：利用excellibrary，http://code.google.com/p/excellibrary/

excellibrary是国人写的开源组件，很容易使用，可惜貌似还不支持.xlsx(Excel 2007)，例子如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

//Create the data set and table DataSet ds =  new  DataSet( "New_DataSet" ); DataTable dt =  new  DataTable( "New_DataTable" ); //Set the locale for each ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture; //Open a DB connection (in this example with OleDB) OleDbConnection con =  new  OleDbConnection(dbConnectionString); con.Open(); //Create a query and fill the data table with the data from the DB string  sql =  "SELECT Whatever FROM MyDBTable;" ; OleDbCommand cmd =  new  OleDbCommand(sql, con); OleDbDataAdapter adptr =  new  OleDbDataAdapter(); adptr.SelectCommand = cmd; adptr.Fill(dt); con.Close(); //Add the table to the data set ds.Tables.Add(dt); //Here's the easy part. Create the Excel worksheet from the data set ExcelLibrary.DataSetHelper.CreateWorkbook( "MyExcelFile.xls" , ds);

 

例子二：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

//create new xls file string  file =  "C:\\newdoc.xls" ; Workbook workbook =  new  Workbook(); Worksheet worksheet =  new  Worksheet( "First Sheet" ); worksheet.Cells[0, 1] =  new  Cell(( short )1); worksheet.Cells[2, 0] =  new  Cell(9999999); worksheet.Cells[3, 3] =  new  Cell(( decimal )3.45); worksheet.Cells[2, 2] =  new  Cell( "Text string" ); worksheet.Cells[2, 4] =  new  Cell( "Second string" ); worksheet.Cells[4, 0] =  new  Cell(32764.5,  "#,##0.00" ); worksheet.Cells[5, 1] =  new  Cell(DateTime.Now,  @"YYYY\-MM\-DD" ); worksheet.Cells.ColumnWidth[0, 1] = 3000; workbook.Worksheets.Add(worksheet); workbook.Save(file); // open xls file Workbook book = Workbook.Load(file); Worksheet sheet = book.Worksheets[0]; // traverse cells foreach  (Pair<Pair< int ,  int >, Cell> cell  in  sheet.Cells) { dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value; } // traverse rows by Index for  ( int  rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++) { Row row = sheet.Cells.GetRow(rowIndex); for  ( int  colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++) { Cell cell = row.GetCell(colIndex); } }

 

方法二：利用EPPlus,http://epplus.codeplex.com/
EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件。
例子如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

var file =  @"Sample.xlsx" ; if  (File.Exists(file)) File.Delete(file); using  (var excel =  new  ExcelPackage( new  FileInfo(file))) { var ws = excel.Workbook.Worksheets.Add( "Sheet1" ); ws.Cells[1, 1].Value =  "Date" ; ws.Cells[1, 2].Value =  "Price" ; ws.Cells[1, 3].Value =  "Volume" ; var random =  new  Random(); for  ( int  i = 0; i < 10; i++) { ws.Cells[i + 2, 1].Value = DateTime.Today.AddDays(i); ws.Cells[i + 2, 2].Value = random.NextDouble() * 1e3; ws.Cells[i + 2, 3].Value = random.Next() / 1e3; }   ws.Cells[2, 1, 11, 1].Style.Numberformat.Format =  "dd/MM/yyyy" ; ws.Cells[2, 2, 11, 2].Style.Numberformat.Format =  "#,##0.000000" ; ws.Cells[2, 3, 11, 3].Style.Numberformat.Format =  "#,##0" ; ws.Column(1).AutoFit(); ws.Column(2).AutoFit(); ws.Column(3).AutoFit(); excel.Save(); }

例子二：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

using  OfficeOpenXml; //指定Templete文档Text.xlsx FileInfo newFile =  new  FileInfo( "D:"  +  @"\Test.xlsx" ); //开启 using  (ExcelPackage pck =  new  ExcelPackage(newFile)) { try { //设定ExcelWorkBook ExcelWorkbook workBook = pck.Workbook; if  (workBook !=  null ) { if  (workBook.Worksheets.Count > 0) { //复制Temp这个Sheet同时命名为《清单》 ExcelWorksheet currentWorksheet = workBook.Worksheets.Copy( "Temp" ,  "清单" ); //可以设定保护Sheet的密码 //currentWorksheet.Protection.SetPassword("1234"); int  StartRow = 4; for  ( int  i = 0; i < tDS.Tables[0].Rows.Count; i++) { //Cells[RowIndex,CellIndex] currentWorksheet.Cells[StartRow + i, 1].Value = Convert.ToString(tDS.Tables[0].Rows[i][0]); currentWorksheet.Cells[StartRow + i, 2].Value = Convert.ToString(tDS.Tables[0].Rows[i][1]); currentWorksheet.Cells[StartRow + i, 3].Value = Convert.ToString(tDS.Tables[0].Rows[i][2]); currentWorksheet.Cells[StartRow + i, 4].Value = Convert.ToString(tDS.Tables[0].Rows[i][3]); currentWorksheet.Cells[StartRow + i, 5].Value = Convert.ToString(tDS.Tables[0].Rows[i][4]); currentWorksheet.Cells[StartRow + i, 6].Value = Convert.ToString(tDS.Tables[0].Rows[i][5]); currentWorksheet.Cells[StartRow + i, 7].Value = Convert.ToString(tDS.Tables[0].Rows[i][6]); currentWorksheet.Cells[StartRow + i, 8].Value = Convert.ToString(tDS.Tables[0].Rows[i][7]); currentWorksheet.Cells[StartRow + i, 9].Value = Convert.ToString(tDS.Tables[0].Rows[i][8]); currentWorksheet.Cells[StartRow + i, 10].Value = Convert.ToString(tDS.Tables[0].Rows[i][9]); currentWorksheet.Cells[StartRow + i, 11].Value = Convert.ToString(tDS.Tables[0].Rows[i][10]); currentWorksheet.Cells[StartRow + i, 12].Value = Convert.ToString(tDS.Tables[0].Rows[i][11]); currentWorksheet.Cells[StartRow + i, 13].Value = Convert.ToString(tDS.Tables[0].Rows[i][12]); } //将Temp 这个Sheet删除 workBook.Worksheets.Delete( "Temp" ); } } //存至Text4.xlsx pck.SaveAs( new  FileInfo( "H:"  +  @"\Test4.xlsx" )); } catch  (Exception e) { oLogger.Fatal(e.ToString()); } }

方法三：NPOI http://npoi.codeplex.com/
NPOI无需Office COM组件且不依赖Office，使用NPOI能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
被人称为操作EXCEL的终极方案，例子如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

//引用 using  NPOI.HSSF.UserModel; using  NPOI.HPSF; using  NPOI.POIFS.FileSystem; using  NPOI.SS.UserModel; //将WorkBook指到我们原本设计好的Templete Book1.xls using  (IWorkbook wb =  new  HSSFWorkbook( new  FileStream( "D:/Book1.xls" , FileMode.Open))) { try { //设定要使用的Sheet为第0个Sheet ISheet TempSheet = wb.GetSheetAt(0); int  StartRow = 4; //tDS为Query回来的资料 for  ( int  i = 0; i < tDS.Tables[0].Rows.Count; i++) { //第一个Row要用Create的 TempSheet.CreateRow(StartRow + i).CreateCell(0).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][0])); //第二个Row之后直接用Get的 TempSheet.GetRow(StartRow + i).CreateCell(1).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][1])); TempSheet.GetRow(StartRow + i).CreateCell(2).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][2])); TempSheet.GetRow(StartRow + i).CreateCell(3).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][3])); TempSheet.GetRow(StartRow + i).CreateCell(4).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][4])); TempSheet.GetRow(StartRow + i).CreateCell(5).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][5])); TempSheet.GetRow(StartRow + i).CreateCell(6).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][6])); TempSheet.GetRow(StartRow + i).CreateCell(7).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][7])); TempSheet.GetRow(StartRow + i).CreateCell(8).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][8])); TempSheet.GetRow(StartRow + i).CreateCell(9).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][9])); TempSheet.GetRow(StartRow + i).CreateCell(10).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][10])); TempSheet.GetRow(StartRow + i).CreateCell(11).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][11])); TempSheet.GetRow(StartRow + i).CreateCell(12).SetCellValue(Convert.ToString(tDS.Tables[0].Rows[i][12])); } //将文档写到指定位置 using  (FileStream file =  new  FileStream( "H:/Test_NPOI4.xls" , FileMode.Create)) { wb.Write(file); file.Close(); file.Dispose(); } } catch  (Exception e) { string  a = e.ToString(); } }