CSE to create Excel workbook

General discussions related to using the C# Script Engine in APL+Win.

Moderators: Tech Support, phpbb_admin

CSE to create Excel workbook

Postby Chizever » 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:
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
Chizever
 
Posts: 64
Joined: March 1st, 2007, 12:12 pm

Re: CSE to create Excel workbook

Postby Ajay Askoolum » September 20th, 2016, 2:55 pm

You also need to load

C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.5.2\WindowsBase.dll

Note the reference to a particular version of the DOT NET Framework; if not installed, use the latest installed version that you have on your computer.

Hence the error:
Object reference not set to an instance of an object

My thoughts:
1. This approach is fraught with unforeseen issues. If Microsoft changes the internal structure of workbooks, no doubt they will also update the SDK. BUT your existing code will stop working.
2. If your code is embedded in a distributed application, your users will also have to download the SDK. Some corporates simply do not allow this.

Since you want to create Excel workbooks WITHOUT Excel being installed, a simple/straightforward solution is to create a CSV file (using []NCREATE) that can be opened in Excel very readily.

My recommendation: Use the ACE OLEDB 12.0 provider- you can create workbooks without Excel being installed.

PS: Take care writing to the root of drive c: - some operating systems e.g. Windows 10 require elevated privileges.
Ajay Askoolum
 
Posts: 645
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Re: CSE to create Excel workbook

Postby Chizever » September 20th, 2016, 3:53 pm

Ajay,

Thanks for the fast response, but I don't think that's it.

The Open XML SDK requires .NET 4.0 (which I have), and I don't have 4.5 so it should be loading the 4.0 WindowsBase.dll. I tried adding both
Code: Select all
⎕cself←'c1' ⎕cse 'Init' 'System' 'System.Windows.Forms' 'WindowsBase'

and
Code: Select all
⎕cse 'LoadAssembly' 'C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\WindowsBase.dll'

but neither one fixed the problem.


As for your other thoughts and concerns.
1. If Microsoft changes the internal structure of workbooks, no doubt they will also update the SDK. BUT your existing code will stop working.
No it won't. This is actually Microsoft's recommended way of creating office documents. If they update the the internal structure and I use the old SDK, I'll create an old version (which Excel will still be able to open). If I upgrade to the new SDK, it will create the new version.

2. If your code is embedded in a distributed application, your users will also have to download the SDK. Some corporates simply do not allow this.
I would distribute the SDK with my application. This is explicitly allowed and I can put the SDK files (only 2 of them) in my app directory so my LoadAssembly knows where to find it and I know that I'm getting the version I want.

3. A simple/straightforward solution is to create a CSV file (using []NCREATE) that can be opened in Excel very readily.
This only works for data. I currently open Excel as a COM object to give it data, printer settings, VBA code, charts/graphs, formulas, etc. This works fine on the client. It used to work acceptably on servers, but the latest server O/S's don't support this anymore.

4. Take care writing to the root of drive c: - some operating systems e.g. Windows 10 require elevated privileges.
Yes, this is just for my testing.


In any case, if anyone has any other ideas why the original sample from Microsoft isn't working, I would greatly appreciate it.

Regards,
Brian
Chizever
 
Posts: 64
Joined: March 1st, 2007, 12:12 pm

Re: CSE to create Excel workbook

Postby Ajay Askoolum » September 20th, 2016, 6:20 pm

Ajay,

Thanks for the fast response, but I don't think that's it.

Watch the attached video.
Attachments
Office Open XML SDK Demo.wmv
(16.55 MiB) Downloaded 48 times
Ajay Askoolum
 
Posts: 645
Joined: February 22nd, 2007, 2:16 am
Location: United Kingdom

Re: CSE to create Excel workbook

Postby Richard Procter » March 14th, 2017, 12:37 pm

FYI, I have done some experimentation with the Open XML SDK and Dyalog APL - you can see my presentation and materials at Dyalog.com > 2015 User Meeting

During that learning curve, I also experimented with APL+Win and CSE. IIRC, essentially I was able to make it work and read the contents of a spreadsheet into an APL array, but I didn't pursue it to make a complete, user-ready tool yet. (ya, something I should get back to and finish one of these days...)

Just mentioning this in case anybody wants to trade further thoughts or notes on the subject. My talk in 2015 discusses many of the general issues involved with this effort, which apply regardless of which APL you use.

Cheers...Richard
Richard Procter
APL Borealis Inc.
Toronto, Canada

APL Software Development,
Sales, Training, and Support
Richard Procter
 
Posts: 1
Joined: June 11th, 2007, 6:06 pm
Location: Toronto, Canada

Re: CSE to create Excel workbook

Postby joe_blaze » March 17th, 2017, 1:43 am

Try using ⎕cse 'ExecStmt' 'var sheetData = new SheetData();' to see if you can create the argument separately and then use it in the method: ⎕cse 'ExecStmt' 'worksheetPart.Worksheet = new Worksheet(sheetData);'
The error message you receive from the first statement may be instructive.

Sorry for the brief reply, but I am using airport wifi which is terrible.
joe_blaze
 
Posts: 383
Joined: February 11th, 2007, 3:09 am
Location: Box 361 Brielle, NJ 08730-0361

Re: CSE to create Excel workbook

Postby Eric.Lescasse » March 17th, 2017, 5:09 am

Hi Brian,

I could successfully run a C# Windows Forms application having a single Form and single Button, using your code:

Code: Select all
private void button1_Click(object sender, EventArgs e)
{
    string filepath = @"c:\temp\test.xlsx";
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet(new SheetData());
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "mySheet"
    };
    sheets.Append(sheet);
    workbookpart.Workbook.Save();
    spreadsheetDocument.Close();
}


I did that on my machine that has Excel 2016 installed.

I noticed that there were several DocumentFormat.OpenXml DLLs on my system (see attachment).
I have been using the one which is highlighted in the attachment image (C:\Program Files (x86)\Microsoft Office\root\Office16\DCF\DocumentFormat.OpenXml.dll 5510KB).

Maybe using this version of DocumentFormat.OpenXml.dll is the key to have your code run OK.

I could upload my TestBrian.exe Windows Forms app for you to test, but I then would need to also upload the DocumentFormat.OpenXml.dll I am using and I don't know if this DLL uses dependencies, which I guess it does.

***

Note that if you need to do anything you want with Excel (.xls or .xlsx) files on a machine that does not have Excel installed, you could use my SSGear product (http://www.lescasse.com/content/ssgear.aspx): it's easier to use than the Microsoft SDK, is about 100 times faster [you can create an Excel .xlsx workbook with 1000 worksheets and populate them all with just one APL instruction in less than a second which means creating one worksheet and populating it in less than 1 millisecond]. There's a 30-day Trial available for download on my Web Site.
Attachments
OpenXmlDll.png
OpenXmlDll.png (13.34 KiB) Viewed 309 times
Eric.Lescasse
 
Posts: 46
Joined: February 8th, 2007, 7:55 pm
Location: Paris, France


Return to APL+Win & The C# Script Engine

Who is online

Users browsing this forum: No registered users and 1 guest

cron