使用 OLEDB 将数据表导出到 Excel 会引发太多字段定义错误

Question

我们有一个包含 2500 列和 2000 行的数据表。当我们尝试使用 OLEDB 导出时，我收到错误“定义的字段太多”。我无法使用 Excel Inter op，因为它会消耗更多时间。有没有其他方法可以解决这个问题

using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties=" + "\"" + "Excel 8.0;HDR=NO;" + "\""))
                {
                    con.Open();
                    OleDbCommand cmdInsert;
                    if (createTable || !File.Exists(fileName))
                    {
                        sql = "CREATE TABLE " + tableName + " (";
                        for (int i = 0; i < tbl.Columns.Count; i++)
                        {
                            sql += "[" + tbl.Columns[i].ColumnName + "]";
                            if (i + 1 == tbl.Columns.Count) //Here we decide should we close insert command or appebd another create column command
                                sql += " " + GetColumnType(tbl.Columns[i]) + ")"; //Close insert
                            else
                                sql += " " + GetColumnType(tbl.Columns[i]) + ","; //there is more columns to add
                        }
                    }
                    if (!String.IsNullOrEmpty(sql))
                    {
                        cmdInsert = new OleDbCommand(sql, con);
                        cmdInsert.ExecuteNonQuery();
                    }
                    foreach (DataRow row in tbl.Rows)
                    {
                        //Dodati parametre na comandu
                        string values = "(";
                        for (int i = 0; i < tbl.Columns.Count; i++)
                        {
                            if (i + 1 == tbl.Columns.Count)
                            {
                                 if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0)" : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ")";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "')";

                                //values += "'" + "test" + "')";
                            }
                            else
                            {
                                if (tbl.Columns[i].DataType == System.Type.GetType("System.Decimal") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Int64") ||
                                     tbl.Columns[i].DataType == System.Type.GetType("System.Double"))
                                    values += String.IsNullOrEmpty(row[i].ToString()) ? "0," : Convert.ToDecimal(row[i]).ToString("#0.00", _infoEn) + ",";
                                else
                                    values += "'" + System.Security.SecurityElement.Escape(row[i].ToString()) + "',";

                                //values += "'" + "test" + "',";
                            }
                        }
                        string sqlInsert = String.Format("Insert into [{0}$] VALUES {1}", tableName, values);
                        cmdInsert = new OleDbCommand(sqlInsert, con);

                        cmdInsert.ExecuteNonQuery();
                    }
                }

Answer 1

我发现使用 oledb 的限制是 255 列。因此，我使用下面的代码将数据表转换为 csv 并将其写入 http 页面的响应中

public void CreateCSVFile(System.Data.DataTable dt, out StringWriter stw)
        {
            stw = new StringWriter();
            int iColCount = dt.Columns.Count;
            for (int i = 0; i < iColCount; i++)
            {
                stw.Write(dt.Columns[i]);
                if (i < iColCount - 1)
                {
                    stw.Write(",");
                }
            }
            stw.Write(stw.NewLine);
            foreach (DataRow dr in dt.Rows)
            {
                for (int i = 0; i < iColCount; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        stw.Write(dr[i].ToString());
                    }
                    if (i < iColCount - 1)
                    {
                        stw.Write(",");
                    }
                }
                stw.Write(stw.NewLine);
            }
        }

，现在我能够解决导出大量数据的问题

Answer 2

Excel（您也正在连接）是否通过“表”定义支持列数限制？这可能是你的瓶颈。

我建议，在构建表格时，对所有列进行此操作。我建议以批处理周期运行它。调用该函数并一次执行 100 列并仅导出 2 或 3 行。因此，可能需要 20 个循环周期才能构建所有 2000 个列，但您可能会在那里找到突破点。如果您确实达到了完整的 2000 列，则展开正在导出的行并找出失败的插入行。可能有一些 NULL 值在杀死您的进程。