C#创建Excel(.xls和.xlsx)文件的三种方法
2018-06-27 09:03:33来源:未知 阅读 ()
生成EXCEL文件是经常需要用到的功能,我们利用一些开源库可以很容易实现这个功能。
方法一:利用excellibrary,http://code.google.com/p/excellibrary/
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); } }
方法二:利用EPPlus,http://epplus.codeplex.com/
EPPlus是一个使用Open Office XML(xlsx)文件格式,能读写Excel 2007/2010 文件的开源组件。
例子如下:
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 { //设定ExcelWorkBook ExcelWorkbook 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.xlsx pck.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.xls using (IWorkbook wb = new HSSFWorkbook(new FileStream("D:/Book1.xls", FileMode.Open))) { try { //设定要使用的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(); } }
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
下一篇:[ 新手] 注册博客的第一天.
- HTML表单概念、语法及创建表单,案例 2020-01-30
- 在用vue-cli4创建的vue2.x项目中通过vue-fontawesome使用fon 2020-01-11
- 如何创建 CSS 2019-11-09
- 博客模仿 2019-08-14
- Vue 学习记录(一)-创建项目 2019-08-14
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash