For Loop of Datatable.rows在C#.Net中将Datatable导出到excel时中断

本文关键字:Datatable excel 中将 中断 Net of Loop rows For | 更新日期: 2023-09-27 18:21:13

我正在尝试从Datatable to Excel导出数据。数据表具有8540 rows31 columns

下面的循环在3500条记录后的某个地方中断:

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

导出时有行限制吗?还是我做错了什么?请帮忙!

我用于导出的代码是:

public static void ExportExcel(this DataTable Tbl, string ExcelFilePath = null)
{
    try
    {
        if (Tbl == null || Tbl.Columns.Count == 0)
            //throw new Exception("ExportToExcel: Null or empty input table!'n");
            Console.WriteLine("ExportToExcel: Null or empty input table!'n");
        // load excel, and create a new workbook
        Excel.Application excelApp = new Excel.Application();
        excelApp.Workbooks.Add();
        // single worksheet
        Excel._Worksheet workSheet = excelApp.ActiveSheet;
        // column headings
        for (int i = 0; i < Tbl.Columns.Count; i++)
        {
            workSheet.Cells[1, (i + 1)] = Tbl.Columns[i].ColumnName;
            workSheet.Cells[1, (i + 1)].Font.Bold = true;
            workSheet.Cells[1, (i + 1)].Font.Size = 12;
        }
        // rows
        for (int i = 0; i < Tbl.Rows.Count; i++)
        {
            // to do: format datetime values before printing
            for (int j = 0; j < Tbl.Columns.Count; j++)
            {
                workSheet.Cells[(i + 2), (j + 1)] = Tbl.Rows[i][j];
            }
        }
        // check fielpath
        if (ExcelFilePath != null && ExcelFilePath != "")
        {
            try
            {
                workSheet.SaveAs(ExcelFilePath);
                excelApp.Quit();
                //MessageBox.Show("Excel file saved!");
            }
            catch (Exception ex)
            {
                //throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.'n"+ ex.Message);
                Console.WriteLine("ExportToExcel: Excel file could not be saved! Check filepath.'n"+ ex.Message);
            }
        }
        else    // no filepath is given
        {
            excelApp.Visible = true;
        }
    }
    catch (Exception ex)
    {
        //throw new Exception("ExportToExcel: 'n" + ex.Message);
        Console.WriteLine("ExportToExcel: 'n" + ex.Message); 
    }
}

For Loop of Datatable.rows在C#.Net中将Datatable导出到excel时中断

如果我可以提供一个替代方案:使用EPPLUS及其出色的LoadFromDataTable*函数-这将使您的代码减少到一行,速度更快,而且不直接依赖Excel。此外:图书馆是免费的,可以在没有许可证问题的情况下提供。