在工作中经常遇到需要用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官方示例代码:
//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文件):
//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开源协议。
