¿La mejor / más rápida forma de leer una hoja de Excel en una DataTable?

Espero que alguien aquí pueda señalarme en la dirección correcta. Estoy intentando crear un progtwig de utilidad bastante robusto para leer los datos de una hoja de Excel (puede ser .xls O .xlsx) en una DataTable tan rápida y delicadamente como posible.

Se me ocurrió esta rutina en VB (aunque estaría igual de feliz con una buena respuesta de C #):

Public Shared Function ReadExcelIntoDataTable(ByVal FileName As String, ByVal SheetName As String) As DataTable Dim RetVal As New DataTable Dim strConnString As String strConnString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & FileName & ";" Dim strSQL As String strSQL = "SELECT * FROM [" & SheetName & "$]" Dim y As New Odbc.OdbcDataAdapter(strSQL, strConnString) y.Fill(RetVal) Return RetVal End Function 

Me pregunto si esta es la mejor manera de hacerlo o si hay formas mejores / más eficientes (o solo formas más inteligentes – ¿Quizás proveedores Linq / nativos de .Net) para usar en su lugar?

TAMBIÉN, solo una pregunta adicional rápida y tonta: ¿Necesito incluir un código como y.Dispose() e y = Nothing o eso será y.Dispose() ya que la variable debería morir al final de la rutina, ¿verdad?

¡¡Gracias!!

Siempre he usado OLEDB para esto, algo así como …

  Dim sSheetName As String Dim sConnection As String Dim dtTablesList As DataTable Dim oleExcelCommand As OleDbCommand Dim oleExcelReader As OleDbDataReader Dim oleExcelConnection As OleDbConnection sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xls;Extended Properties=""Excel 12.0;HDR=No;IMEX=1""" oleExcelConnection = New OleDbConnection(sConnection) oleExcelConnection.Open() dtTablesList = oleExcelConnection.GetSchema("Tables") If dtTablesList.Rows.Count > 0 Then sSheetName = dtTablesList.Rows(0)("TABLE_NAME").ToString End If dtTablesList.Clear() dtTablesList.Dispose() If sSheetName <> "" Then oleExcelCommand = oleExcelConnection.CreateCommand() oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]" oleExcelCommand.CommandType = CommandType.Text oleExcelReader = oleExcelCommand.ExecuteReader nOutputRow = 0 While oleExcelReader.Read End While oleExcelReader.Close() End If oleExcelConnection.Close() 

El proveedor de ACE.OLEDB leerá los archivos .xls y .xlsx y siempre he encontrado que la velocidad es bastante buena.

Si quieres hacer lo mismo en C # basado en Ciarán Respuesta

 string sSheetName = null; string sConnection = null; DataTable dtTablesList = default(DataTable); OleDbCommand oleExcelCommand = default(OleDbCommand); OleDbDataReader oleExcelReader = default(OleDbDataReader); OleDbConnection oleExcelConnection = default(OleDbConnection); sConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Test.xls;Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\""; oleExcelConnection = new OleDbConnection(sConnection); oleExcelConnection.Open(); dtTablesList = oleExcelConnection.GetSchema("Tables"); if (dtTablesList.Rows.Count > 0) { sSheetName = dtTablesList.Rows[0]["TABLE_NAME"].ToString(); } dtTablesList.Clear(); dtTablesList.Dispose(); if (!string.IsNullOrEmpty(sSheetName)) { oleExcelCommand = oleExcelConnection.CreateCommand(); oleExcelCommand.CommandText = "Select * From [" + sSheetName + "]"; oleExcelCommand.CommandType = CommandType.Text; oleExcelReader = oleExcelCommand.ExecuteReader(); nOutputRow = 0; while (oleExcelReader.Read()) { } oleExcelReader.Close(); } oleExcelConnection.Close(); 

Aquí hay otra forma de leer Excel en un DataTable sin usar OLEDB muy rápido. Tenga en cuenta que el archivo ext tendría que ser .CSV para que esto funcione correctamente.

 private static DataTable GetDataTabletFromCSVFile(string csv_file_path) { csvData = new DataTable(defaultTableName); try { using (TextFieldParser csvReader = new TextFieldParser(csv_file_path)) { csvReader.SetDelimiters(new string[] { tableDelim }); csvReader.HasFieldsEnclosedInQuotes = true; string[] colFields = csvReader.ReadFields(); foreach (string column in colFields) { DataColumn datecolumn = new DataColumn(column); datecolumn.AllowDBNull = true; csvData.Columns.Add(datecolumn); } while (!csvReader.EndOfData) { string[] fieldData = csvReader.ReadFields(); //Making empty value as null for (int i = 0; i < fieldData.Length; i++) { if (fieldData[i] == string.Empty) { fieldData[i] = string.Empty; //fieldData[i] = null } //Skip rows that have any csv header information or blank rows in them if (fieldData[0].Contains("Disclaimer") || string.IsNullOrEmpty(fieldData[0])) { continue; } } csvData.Rows.Add(fieldData); } } } catch (Exception ex) { } return csvData; } 
 public DataTable ImportExceltoDatatable(string filepath) { // string sqlquery= "Select * From [SheetName$] Where YourCondition"; string sqlquery = "Select * From [SheetName$] Where Id='ID_007'"; DataSet ds = new DataSet(); string constring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;HDR=YES;\""; OleDbConnection con = new OleDbConnection(constring + ""); OleDbDataAdapter da = new OleDbDataAdapter(sqlquery, con); da.Fill(ds); DataTable dt = ds.Tables[0]; return dt; } 

Esta es la forma de leer de excel oledb

 try { System.Data.OleDb.OleDbConnection MyConnection; System.Data.DataSet DtSet; System.Data.OleDb.OleDbDataAdapter MyCommand; string strHeader7 = ""; strHeader7 = (hdr7) ? "Yes" : "No"; MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fn + ";Extended Properties=\"Excel 12.0;HDR=" + strHeader7 + ";IMEX=1\""); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + wks + "$]", MyConnection); MyCommand.TableMappings.Add("Table", "TestTable"); DtSet = new System.Data.DataSet(); MyCommand.Fill(DtSet); dgv7.DataSource = DtSet.Tables[0]; MyConnection.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } 

Puede usar OpenXml SDK para archivos * .xlsx. Funciona muy rápido. Hice la implementación simple de C # IDataReader para este SDK. Mira aquí . Ahora puede leer fácilmente el archivo de Excel en DataTable y puede importar el archivo de Excel a la base de datos del servidor SQL (use SqlBulkCopy). ExcelDataReader lee muy rápido. En mi máquina, 10000 registra menos 3 segundos y 60000 menos 8 segundos.

Leer en el ejemplo de DataTable:

 class Program { static void Main(string[] args) { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"data.xlsx")) dt.Load(reader); Console.WriteLine("done: " + dt.Rows.Count); Console.ReadKey(); } } 

Esto pareció funcionar bastante bien para mí.

 private DataTable ReadExcelFile(string sheetName, string path) { using (OleDbConnection conn = new OleDbConnection()) { DataTable dt = new DataTable(); string Import_FileName = path; string fileExtension = Path.GetExtension(Import_FileName); if (fileExtension == ".xls") conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;'"; if (fileExtension == ".xlsx") conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"; using (OleDbCommand comm = new OleDbCommand()) { comm.CommandText = "Select * from [" + sheetName + "$]"; comm.Connection = conn; using (OleDbDataAdapter da = new OleDbDataAdapter()) { da.SelectCommand = comm; da.Fill(dt); return dt; } } } } 

Lo encontré bastante fácil así

  using System; using System.Data; using System.IO; using Excel; public DataTable ExcelToDataTableUsingExcelDataReader(string storePath) { FileStream stream = File.Open(storePath, FileMode.Open, FileAccess.Read); string fileExtension = Path.GetExtension(storePath); IExcelDataReader excelReader = null; if (fileExtension == ".xls") { excelReader = ExcelReaderFactory.CreateBinaryReader(stream); } else if (fileExtension == ".xlsx") { excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); } excelReader.IsFirstRowAsColumnNames = true; DataSet result = excelReader.AsDataSet(); var test = result.Tables[0]; return result.Tables[0]; } 

Nota: necesita instalar el paquete SharpZipLib para esto

 Install-Package SharpZipLib 

¡limpio y ordenado! 😉

El código siguiente es probado por mí mismo y es muy simple, comprensible, utilizable y rápido. Este código, inicialmente toma todos los nombres de las hojas, luego coloca todas las tablas de ese archivo Excel en un DataSet.

 public static DataSet ToDataSet(string exceladdress, int startRecord = 0, int maxRecord = -1, string condition = "") { DataSet result = new DataSet(); using (OleDbConnection connection = new OleDbConnection((exceladdress.TrimEnd().ToLower().EndsWith("x")) ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + exceladdress + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'" : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + exceladdress + "';Extended Properties=Excel 8.0;")) try { connection.Open(); DataTable schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); foreach (DataRow drSheet in schema.Rows) if (drSheet["TABLE_NAME"].ToString().Contains("$")) { string s = drSheet["TABLE_NAME"].ToString(); if (s.StartsWith("'")) s = s.Substring(1, s.Length - 2); System.Data.OleDb.OleDbDataAdapter command = new System.Data.OleDb.OleDbDataAdapter(string.Join("", "SELECT * FROM [", s, "] ", condition), connection); DataTable dt = new DataTable(); if (maxRecord > -1 && startRecord > -1) command.Fill(startRecord, maxRecord, dt); else command.Fill(dt); result.Tables.Add(dt); } return result; } catch (Exception ex) { return null; } finally { connection.Close(); } } 

Disfrutar…

 '''  ''' ReadToDataTable reads the given Excel file to a datatable. '''  ''' The table to be populated. ''' The file to attempt to read to. ''' TRUE if success, FALSE otherwise. '''  Public Function ReadToDataTable(ByRef table As DataTable, incomingFileName As String) As Boolean Dim returnValue As Boolean = False Try Dim sheetName As String = "" Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & incomingFileName & ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1""" Dim tablesInFile As DataTable Dim oleExcelCommand As OleDbCommand Dim oleExcelReader As OleDbDataReader Dim oleExcelConnection As OleDbConnection oleExcelConnection = New OleDbConnection(connectionString) oleExcelConnection.Open() tablesInFile = oleExcelConnection.GetSchema("Tables") If tablesInFile.Rows.Count > 0 Then sheetName = tablesInFile.Rows(0)("TABLE_NAME").ToString End If If sheetName <> "" Then oleExcelCommand = oleExcelConnection.CreateCommand() oleExcelCommand.CommandText = "Select * From [" & sheetName & "]" oleExcelCommand.CommandType = CommandType.Text oleExcelReader = oleExcelCommand.ExecuteReader 'Determine what row of the Excel file we are on Dim currentRowIndex As Integer = 0 While oleExcelReader.Read 'If we are on the First Row, then add the item as Columns in the DataTable If currentRowIndex = 0 Then For currentFieldIndex As Integer = 0 To (oleExcelReader.VisibleFieldCount - 1) Dim currentColumnName As String = oleExcelReader.Item(currentFieldIndex).ToString table.Columns.Add(currentColumnName, GetType(String)) table.AcceptChanges() Next End If 'If we are on a Row with Data, add the data to the SheetTable If currentRowIndex > 0 Then Dim newRow As DataRow = table.NewRow For currentFieldIndex As Integer = 0 To (oleExcelReader.VisibleFieldCount - 1) Dim currentColumnName As String = table.Columns(currentFieldIndex).ColumnName newRow(currentColumnName) = oleExcelReader.Item(currentFieldIndex) If IsDBNull(newRow(currentFieldIndex)) Then newRow(currentFieldIndex) = "" End If Next table.Rows.Add(newRow) table.AcceptChanges() End If 'Increment the CurrentRowIndex currentRowIndex += 1 End While oleExcelReader.Close() End If oleExcelConnection.Close() returnValue = True Catch ex As Exception 'LastError = ex.ToString Return False End Try Return returnValue End Function 

Use el siguiente fragmento que será útil.

 string POCpath = @"G:\Althaf\abc.xlsx"; string POCConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + POCpath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";"; OleDbConnection POCcon = new OleDbConnection(POCConnection); OleDbCommand POCcommand = new OleDbCommand(); DataTable dt = new DataTable(); OleDbDataAdapter POCCommand = new OleDbDataAdapter("select * from [Sheet1$] ", POCcon); POCCommand.Fill(dt); Console.WriteLine(dt.Rows.Count); 

He usado este método y para mí es muy eficiente y rápido.

 // Step 1. Download NuGet source of Generic Parsing by Andrew Rissing // Step 2. Reference this to your project // Step 3. Reference Microsoft.Office.Interop.Excel to your project // Step 4. Follow the logic below public static DataTable ExcelSheetToDataTable(string filePath) { // Save a copy of the Excel file as CSV var xlApp = new XL.Application(); var xlWbk = xlApp.Workbooks.Open(filePath); var tempPath = Path.Combine(Environment .GetFolderPath(Environment.SpecialFolder.UserProfile) , "AppData" , "Local", , "Temp" , Path.GetFileNameWithoutExtension(filePath) + ".csv"); xlApp.DisplayAlerts = false; xlWbk.SaveAs(tempPath, XL.XlFileFormat.xlCSV); xlWbk.Close(SaveChanges: false); xlApp.Quit(); // The actual parsing using (var parser = new GenericParserAdapter(tempPath)) { parser.FirstRowHasHeader = true; return parser.GetDataTable(); } } 

Análisis genérico de Andrew Rissing

Aquí hay otra forma de hacerlo

 public DataSet CreateTable(string source) { using (var connection = new OleDbConnection(GetConnectionString(source, true))) { var dataSet = new DataSet(); connection.Open(); var schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (schemaTable == null) return dataSet; var sheetName = ""; foreach (DataRow row in schemaTable.Rows) { sheetName = row["TABLE_NAME"].ToString(); break; } var command = string.Format("SELECT * FROM [{0}$]", sheetName); var adapter = new OleDbDataAdapter(command, connection); adapter.TableMappings.Add("TABLE", "TestTable"); adapter.Fill(dataSet); connection.Close(); return dataSet; } } // private string GetConnectionString(string source, bool hasHeader) { return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=\"Excel 12.0;HDR={1};IMEX=1\"", source, (hasHeader ? "YES" : "NO")); }