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

I am creating a windows form application using visual base (visual studio 2008).

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

I managed to do it with this code (I'll 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 an installation package and install it on the server where I will be using it (which does not have office installed), I get this error:

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

For what I have read it is a problem when trying to use excel when it is not on the computer, I can understand that it really confuses me that I have used applications that export information to succeed even on computers without having office installed on them like can they do it?

And for writing, I need an excel file, not a CSV.

Many thanks.

+2


source to share


3 answers


Your diagnosis is absolutely correct. This is good news. The bad news is that you cannot use Excel Interop without the Excel DLLs installed on your computer.

Your options:

  • Install Excel on the machine where it will work.
  • Pull the DLLs (libraries) and deploy them with your application. By doing this, you are likely to be in violation of the Microsoft license agreements. Do this at your own risk.
  • Create Excel spreadsheets manually in the Open XML format used by Office 2007.


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

In a nutshell, you will be reading your data into a dataset. Then you export it to an XML file. Write this file along with any supporting information into a zip file and change the extension to .xlsx. It's a little tricky, but not much.

0


source


If you really want to ask for an easy way to export to a comma or tab delimited file that is great to handle ...



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

0


source


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 examples here and download your free trial here .

Disclaimer: I own SpreadsheetGear LLC

0


source







All Articles