Performance in reading Excel File using OpenXML

As of now im reading all version of excel files using oledbreader. i referred a dll for reading Excel 2010 files. but i cannot read some excel 2010 files using oledbreader. so i would like to use openxml for 开发者_C百科reading all excel files. is ter any performance issue in this? which is better?

I have had very good luck using the following code to retrieve table names(worksheet names) and column names from Excel spreadsheets.

Private Sub GetWorksheetData

  Dim xlBaseConnStr1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XLS;Extended Properties=""Excel 8.0;HDR=Yes"""
  Dim xlBaseConnStr2 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XLS;Extended Properties=""Excel 12.0 Xml;HDR=YES"""
  Dim xlName As String
  Dim conStr As String

  Dim oDia As New OpenFileDialog

  oDia.ShowDialog()

  xlName = oDia.FileName

  If xlName = "" Then
     Exit Sub
  End If

  Dim extType As String = Path.GetExtension(xlName)
  Select Case extType
     Case ".xls"
        conStr = xlBaseConnStr1.Replace("XLS", xlName)
     Case ".xlsx"
        conStr = xlBaseConnStr2.Replace("XLS", xlName)
     Case Else
        MessageBox.Show("Unrecognized file type")
        Exit Sub
  End Select

  Dim dtSheets As New DataTable

  Using cn As New OleDbConnection(conStr)
     cn.Open()
     dtSheets = cn.GetSchema("Columns")
  End Using

  DataGrid1.ItemsSource = dtSheets.DefaultView

End Sub

The above chunk of code returns the following data from a random Excel spreadsheet that I had laying around.

You will need to import the following namespaces for this to work:

Imports System.Data.OleDb
Imports System.Data
Imports Microsoft.Win32
Imports System.IO

If the spreadsheet that you are trying to access has macros, the above code may fail.