public static void ExportExcelByEPPlus(HttpContext context, DataTable dt, string fileName, string headerName) { using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage()) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "sheet1" : dt.TableName; OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); ws.Cells[2, 1].LoadFromDataTable(dt, true);//从A4的单元格加载datatable中的数据 OfficeOpenXml.Style.ExcelBorderStyle borderStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin; System.Drawing.Color borderColor = System.Drawing.Color.FromArgb(0, 0, 0); using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 2, dt.Columns.Count]) { rng.Style.Font.Name = "宋体"; rng.Style.Font.Size = 11; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; rng.Style.Border.Top.Style = borderStyle; rng.Style.Border.Top.Color.SetColor(borderColor); rng.Style.Border.Bottom.Style = borderStyle; rng.Style.Border.Bottom.Color.SetColor(borderColor); rng.Style.Border.Right.Style = borderStyle; rng.Style.Border.Right.Color.SetColor(borderColor); } //Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count]) //1行1列到1行n列 { rng.Merge = true; //合并单元格 rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0)); rng.Value = headerName; } using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, 2, dt.Columns.Count])//1行1列到1行n列 { rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0)); } //返回到客户端 context.Response.Clear(); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8))); context.Response.ContentEncoding = System.Text.Encoding.UTF8; context.Response.BinaryWrite(pck.GetAsByteArray()); context.Response.Flush(); context.ApplicationInstance.CompleteRequest();//解决捕获的“由于代码已经过优化...”的try catch异常 //HttpContext.Current.Response.End(); } }
//Draw border around range private static void BorderAround(this ExcelRange range, OfficeOpenXml.Style.ExcelBorderStyle style = OfficeOpenXml.Style.ExcelBorderStyle.Thin) { range.Style.Border.BorderAround(style); }
/// <summary> /// Shorthand for setting the border around. /// </summary> private static void SetBorderAround(this ExcelRange excelRange, OfficeOpenXml.Style.ExcelBorderStyle borderStyle, Color borderColor) { excelRange.Style.Border.BorderAround(borderStyle, borderColor); }
//Draw all borders in range private static void BorderEverywhere(this ExcelRange range, OfficeOpenXml.Style.ExcelBorderStyle style = OfficeOpenXml.Style.ExcelBorderStyle.Thin) { range.Style.Border.Top.Style = range.Style.Border.Left.Style = range.Style.Border.Bottom.Style = range.Style.Border.Right.Style = style; }
public void BorderRange(int RowStart, int ColStart, int RowEnd, int ColEnd, OfficeOpenXml.Style.ExcelBorderStyle style = OfficeOpenXml.Style.ExcelBorderStyle.Thin) { var range = worksheet.Cells[RowStart, ColStart, RowEnd, ColEnd]; range.Style.Border.BorderAround(style); }