将数据导出到Excel通过Open XML启用宏

I have an Excel Sheet which I am using as a template file for exporting data.

我有一张Excel工作表，我将其用作导出数据的模板文件。

The Excel Sheet is XLSM file and has few codes written in it in VBA.

Excel工作表是XLSM文件，在VBA中写入的代码很少。

Each time the file is copied and renamed with time stamp and data should be written to copied xlsm file but it is not writing the data.

每次复制文件并使用时间戳重命名时，应将数据写入复制的xlsm文件，但不写入数据。

I am using Open XML library for this.

我正在使用Open XML库。

The same is working if I use xlsx template file.

如果我使用xlsx模板文件，则同样有效。

Is it not possible to write on xlsm Excel Macro-enabled through Open XML?

是不是可以通过Open XML在xlsm Excel Macro上编写？

If yes, Any instructions to keep in mind.

如果是，请记住任何说明。

1 个解决方案

#1

The code in this answer will copy any excel file and switch it to a Macro Enabled Workbook. I have tested it against a simple Excel 2016 Workbook and Macro Enabled Workbook it creates a new file SaveMEW.xlsm in the same directory that the source file lives. Make sure the full path of the source file is put into `_sourceFile variable.

此答案中的代码将复制任何excel文件并将其切换到启用宏的工作簿。我已经针对一个简单的Excel 2016工作簿和宏启用工作簿对其进行了测试，它在源文件所在的同一目录中创建了一个新文件SaveMEW.xlsm。确保将源文件的完整路径放入`_sourceFile变量中。

    var _sourceFile = " PATH TO YOUR TEMPLATE FILE ";

    using (var templateFile = File.Open(_sourceFile, FileMode.Open, FileAccess.Read))
        {
            using (var stream = new MemoryStream())
            {
                templateFile.CopyTo(stream);
                using (var spreadsheetDocument = SpreadsheetDocument.Open(stream, true))
                {                
                    spreadsheetDocument.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
                }
                byte[] buffer = stream.ToArray();
                MemoryStream ms = new MemoryStream(buffer);
                FileStream file = new FileStream(System.IO.Path.GetDirectoryName(_sourceFile) + "/SaveMEW.xlsm",
                    FileMode.Create, FileAccess.Write);
                ms.WriteTo(file);
                file.Close();
            }
        }