如何将一些VBA代码嵌入到使用ClosedXML创建的电子表格中？

I am using ClosedXML to generate spreadsheets from C# (asp.net-mvc) and it works great. I have one additional requirement so I wanted to get some feedback on how I could achieve this.

我使用ClosedXML从C＃（asp.net-mvc）生成电子表格，效果很好。我还有一个额外的要求，所以我希望得到一些关于如何实现这一目标的反馈。

I want to save as a macro enabled workbook and when I just give it a "xlsm" extension it doesn't seem to open (versus a xlsx). Here is my code:

我想保存为启用宏的工作簿，当我给它一个“xlsm”扩展时，它似乎没有打开（相对于xlsx）。这是我的代码：

public ActionResult ExportExcel()
{
    MemoryStream stream = nModel.GenerateSS();
    return File(stream, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", "MySS.xlsx");
}

but if I try to do this:

但如果我尝试这样做：

public ActionResult ExportExcel()
{
    MemoryStream stream = nModel.GenerateSS();
    return File(stream, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", "MySS.xlsm");
}

Excel complains when trying to open.

Excel在尝试打开时抱怨。

Then, assuming I can do #1, I need to be able to take some VBA (assuming just a hard-coded function) and insert that into a module or workbook, so when someone opens the spreadsheet and clicks on Macros, they can run the macros. From googling, that doesn't seem supported by ClosedXML so I was curious if anyone has any alternative ways to achieve this?

然后，假设我可以做＃1，我需要能够采用一些VBA（假设只是一个硬编码的函数）并将其插入到模块或工作簿中，因此当有人打开电子表格并单击宏时，它们可以运行宏。从谷歌搜索，这似乎不支持ClosedXML所以我很好奇，如果有人有任何替代方法来实现这一点？

3 个解决方案

#1

The only way I know for inserting code to a VBA Project is to use Office.Interop.Excel along with Microsoft.Vbe.Interop (not sure about ClosedXML and other 3rd party...but still, wanted to share)

我知道将代码插入VBA项目的唯一方法是使用Office.Interop.Excel和Microsoft.Vbe.Interop（不确定ClosedXML和其他第三方......但仍然想要共享）

But since you are asking for an alternative this is how I would go about;

但既然你要求另一种选择，那就是我要去做的事情;

Creating a workbook and inserting a macro to it using C#

使用C＃创建工作簿并向其插入宏

---

You need to allow programmatic access to VBA Project in Excel.

您需要允许以编程方式访问Excel中的VBA项目。

• (Excel) File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model
• （Excel）文件 - >选项 - >信任中心 - >信任中心设置 - >宏设置 - >信任对VBA项目对象模型的访问

In your C# Solution add COM references to

在您的C＃解决方案中添加COM引用

• Microsoft Visual Basic for Applications Extensibility 5.3

  Microsoft Visual Basic for Applications可扩展性5.3

• Microsoft Excel 14.0 Object Library

  Microsoft Excel 14.0对象库

Add using directives to your C# code behind file

将using指令添加到C＃代码隐藏文件中

using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Vbe.Interop;
using System.Runtime.InteropServices;

Now follow the code and comments

现在按照代码和注释

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

VBProject vbProj = wb.VBProject; // access to VBAProject
VBComponent vbComp = vbProj.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule); // adding a standard coding module
vbComp.CodeModule.DeleteLines(1, vbComp.CodeModule.CountOfLines); //emptying it

// this is VBA code to add to the coding module
string code = "Public Sub HelloWorld() \n" +
                "    MsgBox \"hello world!\" \n" +
                "End Sub";

// code should be added to the module now
vbComp.CodeModule.AddFromString(code); 

// location to which you want to save the workbook - desktop in this case
string fullPath = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\macroWorkbook.xlsm";

// run the macro
xlApp.Run("HelloWorld");

// save as macro enabled workbook
wb.SaveAs(Filename: fullPath, FileFormat: XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
xlApp.Quit();

In the above code;

在上面的代码中;

you create an host Excel application, add a workbook. Access VBA Project Object module, add a new standard coding module to the VBA Project, write a VBA macro to that module. Application.Run("HelloWorld") simply calls the macro - note you can comment out this section if you don't want the box to pop-up. Then in the end you save the workbook as a Macro Enabled Workbook to a location specified in fullPath variable.

您创建一个主机Excel应用程序，添加一个工作簿。访问VBA项目对象模块，向VBA项目添加新的标准编码模块，将VBA宏写入该模块。 Application.Run（“HelloWorld”）只是调用宏 - 注意如果你不想弹出框，你可以注释掉这一部分。然后，最后将工作簿保存为Macro Enabled Workbook到fullPath变量中指定的位置。

PS. Please note I haven't added any error handling.

PS。请注意我没有添加任何错误处理。

For additional tips on C# and VBA see this blog Hope this helps :)

有关C＃和VBA的其他提示，请参阅此博客希望这有助于:)

#2

ClosedXML currently doesn't support writing VBA directly.

ClosedXML目前不支持直接编写VBA。

However you can manually add in a .bin file containing the predefined VBA macros/functions to an existing .XLSX spreadsheet programmatically. Write all of your VBA macros beforehand and save the file as a .XLSM file. Extract the contents to a folder and you'll have a vbaProject.bin containing all of your code. To get the VBA project to load properly, the workbook.xml requires the following additions:

但是，您可以通过编程方式手动将包含预定义VBA宏/函数的.bin文件添加到现有的.XLSX电子表格中。事先编写所有VBA宏并将文件另存为.XLSM文件。将内容解压缩到一个文件夹，你将拥有一个包含所有代码的vbaProject.bin。要正确加载VBA项目，workbook.xml需要以下添加：

<Default Extension="bin" ContentType="application/vnd.ms-office.vbaProject"/>
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.ms-excel.sheet.macroEnabled.main+xml"/>

The vbaProject.bin will need to be placed under the 'xl' folder folder, and the workbook will require a codeName GUID and alias ('ThisWorkbook' by default). All worksheets will also need a codeName (matching the sheet name) so that calls in the macros actually work when referencing named sheets.

vbaProject.bin需要放在'xl'文件夹文件夹下，工作簿需要一个codeName GUID和别名（默认情况下为'ThisWorkbook'）。所有工作表还需要一个codeName（与工作表名称匹配），以便在引用命名工作表时，宏中的调用实际上有效。

John McNamara has created a working proof-of-concept in Perl that's worth checking out here: http://blogs.perl.org/users/john_mcnamara/2012/12/adding-macros-to-excelwriterxlsx.html

John McNamara在Perl中创建了一个工作概念验证，值得一试：http：//blogs.perl.org/users/john_mcnamara/2012/12/adding-macros-to-excelwriterxlsx.html

Scanning over the assembly in IlSpy, this should be fairly straightforward to implement in ClosedXML, therefore if you have success here it would be a good idea to push your contribution over to the main project.

在IlSpy中扫描程序集时，在ClosedXML中实现这一点应该相当简单，因此如果您在此处取得成功，那么将您的贡献推送到主项目将是一个好主意。

#3

return File(stream, @"application/vnd.ms-excel.sheet.macroEnabled.12", "MySS.xlsm");

According to this link, the MIME type for Office Excel 2007 macro-enabled workbook (.xlms) is

根据此链接，Office Excel 2007启用宏的工作簿（.xlms）的MIME类型是

application/vnd.ms-excel.sheet.macroEnabled.12.

应用/ vnd.ms-excel.sheet.macroEnabled.12。

Have you seen this post on the ClosedXml codeplex project regarding support for macros.

您是否在ClosedXml codeplex项目中看到有关支持宏的这篇文章。