Excel数据导出小记

前言

最近经历了一次数据量比较大的导出，也做了各种优化尝试，这里稍记录一下

一、DataTable =>EXCEL

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using DataTable = System.Data.DataTable;
using Microsoft.Office.Interop.Excel;

namespace ClassLibrary
{
    public class ExcelHelper
    {
        /// <summary>
        /// dt输出到新的Excel
        /// </summary>
        public static void OutPut(DataTable dt,string Path, string Filename)
        {
            // 初始化 Excel 应用程序
            Application excelApp = new Application();
            Workbook workbook = null;
            Worksheet worksheet = null;
            try
            {
                // 设置可见性和默认路径
                excelApp.Visible = false; // 隐藏应用程序窗口
                if (!Directory.Exists(Path)) // 验证文件夹是否存在
                {
                    Directory.CreateDirectory(Path); // 若不存在，则创建新文件夹
                }
                // 添加一个新的工作簿
                workbook = excelApp.Workbooks.Add();
                // 获取第一个工作表
                worksheet = (Worksheet)workbook.Sheets[1];
                //列名
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                }
                int rows = 2;//当前行
                             // 获取 DataTable 列数和行数
                int rowCount = dt.Rows.Count;
                int colCount = dt.Columns.Count;

                // 构建数据部分的二维数组
                if (dt.Rows.Count > 0)
                {
                    object[,] dataValues = new object[rowCount, colCount];
                    for (int r = 0; r < rowCount; r++)
                    {
                        for (int c = 0; c < colCount; c++)
                        {
                            dataValues[r, c] = dt.Rows[r][c];
                        }
                    }
                    // 定义目标范围（从 (2, 2) 开始）
                    Range startCell = (Range)worksheet.Cells[2, 1]; // 起始单元格
                    Range endCell = (Range)worksheet.Cells[2 + rowCount - 1, 1 + colCount - 1]; // 结束单元格
                    Range targetRange = worksheet.get_Range(startCell, endCell);
                    // 写入数据
                    targetRange.Value = dataValues;
                    // 自动调整列宽
                    worksheet.Columns.AutoFit();
                }
                //宽度自适应
                workbook.SaveAs(Filename);
                workbook.Close(false);
            }
            catch (Exception ex)
            {
                return;
            }
            finally
            {
                if (worksheet != null) Marshal.ReleaseComObject(worksheet);
                if (workbook != null) Marshal.ReleaseComObject(workbook);

                if (excelApp != null)
                {
                    excelApp.Quit();
                    Marshal.ReleaseComObject(excelApp);
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}

.netframework4.8,支持的Microsoft.Office.Interop.Excel。
将DataTable转换为二维数组，划定范围，一次性塞进去。
比逐单元格赋值效率更高

二、DBReader =>Excel （NPOI）

using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using System.Data;
using System.Data.Common;
public class ExcelHelper
{
    public static async Task<bool> OutPut(DbDataReader reader, string filePath)
    {
        try
        {
            const int maxRowsPerSheet = 1_000_000;  // 每个Sheet最大行数
            const int bufferSize = 1000;            // 行缓存大小（优化内存）
            // 初始化流式工作簿（关键内存优化）
            using var workbook = new SXSSFWorkbook(null, bufferSize, true);
            int sheetIndex = 1;
            ISheet currentSheet = workbook.CreateSheet($"Sheet_{sheetIndex}");
            // 创建标题行
            IRow headerRow = currentSheet.CreateRow(0);
            for (int i = 0; i < reader.FieldCount; i++)
            {
                headerRow.CreateCell(i).SetCellValue(reader.GetName(i));
            }
            int rowIndex = 1;  // 数据行从1开始（0是标题行）
            while (reader.Read())
            {
                // 达到最大行数时切换Sheet
                if (rowIndex >= maxRowsPerSheet)
                {
                    // 正确调用无参数FlushRows（NPOI 2.7.3+）
                    ((SXSSFSheet)currentSheet).FlushRows();  // 刷新当前Sheet[^1]

                    sheetIndex++;
                    currentSheet = workbook.CreateSheet($"Sheet_{sheetIndex}");
                    rowIndex = 1;  // 新Sheet重置行索引
                }

                // 创建数据行
                IRow dataRow = currentSheet.CreateRow(rowIndex);

                // 写入所有列数据（类型安全处理）
                for (int col = 0; col < reader.FieldCount; col++)
                {
                    var cell = dataRow.CreateCell(col);

                    // 根据数据类型安全写入
                    if (reader.IsDBNull(col))
                    {
                        cell.SetCellValue((string)null);
                    }
                    else
                    {
                        switch (Type.GetTypeCode(reader.GetFieldType(col)))
                        {
                            case TypeCode.String:
                                cell.SetCellValue(reader.GetString(col));
                                break;
                            case TypeCode.DateTime:
                                cell.SetCellValue(reader.GetDateTime(col));
                                break;
                            case TypeCode.Int16:
                                cell.SetCellValue(reader.GetInt16(col));
                                break;
                            case TypeCode.Int32:
                                cell.SetCellValue(reader.GetInt32(col));
                                break;
                            case TypeCode.Int64:
                                cell.SetCellValue(reader.GetInt64(col));
                                break;
                            // case TypeCode.Decimal:
                            // cell.SetCellValue(reader.GetDecimal(col));
                            // break;
                            case TypeCode.Double:
                                cell.SetCellValue(reader.GetDouble(col));
                                break;
                            case TypeCode.Single:
                                cell.SetCellValue(reader.GetDouble(col));
                                break;
                            case TypeCode.Boolean:
                                cell.SetCellValue(reader.GetBoolean(col));
                                break;
                            default:
                                cell.SetCellValue(reader.GetValue(col).ToString());
                                break;
                        }
                    }
                }
                rowIndex++;
            }
            // 最终写入文件（使用异步提升性能）
            using var fs = new FileStream(filePath, FileMode.Create, FileAccess.Write, FileShare.None,81920, true);

            // 分块写入策略
            var writeTask = Task.Run(() => workbook.Write(fs, false));

            // 进度刷新控制
            while (!writeTask.IsCompleted)
            {
                if (fs.Position % (10 * bufferSize) == 0)
                {
                    await fs.FlushAsync(); // 异步刷新缓冲区
                }
                await Task.Delay(50); // 减少CPU占用
            }
            await writeTask;
            return true;
        }
        catch (Exception ex)
        {
            return false;
        }
    }
}

using Microsoft.Data.SqlClient;
using (DbDataReader reader = await cmd.ExecuteReaderAsync())
{
    return  await ExcelHelper.OutPut(reader, extra);
}
//查询数据后直接塞进去就行

DBReader=>DataTable=>json=>datable =>excel 通过API 传输过于占用资源，
优化：
DBReader=>excel ,直接输出到服务器共享文件夹。
流输出效率更改、省略传输转换、异步内存清除、百万条分Sheet兼容2007。

三、分页查询 DbReader=>Excel (MiniExcel)

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MiniExcelLibs;
namespace ExcelExport
{
    public class ExcelHelper
    {
        public void GetData()
        {
             SqlConnection sqlCon;
                try
                {
                    var sheets = new Dictionary<string, object>();
                    string excelFilePath = @"D:\Excel\DataFile.xlsx";
                    string connectionString = ConnectionString;
                    SqlCommand sqlcmd;
                    sqlCon = new SqlConnection(connectionString);
                    sqlCon.Open();
                    sqlcmd = new SqlCommand(sql, sqlCon);
                    //先取页数 =符合条件总条数/每页条数 
                    maxLoopCounter = Convert.ToInt32(sqlcmd.ExecuteScalar());
                    for (int i = 1; i <= maxLoopCounter; i++) //循环查询每页数据
                    {
                        string sheetName = "Sheet" + i.ToString();
                      //  string sheetName = "Sheet5" ;
                        sqlCon = new SqlConnection(connectionString);
                        sqlCon.Open();
                        string sql = "";
                        sqlcmd = new SqlCommand(sql, sqlCon);
                        sheets.Add(sheetName, sqlcmd.ExecuteReader());//按页写入sheet
                    }
                    MiniExcel.SaveAs(excelFilePath, sheets);
                }
                catch (Exception exception)
                {
                }
        }
    }
}

SELECT *
FROM BigDataTable
ORDER BY CreateDate DESC
OFFSET @PageIndex * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY; 

可惜的是FETCH 只支持SQL2012+,低版本就只有转存实体表的方式：

declare @ntile_value int =1000000 //一百万一页
SELECT id, NTILE(@ntile_value) OVER (ORDER BY id) AS page  INTO temp_BigTable FROM BigTable;
--使用 NTILE 添加页码字段->写入新表
--个人觉得建表、清表、表占用判断，也挺麻烦

总结：

服务器流导出更快，使用NPOI、和MiniExcel等都是异曲同工。数据量百万级，我觉得应用层分页更好，数据更大那数据库分页后传输更好。Interop作为微软官方com组件确实更方便，但跨平台就不行了。总的来说NPOI确实各方面更适用一些。