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(⊂'c:\xlsx\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

CategoryGuides