reading excel file in .net

nick chan
nick chan used Ask the Experts™
on
hi guys, what's the easiest free .net library to read excel files ? any help is greatly appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
The easiest "free" library would be either the Interops libraries, or using an OLEDB connection to open the spreadsheet.

Interops essentially allows you to automate Excel, which you most likely do not want to do (given that you're on a web platform).

An OLDEB connection is fairly straight forward. Here's the connection string I use:

Provider=" & provider & ";Data Source=" & strFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

Where provider = Microsoft.ACE.OLEDB.12.0 and strFilePath = the full path to my Excel file.
Dim connStr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties='Excel 12.0 Xml;HDR=YES'", filePath)
        Dim dt As DataTable = New DataTable
Dim da As OleDbDataAdapter
        Dim ds As New DataSet
        Try
            Using conn = New System.Data.OleDb.OleDbConnection(connStr)
                conn.Open()
                da = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1]", conn)
                da.Fill(ds)
            End Using
            Return ds.Tables(0)
        Catch ex As Exception
            Console.Write(ex.Message)
            Return Nothing
        End Try

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial