我在将数据插入使用捆绑器的Excel方面遇到问题。

问题:当我试图将不到65,000条线的数据写进Excel时,它就发挥了作用。 但当我试图写上65,000多条线时,出现了错误:

读书全文

我猜测,在某一时间写字日有限制......

我有超过1 000 000条数据线来书写......

a 包括6栏和约100字。

这里是我的源代码。

private static OleDbConnection CreateConnection(string ExcelPath)
    {
        OleDbConnectionStringBuilder ConnectionBuilder = new OleDbConnectionStringBuilder();
        ConnectionBuilder.Provider = "Microsoft.Jet.OLEDB.4.0";
        ConnectionBuilder.DataSource = ExcelPath;
        ConnectionBuilder.Add("Extended Properties", "Excel 8.0");
        return new OleDbConnection(ConnectionBuilder.ToString());
    }

using (OleDbConnection Connection = CreateConnection(@"C:userinfo.xls"))
        {
            Connection.Open();
            OleDbCommand tempCmd = Connection.CreateCommand();
            tempCmd.CommandText = CreateTableQury(dt, "Sheet1");
            tempCmd.ExecuteNonQuery();
            tempCmd.CommandText = CreateInsertQuery(dt, tempCmd.Parameters, "Sheet1");
            int extime = 1;
            foreach (DataRow Row in dt.Rows)
            {
                for (int t = 0; t < tempCmd.Parameters.Count; t++)
                {
                    tempCmd.Parameters[t].Value = Row[t];

                }
                tempCmd.ExecuteScalar();
                extime++;
                if ((extime % 1000) == 0)
                {
                    Console.WriteLine("EXCEL " + extime + " Line / time:" + sw.Elapsed.ToString());
                }
            }
            Connection.Close();
        }


public static string CreateTableQury(DataTable Table, string SheetName)
    {
        string Query = "CREATE TABLE [" + SheetName + "] (";

        for (int i = 0; i < Table.Columns.Count; i++)
        {
            Query += Table.Columns[i].ColumnName + " text";
            if (i < Table.Columns.Count - 1) Query += ", ";
            else Query += ")";
        }

        return Query;
    }

    public static string CreateInsertQuery(DataTable Table, OleDbParameterCollection Parameters, string SheetName)
    {

        string Query = @"INSERT INTO [" + SheetName + "] VALUES (";

        for (int i = 0; i < Table.Columns.Count; i++)
        {
            Query += "@Param" + i.ToString();

            if (i < Table.Columns.Count - 1) Query += ", ";
            else Query += ")";

            OleDbParameter Parameter = new OleDbParameter("@Param" + i.ToString(), DbType.String);
            Parameters.Add(Parameter);

        }

        return Query;
    }