通过使用 epplus 来动态生成 excel 表单

Summary:

  • 本文旨在说明通过建立本地模型类，通过 EPPlus，来统一生成 excel，模型类见下方代码块。
    • 适合需要动态生成整个 excel 表单的业务。
    • 如果无需动态生成，则可以直接给出物理excel文件硬编码即可。
  • 因涉及到公司业务，这里仅给出和业务无关部分代码。

环境说明：

示例

DownLoadExcel api

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
[HttpGet]
[Route("DownloadSpreadSheet/{dataId}")]
[Authorize]
public async Task<IActionResult> DownloadSpreadSheet([Required] int dataId)
{
    //格式化后的数据，行列均在结果集合中，模型见下方 `ExcelDataVO`
    var excelDataList = await _someService_.GetFormatedSpreadSheetDataList(id);

    using (var excel = new ExcelPackage())
    {
        var sheet = excel.Workbook.Worksheets.Add("YOUR WORK SHEET NAME");

        foreach (var excelData in excelDataList)
        {
            var currentCell = sheet.Cells[excelData.Row, excelData.Column];
            currentCell.Value = excelData.IsHeaderRow
                                                                    ? excelData.HeaderName
                                                                    : excelData.Value;
            if (excelData.IsMergeCells)
            {
                //合并单元格
                sheet.Cells[excelData.Row, excelData.MergeCellColumnFromTo.Key, excelData.Row, excelData.MergeCellColumnFromTo.Value].Merge = true;
            }

            if (excelData.CellStyle.IsSetStyle)
            {
                if (excelData.CellStyle.BackgroundColor != Color.Empty)
                {
                    //设置单元格背景色的话，需要先设置 PatternType，否则会提示错误。
                    currentCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    currentCell.Style.Fill.BackgroundColor.SetColor(excelData.CellStyle.BackgroundColor);
                }
                if (excelData.CellStyle.Width > 0)
                {
                    //设置列的宽度：需要说明的是 EPPLUS 的自适配指的内容适配列的宽度，而不是列宽适配内容宽度。
                    sheet.Column(excelData.Column).Width = excelData.CellStyle.Width;
                }
                if (excelData.CellStyle.IsJustifyCenter)
                {
                    //单元格内容居中
                    currentCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
            }
        }
        var lastHeaderColumn = excelDataList.Where(ent => ent.IsHeaderRow).OrderBy(ent => ent.Column).LastOrDefault();

        //需要注意下，`自动过滤`功能无法针对单个单元格来进行设置（单独设置的话，仅对最后一个单元格有效），这里需要通过指定行列的方式来设置单元格自动过滤。
        sheet.Cells[lastHeaderColumn.Row, 1, lastHeaderColumn.Row, lastHeaderColumn.Column].AutoFilter = true;

        byte[] fileBytes = excel.GetAsByteArray();
        return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet,  "EXCEL_NAME.xlsx");
    }
}

ExcelDataVO.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public class ExcelDataVO
{
    public int Row { get; set; }

    public int Column { get; set; }

    public string HeaderName { get; set; }

    public string ParentHeaderName { get; set; }
    public string ParentValue { get; set; }

    public string Value { get; set; }

    public bool IsMergeCells { get; set; }

    public string RawDataProp { get; set; }

    /// <summary>
    /// key= column number starting from, value=column number ending to
    /// </summary>
    public KeyValuePair<int, int> MergeCellColumnFromTo { get; set; }

    public bool IsHeaderRow { get; set; }

    public FlowPlanExcelCellType FlowPlanExcelCellType { get; set; }

    public ExcelCellStyleVO CellStyle { get; set; }
}

FlowPlanExcelCellType.cs

1
2
3
4
5
6
public enum FlowPlanExcelCellType
{
    Normal = 0,
    Wave = 1,
    AutoCalculate = 2
}

