/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <param name="headerCell">Excel表头</param> /// <param name="table">源DataTable</param> /// <param name="savePath">保存路径</param> /// <param name="action">sheet自定义处理委托</param> public static void EPPlusExportExcelToFile(ExcelHeaderCell headerCell, DataTable table, string savePath, Action <ExcelWorksheet> action = null) { if (table?.Rows.Count > 0) { using (var package = new ExcelPackage(new FileInfo(savePath))) { using (var sheet = package.Workbook.Worksheets.Add(table.TableName.IsNullOrEmpty() ? "Sheet1" : table.TableName)) { //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromDataTable(table, false); //单独设置单元格 action?.Invoke(sheet); package.Save(); } } } }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="headerCell">Excel表头</param> /// <param name="list">源泛型集合</param> /// <param name="savePath">保存路径</param> /// <param name="action">sheet自定义处理委托</param> public static void EPPlusExportExcelToFile <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, string savePath, Action <ExcelWorksheet> action = null) where T : class, new() { if (list?.Count() > 0) { using (var package = new ExcelPackage(new FileInfo(savePath))) { using (var sheet = package.Workbook.Worksheets.Add("Sheet1")) { //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false); //单独设置单元格 action?.Invoke(sheet); package.Save(); } } } }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="headerCell">Excel表头</param> /// <param name="list">源泛型集合</param> /// <param name="fileName">文件名</param> /// <param name="ext">扩展名(.xls|.xlsx)可选参数</param> /// <param name="responseEnd">是否输出结束,默认:是</param> /// <param name="action">sheet自定义处理委托</param> public static void EPPlusExportExcel <T>(ExcelHeaderCell headerCell, List <T> list, string fileName, string ext = ".xlsx", bool responseEnd = true, Action <ExcelWorksheet> action = null) where T : class, new() { if (list?.Count > 0) { try { using (var package = new ExcelPackage()) { //配置文件属性 package.Workbook.Properties.Category = "类别"; package.Workbook.Properties.Author = "作者"; package.Workbook.Properties.Comments = "备注"; package.Workbook.Properties.Company = "公司名称"; package.Workbook.Properties.Keywords = "关键字"; package.Workbook.Properties.Manager = "张强"; package.Workbook.Properties.Status = "内容状态"; package.Workbook.Properties.Subject = "主题"; package.Workbook.Properties.Title = "标题"; package.Workbook.Properties.LastModifiedBy = "最后一次保存者"; using (var sheet = package.Workbook.Worksheets.Add("Sheet1")) { //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false); //单独设置单元格 action?.Invoke(sheet); //写到客户端(下载) HttpContext.Current.Response.Clear(); HttpContext.Current.Response.Charset = "utf-8"; HttpContext.Current.Response.AddHeader("content-disposition", $"attachment;filename={HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8)}"); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("utf-8"); HttpContext.Current.Response.BinaryWrite(package.GetAsByteArray()); HttpContext.Current.Response.Flush(); if (responseEnd) { HttpContext.Current.Response.End(); } } } } catch (Exception ex) { LogHelper.Error(ex, "EPPlus导出Excel(2003/2007)"); } } }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="headerCell">Excel表头</param> /// <param name="list">源泛型集合</param> /// <param name="action">sheet自定义处理委托</param> public static byte[] EPPlusExportExcelToBytes <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, Action <ExcelWorksheet> action = null) where T : class, new() { if (list.IsNullOrEmpty()) { return(null); } using var package = new ExcelPackage(); using var sheet = package.Workbook.Worksheets.Add("Sheet1"); //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false); //单独设置单元格 action?.Invoke(sheet); return(package.GetAsByteArray()); }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <param name="headerCell">Excel表头</param> /// <param name="table">源DataTable</param> /// <param name="action">sheet自定义处理委托</param> /// <returns></returns> public static byte[] EPPlusExportExcelToBytes(ExcelHeaderCell headerCell, DataTable table, Action <ExcelWorksheet> action = null) { if (table?.Rows == null || table.Rows.Count == 0) { return(null); } using var package = new ExcelPackage(); using var sheet = package.Workbook.Worksheets.Add(table.TableName.IsNullOrEmpty() ? "Sheet1" : table.TableName); //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromDataTable(table, false); //单独设置单元格 action?.Invoke(sheet); return(package.GetAsByteArray()); }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="headerCell">Excel表头</param> /// <param name="list">源泛型集合</param> /// <param name="fileName">文件名</param> /// <param name="ext">扩展名(.xls|.xlsx)可选参数</param> /// <param name="action">sheet自定义处理委托</param> public static async Task EPPlusExportExcelAsync <T>(ExcelHeaderCell headerCell, IEnumerable <T> list, string fileName, string ext = ".xlsx", Action <ExcelWorksheet> action = null) where T : class, new() { var bytes = EPPlusExportExcelToBytes(headerCell, list, action); if (bytes != null) { await FileHelper.GetFileAsync(bytes, HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8), "application/ms-excel"); } }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <param name="headerCell">Excel表头</param> /// <param name="table">源DataTable</param> /// <param name="fileName">文件名</param> /// <param name="ext">扩展名(.xls|.xlsx)可选参数</param> /// <param name="action">sheet自定义处理委托</param> public static async Task EPPlusExportExcelAsync(ExcelHeaderCell headerCell, DataTable table, string fileName, string ext = ".xlsx", Action <ExcelWorksheet> action = null) { //获取bytes var bytes = EPPlusExportExcelToBytes(headerCell, table, action); if (bytes != null) { await FileHelper.GetFileAsync(bytes, HttpUtility.UrlEncode(fileName + ext, Encoding.UTF8), "application/ms-excel"); } }
/// <summary> /// 构建Excel表头 /// </summary> /// <param name="prevCell">前一个单元格</param> /// <param name="parentSell">父级单元格</param> /// <param name="currCell">当前单元格</param> /// <param name="sheet">Excel WorkSheet</param> public static void BuildExcelHeader(ExcelHeaderCell prevCell, ExcelHeaderCell parentSell, ExcelHeaderCell currCell, ExcelWorksheet sheet) { currCell.FromRow = prevCell != null ? prevCell.FromRow : (parentSell != null ? parentSell.FromRow + 1 : 1); currCell.FromCol = prevCell != null ? prevCell.ToCol + 1 : (parentSell != null ? parentSell.FromCol : 1); currCell.ToRow = currCell.FromRow; currCell.ToCol = currCell.FromCol; if (currCell.IsRowspan) { currCell.ToRow = currCell.FromRow - 1 + currCell.Rowspan; } if (currCell.IsColspan) { currCell.ToCol = currCell.FromCol - 1 + currCell.Colspan; } var sell = sheet.Cells[currCell.FromRow, currCell.FromCol, currCell.ToRow, currCell.ToCol]; //设置单元格属性 sell.Value = currCell.Title; sell.Style.Font.Bold = currCell.Bold; sell.Style.Font.Color.SetColor(currCell.FontColor); if (currCell.HorizontalAlignment != null) { sell.Style.HorizontalAlignment = currCell.HorizontalAlignment.Value; } if (currCell.VerticalAlignment != null) { sell.Style.VerticalAlignment = currCell.VerticalAlignment.Value; } if (currCell.BackgroundColor != null) { sell.Style.Fill.SetBackground(currCell.BackgroundColor.Value); } //合并单元格 if (currCell.IsColspan || currCell.IsRowspan) { sheet.Cells[currCell.FromRow, currCell.FromCol, currCell.ToRow, currCell.ToCol].Merge = true; } //判断是否有子元素,递归调用 if (currCell.ChildHeaderCells?.Count > 0) { foreach (var item in currCell.ChildHeaderCells) { var index = currCell.ChildHeaderCells.IndexOf(item); BuildExcelHeader(index == 0 ? null : currCell.ChildHeaderCells[index - 1], currCell, item, sheet); } } }
/// <summary> /// EPPlus导出Excel(2003/2007) /// </summary> /// <typeparam name="T">泛型类型</typeparam> /// <param name="headerCell">Excel表头</param> /// <param name="list">源泛型集合</param> /// <param name="savePath">保存路径</param> /// <param name="action">sheet自定义处理委托</param> public static void EPPlusExportExcelToFile <T>(ExcelHeaderCell headerCell, List <T> list, string savePath, Action <ExcelWorksheet> action = null) where T : class, new() { if (list?.Count > 0) { try { using (var package = new ExcelPackage(new FileInfo(savePath))) { //配置文件属性 package.Workbook.Properties.Category = "类别"; package.Workbook.Properties.Author = "作者"; package.Workbook.Properties.Comments = "备注"; package.Workbook.Properties.Company = "公司名称"; package.Workbook.Properties.Keywords = "关键字"; package.Workbook.Properties.Manager = "张强"; package.Workbook.Properties.Status = "内容状态"; package.Workbook.Properties.Subject = "主题"; package.Workbook.Properties.Title = "标题"; package.Workbook.Properties.LastModifiedBy = "最后一次保存者"; using (var sheet = package.Workbook.Worksheets.Add("Sheet1")) { //设置边框样式 sheet.Cells.Style.Border.Top.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Right.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells.Style.Border.Left.Style = ExcelBorderStyle.Thin; //水平居中 sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; //垂直居中 sheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //单元格自动适应大小 sheet.Cells.AutoFitColumns(); //构建表头 BuildExcelHeader(null, null, headerCell, sheet); //加载数据 var firstCell = headerCell.ChildHeaderCells.FirstOrDefault(); sheet.Cells[firstCell.ToRow + 1, firstCell.ToCol].LoadFromCollection(list, false); //单独设置单元格 action?.Invoke(sheet); package.Save(); } } } catch (Exception ex) { LogHelper.Error(ex, "EPPlus导出Excel(2003/2007)"); } } }