vba 如何在没有 Excel 对象的情况下通过 VB6 创建 Excel 工作表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/640026/
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
How do you create an Excel worksheet through VB6 without the Excel Object?
提问by darudude
I was recently put in charge of updating a VB6 data collection app, to add the ability to generate Excel reports and print them through the app (both have to be done on same computer). Normally this wouldn't be an issue, I've generated Excel reports with VB6 before using the Excel Object.
我最近负责更新 VB6 数据收集应用程序，以添加生成 Excel 报告并通过该应用程序打印它们的功能（两者都必须在同一台计算机上完成）。通常这不会成为问题，在使用 Excel 对象之前，我已经用 VB6 生成了 Excel 报告。
So I went ahead and coded together the changes and presented them, and then I was told that the machine on which this program will be isntalled won't have a full copy of Excel on it, and I must come up with another solution.
所以我继续将更改编码在一起并呈现出来，然后我被告知安装该程序的机器上没有 Excel 的完整副本，我必须想出另一个解决方案。
I tried creating delimited files (comma seperated or text), but when these files are opened with excel they do not format nicely i.e. if one cell has 20 characters, half the chracters will be cut off due to the fixed cell size.
我尝试创建分隔文件（逗号分隔或文本），但是当这些文件用 excel 打开时，它们的格式不太好，即如果一个单元格有 20 个字符，由于固定的单元格大小，一半的字符将被截断。
I have a couple more ideas:
我还有几个想法：
1) I know openoffice has an api. Can this api be used to generate a properly formatted excel file? Is there a COM object I can use?
1) 我知道 openoffice 有一个 api。这个api可以用来生成格式正确的excel文件吗？有我可以使用的 COM 对象吗？
2)I found this tool: http://www.carlosag.net/Tools/ExcelXmlWriter/However, it was written in VB.NET. Can I still use this tool in VB6?
2）我找到了这个工具：http: //www.carlosag.net/Tools/ExcelXmlWriter/但是，它是用 VB.NET 编写的。我还能在 VB6 中使用这个工具吗？
I'm really stumped and don't know in which direction to head next. Anybody have any ideas regarding the utitiles above? Also, I am also open to any other suggestions/ better methods. Anything that would help me accomplish this task would be appreciated!
我真的很难过，不知道下一步该往哪个方向发展。有人对上述实用程序有任何想法吗？此外，我也愿意接受任何其他建议/更好的方法。任何能帮助我完成这项任务的东西都将不胜感激！
NOTE: The Excel version that will be used to view these reports is Excel 2007
注意：用于查看这些报告的 Excel 版本是 Excel 2007
采纳答案by darudude
I found a solution:
我找到了一个解决方案：
I found a DLL that can be used to create and print Excel Spreadsheets. I think it is the same as the XML writer but for VB6.
我找到了一个可用于创建和打印 Excel 电子表格的 DLL。我认为它与 XML 编写器相同，但适用于 VB6。
回答by Joel Coehoorn
Let's take a look at these two requirements:
我们来看看这两个要求：
add the ability to generate Excel reports and print them through the app.
添加生成 Excel 报告并通过应用程序打印它们的功能。
and
和
the machine on which this program will be isntalled[sic] won't have a full copy of Excel
安装此程序的机器[原文如此] 没有 Excel 的完整副本
Unless I misunderstand you, those seem to be mutually exclusive. If you mean you'll just be generating the reports on one machine and they'll be viewed and printed elsewhere, you might try using SpreadSheetML.
除非我误解了你，否则这些似乎是相互排斥的。如果您的意思是只在一台机器上生成报告，而它们将在其他地方查看和打印，您可以尝试使用SpreadSheetML。
Be careful when googling for additional info on SpreadSheetML: there's a lotof misinformation out there that confuses SpreadSheetML with the new Xml format used for Excel in Office 2007. SpreadSheetML works as far back as OfficeXP, and even in a limited sense in Office 2000.
在 Google 上搜索有关 SpreadSheetML 的其他信息时要小心：有很多错误信息将 SpreadSheetML 与 Office 2007 中用于 Excel 的新 Xml 格式混淆。SpreadSheetML 可以追溯到 OfficeXP，甚至在 Office 2000 中的作用有限。
回答by Will Rickards
You can save excel files as html. In a web app I wrote that needed to export the data as excel, I just created an html file like the one excel exports. Then I served it as application/vnd.ms-excel. Excel opens it just fine. You could probably do something similar.
您可以将 excel 文件另存为 html。在我编写的需要将数据导出为 excel 的 Web 应用程序中，我刚刚创建了一个 html 文件，就像一个 excel 导出文件一样。然后我将它作为 application/vnd.ms-excel 使用。Excel 打开它就好了。你可能会做类似的事情。
You can also find a library like the one you linked that writes the new office file format directly as I think they are derivatives of xml.
您还可以找到一个像您链接的库一样直接编写新的办公文件格式的库，因为我认为它们是 xml 的衍生物。
回答by shahkalpesh
Create the CSV file as you would normally.
像往常一样创建 CSV 文件。
Let your desktop app do the formatting on the machine where XLS files are available, before it is printed.
在打印之前，让您的桌面应用程序在 XLS 文件可用的机器上进行格式化。
回答by RS Conley
If you are willing to live with installing the .NET runtime you can wrap the library in option #2 with a .NET class exposed to COM. The class just passes calls to ExcelXMLwriter.
如果您愿意安装 .NET 运行时，您可以使用公开给 COM 的 .NET 类将库包装在选项 #2 中。该类只是将调用传递给 ExcelXMLwriter。
The alternative is to study the XML standard and write your own writer but that probably overkill for the project.
另一种方法是研究 XML 标准并编写自己的编写器，但这对项目来说可能有点过分。
As for OpenOffice take a look here. Especially the VBA section of this page here. There appears to be a COM component you can use.
回答by RS Conley
Creating a SpreadsheetML file from VB6 should be very simple It's just a text file containing a particular flavor of xml. I belive you can get all the docs for SpreadsheetML and WordML from MSDN.
从 VB6 创建一个 SpreadsheetML 文件应该非常简单它只是一个包含特定风格的 xml 的文本文件。我相信您可以从 MSDN 获得有关 SpreadsheetML 和 WordML 的所有文档。
I currently produce around 60 different custom reports from a vb.net application using SpreadsheetML. There is at least one potential problem due to the changes in file extensions and document security implemented in Excel 2007. If you generate a SpreadsheetML based excel file and use the .xls extension it will open just fine in 2003. Excel 2007 will pop up a dialog informing you that the file extension does not match the file content. It will open and display the file correctly after the warning dialog. If you change the extension on the SpreadsheetML document to .xml, Excel 2007 will open it with no complaint. However your xml based spreadsheets may open in IE on machines running Excel 2003.
我目前使用 SpreadsheetML 从 vb.net 应用程序生成大约 60 个不同的自定义报告。由于 Excel 2007 中实现的文件扩展名和文档安全性的更改，至少存在一个潜在问题。如果生成基于 SpreadsheetML 的 Excel 文件并使用 .xls 扩展名，它将在 2003 年正常打开。Excel 2007 将弹出一个对话框通知您文件扩展名与文件内容不匹配。它将在警告对话框后打开并正确显示文件。如果您将 SpreadsheetML 文档的扩展名更改为 .xml，Excel 2007 将毫无问题地打开它。但是，您的基于 xml 的电子表格可能会在运行 Excel 2003 的机器上的 IE 中打开。
回答by Thunder
回答by Mike D
I too would go with the CSV option. Requires no DLL or other type of object. One caveat is to be VERY careful about the formatting your numbers are using, because a value of 1,024.00 would create two columns and hose the entire workbook. it would be formatted as something like Format(Numberfield,"#########.#0")
我也会选择 CSV 选项。不需要 DLL 或其他类型的对象。一个警告是要非常小心您的数字使用的格式，因为 1,024.00 的值会创建两列并冲洗整个工作簿。它会被格式化为类似 Format(Numberfield,"#########.#0")
回答by onedaywhen
It is possible to create an Excel workbook on a machine without Excel installed** by using a Jet connection -- any Jet connection -- to executing a "make table query" SQL DML or CREATE TABLESQL DLL where the table in question is an Excel one e.g. consider this VBA code:
可以在没有安装 Excel 的机器上创建 Excel 工作簿**，方法是使用 Jet 连接——任何 Jet 连接——执行“生成表查询”SQL DML 或CREATE TABLESQL DLL，其中有问题的表是 Excel 表例如考虑这个 VBA 代码：
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
' Create a new Jet .mdb
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
' Use .mdb's Jet connection to create an Excel table
' (will create a new workbook to contain it)
.ActiveConnection.Execute _
"CREATE TABLE" & _
" [Excel 8.0;HDR=NO;Database=C:\db.xls;].[Sheet1]" & _
" (col FLOAT);"
End With
Jet is a deprecated Windows component that is ever-present on pre-Vista Windows machines and remains a free download from MS via MDAC.
Jet 是一个已弃用的 Windows 组件，它一直存在于 Vista 之前的 Windows 机器上，并且仍然可以通过 MDAC 从 MS 免费下载。
That said, "to print them through the [Excel] app" you clearly need the Excel app!
也就是说，“要通过 [Excel] 应用程序打印它们”，您显然需要 Excel 应用程序！
** I think it is possible: I can't find a machine without Excel on which to test!
** 我认为这是可能的：我找不到没有 Excel 的机器来测试！
回答by NileshChauhan
Check this: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271572
检查这个：http: //support.microsoft.com/default.aspx?scid=kb;en-us;Q271572
Do the same and save the file with .xls extension instead.
执行相同操作并使用 .xls 扩展名保存文件。
Alternatively, you can create HTML Table in-momery content and write it into a file, with extension xls.
或者，您可以在内存中创建 HTML 表格内容并将其写入文件，扩展名为 xls。
