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

2019独角兽企业重金招聘Python工程师标准>>>

在工作中经常遇到需要用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-最新最全的IT资讯]
本文标题:c#如何生成Excel(.xls和.xlsx)文件

转载于:https://my.oschina.net/u/1766067/blog/300322