c#导出excel文件，并对excel格式美化

前言

没有进行格式化导出的excel文件内容不是很看，对观看的人还需要进行修改，加大了工作量，因此使用导出时对excel格式就进行优化

---

优化代码

1 数据准备

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

public dynamic ExportLogisticsOrderExcel()
{
    var ds = SqlDapperHelper.ExecuteDataSet("Sql语句", new {参数名=参数}, HttpHelper.OpenID());
    DataTable dt = ds.Tables["1"];

    // 创建一个新的 DataTable 以保存选定列的数据
    DataTable selectedColumnsData = new DataTable();

    // 添加需要的列到新 DataTable
    string[] fixedColumns = new string[] { "名称", "规格", "详情" };
    foreach (string col in fixedColumns)
    {
        selectedColumnsData.Columns.Add(col, typeof(string));
    }

    // 添加每天的列到新 DataTable
    for (int i = 1; i <= 31; i++)
    {
        selectedColumnsData.Columns.Add(i + "日", typeof(string));
    }

    // 遍历原始 DataTable，选择指定列的数据
    foreach (DataRow row in dt.Rows)
    {
        // 从原始 DataTable 中获取指定列的数据
        string Name = row["Name"].ToString();
        string Novmg = row["Novmg"].ToString();
        string[] SalesInfo = row["SalesInfo"].ToString().Split(new string[] { "," }, StringSplitOptions.None);

        selectedColumnsData.Rows.Add(Name, Novmg, "生成数据", SalesInfo[0], SalesInfo[1], SalesInfo[2],
            SalesInfo[3], SalesInfo[4], SalesInfo[5], SalesInfo[6], SalesInfo[7], SalesInfo[8], SalesInfo[9], SalesInfo[10],
            SalesInfo[11], SalesInfo[12], SalesInfo[13], SalesInfo[14], SalesInfo[15], SalesInfo[16], SalesInfo[17], SalesInfo[18],
            SalesInfo[19], SalesInfo[20], SalesInfo[21], SalesInfo[22], SalesInfo[23], SalesInfo[24], SalesInfo[25], SalesInfo[26],
            SalesInfo[27], SalesInfo[28], SalesInfo[29], SalesInfo[30]);

        string[] OrderQuantityInfo = row["OrderQuantityInfo"].ToString().Split(new string[] { "," }, StringSplitOptions.None);

        selectedColumnsData.Rows.Add(Name, Novmg, "使用数据", OrderQuantityInfo[0], OrderQuantityInfo[1], OrderQuantityInfo[2],
            OrderQuantityInfo[3], OrderQuantityInfo[4], OrderQuantityInfo[5], OrderQuantityInfo[6], OrderQuantityInfo[7], OrderQuantityInfo[8],
            OrderQuantityInfo[9], OrderQuantityInfo[10], OrderQuantityInfo[11], OrderQuantityInfo[12], OrderQuantityInfo[13], OrderQuantityInfo[14],
            OrderQuantityInfo[15], OrderQuantityInfo[16], OrderQuantityInfo[17], OrderQuantityInfo[18], OrderQuantityInfo[19], OrderQuantityInfo[20],
            OrderQuantityInfo[21], OrderQuantityInfo[22], OrderQuantityInfo[23], OrderQuantityInfo[24], OrderQuantityInfo[25], OrderQuantityInfo[26],
            OrderQuantityInfo[27], OrderQuantityInfo[28], OrderQuantityInfo[29], OrderQuantityInfo[30]);
    }

    var res = NPOIExtension.ExportLogisticsOrderExcel(selectedColumnsData);

    return res;
}

2 excel格式美化

/// <summary>
/// 合并单元格 美化格式
/// </summary>
/// <param name="sheet"></param>
/// <param name="c"></param>
/// <returns></returns>
public static dynamic SheetAddMergedRegion(ISheet sheet, int c)
{
    c = c - 1;
    for (int i = 1; i <= c; i += 2)
    {
        // 合并相同数据的行为一个单元格
        for (int j = 0; j < 4; j++)
        {
            CellRangeAddress cellRange = new CellRangeAddress(i, i + 1, j, j);
            sheet.AddMergedRegion(cellRange);
        }
    }

    // 获取工作表的行数和列数
    int rowCount = sheet.LastRowNum + 1;
    int colCount = 0;
    for (int i = 0; i < rowCount; i++)
    {
        colCount = Math.Max(colCount, sheet.GetRow(i)?.LastCellNum ?? 0);
    }

    // 设置单元格样式为居中
    ICellStyle centerStyle = sheet.Workbook.CreateCellStyle();
    centerStyle.Alignment = HorizontalAlignment.Center;
    centerStyle.VerticalAlignment = VerticalAlignment.Center;

    // 遍历每个单元格并应用居中样式
    for (int i = 0; i < rowCount; i++)
    {
        IRow row = sheet.GetRow(i);
        if (row != null)
        {
            for (int j = 0; j < colCount; j++)
            {
                ICell cell = row.GetCell(j);
                if (cell == null)
                {
                    cell = row.CreateCell(j);
                }
                cell.CellStyle = centerStyle;
                // 设置单元格宽度（列宽）
                int columnWidth = 30; // 以字符数为单位
                if (j > 1) { columnWidth = 12; }
                sheet.SetColumnWidth(j, columnWidth * 256); // 设置第一列的宽度
            }
            row.Height = 400;
        }
    }
    return sheet;
}

3 数据保存

public static dynamic ExportLogisticsOrderExcel(DataTable dataTable)
{
    IWorkbook workbook = new HSSFWorkbook();

    ISheet sheet = workbook.CreateSheet("sheet表名称");

    DataRow headerRow = dataTable.Rows[0];
    IRow row = sheet.CreateRow(0);
    for (int i = 0; i < headerRow.ItemArray.Length; i++)
    {
        ICell cell = row.CreateCell(i);
        cell.SetCellValue(headerRow.Table.Columns[i].ColumnName);
    }

    // 写入数据
    for (int i = 0; i < dataTable.Rows.Count; i++)
    {
        DataRow dataRow = dataTable.Rows[i];
        IRow rowData = sheet.CreateRow(i + 1);

        for (int j = 0; j < dataRow.ItemArray.Length; j++)
        {
            ICell cell = rowData.CreateCell(j);
            cell.SetCellValue(dataRow[j].ToString());
        }
    }


    SheetAddMergedRegion(sheet, dataTable.Rows.Count);

    string outputPath = "输出目录地址";
    // 创建文件夹
    if (!Directory.Exists(outputPath))
    {
        Directory.CreateDirectory(outputPath);
    }
    // 保存文件
    string filePath = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
    using (FileStream fileStream = new FileStream(outputPath + filePath, FileMode.Create, FileAccess.Write))
    {
        workbook.Write(fileStream);
    }
    return filePath;
}

---

总结

c#代码实现excel表数据格式化后输出的文件美观使用者也方便。

---