How to export to excel from a winform vb.net 2008 without office installed?

Question

Hello,

I am building a windows form application using visual basic (visual studio 2008).

The idea is to query a MySQL DB and export the results to an excel document.

I managed to do this using this code (I will just show the export to excel part):

    Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports System.Runtime.InteropServices

Private Sub btn_getReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_getReport.Click

    Dim wb As Excel.Workbook
    Dim ex As New Excel.Application
    wb = ex.Workbooks.Add(System.Reflection.Missing.Value)
    Dim sheet As Excel.Worksheet = CType(wb.Worksheets.Add, Excel.Worksheet)
    sheet.Name = "algo"

    Dim i As Integer = 1

    Try
        While reader.Read
            sheet.Cells(i, 1) = CStr(reader.Item(0))
            sheet.Cells(i, 2) = CStr(reader.Item(1))
            sheet.Cells(i, 3) = CStr(reader.Item(2))
            i += 1
        End While
    Catch MyEx As MySqlException
        RaiseEvent MySqlError(Err, MyEx, "read")
    Catch exc As Exception
        RaiseEvent MySqlError(Err, exc, "read")
    End Try


    Dim dialog As New SaveFileDialog
    Dim result As DialogResult = dialog.ShowDialog
    Try
        wb.SaveAs(dialog.FileName)
    Catch exerr As Exception
    End Try
    'Show the spreadsheet.
    'ex.Visible = True
    'wb.Activate()

End Sub

And it works fine on my laptop (which has office 2003 installed), but when I create the setup package and install it on the server where I am going to use it (which does not have office installed), I get this error:

"Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154."

For what I have read, this is a problem while trying to use excel when its not present on the computer, I can understand that, what really confuses me is that I have used apps that export information to excel even running on computers without having office installed on them, how can they do that?

And for the record, I need the excel file, not a CSV.

Thanks a lot.

Answer 1

You've absolutely diagnosed the problem properly. That's the good news. The bad news is that you cannot use Excel Interop without the Excel DLLs installed on the machine.

Your options:

1. Install Excel on the machine where this is going to run.
2. Pull the DLLs (libraries) and deploy them with your application. In doing so, you're probably violating license agreements with Microsoft. Do so at your own risk.
3. Create Excel spreadsheets by hand in the Open XML format used by Office 2007.

To do option three, take a look at the format for Excel 2007 in this article: http://msdn.microsoft.com/en-us/library/aa338205.aspx

In a nutshell, you'll read your data into a dataset. Then you'll export it to an XML file. Cram that file along with any supporting info into a zip file and change the extension to .xlsx. It's a bit more complex than that, but not much.

Answer 2

If you really want to avaid the easy way of exporting to a comma or tabdelimited file which excel can handle perfectly...

...then export to the Excel-XML format (spreadsheetml) by generating xml/using an xsl. You can find an example here. Should work from Excel 2002+.

Answer 3

SpreadsheetGear for .NET can export Excel files, works with any .NET solution including WinForms and does not require Excel to be installed.

You can see live samples here and download the free trial here.

Disclaimer: I own SpreadsheetGear LLC