Number Cruncher's Easter Egg: A fast interface to Excel

[jj2007]

Since I am working a lot with spreadsheets, I always wanted an elegant way to pull tables out of Excel.

The app below is a whopping 68 lines long and does the following:
- open a file in Excel
- get data from three different sheets
- display selected columns
- close the file, open a new one
- write a rectangle with "Masm32 is great" in the middle

include \masm32\MasmBasic\MasmBasic.inc                        ; download the library
  SetGlobals xlsFile$, TopBot$, MalesSheet$, FemalesSheet$        ; define some global variables
  Init
  SetGlobals                ; use ebx to reference them
  xlsConnect                ; no args=System
  .if !Zero?                        ; all errors are returned as Zero?
        Let xlsFile$=MbExeFolder$+"LifeExOECD.xls"        ; three sheets based on OECD data
        xlsClose        ; just in case the file is already open - if not, it will print an error message
        xlsOpen xlsFile$        ; [open("'+MyFile.xls+'")]
        .if !Zero?
                PrintLine "Indicators found in ", xlsFile$, ":"
                Let MalesSheet$="LE Males at birth"
                Let FemalesSheet$="LE Females at birth"
                xlsConnect "LE Total population at birth"
                .if !Zero?
                        PrintLine xlsRead$("R2C1")
                        xlsCommand '[select("R5C1")]'                ; select cell A5
                        xlsCommand "[select.special(5)]"                ; select the current region - you may want to download the Excel 4.0 macro help file
                        StringToArray xlsRead$(), All$(), tab        ; convert returned data table to a two-dimensional string array (tab = tab-delimited data)
                        xlsConnect FemalesSheet$
                        .if !Zero?
                                PrintLine xlsRead$("R2C1")
                                xlsCommand '[select("R5C1")]'                  ; select cell A5 ("Australia")
                                xlsCommand "[select.special(5)]"          ; select the current region
                                StringToArray xlsRead$(), Males$(), tab
                                xlsConnect MalesSheet$
                                .if !Zero?
                                        PrintLine xlsRead$("R2C1"), CrLf$, CrLf$, "2010", Tb$, Tb$, Tb$, "All", Tb$, "Women", Tb$, "Men"
                                        xlsCommand '[select("R5C1")]'                ; select the "Australia" cell
                                        xlsCommand "[select.special(5)]"                ; select the current region
                                        StringToArray xlsRead$(), Females$(), tab        ; returns #rows in eax
                                        For_ ecx=1 To eax-2                                ; we omit the header (element 0) and the last row
                                                Print Left$(Cat$(All$(ecx, 0)+Space$(24)), 24)
                                                Print All$(ecx, 51), Tb$
                                                Print Males$(ecx, 51), Tb$                        ; print column AZ for all three sheets
                                                Print Females$(ecx, 51), CrLf$
                                        Next
                                        MsgBox 0, "Reading was great. Ready to write a little bit?", "Hi", MB_YESNO
                                .endif
                        .endif
                .endif
        .endif
        .if eax==IDYES
                xlsCommand "[new(1)]"                ; let Excel create a new workbook
                Let esi=xlsSysRead$("Topics")        ; we must find the sheet
                mov ecx, Instr_(esi, "[Book")        ; this might fail for a non-English Excel version
                .if ecx
                        .if Instr_(ecx, esi, Tb$)
                                xlsConnect Mid$(esi, ecx, edx-1)        ; connect to the new sheet
                        Let TopBot$=String$(10, Cat$("x"+Tb$))
                        xlsWrite "R2C3:R2C14", TopBot$
                        xlsWrite "R9C7", "Masm32 is great"
                        For_ ecx=3 To 15
                                xlsWrite Str$("R%iC3", ecx), "#"
                                xlsWrite Str$("R%iC12", ecx), "#"
                        Next
                        xlsWrite "R15C3:R15C14", TopBot$
                                xlsCommand "[App.Activate()]"                ; put Excel to the foreground
                                xlsClose                ; no arg means close current workbook, ask if changes were made
                        .endif
                .endif
        .else
                Inkey "bye"
        .endif
        xlsDisconnect
.endif
  Exit
end start

Output (shortened):
Indicators found in D:\Masm32\RichMasm\Res\LifeExOECD.xls:
Life expectancy, Total population at birth, Years
Life expectancy, Female population at birth, Years
Life expectancy, Male population at birth, Years

2010                    All     Women   Men
Australia               81.8    84.0    79.5
Canada                  80.8    83.1    78.5
Italy                   82.1    84.6    79.4
United Kingdom          80.6    82.6    78.6
United States           78.7    81.1    76.2

You need the Easter version of MasmBasic to assemble this code 

@Hutch & Sinsi: Don't be scared by these 79.5 years: that is LE "at birth". At your age, it is roughly 84 years ;-)

P.S.: Special greetings to czerny, who inspired me to write these macros :t

[Gunther]

Jochen,

thank you for that nice Easter Egg.  :t

Gunther

[Antariy]

Variables in this HLL source are suspiciously look like register names
Jokes apart: very impressive source of metalanguage (and take notice on wide usage of ECX, too! ), Jochen, as usual.

[jj2007]

Update: MasmBasic version 5 April has finally learned that some of these stupid spreadsheets have spaces in their name :(

And the hotlinks work now, see \Masm32\RichMasm\Res\XlsViewer.asc 

[hutch--]

 

> @Hutch & Sinsi: Don't be scared by these 79.5 years: that is LE "at birth". At your age, it is roughly 84 years ;-)

I intend to live to 150 just to catch up on what I have missed and while that may not happen, I will probably be potty enough not to worry about it.  :P