c#如何生成Excel(.xls和.xlsx)文件

在工作中经常遇到需要用c#生成Excel文件(.xls和.xlsx格式),完全免费开源的ExcelLibrary是一个不错的选择。

ExcelLibrary项目的地址为:

https://code.google.com/p/excellibrary/

ExcelLibrary源码下载地址:  

https://code.google.com/p/excellibrary/downloads/list

  ExcelLibrary提供了一个基于本地.NET应用程序的解决方案，可以用来新建，读取和修改Excel文件而不需要使用COM或者OLEDB。现在已经支持.xls文件格式，.xlsx(Excel 2007)也即将被支持。

ExcelLibrary官方示例代码:

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);       }   }

ExcelLibrary示例代码二(从数据库中获取数据然后创建Excel文件):

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);

另外，还可以使用EPPlus, EPPlus支持生成Excel 2007/2010 格式的文件(.xlsx) 其主页为:

http://epplus.codeplex.com/

EPPlus项目基于LGPL开源协议。

文章转载自:[169it科技资讯]
本文标题:c#如何生成Excel(.xls和.xlsx)文件