c# EPPlus导出,设置单元格格式,设置列标题样式,设置单元格边框,格式化日期时间...

最新推荐文章于 2023-03-01 15:50:55 发布

weixin_30737433

最新推荐文章于 2023-03-01 15:50:55 发布

阅读量5.8k

点赞数 2

文章标签： c#

原文链接：http://www.cnblogs.com/BenPaoWoNiu/p/11535727.html

版权

//数据源DataTable
DataTable dataTable = new DataTable();
//...
//...

ExcelPackage package = new ExcelPackage();

//创建Sheet
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(Sheet1);

//设置单元格数据格式
foreach (DataColumn item in dataTable.Columns)
{
    if (item.DataType == typeof(DateTime))
    {
        var i = dataTable.Columns.IndexOf(item);
        //设置列格式为自定义 "yyyy/MM/dd HH:mm:ss"
        workSheet.Cells[2, i + 1, dataTable.Rows.Count + 1, i + 1].Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";
    }
}

//设置第一行列标题样式
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
    r.Style.Font.Color.SetColor(System.Drawing.Color.White);
    r.Style.Font.Bold = true;
    r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
    r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
}

//设置单元格边框
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

    r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
    r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
    r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
    r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}

//加载DataTable到Excel单元格
workSheet.Cells["A1"].LoadFromDataTable(dataTable, true);

//输出为字节
byte[] result = package.GetAsByteArray();

return result;