Convert xls to xlsx without interop

Question

I have a computer(actually several machines) on my companies network that do not have office installed. I need to develop a vb.net program that will parse information from a spreadsheet which is usually in an .xls format. I don't feel that there are many options for reading data from an .xls document without the interop. If so what are some options?

If I could even convert an .xls to .xlsx programmatically without the interop, that would be an option too. I could then use ClosedXML to parse from a xlsx. Or is there a way to read an .xls file with ClosedXML that I'm not seeing?

Any help would be great as I am having trouble finding the answer to this one.

Matt Wilko · Accepted Answer · 2014-03-26T16:12:24.917

You don't need to use com interop to read from an Excel file. You can use OleDb (although you might need to install the office data connectivity components)

Imports System.Data.OleDb:

Public Shared Function ReadExcelWorkSheet(ByVal sheetName As String, ByVal connectionString As String) As DataTable
    Dim sql As String = String.Format("SELECT * FROM [{0}$]", sheetName)
    Dim dt = New DataTable()

    Using conn As OleDbConnection = New OleDbConnection(connectionString)
        Using cmd = New OleDbCommand(sql, conn)
            conn.Open()
            Using adapter = New OleDbDataAdapter(cmd)
                adapter.Fill(dt)
            End Using
        End Using
        conn.Close()
    End Using
    Return dt
End Function

Where connection string is for xlsx files:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

or for xls files:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myOldExcelFile.xls;Extended Properties="Excel 8.0;HDR=YES";

more info on the connection strings here

Thanks! I appreciate the code snippet too. I'll just have to install the Microsoft.Ace.OleDB.12.0 provider, but I think this should work. — HKImpact, Mar 26 '14 at 16:19

score 1 · Answer 2 · answered Mar 26 '14 at 16:05

1

You can read it via ADO.Net. I'd need to see your existing code, but this should allow you to open it without Excel.

System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties='Excel 12.0 Xml;HDR=YES';")

answered Mar 26 '14 at 16:05

Jimmy Smith

2,452
1
16
19

Update: The components this uses may have to be installed separately, per this [other answer](http://stackoverflow.com/a/3971925/2258) – Jimmy Smith Mar 26 '14 at 16:09
Thanks Jimmy, this is pretty much what I'm going to do. Appreciate the help – HKImpact Mar 26 '14 at 16:20

Convert xls to xlsx without interop

2 Answers2