参考：https://zhuanlan.zhihu.com/p/34839707  流的方式

https://blog.csdn.net/qq_34220236/article/details/80841529   URL

两种都是使用Epplus

1.

EPPlus的基本介绍

EPPlus是一个使用Open Office XML(xlsx)文件格式，能读写Excel 2007/2010 文件的开源组件，在导出Excel的时候不需要电脑上安装office。官网地址：http://epplus.codeplex.com/

使用的话直接NuGet上获取对应的dll即可。

但有一点注意，EPPlus不支持2003版本的Excel。

using (ExcelPackage package=new ExcelPackage())
{
   ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");//创建worksheet
   package.Save();
}

　　

单元格赋值
单元格赋值很简单，指定对应的单元格就可以直接赋值，价格遍历循环就可以进行批量的操作了

worksheet.Cells[1, 1].Value = "测试";//直接指定行列数进行赋值
worksheet.Cells["A1"].Value = "赋值";//直接指定单元格进行赋值
设置单元格样式
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.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;//设置列宽
设置单元格格式
worksheet.Cells[1, 1].Style.Numberformat.Format = "#,##0.00";//这是保留两位小数
设置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");//设置背景图片
隐藏sheet
worksheet.Hidden = eWorkSheetHidden.Hidden;//隐藏sheet
worksheet.Column(1).Hidden = true;//隐藏某一列
worksheet.Row(1).Hidden = true;//隐藏某一行
图片操作
有时候需求会将某个图片保存至Excel中，代码如下：

ExcelPicture picture = worksheet.Drawings.AddPicture("picture", Image.FromFile(@"firstbg.jpg"));//插入图片
picture.SetPosition(100, 100);//设置图片的位置
picture.SetSize(100, 100);//设置图片的大小
Excel加密和锁定
有时候导出的Excel不希望别人修改，可对Excel进行加密，代码如下：

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;

　　

实际demo
首先定义一个通用创建ExcelPackage的方法：

/// <summary>
/// 创建ExcelPackage
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="datas">数据实体</param>
/// <param name="columnNames">列名</param>
/// <param name="outOfColumns">排除列</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="title">标题</param>
/// <param name="isProtected">是否加密</param>
/// <returns></returns>
private static ExcelPackage CreateExcelPackage<T>(List<T> datas, Dictionary<string, string> columnNames, List<string> outOfColumns, string sheetName = "Sheet1",string title="",int isProtected=0)
{
var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add(sheetName);
if(isProtected==1)
{
    worksheet.Protection.IsProtected = true;//设置是否进行锁定
    worksheet.Protection.SetPassword("xiangzhidaomimama");//设置密码
    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;
}

var titleRow = 0;
if(!string.IsNullOrWhiteSpace(title))
{
    titleRow = 1;
    worksheet.Cells[1, 1, 1, columnNames.Count()].Merge = true;//合并单元格
    worksheet.Cells[1, 1].Value = title;
    worksheet.Cells.Style.WrapText = true;
    worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
    worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
    worksheet.Row(1).Height = 30;//设置行高
    worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
}

//获取要反射的属性,加载首行
Type myType = typeof(T);
List<PropertyInfo> myPro = new List<PropertyInfo>();
int i = 1;
foreach (string key in columnNames.Keys)
{
    PropertyInfo p = myType.GetProperty(key);
    myPro.Add(p);

    worksheet.Cells[1+ titleRow, i].Value = columnNames[key];
    i++;
}

int row = 2+ titleRow;
foreach (T data in datas)
{
    int column = 1;
    foreach (PropertyInfo p in myPro.Where(info => !outOfColumns.Contains(info.Name)))
    {
        worksheet.Cells[row, column].Value = p == null ? "" : Convert.ToString(p.GetValue(data, null));
        column++;
    }
    row++;
}
return package;
}


然后将ExcelPackage转换成Byte类型，以流的方式进行导出：

public static Byte[] GetByteToExportExcel<T>(List<T> datas, Dictionary<string, string> columnNames, List<string> outOfColumn, string sheetName = "Sheet1",string title="",int isProtected=0)
{
    using (var fs = new MemoryStream())
    {
        using (var package = CreateExcelPackage(datas, columnNames, outOfColumn, sheetName, title, isProtected))
        {
            package.SaveAs(fs);
            return fs.ToArray();
        }
    }
}


最后就可以直接进行导出了：

public async Task<IActionResult> GetExcel(UserModel entity,int isProtected=0)
{
    var result = await ReportServices.GetAttendance(entity);
    var columns = new Dictionary<string, string>() {
        { "Id","序号"},
        { "UserName","用户名"},
        { "Remark","备注"}
    };
    var fs = ExcelHelper.GetByteToExportExcel(result.Collection.ToList(), columns, new List<string>(),"Sheet1","", isProtected);
    return File(fs, "application/vnd.android.package-archive", $"ExcelDemo.xlsx");
}

2.

 private IHostingEnvironment _hostingEnvironment;



        public XlsxController(IHostingEnvironment hostingEnvironment)//名称对应控制器名

        {

            _hostingEnvironment = hostingEnvironment;

        }

 string sWebRootFolder = _hostingEnvironment.WebRootPath;
            string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            fileinfo.Delete();
            using (ExcelPackage package = new ExcelPackage(fileinfo))
            {
                // 添加worksheet

                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName);
                //添加头
                DailySignInfo cell = new DailySignInfo()
                {
                    Date = "日期",
                    Name = "姓名",
                    Attendancetime = "时段",
                    Time = "时间",
                    Status = "状态"

                };
                worksheet.Cells[1, 1].Value = cell.Date;
                worksheet.Cells[1, 2].Value = cell.Name;
                worksheet.Cells[1, 3].Value = cell.dancetime;
                worksheet.Cells[1, 4].Value = cell.Time;
                worksheet.Cells[1, 5].Value = cell.Status;
                //添加值

                int cellnum = 2;
                foreach (var item in query)
                {
                    worksheet.Cells["A" + cellnum].Value = item.date;
                    worksheet.Cells["B" + cellnum].Value = item.realname;
                    worksheet.Cells["C" + cellnum].Value = item.period_name;
                    worksheet.Cells["D" + cellnum].Value = item.create_time;
                    worksheet.Cells["E" + cellnum].Value = item.status_name;

                    cellnum++;
                }
                package.Save();
                string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                return this.File(sFileName, type, sFileName);
            }