[C#] EPPlus套件，产生Excel

一、安装
可于NuGet套件管理员安装EPPlus加入参考

二、建立类别

/// <summary>Excel栏位资料</summary>public class MExcelCell{    /// <summary>栏位内容</summary>    public object Content { get; set; } = = string.Empty;    /// <summary>背景颜色</summary>    public Color BackgroundColor { get; set; } = Color.White;    /// <summary>样式</summary>    public ExcelFillStyle PatternType { get; set; } = ExcelFillStyle.None;    /// <summary>数字格式</summary>    public string NumberFormat { get; set; } = = string.Empty;    /// <summary>公式</summary>    public string Formula { get; set; } = = string.Empty;    /// <summary>文字水平位置</summary>    public ExcelHorizontalAlignment HorAlign { get; set; } = ExcelHorizontalAlignment.General;}

/// <summary>Excel方法</summary>public class CExcelPackage{    /// <summary>起始列</summary>    public int BeginCell { get; set; }    /// <summary>终点列</summary>    public int EndCell { get; set; }    /// <summary>建立Excel</summary>    private ExcelPackage Epackage = new ExcelPackage();    /// <summary>建立第一个Sheet，后方为定义Sheet的名称</summary>    private ExcelWorksheet Sheet;        /// <summary>加入工作表</summary>    public void AddSheet(List<List<MExcelCell>> Table, string SheetName)    {        //建立一个Sheet，后方为定义Sheet的名称        Sheet = Epackage.Workbook.Worksheets.Add(SheetName);        //内文        for (int col = 0; col < Table.Count; col++)        {            for (int row = 0; row < Table[col].Count; row++)            {                int x = col + 1;                int y = row + 1;                if (!string.IsNullOrEmpty(Table[col][row].Formula))                {                    Sheet.Cells[x, y].Formula = Table[col][row].Formula;                }                else                    Sheet.Cells[x, y].Value = Table[col][row].Content;                if (!string.IsNullOrEmpty(Table[col][row].NumberFormat))                    Sheet.Cells[x, y].Style.Numberformat.Format = Table[col][row].NumberFormat;                if (Table[col][row].PatternType != ExcelFillStyle.None)                    Sheet.Cells[x, y].Style.Fill.PatternType = Table[col][row].PatternType;                if (Table[col][row].BackgroundColor != System.Drawing.Color.White)                    Sheet.Cells[x, y].Style.Fill.BackgroundColor.SetColor(Table[col][row].BackgroundColor);                Sheet.Cells[x, y].Style.Border.BorderAround(ExcelBorderStyle.Thin);            }        }        //自动调整栏宽        for (int i = 1; i <= Table[1].Count; i++)        {            Sheet.Column(i).AutoFit();            Sheet.Column(i).Width += 2;        }        //高度设定        for (int i = 3; i <= Table.Count; i++)        {            Sheet.Row(i).Height = 20;        }        //调整边距        decimal inch = 1M / 2.54M;        Sheet.PrinterSettings.TopMargin = inch;//因为EPPlus单位都是英吋        Sheet.PrinterSettings.LeftMargin = inch;        Sheet.PrinterSettings.RightMargin = inch;        Sheet.PrinterSettings.BottomMargin = inch;        //取得起始列        GetBeginCell();        //取得终点列        GetEndCell();    }    /// <summary>合併储存格</summary>    public void MergeColumn(int row1, int col1, int row2, int col2)    {        Sheet.Cells[row1, col1, row2, col2].Merge = true;    }    /// <summary>设定宽度</summary>    public void ColumnWidth(int col, int value)    {        Sheet.Column(col).Width = value;    }    /// <summary>取得起始列</summary>    public void GetBeginCell()    {        string address = Sheet.Dimension.Address;        string[] cells = address.Split(new char[] { ':' });        BeginCell = Int32.Parse(Regex.Replace(cells[0], "[^0-9]", ""));    }    /// <summary>取得终点列</summary>    public void GetEndCell()    {        string address = Sheet.Dimension.Address;        string[] cells = address.Split(new char[] { ':' });        EndCell = Int32.Parse(Regex.Replace(cells[1], "[^0-9]", ""));    }    /// <summary>指定列文字对齐(目前测试只有套用到2列以上才会生效)</summary>    public void Alignment(string range, ExcelHorizontalAlignment HorAlign = ExcelHorizontalAlignment.Center, ExcelVerticalAlignment VerAlign = ExcelVerticalAlignment.Center)    {        Sheet.Cells[range].Style.HorizontalAlignment = HorAlign;        Sheet.Cells[range].Style.VerticalAlignment = VerAlign;    }    /// <summary>汇出物件资料</summary>    public MemoryStream Export()    {        //因为ep.Stream是加密过的串流，故要透过SaveAs将资料写到MemoryStream，        //在将MemoryStream使用FileStreamResult回传到前端。        MemoryStream fileStream = new MemoryStream();        Epackage.SaveAs(fileStream);        Epackage.Dispose();        //如果这边不下Dispose，建议此ep要用using包起来，        //但是要记得先将资料写进MemoryStream在Dispose。        fileStream.Position = 0;        //不重新将位置设为0，excel开启后会出现错误        //经销商审核资料OR店务资料        return fileStream;    }}

三、使用方法
前端HTML

<form id="formName" action="Export" method="post">    <input type="text" name="Content" value="test"></form>

前端JS

$('#formName').attr('action', 'Export').submit();

后端

public ActionResult ExportSalesReport(String Content){    String fileName = "档案名称";    List<List<MExcelCell>> Table = new List<List<MExcelCell>>();     List<MExcelCell> Row = new List<MExcelCell>();    Row.Add(new MExcelCell {        Content = Content,        PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid,        BackgroundColor = System.Drawing.Color.FromArgb(255, 255, 255),    });    Table.Add(Row);    CExcelPackage cExcelPackage = new CExcelPackage();    cExcelPackage.AddSheet(Table, fileName);        return File(cExcelPackage.Export(),        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",        $"{FileName}.xlsx");}