个人博客 - 刘佳华

NPOI 以内存方式导出 Excel

发表于 分类于

服务端以内存方式导出 Excel 并设置自动列宽

简介

基于 NPOI/DOCX 帮助类 修改，在服务端导出 Excel 应尽量直接在内存中处理，减少磁盘写入，同时实现设置自动列宽。

代码

帮助类修改

基于帮助类中 FillDataTable 方法修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
/// <summary>
/// 设置自动列宽
/// </summary>
/// <param name="sheet"></param>
/// <param name="cols"></param>
public static void AutoColumnWidth(ISheet sheet, int cols)
{
    for (int col = 0; col <= cols; col++)
    {
        //获取当前列宽度
        int columnWidth = sheet.GetColumnWidth(col) / 256;
        for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
        {
            IRow row = sheet.GetRow(rowIndex);
            ICell cell = row.GetCell(col);
            //获取当前单元格的内容宽度
            int contextLength = Encoding.UTF8.GetBytes(cell.ToString()).Length;
            columnWidth = columnWidth < contextLength ? contextLength : columnWidth;

        }
        sheet.SetColumnWidth(col, columnWidth * 256);
    }
}

/// <summary>
/// 在指定Excel中指定Sheet指定位置填充DataTable
/// 在内存中处理
/// </summary>
/// <param name="strSheetName">需要填充的Sheet名称(如果没有则添加,如果冲突则使用冲突Sheet)</param>
/// <param name="dtSourceData">DataTable数据</param>
/// <param name="WhetherThereFieldName">是否有列名(true保留DataTable字段名)</param>
/// <param name="iRows">起始行</param>
/// <param name="iColumn">起始列</param>
/// <returns>成功返回true,失败返回false</returns>
public static MemoryStream FillDataTableStream(string strSheetName, DataTable dtSourceData, bool WhetherThereFieldName, int iRows, int iColumn)
{
    try
    {
        if (string.IsNullOrEmpty(strSheetName) || dtSourceData.Rows.Count < 1)
        {
            return null;
        }

        IWorkbook iWorkBook = new XSSFWorkbook();
        ISheet iSheet = iWorkBook.CreateSheet(strSheetName);

        if (WhetherThereFieldName)
        {
            IRow rowDataTableField = iSheet.CreateRow(iRows);
            for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
            {
                ICell cellErrstatist = rowDataTableField.CreateCell(iDataTableColumns + iColumn);
                cellErrstatist.SetCellValue(dtSourceData.Columns[iDataTableColumns].ColumnName);
            }
            for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
            {
                IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows + 1);
                for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
                {
                    ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
                    string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
                    Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
                    if (regexIsNumeric.IsMatch(strSourceData))
                    {
                        cellErrstatist.SetCellValue(double.Parse(strSourceData));
                    }
                    else
                    {
                        cellErrstatist.SetCellValue(strSourceData);
                    }
                }

                AutoColumnWidth(iSheet, dtSourceData.Columns.Count - 1);
            }
        }
        else
        {
            for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
            {
                IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows);
                for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
                {
                    ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
                    string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
                    Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
                    if (regexIsNumeric.IsMatch(strSourceData))
                    {
                        cellErrstatist.SetCellValue(double.Parse(strSourceData));
                    }
                    else
                    {
                        cellErrstatist.SetCellValue(strSourceData);
                    }
                }
            }
        }

        MemoryStream stream = new MemoryStream();
        iWorkBook.Write(stream, false);
        iWorkBook.Close();
        return stream;
    }
    catch (Exception ex)
    {
        return null;
    }
}

代码调用

调用使用服务端 .NET 6.0 API

1
2
3
4
5
6
7
8
9
10
11
[HttpGet]
public IActionResult Export()
{
    DataTable dataTable = new DataTable();

    ...

    MemoryStream msExcel = Helper.ExcelHelper.FillDataTableStream("Sheet1", dataTable, true, 0, 0);
    MemoryStream memoryStream = new MemoryStream(msExcel.ToArray());
    return File(memoryStream, "application/octet-stream", Path.GetFileName($"{Guid.NewGuid()}.xlsx"), true);
}