操作Excel是一個比較常見的業務場景，本篇將使用EPPlus簡單演示一個匯入匯出的示例，
EPPlus開源地址：https://github.com/EPPlusSoftware/EPPlus
在專案中添加EPPlus組件
Install-Package EPPlus
匯入
先準備一個Excel檔案，將其內容讀取出來，并序列化成JSON字串進行回傳，
[HttpPost]
public List<ExcelDemoDto> Import([FromForm] ImportExcelInput input)
{
var list = new List<ExcelDemoDto>();
using (var package = new ExcelPackage(input.ExcelFile.OpenReadStream()))
{
// 獲取到第一個Sheet，也可以通過 Worksheets["name"] 獲取指定的作業表
var sheet = package.Workbook.Worksheets.First();
#region 獲取開始和結束行列的個數，根據個數可以做各種校驗作業
// +1 是因為第一行往往我們獲取到的都是Excel的標題
int startRowNumber = sheet.Dimension.Start.Row + 1;
int endRowNumber = sheet.Dimension.End.Row;
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
#endregion
// 回圈獲取整個Excel資料表資料
for (int currentRow = startRowNumber; currentRow <= endRowNumber; currentRow++)
{
list.Add(new ExcelDemoDto
{
AAA = sheet.Cells[currentRow, 1].Text,
BBB = sheet.Cells[currentRow, 2].Text,
CCC = sheet.Cells[currentRow, 3].Text,
DDD = sheet.Cells[currentRow, 4].Text,
EEE = sheet.Cells[currentRow, 5].Text,
FFF = sheet.Cells[currentRow, 6].Text
});
}
}
return list;
}
public class ExcelDemoDto
{
public string AAA { get; set; }
public string BBB { get; set; }
public string CCC { get; set; }
public string DDD { get; set; }
public string EEE { get; set; }
public string FFF { get; set; }
}
public class ImportExcelInput
{
public IFormFile ExcelFile { get; set; }
}
匯出
[HttpGet]
public async Task<string> Export()
{
using var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("sheet1");
var headers = new string[] { "AAA", "BBB", "CCC", "DDD", "EEE", "FFF" };
for (int i = 0; i < headers.Length; i++)
{
worksheet.Cells[1, i + 1].Value = https://www.cnblogs.com/meowv/p/headers[i];
worksheet.Cells[1, i + 1].Style.Font.Bold = true;
}
// 模擬資料
var list = new List();
for (int i = 1; i <= 10; i++)
{
list.Add(new ExcelDemoDto
{
AAA = $"A{i}",
BBB = $"B{i}",
CCC = $"C{i}",
DDD = $"D{i}",
EEE = $"E{i}",
FFF = $"F{i}"
});
}
// 支持各種直接獲取資料的方法
// worksheet.Cells.Load*...
int row = 2;
foreach (var item in list)
{
worksheet.Cells[row, 1].Value = item.AAA;
worksheet.Cells[row, 2].Value = item.BBB;
worksheet.Cells[row, 3].Value = item.CCC;
worksheet.Cells[row, 4].Value = item.DDD;
worksheet.Cells[row, 5].Value = item.EEE;
worksheet.Cells[row, 6].Value = item.FFF;
row++;
}
// 通常做法是，將excel上傳至物件存盤，獲取到下載鏈接，這里將其輸出到專案根目錄，
var path = Path.Combine(Directory.GetCurrentDirectory(), $"excel.xlsx");
await package.GetAsByteArray().DownloadAsync(path);
return path;
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/44.html
標籤：.NET Core