C# DataTable 数据导出到 Excel 多个 Sheet 页

1. 首先需要安装 Microsoft.Office.Interop.Excel 组件，可以在 Nuget 中搜索安装。

2. 创建 Excel 应用程序对象和工作簿对象。

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();

3. 遍历 DataTable 列表，将数据写入 Excel 中。

foreach (DataTable dt in dtList)
{
    Excel.Worksheet xlWorksheet = xlWorkbook.Sheets.Add();
    xlWorksheet.Name = dt.TableName;

    for (int i = 0; i < dt.Columns.Count; i++)
    {
        xlWorksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            xlWorksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
        }
    }

    Marshal.ReleaseComObject(xlWorksheet);
}

4. 保存 Excel 文件并释放资源。

xlWorkbook.SaveAs("file path");
xlWorkbook.Close();
xlApp.Quit();

Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);

完整代码如下：

using Excel = Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;

public static void ExportToExcel(List<DataTable> dtList)
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Add();

    foreach (DataTable dt in dtList)
    {
        Excel.Worksheet xlWorksheet = xlWorkbook.Sheets.Add();
        xlWorksheet.Name = dt.TableName;

        for (int i = 0; i < dt.Columns.Count; i++)
        {
            xlWorksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
        }

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                xlWorksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
            }
        }

        Marshal.ReleaseComObject(xlWorksheet);
    }

    xlWorkbook.SaveAs("file path");
    xlWorkbook.Close();
    xlApp.Quit();

    Marshal.ReleaseComObject(xlWorkbook);
    Marshal.ReleaseComObject(xlApp);
}