• 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 (EP) 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'

..and then use them like this:

 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

Example 3 - Styles

A common thing to do with spreadsheets is to set the formatting style for a cell. For example you might want to format dates, currencies and percentages in a presentable fashion. There is currently no support to do this directly from the ExcelPackage object model. The only supported way to set styles is by creating named cell-styles in a file in Excel and then use this file as a template.

In this example I have created a file called template.xlsx in Excel that contains a style named 'Date'.

 xlpg←⎕NEW ExcelPackage({⎕NEW FileInfo(⊂⍵)}¨'sample.xlsx' 'template.xlsx')
 ws←xlpg.Workbook.Worksheets.Add⊂'MyWorkSheet'

 data←worksheet Range'A2:D21'
 data[;1].Value←⍕¨(⍳20)+#.DateToIDN 2007 1 1
 data[;1].Style←⊂'Date'

CategoryGuides