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.
source to share
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.
source to share
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
source to share