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 (http://masm32.com/board/index.php?topic=94.0) 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 (http://www.oecd.org/els/health-systems/OECDHealthData2012FrequentlyRequestedData_Updated_October.xls) 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 (http://download.microsoft.com/download/excel97win/utility4/1/win98/en-us/macrofun.exe)
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 (http://masm32.com/board/index.php?topic=94.0) to assemble this code :biggrin:
@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 (http://forum.pellesc.de/index.php?topic=5256.0), who inspired me to write these macros :t
Jochen,
thank you for that nice Easter Egg. :t
Gunther
Variables in this HLL source are suspiciously look like register names :biggrin:
Jokes apart: very impressive source of metalanguage (and take notice on wide usage of ECX, too! :biggrin:), Jochen, as usual.
Update: MasmBasic version 5 April (http://masm32.com/board/index.php?topic=94.0) 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 :bgrin:
:biggrin:
> @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