Importing Excel into a DataTable Quickly

Question

I am trying to read an Excel file into a list of Data DataTable  although with my current method it can take a very long time  I essentually go Worksheet by Worksheet  cell by cell  and it tends to take a very long time  Is there a quicker way of doing this  Here is my code       List lt DataTable gt  List   new List lt DataTable gt             Counting sheets     for  int count   1  count  lt  WB Worksheets Count    count                   Create a new DataTable for every Worksheet         DATA DataTable DT   new DataTable             WS    EXCEL Worksheet WB Worksheets get Item count            textBox1 Text   count ToString                Get range of the worksheet         Range   WS UsedRange               Create new Column in DataTable         for  cCnt   1  cCnt  lt   Range Columns Count  cCnt                          textBox3 Text   cCnt ToString                      Column   new DataColumn                    Column DataType   System Type GetType  System String                    Column ColumnName   cCnt ToString                    DT Columns Add Column                   Create row for Data Table             for  rCnt   0  rCnt  lt   Range Rows Count  rCnt                                  textBox2 Text   rCnt ToString                     try                                       cellVal    string  Range Cells rCnt  cCnt  as EXCEL Range  Value2                                    catch  Microsoft CSharp RuntimeBinder RuntimeBinderException                                        ConvertVal    double  Range Cells rCnt  cCnt  as EXCEL Range  Value2                      cellVal   ConvertVal ToString                                          Add to the DataTable                 if  cCnt    1                                         Row   DT NewRow                        Row cCnt ToString      cellVal                      DT Rows Add Row                                     else                                        Row   DT Rows rCnt                       Row cCnt ToString      cellVal                                                        Add DT to the list  Then go to the next sheet in the Excel Workbook         List Add DT

User · Answer

class DataReader               Excel Application xlApp          Excel Workbook xlBook          Excel Range xlRange          Excel Worksheet xlSheet          public DataTable GetSheetDataAsDataTable String filePath  String sheetName                        DataTable dt   new DataTable                try                               xlApp   new Excel Application                    xlBook   xlApp Workbooks Open filePath                   xlSheet   xlBook Worksheets sheetName                   xlRange   xlSheet UsedRange                  DataRow row null                  for  int i   1  i  lt   xlRange Rows Count  i                                          if  i    1                          row   dt NewRow                        for  int j   1  j  lt   xlRange Columns Count  j                                                  if  i    1                              dt Columns Add xlRange Cells 1  j  value                           else                             row j-1    xlRange Cells i  j  value                                            if row   null                          dt Rows Add row                                               catch  Exception ex                                Console WriteLine ex Message                             finally                               xlBook Close                    xlApp Quit                              return dt

User · Answer

Caling  Value2 is an expensive operation because it s a COM-interop call  I would instead read the entire range into an array and then loop through the array   object    data   Range Value2      Create new Column in DataTable for  int cCnt   1  cCnt  lt   Range Columns Count  cCnt          textBox3 Text   cCnt ToString         var Column   new DataColumn        Column DataType   System Type GetType  System String        Column ColumnName   cCnt ToString        DT Columns Add Column           Create row for Data Table     for  int rCnt   1  rCnt  lt   Range Rows Count  rCnt                  textBox2 Text   rCnt ToString             string CellVal   String Empty          try                       cellVal    string  data rCnt  cCnt                      catch  Microsoft CSharp RuntimeBinder RuntimeBinderException                        ConvertVal    double  data rCnt  cCnt                cellVal   ConvertVal ToString                       DataRow Row              Add to the DataTable         if  cCnt    1                         Row   DT NewRow                Row cCnt ToString      cellVal              DT Rows Add Row                     else                        Row   DT Rows rCnt   1               Row cCnt ToString      cellVal

User · Answer

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  gt  0 Then     sSheetName   dtTablesList Rows 0   TABLE NAME   ToString End If  dtTablesList Clear   dtTablesList Dispose    If sSheetName  lt  gt     Then      oleExcelCommand   oleExcelConnection CreateCommand       oleExcelCommand CommandText    Select   From     amp  sSheetName  amp          oleExcelCommand CommandType   CommandType Text      oleExcelReader   oleExcelCommand ExecuteReader      nOutputRow   0      While oleExcelReader Read      End While      oleExcelReader Close    End If  oleExcelConnection Close

User · Answer

In case anyone else is using EPPlus  This implementation is pretty naive  but there are comments that draw attention to such  If you were to layer one more method GetWorkbookAsDataSet   on top it would do what the OP is asking for            lt summary gt          Assumption  Worksheet is in table format with no weird padding or blank column headers                   Assertion  Duplicate column names will be aliased by appending a sequence number  eg  Column  Column1  Column2           lt  summary gt           lt param name  worksheet  gt  lt  param gt           lt returns gt  lt  returns gt      public static DataTable GetWorksheetAsDataTable ExcelWorksheet worksheet                var dt   new DataTable worksheet Name           dt Columns AddRange GetDataColumns worksheet  ToArray             var headerOffset   1    have to skip header row         var width   dt Columns Count          var depth   GetTableDepth worksheet  headerOffset           for  var i   1  i  lt   depth  i                          var row   dt NewRow                for  var j   1  j  lt   width  j                                  var currentValue   worksheet Cells i   headerOffset  j  Value                     have to decrement b c excel is 1 based and datatable is 0 based                  row j - 1    currentValue    null   null   currentValue ToString                               dt Rows Add row                      return dt                  lt summary gt          Assumption  There are no null or empty cells in the first column          lt  summary gt           lt param name  worksheet  gt  lt  param gt           lt returns gt  lt  returns gt      private static int GetTableDepth ExcelWorksheet worksheet  int headerOffset                var i   1          var j   1          var cellValue   worksheet Cells i   headerOffset  j  Value          while  cellValue    null                        i                cellValue   worksheet Cells i   headerOffset  j  Value                     return i - 1    subtract one because we re going from rownumber  1 based  to depth  0 based             private static IEnumerable lt DataColumn gt  GetDataColumns ExcelWorksheet worksheet                return GatherColumnNames worksheet  Select x   gt  new DataColumn x               private static IEnumerable lt string gt  GatherColumnNames ExcelWorksheet worksheet                var columns   new List lt string gt              var i   1          var j   1          var columnName   worksheet Cells i  j  Value          while  columnName    null                        columns Add GetUniqueColumnName columns  columnName ToString                  j                columnName   worksheet Cells i  j  Value                     return columns             private static string GetUniqueColumnName IEnumerable lt string gt  columnNames  string columnName                var colName   columnName          var i   1          while  columnNames Contains colName                         colName   columnName   i ToString                i                       return colName

User · Answer

MS Office Interop is slow and even Microsoft does not recommend Interop usage on server side and cannot be use to import large Excel files  For more details see why not to use OLE Automation from Microsoft point of view   Instead  you can use any Excel library  like EasyXLS for example  This is a code sample that shows how to read the Excel file   ExcelDocument workbook   new ExcelDocument    DataSet ds   workbook easy ReadXLSActiveSheet AsDataSet  excel xls    DataTable dataTable   ds Tables 0     If your Excel file has multiple sheets or for importing only ranges of cells  for better performances  take a look to more code samples on how to import Excel to DataTable in C  using EasyXLS

User · Answer

Please check out the below links  http   www codeproject com Questions 376355 import-MS-Excel-to-datatable  6 solutions posted   Best  Fastest way to read an Excel Sheet into a DataTable

[c#] Importing Excel into a DataTable Quickly

Examples related to c#

Examples related to .net

Examples related to excel

Examples related to office-interop