ExcelCellStyleVO.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public struct ExcelCellStyleVO
{
    public ExcelCellStyleVO(Color backgroundColor, string widthStr, bool isJustifyCenter = false)
    {
        BackgroundColor = backgroundColor;
        Double.TryParse(widthStr?.Replace("px", string.Empty, StringComparison.OrdinalIgnoreCase).Trim(), out var width);
        Width = width / 10; // ratio:convert  px(used in FPlan) to widh(used in excel)
        IsJustifyCenter = isJustifyCenter;
        IsSetStyle = true;
    }

    /// <summary>
    /// 
    /// </summary>
    public Color BackgroundColor { get; set; }

    public double Width { get; set; }

    public bool IsSetStyle { get; private set; }

    public bool IsJustifyCenter { get; set; }
}

EPPLUS 的一些额外设置

保存到本地磁盘位置

1
2
3
4
5
6
7
8
9
10
11
 FileInfo newFile = new FileInfo(@"x:\test.xlsx");
if (newFile.Exists)
{
    newFile.Delete();
    newFile = new FileInfo(@"x:\test.xlsx");
}
using (ExcelPackage package=new ExcelPackage(new FileInfo(@"X:\test.xlsx")))
{ 
     var worksheet = package.Workbook.Worksheets.Add("sheet_1");//创建worksheet
     package.Save();//保存excel
}

添加数据

1
2
worksheet.Cells[1, 1].Value = "名称";//通过指定行列数
worksheet.Cells["A1"].Value = "名称";//通过指定单元格名

使用公式

1
2
worksheet.Cells["D2:D5"].Formula = "B2*C2";//这是乘法的公式，意思是第二列乘以第三列的值赋值给第四列，这种方法比较简单明了
worksheet.Cells[6, 2, 6, 4].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2, 2, 5, 2).Address);//这是自动求和的方法，至于subtotal的用法你需要自己去了解了

设置样式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
//设置单元格格式
worksheet.Cells[5, 3].Style.Numberformat.Format = "#,##0.00";//这是保留两位小数

//设置对齐方式
worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
worksheet.Cells[1, 4, 1, 5].Merge = true;//合并单元格
worksheet.Cells.Style.WrapText = true;//自动换行

//设置单元格字体样式
worksheet.Cells[1, 1].Style.Font.Bold = true;//字体为粗体
worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White);//字体颜色
worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑";//字体
worksheet.Cells[1, 1].Style.Font.Size = 12;//字体大小

//设置单元格背景样式
worksheet.Cells[1, 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色

//设置单元格边框，两种方法
worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//单独设置单元格底部边框样式和颜色（上下左右均可分开设置）
worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191));

//设置单元格的行高和列宽
worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
worksheet.Row(1).Height = 15;//设置行高
worksheet.Row(1).CustomHeight = true;//自动调整行高
worksheet.Column(1).Width = 15;//设置列宽

//设置sheet背景
worksheet.View.ShowGridLines = false;//去掉sheet的网格线
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid;
worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray);//设置背景色
worksheet.BackgroundImage.Image = Image.FromFile(@"firstbg.jpg");//设置背景图片

插入图片和形状

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//插入图片
ExcelPicture picture = worksheet.Drawings.AddPicture("logo", Image.FromFile(@"firstbg.jpg"));//插入图片
picture.SetPosition(100, 100);//设置图片的位置
picture.SetSize(100, 100);//设置图片的大小


//插入形状
ExcelShape shape = worksheet.Drawings.AddShape("shape", eShapeStyle.Rect);//插入形状
shape.Font.Color = Color.Red;//设置形状的字体颜色
shape.Font.Size = 15;//字体大小
shape.Font.Bold = true;//字体粗细
shape.Fill.Style = eFillStyle.NoFill;//设置形状的填充样式
shape.Border.Fill.Style = eFillStyle.NoFill;//边框样式
shape.SetPosition(200, 300);//形状的位置
shape.SetSize(80, 30);//形状的大小
shape.Text = "test";//形状的内容

添加超链接

1
2
3
4
5
//给图片加超链接
ExcelPicture picture = worksheet.Drawings.AddPicture("logo", Image.FromFile(@"firstbg.jpg"), new ExcelHyperLink("http:\\www.baidu.com", UriKind.Relative));

