Asp.net core 学习笔记 (Excel 读写)
2023-09-27 14:23:55 时间
EPPlus 已经支持 .net core 了
https://www.nuget.org/packages/EPPlus
https://github.com/JanKallman/EPPlus
refer: https://stackoverflow.com/questions/40209636/epplus-number-format/40214134 常用 format
note: excel datetimeoffset 不支持的
写入 excel
public async Task<IActionResult> About() { ViewData["Message"] = "Your application description page."; var products = new List<Product> { new Product { name = "mk100", date = DateTime.Now, amount = 99.33, published = false, sort = 1 }, new Product { name = "mk200", date = DateTime.Now, amount = 99.33, published = true, sort = 2 } }; byte[] responseBytes; string path = Path.Combine(HostingEnvironment.WebRootPath, "excel", "abc.xlsx"); using (var package = new ExcelPackage()) { // Add a new worksheet to the empty workbook ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory"); // 2 种方式做 select // - 从新创建想要的对象 (比较灵活) // - 通过 memberInfoes 选出要的 //var memberInfoes = "name,date,amount,published,sort".Split(',').Select(v => typeof(Product).GetProperty(v)).ToArray(); var datas = products.Select(p => new { p.name, p.date }); worksheet.Cells["A1"].LoadFromCollection( datas, true, TableStyles.None //BindingFlags.Public, //memberInfoes ); // 弄 date/datetime format using (var cellRanges = worksheet.Cells[$"B2:B{datas.Count() + 1}"]) { cellRanges.Style.Numberformat.Format = "yyyy-mm-dd"; } //Add the headers //worksheet.Cells[1, 1].Value = "String"; //worksheet.Cells[1, 2].Value = "Int"; //worksheet.Cells[1, 3].Value = "Double"; //worksheet.Cells[1, 4].Value = "Boolean"; //worksheet.Cells[1, 5].Value = "Date"; //worksheet.Cells[2, 1].Value = "dasd"; //worksheet.Cells[2, 2].Value = 12312; //worksheet.Cells[2, 3].Value = 123.123123; //worksheet.Cells[2, 4].Value = true; //worksheet.Cells[2, 5].Value = DateTime.Now; //worksheet.Cells[2, 5].Style.Numberformat.Format = "yyyy-mm-dd"; //worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0"; //worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells System.IO.File.Delete(path); // note package.SaveAs 和 package.GetAsByteArray() 不能一起用 // 用了一个另一个就不能用了,通常我们是选其中一个用而已啦,很少 2 个都需要的 // 解决方法很简单,用 byte 然后 file stream 写 bytes 进去 responseBytes = package.GetAsByteArray(); using (var fs = System.IO.File.Create(path)) { await fs.WriteAsync(responseBytes); //package.SaveAs(fs); } } //return File(responseBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report.xlsx"); return File(await System.IO.File.ReadAllBytesAsync(path), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "report.xlsx"); }
读 excel
public IActionResult Contact() { var path = Path.Combine(HostingEnvironment.WebRootPath, "excel", "abc.xlsx"); using (var fs = System.IO.File.Open(path, FileMode.Open, FileAccess.Read)) using (var package = new ExcelPackage(fs)) { var worksheet = package.Workbook.Worksheets["Inventory"]; var sc = worksheet.Dimension.Start.Column; var ec = worksheet.Dimension.End.Column; var sr = worksheet.Dimension.Start.Row; var er = worksheet.Dimension.End.Row; var value = worksheet.Cells[sc, sr + 1].Value; } ViewData["Message"] = "Your contact page."; return View(); }
相关文章
- ASP.NET MVC在线预览Excel、Word、TXT、PDF文件
- SolrCloud-如何在.NET程序中使用
- ASP.NET网页动态添加数据行
- Stack Overflow研发副总裁:.NET技术并不差,合适自己就好
- ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)
- 用ado.net对word,excel进行存取。
- ASP.NET Core – ADO.NET
- ASP.NET Core Library – Excel 读写
- ASP.NET Web API 如何通过程序控制返回xml还是json
- .NET Core 基于Websocket的在线聊天室
- .Net中的RealProxy实现AOP
- Rebex ZIP for .NET加密或提取ZIP档案变得容易
- SpreadsheetGear for .NET繁华的Excel制作
- .Net之路(十四)com组件、OLEDB导入EXCEL
- ASP.NET Web API身份验证和授权
- .NET Core NPOI Linux上配置使用及生成Word和Excel文件示例代码
- .Net Core Aspose.Cells创建和读取Excel(.xls,.xlsx)数据
- .net 6简单使用NPOI 读取 Excel 案例+流程
- 2018-9-21-dot-net-core-使用-usb
- 《ASP.NET Core跨平台开发从入门到实战》Web API自定义格式化protobuf
- VB.NET版机房收费系统---导出Excel表格