NET Core 使用EPPlus导出 - ParallelForEach

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

public async Task<ActionResult> ExportLog()
            //string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Excel";
            //if (!Directory.Exists(sWebRootFolder))
            //    Directory.CreateDirectory(sWebRootFolder);//不存在则创建
            string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            //FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            //fileinfo.Delete();
            var strem = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(strem))
                // 添加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.Attendancetime;
                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+ item.create_time;
                    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["D" + cellnum].Value = item.status_name;
                    cellnum++;
                var arr= package.GetAsByteArray();
                strem.Dispose();
                string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取响应头里面的Content-Disposition
                //return Ok("/Excel/" + sFileName);
                //return this.File(sFileName, type, sFileName);
                return new FileContentResult(arr, type)
                    FileDownloadName = sFileName
 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);
单元格赋值很简单，指定对应的单元格就可以直接赋值，价格遍历循环就可以进行批量的操作了




    

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;
public async Task<ActionResult> ExportLog()
            //string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Excel";
            //if (!Directory.Exists(sWebRootFolder))
            //    Directory.CreateDirectory(sWebRootFolder);//不存在则创建
            string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            //FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            //fileinfo.Delete();
            var strem = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(strem))
                // 添加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.Attendancetime;
                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+ item.create_time;
                    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["D" + cellnum].Value = item.status_name;
                    cellnum++;
                var arr= package.GetAsByteArray();
                strem.Dispose();
                string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取响应头里面的Content-Disposition
                //return Ok("/Excel/" + sFileName);
                //return this.File(sFileName, type, sFileName);
                return new FileContentResult(arr, type)
                    FileDownloadName = sFileName
 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);