我怎么可以嵌入一些VBA code到A S preadsheet创建使用ClosedXML？ [英] How can I embed some VBA code into a spreadsheet created using ClosedXML?

问题描述

我使用ClosedXML生成从C＃（asp.net-MVC）■preadsheets和它的伟大工程。我有一个额外的要求，所以我想获得我怎么能做到这一点一些反馈。

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.

我想作为宏保存工作簿启用时，我只是给它一个XLSM扩展它似乎不开（相对于XLSX）。这里是我的code：

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:

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的抱怨。

Excel complains when trying to open.

然后，假设我能做到＃1，我需要能够采取一些VBA（假设只是一个硬codeD功能），并插入到一个模块或工作簿，所以当有人打开在S preadsheet和点击的宏，它们可以运行宏。从谷歌搜索，这似乎并不受ClosedXML支持，所以我很好奇，如果任何人有任何其他的方式来实现这一目标？

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?

推荐答案

我知道插入code到VBA项目的唯一方法是使用 Office.Interop.Excel 与 Microsoft.Vbe.Interop （不知道ClosedXML和其他第三方......但尽管如此，想分享）

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)

不过既然你问了一个替代我这是怎么会去;

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

创建一个工作簿和使用C＃中插入一个宏它

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

您需要允许在Excel编程访问VBA项目。

• （EXCEL）文件 - >选项 - >信任中心 - >信任中心设置 - >宏设置 - >信任对VBA项目对象模型

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

In your C# Solution add COM references to

• Microsoft Visual Basic的应用程序扩展5.3

• Microsoft Visual Basic for Applications Extensibility 5.3

的Microsoft Excel 14.0对象库

Microsoft Excel 14.0 Object Library

使用指令添加到您的C＃code隐藏文件

Add using directives to your C# code behind file

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

现在按照code和评论

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();

在上面的code;

您创建一个主机Excel应用程序，添加一个工作簿。访问VBA项目对象模块，添加一个新的标准编码模块的VBA项目，编写VBA宏的模块。 Application.Run（HelloWorld的）只需调用宏 - 注意，您可以注释掉这一部分，如果你不想框弹出。那么到底保存工作簿为的启用宏工作簿的在指定的位置完整路径变量。

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.

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

有关C＃和VBA额外的提示，请参见这个博客
希望这有助于：）

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

这篇关于我怎么可以嵌入一些VBA code到A S preadsheet创建使用ClosedXML？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！