Error of writing excel with a number of data by Using oledb

Question

I get a problem with inserting data into Excel using oledb

The problem: when I try to write less than 65000 lines of data into Excel, it works. But when I try to write more than 65000 lines, an error occurs:

Spreadsheet is full

I guess there is a limit to write date at one time...

I have more than 1,000,000 lines of data to write...

A line consists of 6 columns and appoximately 100 characters

Here is my source code.

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;
    }

Answer 1

It might the version you opened in 2003. Because when you open 2003 excel in 2007, it won't have more than 65K rows.

Hence, Please save the excel into 2007 and try the same.