一、ExcelPackage概述

做后端开发经常会进行Excel的操作，这里推荐下ExcelPackage。推荐的理由：

1、可以保存为Stream

public ExcelPackage(Stream newStream);

2、可以根据模板生成Excel

public ExcelPackage(FileInfo template, bool useStream);

3、可以直接生成新Excel

public ExcelPackage(FileInfo newFile);

二、ExcelPackage的使用

我常用的方式是生成流文件，直接上传OSS，不用在本地保存一份Excel了。这里介绍下生成流文件的方法

1、普通的Excel

效果图如下：

 

 

 

 

 

 

  

 

先在nuget中安装截图中的组件：

 

代码如下：

using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace TechnologyTemplate.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ExcelPackageController : ControllerBase
    {
        /// <summary>
        /// 创建普通的Excel
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public Task CreateExcel()
        {
            MemoryStream memory = new MemoryStream();
            Random rd = new Random();
            using (var excel = new ExcelPackage(memory))
            {
                var ws = excel.Workbook.Worksheets.Add($"{DateTime.Now:yyyymmdd}普通表格");
                ws.Cells[1, 1].Value = "开户名";//第一行的第一列
                ws.Cells[1, 2].Value = "开户日期";//第一行的第二列，依次类推
                ws.Cells[1, 3].Value = "开户银行";
                ws.Cells[1, 4].Value = "银行账户";
                ws.Cells[1, 5].Value = "金额";
                for (int i = 0; i < 10; i++)
                {
                    ws.Cells[i + 2, 1].Value = $"小明_{i}";
                    ws.Cells[i + 2, 2].Value = DateTime.Now.ToString("yyyymmdd");
                    ws.Cells[i + 2, 3].Value = "工商银行";
                    ws.Cells[i + 2, 4].Value = $"xxxxxxx_{i}";
                    ws.Cells[i + 2, 5].Value = rd.Next(100, 1000);
                }
                ws.Column(1).AutoFit();
                ws.Column(2).AutoFit();
                ws.Column(3).AutoFit();
                ws.Column(4).AutoFit();
                ws.Column(5).AutoFit();
                //生成Excel流，可根据自己的情况进行处理
                MemoryStream ms = new MemoryStream(excel.GetAsByteArray());
                //方便测试，这里直接保存本地               
                FileStream fs = new FileStream($"D:\\test.xlsx", FileMode.Create);
                BinaryWriter bw = new BinaryWriter(fs);
                bw.Write(ms.ToArray());
                bw.Close();
                fs.Close();
                return Task.CompletedTask;
            }
        }
    }
}

2、稍复杂的Excel，跨行、合并

效果图如下：

 

 代码如下：

using Microsoft.AspNetCore.Mvc;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace TechnologyTemplate.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ExcelPackageController : ControllerBase
    {
        /// <summary>
        /// 创建复杂的Excel
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public Task CreateComplexExcel()
        {
            try
            {
                MemoryStream memory = new MemoryStream();
                using (var excel = new ExcelPackage(memory))
                {
                    #region sheet1
                    var sheet1 = excel.Workbook.Worksheets.Add($"sheet1");
                    sheet1.Cells[1, 1].Value = "学校名称";
                    sheet1.Cells[1, 2].Value = "学校地址";
                    sheet1.Cells[1, 3].Value = "班级名称";
                    sheet1.Cells[1, 4].Value = "学生数量";
                    int mergeRowAfter = 2;
                    for (int i = 0; i < 3; i++)
                    {
                        sheet1.Cells[mergeRowAfter, 1].Value = $"学校_{i}";
                        sheet1.Cells[mergeRowAfter, 2].Value = $"xx市{i}号";
                        for (int j = 0; j < 5; j++)
                        {
                            sheet1.Cells[j + mergeRowAfter, 3].Value = $"班级_{j}";
                            sheet1.Cells[j + mergeRowAfter, 4].Value = j + 10;
                        }
                        ExcelRange excelRange1 = sheet1.Cells[mergeRowAfter, 1, mergeRowAfter + 4, 1];
                        ExcelRange excelRange2 = sheet1.Cells[mergeRowAfter, 2, mergeRowAfter + 4, 2];
                        excelRange1.Merge = true;
                        excelRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        excelRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        excelRange2.Merge = true;
                        excelRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        excelRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        mergeRowAfter += 5;
                    }
                    sheet1.Column(1).AutoFit();
                    sheet1.Column(2).AutoFit();
                    sheet1.Column(3).AutoFit();
                    sheet1.Column(4).AutoFit();
                    #endregion
                    //生成Excel流，可根据自己的情况进行处理
                    MemoryStream ms = new MemoryStream(excel.GetAsByteArray());
                    //方便测试，这里直接保存本地               
                    FileStream fs = new FileStream($"D:\\test.xlsx", FileMode.Create);
                    BinaryWriter bw = new BinaryWriter(fs);
                    bw.Write(ms.ToArray());
                    bw.Close();
                    fs.Close();
                    return Task.CompletedTask;
                }
            }
            catch (Exception ex)
            {
                return Task.CompletedTask;
            }
        }
    }
}