• ExcelPackage

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 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 then cause an exception when trying to add the worksheet as a sheet with the same name already exists. Let's abstract the Worksheet handling so it only adds the sheet if it doesn't already exist.

 getSheet←{6::⍺.Add ⊂⍵ ⋄ ⍺[⊂⍵]}
 worksheet←xlpg.Workbook.Worksheets getSheet'Tinned Goods'

Reading and writing to a cell is done as text strings using the Value property.

When we're done we just save and release the file by calling the Save and Dispose methods of the instance.

Saving a file without setting at least one cell in each worksheet will cause an exception (bug reported).

Example 2 - R/W Ranges

In the current version of ExcelPackage there is no Range object, but we can see that it would simplify coding a lot if we could group together cells. We write a Range function that takes a sheet as a left argument and a string as right argument and returns a rank 2 matrix of cells in the requested range.

 Range←{
⍝ coords → 'B3:C4' ←→   (3 2)(3 3)
⍝                       (4 2)(4 3)
     part←{(⍴,⍺)↓¨(⍺,⍵)⊂⍨⍺⍷⍺,⍵}    ⍝ partition ⍵ by ⍺
     split←{⍵⊂⍨1,1↓<\⍵∊⎕D}         ⍝ split ⍵ after first digit ('ABC123'→'ABC' '123')
     decode←⎕A∘{(⍴⍺)⊥⍺⍳⍵}          ⍝ decode alpha base (base 26; 'AA'←→27)

     sheet coords←⍺ ⍵
     s e←{⌽(decode ⍺),⍎⍵}/↑split¨':'part coords
     sheet.Cell¨(⊂s-1)+⍳e-s-1
 } 

We can now use this function to define a couple of ranges.

 header←worksheet Range'A1:D1'
 data←worksheet Range'A2:D21'

 header.Value←1 4⍴'Date' 'Price' 'Items Sold' 'Total'
 data[;1].Value←⍕¨(⍳20)+#.DateToIDN 2007 1 1
 data[;2 3].Value←⍕¨?20 2⍴10 100
 data[;4].Value←⍕¨×/⍎¨data[;2 3].Value

CategoryGuides