生成Excel文件是經常需要用到的功能，我們利用一些開源庫可以很容易實現這個功能。
方法一：利用excellibrary，http://code.google.com/p/excellibrary/
excellibrary是國人寫的開源組件，很容易使用，可惜貌似還不支持.xlsx(Excel 2007)，例子如下：
//Create the data set and tableDataSet ds = new DataSet("New_DataSet");DataTable dt = new DataTable("New_DataTable");//Set the locale for eachds.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 DBstring 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 setds.Tables.Add(dt);//Here's the easy part. Create the Excel worksheet from the data setExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);
例子二：
//create new xls filestring 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 fileWorkbook book = Workbook.Load(file);Worksheet sheet = book.Worksheets[0];// traverse cellsforeach (Pair<Pair<int, int>, Cell> cell in sheet.Cells){dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;}// traverse rows by Indexfor (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 文件的開源組件。
例子如下：
例子二：
using OfficeOpenXml;//指定Templete文檔Text.xlsxFileInfo newFile = new FileInfo("D:" + @"/Test.xlsx");//開啟using (ExcelPackage pck = new ExcelPackage(newFile)){try{//設定ExcelWorkBookExcelWorkbook 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.xlsxpck.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的終極方案，例子如下：
//引用using NPOI.HSSF.UserModel;using NPOI.HPSF;using NPOI.POIFS.FileSystem;using NPOI.SS.UserModel;//將WorkBook指到我們原本設計好的Templete Book1.xlsusing (IWorkbook wb = new HSSFWorkbook(new FileStream("D:/Book1.xls", FileMode.Open))){try{//設定要使用的Sheet為第0個SheetISheet 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();}}
新聞熱點
疑難解答
