如何在C＃中逐步读取有限记录中的大型excel文件？ [英] How to read large excel file in step by step limited records in C# ?

问题描述

我有一个3,00,000条记录的Excel文件，所以我想在每个进程中导入30,000条记录。如何在C＃中执行此操作？



我使用 OLEDB连接喷气机提供程序导入记录，但它在Datatable中一次性导入记录并且有时会导致错误：内存不足以处理异常



我尝试过：

I have a 3,00,000 records Excel file, so I want to import records by 30,000 in each process. How to do that in C#?

I have used the OLEDB connection jet provider to import records but it imports records in one go in Datatable and sometimes causes error: out of memory to process Exception

What I have tried:

string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";

string conStr, sheetName;
conStr = string.Empty;

switch (extension)
{
    case ".xls": //Excel 97-03
        conStr = string.Format(Excel03ConString, fileName);
        break;
    case ".xlsx": //Excel 07
        conStr = string.Format(Excel07ConString, fileName);
        break;
}

//Get the name of the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
    using (OleDbCommand cmd = new OleDbCommand())
    {
        cmd.Connection = con;
        con.Open();
        DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
        con.Close();
    }
}

//Read Data from the First Sheet.
using (OleDbConnection con = new OleDbConnection(conStr))
{
    using (OleDbCommand cmd = new OleDbCommand())
    {
        using (OleDbDataAdapter oda = new OleDbDataAdapter())
        {
            cmd.CommandText = "SELECT * From [" + sheetName + "]";
            cmd.Connection = con;
            con.Open();
            oda.SelectCommand = cmd;
            //dt.Locale = CultureInfo.CurrentCulture;
            oda.Fill(dt);
            con.Close();
        }
    }
}

推荐答案

第一眼看，问题出在这一行（带下划线的部分）：

On the first look, the issue is in this line (underlined part):

string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";

有关MS Excel 2007及更高版本的正确连接字符串，请访问此站点： Excel连接字符串 - ConnectionStrings.com [ ^ ]

这篇关于如何在C＃中逐步读取有限记录中的大型excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！