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);
}
