vb.net 适用于所有版本的excel的程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18873799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Program that works in all version of excel
提问by Sathish Kothandam
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
我最近开发了一个 Windows 窗体应用程序，它有几个数据网格。我的客户需要将数据导出到 Excel 中。他们使用不同版本的 Excel（即 2003、2007、2010、2013），但我使用的是 Office 2013。我使用了 excel 2013 参考
(Microsoft excel 15.0 object library)
（Microsoft excel 15.0 对象库）
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.
在我的程序中。最近，我的客户报告说导出选项不起作用。从我的调查来看，我的应用程序对于那些使用 Office 2013 但不适用于以前的 Office 版本的人来说似乎工作正常。
What can I do to make sure my application works in older versions of Excel?
我该怎么做才能确保我的应用程序可以在旧版本的 Excel 中运行？
回答by Steve
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.
如果您知道所有用户都拥有 excel，则可以将 excel 引用转换为“对象”而不是硬编码的 excel 对象，然后从项目中删除 DLL 引用。只要您不使用旧版本中没有的某些新功能，这将适用于所有版本的 office。
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:
除了 excel 应用程序的实例化之外，一切都一样（除了没有智能）：
_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.
我已经这样做了 15 年，从未根据不同版本更改我的代码。
回答by Antonio Bakula
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.
即使根本没有安装 Excel，您的应用程序也有一种方法可以工作。您可以使用本机 excel 库，也有免费的 .NET 库可用于此目的。
For XLS format you can use NPOIand EPPlusfor XLSX, downside of this approach is that you will have to change your existing code if you decide to go this way.
对于 XLS 格式，您可以将NPOI和EPPlus用于 XLSX，这种方法的缺点是，如果您决定采用这种方式，则必须更改现有代码。
EPPlus example of exporting DataTable to excel, from this SO question:
将 DataTable 导出到 excel 的 EPPlus 示例，来自这个 SO问题：
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 :
对于 NPOI 示例，请看这里：
http://dotnetslackers.com/DataSet/re-511450_Export_an_ADO_NET_DataTable_to_Excel_using_NPOI.aspx
http://dotnetslackers.com/DataSet/re-511450_Export_an_ADO_NET_DataTable_to_Excel_using_NPOI.aspx
回答by Sathish Kothandam
Thanks for your help @Steve and @Antonio Bakula
感谢您的帮助@Steve 和@Antonio Bakula
Here is my late binding code which will work on all version of excel ..
这是我的后期绑定代码，适用于所有版本的 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 .
提取完成后释放 excel 对象。
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
