C# EPPlus.dll操作Excel
By
hi_aardio
at 2022-03-23 • 0人收藏 • 1116人看过
PPlus是一个使用Open Office XML(xlsx)文件格式,能读写Excel 2007/2010,不支持2003版本的Excel。
文件的开源组件,在导出Excel的时候不需要电脑上安装office。官网地址:http://epplus.codeplex.com/
import win.ui; /*DSG{{*/ mainForm = win.form(text = "测试EPPlus使用Excel应用"; right = 959; bottom = 591) mainForm.add() /*}}*/ import console; import dotNet; console.open() dll = dotNet.loadFile("res/EPPlus.dll") officeOpenXml_1 = dll.import("OfficeOpenXml") var package = officeOpenXml_1.ExcelPackage(io.fullpath("\MyTemplate.xlsx")); //获取sheet名 var worksheet = package.Workbook.Worksheets[1]; console.log("sheet名:", worksheet) //获取表格的列数和行数 var rowCount = worksheet.Dimension.Rows; var colCount = worksheet.Dimension.Columns; console.log("rowCount:", rowCount) console.log("colCount:", colCount) //循环读取表格 for (row = 1; rowCount; 1) { // 具体的获取数据 console.log(worksheet.Cells.Item(row, 1).Value) } //添加sheet //var worksheet_add = package.Workbook.Worksheets.Add("核桃及阿紫") //设定sheet值 公式 worksheet.Cells.Item(1, 1).Value = "我是增加的11"; //直接指定行列数进行赋值 worksheet.Cells.Item("B3").Value = "你是B3"; //直接指定单元格进行赋值 worksheet.Cells.Item("J3").Formula = "AVERAGE(J1, J2)" //设置单元格格式 worksheet.Cells.Item("E1").Style.Numberformat.Format = "#,##0.00"; //这是保留两位小数 //设置对齐方式 worksheet.Cells.Style.WrapText = true; //自动换行 //设置单元格字体样式 worksheet.Cells.Item("E4").Style.Font.Bold = true; //字体为粗体 worksheet.Cells.Item("E6").Style.Font.Name = "微软雅黑"; //字体 worksheet.Cells.Item("E7").Style.Font.Size = 12; //字体大小 //设置单元格的行高和列宽 worksheet.Cells.Style.ShrinkToFit = true; //单元格自动适应大小 worksheet.Row(1).Height = 15; //设置行高 worksheet.Row(1).CustomHeight = true; //自动调整行高 worksheet.Column(1).Width = 15; //设置列宽 //设置sheet背景 worksheet.View.ShowGridLines = false; //去掉sheet的网格线 package.Save(); //Save the workbook mainForm.show(); return win.loopMessage();
7 个回复 | 最后更新于 2022-03-24
2022-03-24
#5
回复#1 @hi_aardio :
现在不用合并程序集了,这样写即可
import console; console.open(); import dotNet; dotNet.reference({ ["OfficeOpenXml"] = "res\EPPlus.dll"; ["Microsoft.IO.RecyclableMemoryStream"] = "res\Microsoft.IO.RecyclableMemoryStream.dll"; }); OfficeOpenXml = dotNet.import("OfficeOpenXml"); var package = OfficeOpenXml.ExcelPackage(io.fullpath("\MyTemplate.xlsx")); var worksheet = package.Workbook.Worksheets[1]; console.log("sheet名:", worksheet) console.pause(true);
依赖包加上就行。
登录后方可回帖
res_dll.rar
EPPlus.dll 打包