Generating cell refs in Excel

In this post James asks for help from his colleagues and the greater APL community regarding problem he has generating cell refs in excel.

I’ve been working on a project for our Finance Manager Jenny helping to generate the Networking Finance reports. In a nutshell this involves a query on the Coretime database to retrieve any relevant time entries- those of specific clients, projects and those made by appropriate employees, and also between a time-frame of one calendar month. These entries get sorted and the data is fitted with a header, detailing which clients and employees have been used for the report, which time frame has been used, and other useful information. This data is then written to Excel spreadsheet, which is where the heart of this problem arose.

How do Excel refs work?

To write data to an Excel spreadsheet you must first know the range of the given data. If you had a 4×5 matrix of random integers, for example, you would need to tell Excel the top left and bottom right corners of the data in Excel-speak so it would know where to place it. For our 4×5 matrix the range needed would be ‘A1:E4’. If we assume that we’re always starting at A1 then we can ignore the first half of the range of cells. We’re then left with E4- the 4 being fairly trivial, you can see this as the number of rows in the matrix. What was not so trivial to Gil and myself was taking the number of columns and determining the letter equivalent, especially as it doesn’t end at Z (going AA, etc).

Using APL to generate cell refs in Excel

Because of this repeating-letter nature, and the number of letters in the alphabet, Gil suggested that it would be possible to get a solution using encode and base 26. This made sense to me- if we treat each position (260, 261, etc.) as indices, which we can use to pick letters from their positions in the alphabet and format that string of letters to create a valid range together with the number of rows, and the initial A1 starting point.

The first stage of the problem was converting from decimal to base 26. Gil showed me a neat little trick to do this using inverse functions.

enc←base⊥⍣¯1⊢⍵ ⍝ encode in given base

Essentially by passing a single base as a left argument to decode, and running it as an inverse function using the power operator, the scalar base gets extended as many times as necessary to display the number in all its glory. This is certainly cleaner than my method of using logarithms which I’ll spare you the details of seeing…

My first attempt at a solution

For my solution ⎕IO is set to 1. I will say this now to save any confusion later.

Converting straight to base 26 sounds ideal since there are 26 letters in the alphabet but when you convert the number 27 you get (1 0). You can’t use 0 as an index given the ⎕IO settings; when it rolls over it restarts at AA, which maps to (1 1). The next stage of the problem was ensuring that anything with more than one letter came out without throwing an index error. My way of tackling this was essentially by adding a one to all positions but the first.

newBase+←~(≢newBase)↑1 ⍝ Add 1 to all trailing 'bits'

What I didn’t realise at this point is that Z was not coming out in my solution. It will work for ZZ, and any other variant of Z, but not solitary Z. This is due to the way 26 rolls over to (1 0). Any range starting with a Z, e.g. ZD, would not come out previous to the following introduction:

power←26*{⌊0.5+26⍟⍵}num⍝ Find the nearest power of 26 to the given number
 bot←power-26×≢newBase  ⍝ Subtract 26 times n where n is the number of digits in newBase
 :If 1≠≢newBase ⍝ Don't add in the first parse unless you want an index error
 :AndIf (num≥bot)∧num<power ⍝ Determine if it's in Z-range. Going to be within 26*n less than of a power of 26.
    newBase[1]+←1
:EndIf

The numbers that map to a range which starts with a Z can be determined as ones which are (26*n)-(26×n); essentially the last 26th (last starting letter). When I finally realised this, I was able to put in a check to determine whether the number was within this range. If it was, increment the first position by 1 so to get the Z starting element. By introducing Z though I seem to have pushed out those starting with Y except the initial solitary Y… It all got a little too complex for me at this point, and I think I’ll have to go back to the drawing board.

My attempt at a full “solution” can be seen below. Please keep in mind that it is incomplete at the time of me writing this and it doesn’t actually work as intended.

out←base getRange num;newBase;power;bot;⎕IO
⎕IO←1
:If num≤0
 newBase←0  ⍝ Cover as base conversion doesn't like 0
:Else
 newBase←base(⊥⍣¯1)num  ⍝ Convert to specified base
 newBase+←~(≢newBase)↑1 ⍝ Add 1 to all trailing 'bits'
 power←26*{⌊0.5+26⍟⍵}num⍝ Find the nearest power of 26 to the given number
 bot←power-26×≢newBase  ⍝ Subtract 26*n where n is the number of digits in newBase
 :If 1≠≢newBase ⍝ Don't add in the first parse unless you want an index error
 :AndIf (num≥bot)∧num

A solution from the community

Gil created a far more elegant solution to retrieve the letters. This can be seen below (I put in a commented, line separated version of the shift function for readability, but essentially all that’s needed is xlbase). This could be extended by attaching the number of rows and the initial ‘A1:’ starting point to solve the full problem.

shift←⊃{   ⍝ replace zeros by shifting from left
    0<⊃⍵:⍺,⍵   ⍝ if positive, leave unchanged
    (⍺-1),(base+⊃⍵),1↓⍵⍝ else take from left and add
 }/enc  ⍝ reduce over encoded value

xlbase←{
    ⎕IO←1
    enc26←26⊥⍣¯1⊢⍵
    shift←⊃{0<⊃⍵:⍺,⍵ ⋄ (⍺-1),(26+⊃⍵),1↓⍵}/enc26
    ⎕A[shift~0]
}

• Setting coding priorities and quality bars as project groundwork
• The Skirting Board Problem – Solved
• My First Month (and a half) at Optima Systems
• APL (A Programming Language) - What is it?
• Using a Gaming Mouse at Work
• Bridging the gap between APL and Python
• Rehearsal For Armageddon – The Balkan Wars 1912 – 13
• Indexing arrays in APL: Squad indexing on Multi-dimensional Arrays