CSE to create Excel workbook
Posted: September 20th, 2016, 12:13 pm
I'm looking at the Office Open XML SDK to create Excel workbooks, without needing Excel. I downloaded the SDK and then tried to follow some examples using CSE. I'm running into a problem and have no idea why.
This MSDN page, https://msdn.microsoft.com/en-us/librar ... -snippet-7, has a sample function called CreateSpreadsheetWorkbook. I copied that script into an APL variable called cse_CreateSpreadsheetWorkbook and then ran this code:
I get this error:
So there's something wrong with cse_CreateSpreadsheetWorkbook, but I don't know what. I took that function and in-lined it into my APL:
When I run the above Create function, I get this error:
So I know what line is failing, but I'm not sure why. I'm not sure if it's CSE, or sample, or something else. Any help would be appreciated.
Thanks,
Brian
This MSDN page, https://msdn.microsoft.com/en-us/librar ... -snippet-7, has a sample function called CreateSpreadsheetWorkbook. I copied that script into an APL variable called cse_CreateSpreadsheetWorkbook and then ran this code:
- Code: Select all
∇ xlsx
[1]
[2] ⎕cself←'c1' ⎕cse 'Init' 'System' 'System.Windows.Forms' 'System.Linq'
[3] ⎕cse 'returnonerror' 1
[4]
[5] ⎕cse 'ExecStmt' 'using System.Windows.Forms;' ⍝ allow use of MessageBox
[6]
[7] ⎕cse 'LoadAssembly' "C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll"
[8] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml;'
[9] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml.Packaging;'
[10] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml.Spreadsheet;'
[11]
[12] ⎕cse 'Exec' cse_CreateSpreadsheetWorkbook
[13]
[14] ⎕cse 'ExecStmt' 'CreateSpreadsheetWorkbook(@"c:\test1.xlsx");'
[15]
[16] ⎕cse 'Close'
∇
I get this error:
- Code: Select all
CSE ERROR: Object reference not set to an instance of an object.
xlsx[12] ⎕cse 'Exec' cse_CreateSpreadsheetWorkbook
^
So there's something wrong with cse_CreateSpreadsheetWorkbook, but I don't know what. I took that function and in-lined it into my APL:
- Code: Select all
∇ Create
[1]
[2] ⎕cself←'c1' ⎕cse 'Init' 'System' 'System.Windows.Forms'
[3] ⎕cse 'returnonerror' 1
[4]
[5] ⎕cse 'ExecStmt' 'using System.Windows.Forms;' ⍝ allow use of MessageBox
[6]
[7] ⎕cse 'LoadAssembly' "C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll"
[8] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml;'
[9] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml.Packaging;'
[10] ⎕cse 'ExecStmt' 'using DocumentFormat.OpenXml.Spreadsheet;'
[11]
[12] ⍝ filepath
[13] ⎕cse 'ExecStmt' 'string filepath = @"c:\test1.xlsx";'
[14]
[15] ⍝ // Create a spreadsheet document by supplying the filepath.
[16] ⍝ // By default, AutoSave = true, Editable = true, and Type = xlsx.
[17] ⎕cse 'ExecStmt' 'SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);'
[18]
[19] ⍝ // Add a WorkbookPart to the document.
[20] ⎕cse 'ExecStmt' 'WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();'
[21] ⎕cse 'ExecStmt' 'workbookpart.Workbook = new Workbook();'
[22]
[23] ⍝ // Add a WorksheetPart to the WorkbookPart.
[24] ⎕cse 'ExecStmt' 'WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();'
[25] ⎕cse 'ExecStmt' 'worksheetPart.Worksheet = new Worksheet(new SheetData());'
[26]
[27] ⍝ // Add Sheets to the Workbook.
[28] ⎕cse 'ExecStmt' 'Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());'
[29]
[30] ⍝ // Append a new worksheet and associate it with the workbook.
[31] ⎕cse 'ExecStmt' 'Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet
" };'
[32] ⎕cse 'ExecStmt' 'sheets.Append(sheet);'
[33]
[34] ⎕cse 'ExecStmt' 'workbookpart.Workbook.Save();'
[35]
[36] ⍝ // Close the document.
[37] ⎕cse 'ExecStmt' 'spreadsheetDocument.Close();'
[38]
[39] ⎕cse 'Close'
∇
When I run the above Create function, I get this error:
- Code: Select all
CSE ERROR: Object reference not set to an instance of an object.
Create[25] ⎕cse 'ExecStmt' 'worksheetPart.Worksheet = new Worksheet(new SheetData());'
^
So I know what line is failing, but I'm not sure why. I'm not sure if it's CSE, or sample, or something else. Any help would be appreciated.
Thanks,
Brian