如何仅使用 .xla 文件中的代码从 Excel VBA 中 GZip 压缩文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/167888/
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 can I GZip compress a file from Excel VBA using code in an .xla file only?
提问by Matthew Murdoch
I need to be able to GZip compress a file in an Excel VBA function. Specifically I need to be able to use the 'deflate' algorithm.
我需要能够在 Excel VBA 函数中 GZip 压缩文件。具体来说，我需要能够使用“放气”算法。
Is there a way to do this without having to exec a command line application? With no dependency on external tools the code will be more robust.
有没有办法在不必执行命令行应用程序的情况下做到这一点？由于不依赖于外部工具，代码将更加健壮。
Ideally the code would make use of pre-installed VBA or COM library functions - I don't want to have to implement this logic myself or install DLLs etc.
理想情况下，代码将使用预安装的 VBA 或 COM 库函数 - 我不想自己实现这个逻辑或安装 DLL 等。
If possible, I want installation of the function to be as simple as adding a .xla to the available Excel Add-Ins. No DLLs, EXEs, registry entries etc. required.
如果可能，我希望该功能的安装就像将 .xla 添加到可用的 Excel 加载项一样简单。不需要 DLL、EXE、注册表项等。
EditCan I make use of the .NET GZipStream to do this?
编辑我可以使用 .NET GZipStream 来做到这一点吗？
采纳答案by Joe Pineda
VBA (which is really a dialect of VB6) is slow for these kind of applications. I remember I once implemented Shannon-Fano algorithm on VB6 and on C, the C version was about 10 times faster, even after being turned into a DLLMain and called from there rather than on a command-line executable.
VBA（实际上是 VB6 的一种方言）对于这类应用程序来说很慢。我记得我曾经在 VB6 和 C 上实现过 Shannon-Fano 算法，C 版本快了大约 10 倍，即使在变成 DLLMain 并从那里调用而不是在命令行可执行文件上调用之后也是如此。
There are lots of COM DLLs that provide compression services, both open source and shareware, and some of them implement GZIP's deflate algorithm. It'd be really simple to just call one function from such a DLL from your VBA code to do the compression on your behalf.
有许多 COM DLL 提供压缩服务，包括开源和共享软件，其中一些实现了 GZIP 的 deflate 算法。只需从您的 VBA 代码中的此类 DLL 中调用一个函数来代表您进行压缩，这将非常简单。
I understand your being reluctant on using something external to your application, though in this case you might have to apply an exception for performance's sake.
我理解您不愿意使用应用程序外部的东西，但在这种情况下，您可能必须为了性能而应用例外。
In an effort to completely spoil your fun, examine file ZIPFLDR.DLL on windows\system32. you may also like to take a look at these links:
为了彻底破坏您的乐趣，请检查 windows\system32 上的文件 ZIPFLDR.DLL。您可能还想看看这些链接：
- This has an example of how to do what you want (zipping using windows built-in ZIP capabilities) from VB.NET, it shouldn't be much different from VBA or VB6: Transparent ZIP with DLL call
- This one has a sample application on VB6 using windows built-in capabilities to zip (in ZIP rather than GZIP format, of course): Using Windows XP "Compressed Folder" shell extension to work with .zip files
- 这有一个示例说明如何从 VB.NET 执行您想要的操作（使用 Windows 内置 ZIP 功能进行压缩），它与 VBA 或 VB6 应该没有太大区别： Transparent ZIP with DLL call
- 这个在 VB6 上有一个示例应用程序，它使用 Windows 内置的压缩功能（当然是 ZIP 而不是 GZIP 格式）：Using Windows XP“Compressed Folder” shell extension to work with .zip files
Found both thru googling, you should be able to find more/better examples.
通过谷歌搜索找到两者，你应该能够找到更多/更好的例子。
回答by JPLemme
OK, I think I have an answer for you.
好的，我想我有一个答案给你。
zlib is a library written by the guy that wrote the deflate algorithm you don't want to implement. There is a win32 DLL available. Here's the FAQ regarding using it from Windows:
zlib 是由编写您不想实现的放气算法的人编写的库。有一个 win32 DLL 可用。以下是有关从 Windows 使用它的常见问题解答：
http://www.zlib.net/DLL_FAQ.txt
http://www.zlib.net/DLL_FAQ.txt
Check out question 7. The authors don't seem too keen on Windows users, and don't seem at all keen on VB users, but as long as they're kind enough to provide the library we can do the rest.
看看问题 7。作者似乎不太热衷于 Windows 用户，也不太热衷于 VB 用户，但只要他们足够好心提供库，我们就可以完成剩下的工作。
If this is enough to help you, then great. If you want help with calling the C library from VBA add a comment and we'll figure it out. I haven't done any VB-to-C calls in years--it sounds like fun.
如果这足以帮助您，那就太好了。如果您需要有关从 VBA 调用 C 库的帮助，请添加注释，我们会弄清楚的。多年来我没有做过任何 VB-to-C 调用——这听起来很有趣。
回答by JPLemme
If somebody wanted to compress files without relying on 3rd-party software they would generally implement it as a COM object/DLL so it would be available to more than just Excel. If somebody wanted to incorporate zip functionality into Excel they would use 3rd-party tools so they wouldn't have to re-implement the algorithm. So you're swimming against the tide. However...
如果有人想在不依赖第 3 方软件的情况下压缩文件，他们通常会将其实现为 COM 对象/DLL，因此它不仅可用于 Excel。如果有人想将 zip 功能合并到 Excel 中，他们将使用 3rd-party 工具，这样他们就不必重新实现算法。所以你在逆潮流而行。然而...
http://www.cpearson.com/excel/SaveCopyAndZip.htm
http://www.cpearson.com/excel/SaveCopyAndZip.htm
There are two versions. The COM Add-in version "...allows you to zip any workbook that has been saved to disk (but it may be in an unsaved state)." It relies on a Moonlight Software component but all the components and set-up are contained in the installer. It's not quite public domain but the license is less restrictive than the GPL. The end result is an Excel add-in (that uses a 3rd-party component).
有两个版本。COM 插件版本“...允许您压缩任何已保存到磁盘的工作簿（但它可能处于未保存状态）。” 它依赖于 Moonlight 软件组件，但所有组件和设置都包含在安装程序中。它不是完全公共领域，但许可证的限制不如 GPL。最终结果是一个 Excel 加载项（使用第 3 方组件）。
But if you really, truly don't want any dependencies on external tools you're either going to have to implement the compression algorithm yourself or wait until Microsoft builds that functionality into Windows and exposes it through Excel.
但是，如果您真的、真的不想依赖任何外部工具，您要么必须自己实现压缩算法，要么等到 Microsoft 将该功能构建到 Windows 中并通过 Excel 公开它。
I hope this helps.
我希望这有帮助。
回答by Philippe Grondier
It seems that you want to open a bottle of wine but you definitly refuse to use a bottle-opener. As long as there is no VBA function allowing the GZipping of a file, you will not be able to do the job without some external ressource such as a dll or exe file.
看起来你想开一瓶酒，但你绝对拒绝使用开瓶器。只要没有允许 GZipping 文件的 VBA 功能，如果没有一些外部资源（例如 dll 或 exe 文件），您将无法完成这项工作。
回答by JPLemme
If you want to implement the algorithm in VBA, you would need to (in VBA) save the spreadsheet and then use VB's I/O functions to open the file, deflate it, and save it again. For all intents and purposes it's identical to writing an ordinary VB application that works on a file. You might need to put the VBA macro in a separate workbook to avoid "file in use" types of errors, but if you reopen the file as read-only and save it with a different filename you should be OK keeping everything in one workbook.
如果你想在 VBA 中实现算法，你需要（在 VBA 中）保存电子表格，然后使用 VB 的 I/O 函数打开文件，压缩它，然后再次保存。就所有意图和目的而言，它与编写一个处理文件的普通 VB 应用程序相同。您可能需要将 VBA 宏放在一个单独的工作簿中以避免“文件正在使用”类型的错误，但是如果您以只读方式重新打开文件并使用不同的文件名保存它，您应该可以将所有内容保存在一个工作簿中。
But I'm almost certain that shelling out to gzip from within the VBA would be functionally identical and infinitely easier.
但我几乎可以肯定，从 VBA 中转至 gzip 会在功能上相同并且非常容易。
EDIT:Some code. It didn't fail when I ran it, so it's OK to keep everything in the same workbook.
编辑：一些代码。当我运行它时它没有失败，所以可以将所有内容保存在同一个工作簿中。
Sub main()
ActiveWorkbook.Save
Open "macrotest.xls" For Binary Access Read As #1
Open "newfile.zip" For Binary Access Write As #2
'do your stuff here
Close #2
Close #1
End Sub
