APL Wiki
Excel from APL
This guide aims at explaining how to create and manipulate Excel files from APL without invoking the Excel application. This is done by using a stripped down dot net library that can read and write the new OpenXML files that are used in the new version of Office.
The library we will be using is called ExcelPackage and can be found here: http://www.codeplex.com/ExcelPackage
You will also need .NET 3.0 installed as ExcelPackage makes use of the new Packaging API introduced. http://www.netfx3.com/
Example 1 - Create a file
In this example we will create a new file and write some data to it.
⎕USING←'System.IO' 'OfficeOpenXml,C:\ExcelPackage.dll' ⍝ path to the dll file xlpg←⎕NEW ExcelPackage (⎕NEW FileInfo(⊂'sample1.xlsx')) ws←xlpg.Workbook.Worksheets.Add⊂'MyWorkSheet' ⍝ Write to sheet ws.(Cell 1 1).Value←'A string' ws.(Cell 1 2).Value←,'x' ws.(Cell 2 1).Value←'123' ws.(Cell 2 2).Value←,'4' ⍝ Save and Dispose the file xlpg.Save xlpg.Dispose
This code will open the chosen file if it exists, otherwise it will create it. We add a new worksheet called MyWorkSheet and write some values to it.
Note that if you try rerunning the code and the file already exists, it will be opened. The code will cause an exception when trying to add the worksheet as a sheet with that name already exists.
Reading and writing to a cell is done as text strings using the Value property.