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

浅浅的花香味﹌ 2023-08-27 08:36 10862阅读 0赞

//数据源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;

转载于:https://www.cnblogs.com/BenPaoWoNiu/p/11535727.html