.NET Core 导入导出 Excel

仅作记录,使用 EPPlus 组件,文字、图片、代码均来自参考链接

参考

示例

EPPlus 官方示例

In most cases you probably have some data that you want to move to an Excel spreadsheet, do some styling, maybe add a formula or a chart.

But before we get started, here’s something to keep in mind when you work with EPPlus:
Cell addresses, number formats and formulas are culture-insensitive, meaning things might look a little bit different when you write your code. This is the way OOXML is stored and is then translated too your culture when the workbook is opened in Excel.

Addresses are separated by a comma (,).
Example worksheet.Cells["A1:C1,C3"].Style.Font.Bold = true.

Numberformats use dot for decimal (.) and comma (,) for thousand separator.
Example worksheet.Cells["B2:B3"].Style.NumberFormat.Format = "#,##0.00";.

Formulas use comma (,) to separate parameters.
Example worksheet.Cells["C11"].Formula="SUBTOTAL(9,\"C1:C10\")";.

The first thing you do is to create an instance to the ExcelPackage class. To do that you first need to add a using directive to OfficeOpenXml namespace in the top of your file. This is the top namespace in EPPlus;

using OfficeOpenXml;

You can now reference the Excelpackage class directly for your class. The ExcelPackage class has few different constructors depending on what you want to do…

        //Creates a blank workbook. Use the using statment, so the package is disposed when we are done.
    using (var p = new ExcelPackage())
        {
           //A workbook must have at least on cell, so lets add one... 
           var ws=p.Workbook.Worksheets.Add("MySheet");
           //To set values in the spreadsheet use the Cells indexer.
           ws.Cells["A1"].Value = "This is cell A1";
           //Save the new workbook. We haven't specified the filename so use the Save as method.
           p.SaveAs(new FileInfo(@"c:\workbooks\myworkbook.xlsx"));
        }

You can also specify a workbook directly in the constructor.

        //Open the workbook (or create it if it doesn't exist)
        var fi=new FileInfo(@"c:\workbooks\myworkbook.xlsx")
    using (var p = new ExcelPackage(fi))
        {
           //Get the Worksheet created in the previous codesample. 
           var ws=p.Workbook.Worksheets["MySheet"];
           Set the cell value using row and column.
           ws.Cells[2, 1].Value = "This is cell A2. It is set to bolds";
           //The style object is used to access most cells formatting and styles.
           ws.Cells[2, 1].Style.Font.Bold=true;
           //Save and close the package.
           p.Save();
        }

ASP.NET 示例

导入

图片来自dotNET跨平台 – .NET Core 中导入导出Excel

[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 = headers[i];
        worksheet.Cells[1, i + 1].Style.Font.Bold = true;
    }

    // 模拟数据
    var list = new List<ExcelDemoDto>();
    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;
}

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据