c# - 使用 OleDB 打开 Excel(使用 EPPLUS 创建)

Question

我有一个同事的代码，这段代码用 Epplus 创建了一些 excel 表。使用我的代码，我想添加一个数据库提取 10k+/- 行。由于数据量大，使用 Epplus 花费的时间太长，因为您需要写入每个单元格。使用 OleDB 只需几秒钟。但是我无法使用 OleDB 打开 Epplus 之前创建的 excel。即使使用不同的连接字符串。

如果将两个代码块分开，我的代码将完美运行。

var excelPath = "C:\\test_" + DateTime.Today.ToString("yyyyMMdd_") + DateTime.Now.ToString("hh") + DateTime.Now.Minute.ToString() + ".xlsx";
using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo(excelPath)))
{
    ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Schedule V");
    worksheet.Cell(1, 1).Value = "test";
    xlPackage.Save();
    xlPackage.Dispose();
}
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml';";
using (OleDbConnection conn = new OleDbConnection(strCn))
{
    conn.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conn;
    cmd.CommandText = "CREATE TABLE [table1] (id INT, name VARCHAR, datecol DATE );";
    cmd.ExecuteNonQuery();
    cmd.CommandText = "INSERT INTO [table1](id,name,datecol) VALUES(1,'AAAA','2014-01-01');";
    cmd.ExecuteNonQuery();
    conn.Close();
}

我尝试了以下连接字符串，但它们都给出了相同的错误:

OleDbException was unhandled, External table is not in the expected format.

我试过的不同连接字符串:

var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
var strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=YES';";
var strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";

我在这里做错了什么？

Answer 1

我想，它应该是这样的，而不是硬编码你应该使用 OleDbConnectionStringBuilder 类

OleDbConnectionStringBuilder connectionStringBuilder = new OleDbConnectionStringBuilder();
connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
connectionStringBuilder.DataSource = excelPath; // This is your Excel File Full Path
connectionStringBuilder.Add("Mode", "Read");

const string extendedProperties = "Excel 12.0;IMEX=1;HDR=YES";
connectionStringBuilder.Add("Extended Properties", extendedProperties);

String connectionString = connectionStringBuilder.ToString();
// Create connection object by using the preceding connection string.
using (var objConn = new OleDbConnection(connectionString))
{
     // Open connection with the database.
      objConn.Open();

   // Do operations with your File here
}