1、添加Epplus的dll文件
2、添加引用
using OfficeOpenXml;
using OfficeOpenXml.Style;
3、使用
DataTable dt = GetData(sql);
if (dt.Rows.Count > 0)
{
ExpExcel(dt, file);
}
private static void ExpExcel(DataTable dt, string strFileName)
{
//所有的操作语句需要放置在下面的using中
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");//添加sheet
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(dt, true);
List<int> list = new List<int>();
for (int i = 0; i < dt.Columns.Count; i++)
{
DataColumn dc = dt.Columns[i];
if (dc.DataType.ToString().ToUpper().Contains("DATETIME"))
{
list.Add(i + 1);
}
}
//Format the row
ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
Color borderColor = Color.FromArgb(155, 155, 155);
using (ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 1, dt.Columns.Count])
{
rng.Style.Font.Name = "宋体";
rng.Style.Font.Size = 10;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));
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);
}
using (ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count])
{
rng.Style.Font.Bold = true;//设置单元格字体加粗
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
}
for (int i = 0; i < list.Count; i++)
{
using (ExcelRange rng = ws.Cells[2, list[i], dt.Rows.Count + 1, list[i]])
{
rng.Style.Numberformat.Format = "yyyy-MM-dd";
}
}
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
pck.SaveAs(fs);
}
}
}