如何将一些VBA代码嵌入到使用ClosedXML创建的电子表格中？ [英] How can I embed some VBA code into a spreadsheet created using ClosedXML?

问题描述

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

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

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

但如果我尝试这样做：

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

Excel在尝试打开时抱怨。

然后，假设我可以做＃1，我需要能够采取一些VBA（假设只是一个硬编码的功能），并将其插入模块或工作簿，所以当有人打开电子表格并点击在宏上，他们可以运行宏。从Google搜索结果来看，ClosedXML似乎并不支持，所以如果有人有任何其他方法可以实现这一点，我很好奇。

解决方案

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

但是，由于你要求一个替代方案是我将如何去做;

创建一个工作簿并使用C＃插入一个宏

---

您需要
希望这有帮助：）

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.

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 complains when trying to open.

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?

解决方案

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;

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

---

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

• (Excel) File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model

In your C# Solution add COM references to

• Microsoft Visual Basic for Applications Extensibility 5.3

• Microsoft Excel 14.0 Object Library

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;

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.

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

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

这篇关于如何将一些VBA代码嵌入到使用ClosedXML创建的电子表格中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！