Asp.net core 学习笔记 (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 =
},
new Product
{
name = "mk200",
date = DateTime.Now,
amount = 99.33,
published = true,
sort =
}
};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(); //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");
}

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 + ].Value;
} ViewData["Message"] = "Your contact page."; return View();
}