Program that works in all version of excel
Solution 1
If you know all your users will have excel, you can convert your excel references to "Object"s instead of hardcoded excel objects and then remove the DLL references from your project. This will work will all versions of office, as long as you don't use some NEW function that is not in the older versions.
For example, instead of this:
Dim _xlApp As Excel.Application
Dim _xlBook As Excel.Workbook
Try this:
Dim _xlApp As Object 'Excel.Application
Dim _xlBook As Object 'Excel.Workbook
Everything works the same (except no intellisence) except for instantiation of the excel application:
_xlApp = CreateObject("Excel.Application") 'New Excel.Application
I have done it this way for 15 years without ever changing my code based on different versions.
Solution 2
There is a way for your application to work even if there is no excel installed at all. You can use native excel libraries, there are free .NET libraries that you can use for that purpose.
For XLS format you can use NPOI and EPPlus for XLSX, downside of this approach is that you will have to change your existing code if you decide to go this way.
EPPlus example of exporting DataTable to excel, from this SO question :
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
pck.Save();
}
For NPOI example look here :
http://dotnetslackers.com/DataSet/re-511450_Export_an_ADO_NET_DataTable_to_Excel_using_NPOI.aspx
Solution 3
Thanks for your help @Steve and @Antonio Bakula
Here is my late binding code which will work on all version of excel ..
Try
Dim app As Object
Dim xlbook As Object
Dim xlsheet As Object
app = CreateObject("Excel.Application")
xlbook = app.Workbooks.Add()
xlsheet = xlbook.ActiveSheet
app.Visible = True
Dim iX As Integer
Dim iY As Integer
Dim iC As Integer
Dim iz As Integer
For iC = 0 To DataGridView1.Columns.Count - 1
xlsheet.Cells(1, iC + 1).Value = DataGridView1.Columns(iC).HeaderText
Next
iz = 1
For iX = 0 To DataGridView1.Rows.Count - 1
For iY = 0 To DataGridView1.Columns.Count - 1
Dim a As String = DataGridView1(iY, iX).Value
If a <> Nothing Then xlsheet.Cells(iz + 1, iY + 1).value = DataGridView1(iY, iX).Value.ToString
Next
iz = iz + 1
Next
MsgBox("Export Done", MsgBoxStyle.Information, "MODEL AND WARRANTY")
app.Visible = True
app.UserControl = True
releaseobject(app)
releaseobject(xlbook)
releaseobject(xlsheet)
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Release the excel object once extraction is done .
Sub releaseobject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
End Try
End Sub
Sathish Kothandam
Worked with Amazon(Business Intelligence Engineer) & HP(MIS) Currently working with FPS as DBA. More at LinkedIn : https://www.linkedin.com/in/sathish-kothandam-67984495/
Updated on July 11, 2022Comments
-
Sathish Kothandam almost 2 years
I have recently developed a Windows Form application which has several datagrids. My client needs to export the data into Excel. They are using different versions of Excel (i.e 2003,2007,2010,2013) but I'm using Office 2013. I have used excel 2013 references
(Microsoft excel 15.0 object library)
in my program. Recently, my client reported that the export option was not working. From my investigation it seems that my application is working fine for those who are using Office 2013 but not working for previous Office versions.
What can I do to make sure my application works in older versions of Excel?