使用 OLEDB 读取 Excel 文件？

发布于 2025-01-04 03:55:30 字数 977 浏览 4 评论 0原文

您好，我正在使用 oledb 读取 Excel 文件（该文件有 100000 行）。我必须快速读取文件。

string conn;

                conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
                ("Data Source=" + _filename + ";" +
                "Extended Properties=\"Excel 12.0;\""));
                OleDbConnection oleDBCon = new OleDbConnection(conn);
                oleDBCon.Open();
                DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string excelsheetname = dt.Rows[0].ItemArray[2].ToString();
                string SSQL = "SELECT * from [" + excelsheetname + "]";

                OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
                DataSet ds = new DataSet();
                oleDA.Fill(ds);
                DataTable _DtTable = ds.Tables[0]; // or [ ds ]
                oleDBCon.Close();

然后在 _DtTable 中使用 for 循环将这些单元格插入到数据库中。如何快速读取这个非常大的 Excel？并插入数据库？我使用了 Parallel.For 但这对我来说不是真正的解决方案..有什么想法吗？

原文

Hi I am reading an excel file with oledb(The file has 100000 rows). I must read file quickly.

string conn;

                conn = ("Provider=Microsoft.ACE.OLEDB.12.0;" +
                ("Data Source=" + _filename + ";" +
                "Extended Properties=\"Excel 12.0;\""));
                OleDbConnection oleDBCon = new OleDbConnection(conn);
                oleDBCon.Open();
                DataTable dt = oleDBCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string excelsheetname = dt.Rows[0].ItemArray[2].ToString();
                string SSQL = "SELECT * from [" + excelsheetname + "]";

                OleDbDataAdapter oleDA = new OleDbDataAdapter(SSQL, conn);
                DataSet ds = new DataSet();
                oleDA.Fill(ds);
                DataTable _DtTable = ds.Tables[0]; // or [ ds ]
                oleDBCon.Close();

and then in _DtTable with a for loop I am inserting these cells to DB.. How can I read this very large excel quickly? And insert to DB? I used Parallel.For but it is not true solution for me.. Any idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贱贱哒 2025-01-11 03:55:30

要使用 ADO 将记录添加到 MyTable，您可以使用类似于以下的代码：

'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Joe', 'Thomas')"
conn.Close

这来自 MSDN：http:// /support.microsoft.com/kb/247412

To add records to MyTable using ADO, you can use code similar to the following:

'Create a new connection object for Book1.xls
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Bill', 'Brown')"
conn.Execute "Insert into MyTable (FirstName, LastName)" & _
  " values ('Joe', 'Thomas')"
conn.Close

This is from MSDN: http://support.microsoft.com/kb/247412

回复收藏 0 原文