OpenXML SDK 2.0 でExcelVBA作成に失敗
うーん、コードがヘタれ。
ぐぐってもなかなか情報が出てこない。ぐぐるのもヘタれ。
ExcelPackageでシートは作成できるけど、VBAは作成できない。
ベースのOpenXML SDK2.0 でも作成の仕方が解らない。
以下、ヘタれコード
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using OfficeOpenXml; using System.IO; using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml.Vml.Spreadsheet; using DocumentFormat.OpenXml.Office.Excel; namespace fromtest1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { FileInfo newFile = new FileInfo(@"c:\\test\\mynewfile4.xlsx"); using (ExcelPackage xlPackage = new ExcelPackage(newFile)) { ExcelWorksheet wksheet = xlPackage.Workbook.Worksheets.Add("test"); wksheet.Cell(1, 1).Value = "aaa"; wksheet.Cell(1, 2).Value = "bbb"; wksheet.Cell(2, 1).Formula = "3+2"; xlPackage.Save(); } } private void button2_Click(object sender, EventArgs e) { //FileInfo newFile = new FileInfo(@"C:\\test\\moduletest.xlsm"); using ( SpreadsheetDocument xl = SpreadsheetDocument.Create(@"c:\\test\\macro.xlsm", SpreadsheetDocumentType.MacroEnabledWorkbook)) { xl.AddWorkbookPart(); xl.WorkbookPart.Workbook = new Workbook(); xl.WorkbookPart.AddNewPart(); xl.WorkbookPart.AddNewPart (); string text = ""; text = @"public funcion aaa() as integer dim x as integer x = 1 aaa = x end function"; DocumentFormat.OpenXml.Office.Excel.Macrosheet ms = new DocumentFormat.OpenXml.Office.Excel.Macrosheet(); FormulaMacro fm = new FormulaMacro(); fm.Text = text; Macrosheet m = new Macrosheet(); //m.Append(fm); m.AppendChild (fm); //VbaProjectPart p = xl.WorkbookPart.VbaProjectPart; //VbaDataPart vd = p.AddNewPart (); //xl.WorkbookPart.VbaProjectPart.Parts.First().OpenXmlPart = p; //MacroSheetPart msp = new MacroSheetPart(); //msp.Macrosheet = m; xl.WorkbookPart.MacroSheetParts.First().Macrosheet = m; xl.WorkbookPart.MacroSheetParts.First().Macrosheet.Save(); //xl.WorkbookPart.MacroSheetParts.First().Macrosheet = /// new DocumentFormat.OpenXml.Office.Excel.Macrosheet(fm); //xl.WorkbookPart.MacroSheetParts.First().Macrosheet.Save(); //sheet xl.WorkbookPart.WorksheetParts.First().Worksheet = new Worksheet( new SheetData( new Row( new Cell( new InlineString( new DocumentFormat.OpenXml.Spreadsheet.Text("aaa"))) { DataType = CellValues.InlineString } ))); xl.WorkbookPart.WorksheetParts.First().Worksheet.Save(); xl.WorkbookPart.Workbook.AppendChild(new Sheets()); xl.WorkbookPart.Workbook.GetFirstChild ().AppendChild( new Sheet(){ Id = xl.WorkbookPart.GetIdOfPart( xl.WorkbookPart.WorksheetParts.First() ), SheetId = 1, Name = "sheetnm" } ); //macro //xl.WorkbookPart.Workbook.AppendChild(new Sheets()); xl.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Office.Excel.Macrosheet()); xl.WorkbookPart.Workbook.GetFirstChild ().AppendChild( xl.WorkbookPart.MacroSheetParts.First().Macrosheet ); //xl.WorkbookPart.Workbook.AppendChild(xl.WorkbookPart.MacroSheetParts.First().Macrosheet); //save xl.WorkbookPart.Workbook.Save(); } } } }