//给单元格加超链接
worksheet.Cells[1, 1].Hyperlink = new ExcelHyperLink("http:\\www.baidu.com", UriKind.Relative);

隐藏sheet

1
2
3
worksheet.Hidden = eWorkSheetHidden.Hidden;//隐藏sheet
worksheet.Column(1).Hidden = true;//隐藏某一列
worksheet.Row(1).Hidden = true;//隐藏某一行

图标设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//创建图表
ExcelChart chart = worksheet.Drawings.AddChart("chart", eChartType.ColumnClustered);//eChartType中可以选择图表类型

//选择数据
//chart.Series.Add()方法所需参数为：chart.Series.Add(Y轴数据区,X轴数据区) 
ExcelChartSerie serie = chart.Series.Add(worksheet.Cells[2, 3, 5, 3], worksheet.Cells[2, 1, 5, 1]);//设置图表的x轴和y轴
serie.HeaderAddress = worksheet.Cells[1, 3];//设置图表的图例

//设置图标样式
chart.SetPosition(150, 10);//设置位置
chart.SetSize(500, 300);//设置大小
chart.Title.Text = "销量走势";//设置图表的标题
chart.Title.Font.Color = Color.FromArgb(89, 89, 89);//设置标题的颜色
chart.Title.Font.Size = 15;//标题的大小
chart.Title.Font.Bold = true;//标题的粗体
chart.Style = eChartStyle.Style15;//设置图表的样式
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Color = Color.FromArgb(217, 217, 217);//设置图例的样式

嵌入VBA代码

1
2
3
//首先将vba代码保存成txt文本格式，然后用epplus去调用这个txt文本文件即可
worksheet.CodeModule.Name = "sheet" ;
worksheet.CodeModule.Code = File.ReadAllText(@"VBA-Code\vba.txt"), Encoding.Default);

Excel加密和锁定

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
worksheet.Protection.IsProtected = true;//设置是否进行锁定
worksheet.Protection.SetPassword("yk");//设置密码
worksheet.Protection.AllowAutoFilter = false;//下面是一些锁定时权限的设置
worksheet.Protection.AllowDeleteColumns = false;
worksheet.Protection.AllowDeleteRows = false;
worksheet.Protection.AllowEditScenarios = false;
worksheet.Protection.AllowEditObject = false;
worksheet.Protection.AllowFormatCells = false;
worksheet.Protection.AllowFormatColumns = false;
worksheet.Protection.AllowFormatRows = false;
worksheet.Protection.AllowInsertColumns = false;
worksheet.Protection.AllowInsertHyperlinks = false;
worksheet.Protection.AllowInsertRows = false;
worksheet.Protection.AllowPivotTables = false;
worksheet.Protection.AllowSelectLockedCells = false;
worksheet.Protection.AllowSelectUnlockedCells = false;
worksheet.Protection.AllowSort = false;

Excel本身的一些属性设置

1
2
3
4
epk.Workbook.Properties.Title = "inventory";//设置excel的标题
epk.Workbook.Properties.Author = "mei";//作者
epk.Workbook.Properties.Comments = "this is a test";//备注
epk.Workbook.Properties.Company = "ABC";//公司

下拉框

1
2
3
4
5
//设置下拉框时首先需要设置下拉框显示的数据区域块并将其命名。
var val = worksheet.DataValidations.AddListValidation(worksheet.Cells[7, 8].Address);//设置下拉框显示的数据区域
val.Formula.ExcelFormula = "=parameter";//数据区域的名称
val.Prompt = "下拉选择参数";//下拉提示
val.ShowInputMessage = true;//显示提示内容

冻结单元格

1
2
//冻结行的下一行，列设置为1，否则会冻结行*列导致无法滚动 x 轴
sheet.View.FreezePanes(headerRowIndex + 1, 1);

参考信息：https://www.cnblogs.com/rumeng/p/3785748.html