EXCEL文件是經(jīng)常需要用到的功能,，我們利用一些開源庫可以很容易實現(xiàn)這個功能,。excellibrary是國人寫的開源組件,，很容易使用,，可惜貌似還不支持.xlsx(Excel 2007)，例子如下： //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); 例子二： //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); } } 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(); } 例子二: using OfficeOpenXml; //指定Templete文檔Text.xlsx FileInfo newFile = new FileInfo("D:" + @"\Test.xlsx"); //開啟 using (ExcelPackage pck = new ExcelPackage(newFile)) { try { //設(shè)定ExcelWorkBook ExcelWorkbook workBook = pck.Workbook; if (workBook != null) { if (workBook.Worksheets.Count > 0) { //復(fù)制Temp這個Sheet同時命名為《清單》 ExcelWorksheet currentWorksheet = workBook.Worksheets.Copy("Temp", "清單"); //可以設(shè)定保護(hù)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./ NPOI無需Office COM組件且不依賴Office，使用NPOI能夠幫助開發(fā)者在沒有安裝微軟Office的情況下讀寫Office 97-2003的文件,，支持的文件格式包括xls, doc, ppt等,。NPOI是構(gòu)建在POI 3.x版本之上的，它可以在沒有安裝Office的情況下對Word/Excel文檔進(jìn)行讀寫操作。 被人稱為操作EXCEL的終極方案,，例子如下： //引用 using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.SS.UserModel; //將WorkBook指到我們原本設(shè)計好的Templete Book1.xls using (IWorkbook wb = new HSSFWorkbook(new FileStream("D:/Book1.xls", FileMode.Open))) { try { //設(shè)定要使用的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(); } } |
|
|
