:Namespace XL (⎕IO ⎕ML ⎕WX)←3⍴1 ⍝⎕WX needed for Dyalog 11 & 12 PlotSpecs←42 1⍴'Syntax: c ← {⍺} plot ⍵' (23⍴'-') '' 'c: a plot object or a character string (error message)' '' '' '⍺: plot type - a simple string (one of choices below) or an integer' ' ''l'' = line plot' ' ''lm'' = line plot with markers' ' ''cc'' = clustered columns' ' ''sc'' = stacked columns' ' ''pie''= pie chart' '' 'All plot types can be listed by typing xl.XlChartType; use any variable or value from the list.' '' '' '⍵: a vector of alternating parameter names and values OR a numeric vector or matrix of plot data.' 'If ⍵ is a numeric vector or matrix, it may contain ⎕null, ⍬, or '''' to represent null values.' '' 'If ⍵ is a vector of names and values, the allowed names and their associated values are:' '' 'data A vector or matrix of values to plot; ⍪ is applied to a vector. Data elements may include ⎕null, ⍬ or' ' '''' to represent null values. If ''data'' is specified, the data array is written to the active sheet at' ' location ''dataposn''.' 'dataposn The cell containing the top left corner of the plot data; default = 1 1.' 'datarowscols The number of rows and columns of data to plot; this is required only if ''data'' is omitted.' 'null How to handle null data points: 1=omit, 2=use zero, 3=interpolate (if possible).' 'title Chart title.' 'xtitle X-axis title.' 'ytitle Y-axis title.' 'yrange Maximum and minimum values and interval for y-axis labels and tick marks; use ⍬ for any item to use' ' Excel''s default value.' 'legend Set of strings, one per plot series.' 'legposn Location of the legend: ''t''=top, ''b''=bottom, ''l''=left'', ''r''=right, ''c''=top right corner.' 'labels X-axis labels: character matrix of labels or numeric vector with one row or value per row of ''data''.' ' If ''labels'' is specified, the label array is written to the active sheet at location ''labelposn''.' 'labelposn The cell containing the top cell of the column of labels.' 'plotposn The co-ordinates of the top left corner of the chart frame. These co-ordinates are not cell co-ordinates;' ' see the written documentation for more information.' 'scaling Vector of up to 4 elements comprising height factor, width factor, vertical scaling origin and horizontal' ' scaling origin. ⍬ implies default value; defaults are 1 1 0 0. Scaling origin: 0= top or left border,' ' 1= centre, 2= bottom or right border.' active←{ ⍵≡'?':'return name and fullname of active workbook and name of active sheet; ⍵:empty vector' xl.ActiveWorkbook.(Name FullName),⊂xl.ActiveSheet.Name } addsheet←{ ⍵≡'?':'insert sheets into active workbook; ⍵[1]= # of sheets to add; ⍵[2]= {b|a|e} (before, after, or end (default)); ⍵[3]= sheet name or # if ⍵[2]∊''ba'' (not required if ''e'')' ⎕ML←⎕IO←1 sh←xl.Sheets 11::'Domain Error: ⍵[1] must be positive integer; ⍵[3] must be valid sheet name or number' 3::'Index Error: ⍵ requires 2 or 3 elements' ⍝ 1=×/⍴⍵:sh.Add('After'(sh.Item sh.Count))('Count'(⊃⍵)) ⍝add to end ⍝ 'e'≡2⊃⍵:sh.Add('After'(sh.Item sh.Count))('Count'(⊃⍵)) ⍝ditto ⍝ 'b'≡2⊃⍵:sh.Add('Before'(sh.Item 3⊃⍵))('Count'(⊃⍵)) ⍝insert before ⍵[3] ⍝ 'a'≡2⊃⍵:sh.Add('After'(sh.Item 3⊃⍵))('Count'(⊃⍵)) ⍝insert after ⍵[3] 1=×/⍴⍵:sh.Add('After'(sh[sh.Count]))('Count'(⊃⍵)) ⍝add to end 'e'≡2⊃⍵:sh.Add('After'(sh[sh.Count]))('Count'(⊃⍵)) ⍝ditto 'b'≡2⊃⍵:sh.Add('Before'(sh[⍵[3]]))('Count'(⊃⍵)) ⍝insert before ⍵[3] 'a'≡2⊃⍵:sh.Add('After'(sh[⍵[3]]))('Count'(⊃⍵)) ⍝insert after ⍵[3] } bcolor←{ ⍵≡'?':'set background color; ⍵: single cell or cell range; ⍺:integer value for ColorIndex, range 0 (=black) to 57' ⎕ML←1 c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Interior.ColorIndex←⍺ (xl.Range xl.Cells.Item¨c).Interior.ColorIndex←⍺} bcolour←{ ⍵≡'?':'set background colour; ⍵: single cell or cell range; ⍺:integer value for ColorIndex, range 0 (=black) to 57' ⎕ML←1 c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Interior.ColorIndex←⍺ (xl.Range xl.Cells.Item¨c).Interior.ColorIndex←⍺} bold←{ ⍵≡'?':'set or cancel bold font; ⍵: single cell or cell range; ⍺: 1=set (default) or 0=cancel;' ⎕ML←1 ⍺←1 ⍝default=set c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Font.Bold←⍺ (xl.Range xl.Cells.Item¨c).Font.Bold←⍺} books←{ ⍵≡'?':'return matrix of short and full names of open workbooks; ⍵: empty vector' ⎕IO←⎕ML←1 6::'Value Error: wb probably not defined - execute ∇startexcel' c←wb.Count c=0:0 2⍴'' ↑wb[⍳c].(Name FullName)} border←{ ⍵≡'?':'set or remove top, bottom, left or right border; ⍵: single cell or cell range; ⍺[1]: one of "tblr"; ⍺[2]: one of 0,⍳13; positive integer sets border (1=solid (default), 2=dotted, etc.), 0=remove border' ⍝other properties: TintAndShade (default=0) and Weight (default=xlThin) ⍝xlInsideVertical & xlInsideHorizontal are not set ⎕IO←⎕ML←1 6::'Value Error: specify border as one of "tblr"' edge←((⊃⍺)='tblr')/xl.(xlEdgeTop xlEdgeBottom xlEdgeLeft xlEdgeRight) ls←{⍵=0:xl.xlNone ⍵}2⊃2↑⍺,1 ⍝line style: range 0 to 13 but substitute xlNone for 0 11::'Domain Error: ⍺[1] must be one of "tblr"; ⍺[2] must be in 0,⍳13' c←rc ⍵ ⍝cell co-ords 1=≡c:((xl.Cells.Item c).Borders.Item edge).LineStyle←ls ((xl.Range xl.Cells.Item¨c).Borders.Item edge).LineStyle←ls} border_all←{ ⍵≡'?':'set or remove all borders; ⍵: single cell or cell range; ⍺[1]: one of "tblr"; ⍺[2]: one of 0,⍳13; positive integer sets border (1=solid (default), 2=dotted, etc.), 0=remove border' ⍝other properties: TintAndShade (default=0) and Weight (default=xlThin) ⍺←1 ⍝default = solid line edge←xl.(xlEdgeTop xlEdgeBottom xlEdgeLeft xlEdgeRight xlInsideVertical xlInsideHorizontal) ls←{⍵=0:xl.xlNone ⍵}⍺ ⍝line style: range 0 to 13 but substitute xlNone for 0 c←rc ⍵ ⍝cell co-ords 1=≡c:((xl.Cells.Item c).Borders.Item¨edge).LineStyle←ls ((xl.Range xl.Cells.Item¨c).Borders.Item¨edge).LineStyle←ls} cellrange←{ ⍵≡'?':'return array of all co-ordinate pairs in range ⍵; ⍵: cell range' ⍝result is array of co-ords in r→l, t→b order ⎕IO←⎕ML←1 c←rc ⍵ ⍝cell co-ords rnge←{n←⍵[⍋⍵] n[1]+0,⍳|-/n} (rnge∊⊃¨c)∘.,(rnge∊2⊃¨c)} centre←{ ⍵≡'?':'centre text; ⍵: single cell or cell range' ⎕ML←1 c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).HorizontalAlignment←xl.xlCenter (xl.Range xl.Cells.Item¨c).HorizontalAlignment←xl.xlCenter} charts←{ ⍵≡'?':'Return matrix of names and titles of charts in sheet ⍵; empty vector implies active worksheet' ⎕ML←⎕IO←1 s←{⍵≡'':xl.ActiveSheet.Shapes (xl.Sheets[⊂⍵]).Shapes}⍵ n←s.Count n=0:0 2⍴'' b←3=∊(s.Item¨⍳n).Type ⍝select charts only; s[⍳n].Type not implemented in Dyalog 12.1 0=∨/b:0 2⍴'' ti←{⍵.Chart.HasTitle=0:'' ⍵.Chart.ChartTitle.Text} ↑{c←s.Item ⍵ ⍝⍵ is # (c.Name)(ti c)}¨b/⍳n} check←{ ⍵≡'?':'return boolean array where 1 indicates an error in a cell; ⍵: single cell or cell range' c←rc ⍵ 1=≡c:xl.WorksheetFunction.IsError xl.Cells.Item c cc←cellrange c {xl.WorksheetFunction.IsError xl.Cells.Item ⍵}¨cc } clean←{ ⍝clean data before writing to a sheet ⍵≡⍬:⎕NULL ⍵≡⎕NULL:⍵ ~(10|⎕DR ⍵)∊0 2:⍵ (⎕IO-(' '≠⌽,⍵)⍳1)↓⍵} ∇ z←clear Y;c;lastcell :If Y≡'?' z←'clear contents of cells in range ⍵; ⍵: single cell or cell range; ⍵[2]←''end'' means clear from ⍵[1] to end of sheet' ⋄ →0 :EndIf ⍝ClearContents returns 1 so suppress via "0 0 ⍴" ⎕IO←⎕ML←1 :If 1=≡Y ⍝single cell or range (as string) c←rc Y :If 1=≡c z←0 0⍴(xl.Cells.Item c).ClearContents :Else z←0 0⍴(xl.Range xl.Cells.Item¨c).ClearContents ⍝range :EndIf :ElseIf 2=≡Y :If (2⊃Y)≡'end' ⍝clear to end of sheet: c←rc⊃Y lastcell←2⊃rc xl.ActiveSheet.UsedRange.Address'' z←0 0⍴(xl.Range xl.Cells.Item¨c lastcell).ClearContents :Else ⍝clear range c←rc Y z←0 0⍴(xl.Range xl.Cells.Item¨c).ClearContents ⍝range :EndIf :EndIf ∇ close←{ ⍵≡'?':'close a workbook; ⍵: empty vector (=active workbook) or name or number of any open workbook; ⍺: ''s''= save outstanding changes, ''d''= discard outstanding changes, ''p''= Excel prompt (default)' ⎕IO←1 ⍺←'p' wbk←{0=⍴,⍵:xl.ActiveWorkbook wb[⊂⍵]}⍵ (⍺∊'ps')∧(wbk.Saved=0)∧(wbk.ReadOnly=1):'This workbook is Read Only and cannot be saved' wbk.Saved=1:wbk.Close'' ⍝no unsaved changes alerts←xl.DisplayAlerts ⍝is this necessary? xl.DisplayAlerts←⍺='p' ⍝unsaved changes are discarded if DisplayAlerts=0 ⍺∊'dp':wbk.Close'' ⍝Excel prompt ensues only if 'p' wbk.Save wbk.Close'' xl.DisplayAlerts←alerts} ∇ z←copysheet Y;⎕IO;⎕ML;len;srcesheets;targetbk;cpmv;location;anchorsheet;parms;target;source;sns z←0 0⍴0 ⎕IO←⎕ML←1 :If Y≡'?' z←'copy or move sheet(s); ⍵: 1=source sheet(s) [, 2=target book, [3=c/m [, 4=e/b/a [, 5=target sheet]]]]; defaults implied by empty vector or omitted item: 1=active sheet, 2=active book, 3=c, 4=e, 5=last sheet' →0 :EndIf :If (10|⎕DR Y)∊0 2 :AndIf Y≢'' Y←,⊂Y :EndIf len←⍴,Y :If len=5 (srcesheets targetbk cpmv location anchorsheet)←Y :ElseIf len∊0,⍳5 (srcesheets targetbk cpmv location anchorsheet)←Y,(len-5)↑'' '' 'c' 'e' '' :Else '⍵ must have between 0 and 5 elements; if several sheets are specified, nest them'⎕SIGNAL 500 →0 :EndIf ⍝define the parameters for the target location: :If 0=⍴parms←,(location='bae')/'Before' 'After' 'After' z←'⍵[3] must be "b" (Before), "a" (After) or "e" (End)' ⋄ →0 :EndIf target←{⍵≡'':xl.Sheets wb[⊂⍵].Sheets}targetbk :If location='e' parms,←target[target.Count] :ElseIf anchorsheet≡'' '⍵[5] must contain sheet name or number if ⍵[4] is "b" (before) or "a" (after)'⎕SIGNAL 500 :Else parms,←target[⊂anchorsheet] :EndIf ⍝define the source sheets: :If srcesheets≡'' source←xl.ActiveSheet :Else :If 2=|≡srcesheets srcesheets[(srcesheets≡¨⊂'')/⍳⍴srcesheets]←⊂xl.ActiveSheet.Name :EndIf :If (10|⎕DR srcesheets)∊0 2 srcesheets←⊂srcesheets :EndIf ⍝nb: source←xl.Sheets[⊂srcesheets] works even if `srcesheets is a list ⍝ but then `source is always a scalar, which is not what's needed below source←xl.Sheets[srcesheets] ⍝1 or multiple sheet #'s or names :EndIf ⍝do the copy or move: ⍝beware that you can't move all sheets from book A to book B ⍝excel always handles name conflicts ⍝target workbook becomes active workbook :If ~cpmv∊'cm' '⍵[3] must be "c" (copy) or "m" (move)'⎕SIGNAL 500 :EndIf :If 1=⍴,source ⍝1 sheet :If cpmv='c' source.Copy⊂parms :Else source.Move⊂parms ⍝need to do this seriatim but must replace sheet #s by names :EndIf :Else ⍝multiple sheets ⍝must do sheets in Index order if 'before' but reverse Index order if 'after' ⍝it's as if copy/move command is performed seriatim, in which case it doesn't ⍝quite correspond to Excel, as in: Sheets(Array(sheet1, sheet2)).Copy ... sns←source.Index :If location='b' source←source[⍋sns] :Else source←source[⍒sns] :EndIf :If cpmv='c' source.Copy⊂⊂parms ⍝seems an odd construct; ⊂⊂ suggests that ¨ is implied :Else source.Move⊂⊂parms :EndIf :EndIf ∇ dateToDaynum←{ ⍵≡'?':'return array of Excel day numbers; ⍵: vector of numeric dates in the form yyyymmdd' ⎕ML←⎕IO←1 s←⍴⍵ ⍝ dnums←#.DateToIDN¨⊂[1]10000 100 100⊤,⍵ dnums←{2 ⎕NQ'.' 'DateToIDN'⍵}¨⊂[1]10000 100 100⊤,⍵ s⍴dnums+dnums>59 ⍝deal with Excel error (assumes 1900 is leap year) } daynumToDate←{ ⍵≡'?':'return array of dates from Excel day numbers (non-integers ignored); ⍵: array of day numbers; ⍺: controls format: 0=array of y m d dow, 1=yyyymmdd, 2=dd/mm/yyyy, 3=mm/dd/yyyy' ⍺←0 ⋄ ⎕ML←⎕IO←1 s←⍴⍵ ok←∨/(∊⎕DR¨v←,⍵)∘.=83 163 323 ⍝date is integer but timestamp is ignored ... 0=⍴dnums←ok/v:⍵ ~∨/posint←dnums>0:⍵ ok∧←ok\posint dnums←posint/dnums ⍝ dates←#.IDNToDate¨,⍵-⍵>59 dates←{2 ⎕NQ'.' 'IDNToDate'⍵}¨,dnums-dnums>59 ⍝1900 not a leap year! insert←{~0∊⊃⍺:⍵ d←(⊃⍺)\⍵ ((0=⊃⍺)/d)←2⊃⍺ d} a←ok((~ok)/v) ⍺=0:s⍴a insert dates ⍝y m d dow dates←¯1↓¨dates ⍝discard dow ⍺=1:s⍴a insert 100⊥⍉↑dates ⍝yyyymmdd ⍺=2:s⍴a insert↓'2(ZI2,),I4'⎕FMT⌽↑dates ⍝dd/mm/yyyy ⍺=3:s⍴a insert↓'2(ZI2,),I4'⎕FMT 1⌽↑dates ⍝mm/dd/yyyy } ∇ z←deletesheet Y;⎕ML;alerts;sh :If Y≡'?' z←'delete sheet(s) from active workbook; ⍵: list of sheet names and/or numbers; empty name implies active sheet' ⋄ →0 :EndIf alerts←xl.DisplayAlerts xl.DisplayAlerts←0 ⎕ML←⎕IO←1 ⍝nb: ok if a sheet name or number is repeated but error if, eg "2 'alpha'" specified where sheet 2 is 'alpha' :Trap 11 sh←,Y :If 0=⍴sh xl.ActiveSheet.Delete :ElseIf ((10|⎕DR sh)∊0 2)∨(1=⍴sh) (xl.Sheets[⊂sh]).Delete ⍝1 sheet name or # :Else :If 2=|≡sh sh[(sh≡¨⊂'')/⍳⍴sh]←⊂xl.ActiveSheet.Name :EndIf xl.Sheets[∪sh].Delete ⍝1 sheet # or multiple sheets :EndIf xl.DisplayAlerts←alerts :Else xl.DisplayAlerts←alerts 'domain error: possibly invalid sheet name or number or deleting all sheets'⎕SIGNAL 500 :EndTrap z←0 0⍴'' ∇ dim←{ ⍵≡'?':'return sheet dimensions; ⍵: empty vector' ⍝Version 12.0=Office 2007 ⎕IO←1 12≤2⊃⎕VFI xl.Application.Version:1048576 16384 65536 256 } ∇ z←endexcel Y;_clean_;_obs_;_tkns_ :If Y≡'?' z←'erase Excel objects in the current and toolkit namespaces without terminating Excel' →0 :EndIf ⎕ML←1 ⍝fn to erase objects whose display form includes '#.XL.xl.', eg: _tkns_←⎕CS'' _clean_←{b←(⊂⍺,'.xl.')≡¨8↑¨⍕∘⍎¨↓⍵ ⎕EX b⌿⍵} ⍝(1) clean up the toolkit ns: :If ×⊃⍴_obs_←⎕NL 9 {}_tkns_ _clean_ _obs_ ⍝then erase xl itself: {}⎕EX'xl' :EndIf ⍝(2) clean up calling ns: :If (⎕CS'')≢⎕NSI ⍝calling ns ≢ toolkit ns ('_clean_' '_tkns_')⎕CS⊃⎕NSI ⍝calling ns :If ×⊃⍴_obs_←⎕NL 9 {}_tkns_ _clean_ _obs_ :EndIf :EndIf z←0 0⍴0 ∇ ∇ z←endexcel2 Y;_clean_;_obs_;_tkns_ :If Y≡'?' z←'erase Excel objects in the current namespace without terminating Excel' →0 :EndIf ⎕ML←1 ⍝fn to erase objects whose display form includes '#.XL.xl.', eg: _tkns_←⎕CS'' _clean_←{b←(⊂⍺,'.xl.')≡¨8↑¨⍕∘⍎¨↓⍵ ⎕EX b⌿⍵} ⍝(1) clean up the toolkit ns: :If ×⊃⍴_obs_←⎕NL 9 {}_tkns_ _clean_ _obs_ ⍝then erase xl itself: {}⎕EX'xl' :EndIf ⍝(2) clean up calling ns: :If (⎕CS'')≢⎕NSI ⍝calling ns ≢ toolkit ns ('_clean_' '_tkns_')⎕CS⊃⎕NSI ⍝calling ns :If ×⊃⍴_obs_←⎕NL 9 {}_tkns_ _clean_ _obs_ :EndIf :EndIf z←0 0⍴0 ∇ font←{ ⍵≡'?':'set font; ⍵: single cell or cell range; ⍺: any name shown in Excel font drop-down box' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Font.Name←⍺ (xl.Range xl.Cells.Item¨c).Font.Name←⍺} fontcolor←{ ⍵≡'?':'set font color; ⍵: single cell or cell range; ⍺: integer for ColorIndex, range 0 (black) to 57; ' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Font.Color←⍺ (xl.Range xl.Cells.Item¨c).Font.Color←⍺} fontcolour←{ ⍵≡'?':'set font colour; ⍵: single cell or cell range; ⍺: integer for ColorIndex, range 0 (black) to 57; ' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Font.Color←⍺ (xl.Range xl.Cells.Item¨c).Font.Color←⍺} fontsize←{ ⍵≡'?':'set font size; ⍵: single cell or cell range; ⍺: any integer shown in the Excel Font Size drop-down box' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Font.Size←⍺ (xl.Range xl.Cells.Item¨c).Font.Size←⍺} format←{ ⍵≡'?':'set NumberFormat; ⍵: single cell or cell range; ⍺: any legitimate NumberFormat;' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).NumberFormat←⍺ (xl.Range xl.Cells.Item¨c).NumberFormat←⍺} frame←{ ⍵≡'?':'set or remove outside borders around a range; ⍵: single cell or cell range; ⍺: one of 0,⍳13; positive integer sets border (1=solid (default), 2=dotted, etc.), 0=remove border' ⍺←1 ⍝default = solid line ⍝other properties: TintAndShade (default=0) and Weight (default=xlThin) ⍝nb: BorderAround method also draws a border but (i) linestyle argument 0 does not remove an existing border ⍝ and (ii) linestyles 6, 8, 10→12 yield borders that are inconsistent from what use of Borders produces ⍝ Possibly BorderAround is an obsolete method. edge←xl.(xlEdgeTop xlEdgeBottom xlEdgeLeft xlEdgeRight) ls←{⍵=0:xl.xlNone ⍵}⍺ ⍝line style: range 0 to 13 but substitute xlNone for 0 c←rc ⍵ ⍝cell co-ords 1=≡c:((xl.Cells.Item c).Borders.Item¨edge).LineStyle←ls ((xl.Range xl.Cells.Item¨c).Borders.Item¨edge).LineStyle←ls} freeze←{ ⍵≡'?':'freeze panes: ⍵ is the number of rows and columns to freeze, or 0 to cancel' 1<|≡⍵:'⍵ must be 2-element integer vector' ∧/⍵=0:xl.ActiveWindow.FreezePanes←0 (,2)≢⍴⍵:'⍵ must be 2-element integer vector' xl.ActiveWindow.FreezePanes←0 r←(xl.Cells.Item ⍵+1).Select ⍝r=1 if successful; sometimes Select method fails xl.ActiveWindow.FreezePanes←1} ∇ help;⎕PW ⎕PW←172 'active ',active'?' 'addsheet ',addsheet'?' 'bcolour ',bcolour'?' 'bold ',bold'?' 'books ',books'?' 'border ',border'?' 'border_all ',border_all'?' 'cellrange ',cellrange'?' 'centre ',centre'?' 'charts ',charts'?' 'check ',check'?' 'clean ','clean data prior to writing to a sheet' 'clear ',clear'?' 'close ',close'?' 'copysheet ',copysheet'?' 'dateToDaynum ',dateToDaynum'?' 'daynumToDate ',daynumToDate'?' 'deletesheet ',deletesheet'?' 'dim ',dim'?' 'endexcel ',endexcel'?' 'font ',font'?' 'fontcolour ',fontcolour'?' 'fontsize ',fontsize'?' 'format ',format'?' 'frame ',frame'?' 'freeze ',freeze'?' 'indent ',indent'?' 'lastcell ',lastcell'?' 'lc ','convert uppercase characters to lowercase in a character array' 'ljust ',ljust'?' 'open ',open'?' 'plot ',plot'?' 'quitexcel ',quitexcel'?' 'range ',range'?' 'rc ',rc'?' 'rd ',rd'?' 'read ',read'?' 'readf ',readf'?' 'readt ',readt'?' 'rename ',rename'?' 'rjust ',rjust'?' 'saveas ',saveas'?' 'setbook ',setbook'?' 'setsheet ',setsheet'?' 'sheets ',sheets'?' 'startexcel ',startexcel'?' 'usedrange ',usedrange'?' 'wrap ',wrap'?' 'write ',write'?' 'writef ',writef'?' '' ⋄ 'xread' ⋄ xread'?' '' ⋄ 'xwrite' ⋄ xwrite'?' ∇ indent←{ ⍵≡'?':'set indent level; ⍵: single cell or cell range; ⍺: 0 (no indent), 1 (default), 2, 3, etc' ⍺←1 ⍝default c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).(HorizontalAlignment IndentLevel)←(xl.xlLeft ⍺) (xl.Range xl.Cells.Item¨c).(HorizontalAlignment IndentLevel)←(xl.xlLeft ⍺)} ∇ z←{X}lastcell Y;⎕ML;c;offset;nextcell;firstcell;lastcell;defined;dimen;direction ⎕ML←⎕IO←1 :If Y≡'?' z←'return co-ords of cell at end of range in direction ⍺ from cell ⍵; ⍵: single cell; ⍺: one of "udlr" for up, down, left, right; result is ⍵ if no defined cells in direction ⍺' ⋄ →0 :ElseIf 0=⎕NC'X' z←'left argument is required for ∇lastcell' ⋄ →0 :EndIf ⍝X: u/d/r/l = up/down/right/left direction←(X='drul')/xl.(xlDown xlToRight xlUp xlToLeft) ⍝a bug: if we read a single cell and it's empty, get value error, so read two offset←⊃(X='drul')/(1 0)(0 1)(¯1 0)(0 ¯1) ⍝offset to next cell in direction X c←rc Y ⍝cell co-ords nextcell←c+offset dimen←dim'' :If ∨/nextcell<1 z←c ⍝c in 1st row or column :ElseIf ∨/nextcell>dimen z←c ⍝c in last row or column :Else ⍝are c and nextcell defined or empty? defined←∊×∘⍴¨(xl.Cells.Item¨c(c+offset)).Text :If 1 0≡defined z←c :ElseIf 1 1≡defined z←((xl.Cells.Item c).End direction).(Row Column) :Else ⍝cell c is empty so locate first non-empty cell firstcell←((xl.Cells.Item c).End direction).(Row Column) :If (X='durl')/(2/firstcell)=,dimen,[1.5]1 ⍝we've reached limit in direction X :If ×⍴(xl.Cells.Item firstcell).Text z←firstcell ⍝the limit in direction X :Else z←c ⍝nothing defined in direction X :EndIf :Else ⍝firstcell not at end of sheet => cell is defined :If 0=⍴(xl.Cells.Item firstcell+offset).Text ⍝next cell empty? z←firstcell :Else lastcell←((xl.Cells.Item firstcell).End direction).(Row Column) :If ×⍴(xl.Cells.Item lastcell).Text z←lastcell ⍝end of range in direction X :Else z←firstcell ⍝ditto :EndIf :EndIf :EndIf :EndIf :EndIf ∇ lc←{ a←⎕UCS,⍵ b←(a≥65)∧(a≤90) (b/a)+←32 ⎕UCS(⍴⍵)⍴a} ljust←{ ⍵≡'?':'align left; ⍵: single cell or cell range' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).HorizontalAlignment←xl.xlLeft (xl.Range xl.Cells.Item¨c).HorizontalAlignment←xl.xlLeft} open←{ ⍵≡'?':'open a workbook; ⍵: empty vector (= new book) else full path & suffix of existing book; ⍺: 0= normal open (default), 1= open under a new name' ⍺←0 ⍝default = normal open (⍺=0)∧(0=⍴,⍵):wb.Add ⍬ ⍝new book 11::'Domain Error: file is probably not an Excel workbook or else doesn''t exist' ⍺=0:wb.Open⊂⍵ ⍝normal open wb.Add⊂⍵ ⍝open as } ∇ c←{X}plot Y;⎕ML;⎕IO;parms;all_parms;defined;dataposn;data;legend;n;cols;type;b;labelposn;labels;xv;as;drc;datacols;frc;legposn;ok;values;names;yrange;x;scaling;e;s;plotposn;nullhandling;ver;scatter;pie;doughnut;stktypes;bubble;radar;chk ⍝Y: name-value pairs OR numeric data array ⍝X: chart type: number or character string (default = line) ⍝c= chart object or text string (if error) all_parms←'data' 'dataposn' 'title' 'xtitle' 'ytitle' 'legend' 'labels' 'labelposn' 'datarowscols' 'legposn' 'plotposn' 'null' 'yrange' 'scaling' ⍝ 1 2 3 4 5 6 7 8 9 10 11 12 13 14 ⍝if ⊃Y is a number, ⎕null, ⍬, or '', assume that Y is a data array ⎕ML←⎕IO←1 :If Y≡'?' c←'Make a chart in the active worksheet; ⍵= list of alternating property names and values; ⍺= chart type (default=line); see PlotSpecs for more info' ⋄ →0 :ElseIf 2=⍴⍴Y ⍝data matrix parms←1 2⍴'data'Y defined←(⍴all_parms)↑1 :ElseIf 1=⍴⍴Y ⍝data vector OR set of names & values :If 1=≡Y ⍝simple data vector :If (10|⎕DR Y)∊0 2 ⍝string c←'Right argument must comprise alternating set of property names and values OR numeric data array' ⋄ →0 :Else parms←1 2⍴'data'(⍪Y) defined←(⍴all_parms)↑1 :EndIf :ElseIf (¯2=≡Y)∧(1≥⍴,⊃Y)∧~(10|⎕DR⊃Y)∊0 2 ⍝data vector can be depth ¯2 if it includes ⍬ or ''; Y[1] may not be non-empty string parms←1 2⍴'data'(⍪Y) defined←(⍴all_parms)↑1 :ElseIf (|≡Y)∊2 3 :If (10|⎕DR⊃Y)∊0 2 ⍝assume name-value list if first element is string :If 0≠2|⍴Y c←'Right argument must comprise alternating set of property names and values OR numeric data array' ⋄ →0 :Else parms←(((⍴Y)÷2),2)⍴Y :EndIf :Else c←'Right argument must comprise alternating set of property names and values OR numeric data array' ⋄ →0 :EndIf :Else c←'Right argument has invalid structure' ⋄ →0 :EndIf :Else c←'Right argument has invalid structure' ⋄ →0 :EndIf :If ~∧/b←parms[;1]∊all_parms c←'Invalid property name(s): ',⍕(~b)/parms[;1] ⋄ →0 :EndIf defined←all_parms∊parms[;1] values←(⍴all_parms)⍴⊂⍬ values[all_parms⍳parms[;1]]←parms[;2] ⍝-------- check the parameters ------------------------- chk←{((⍴⍵)≢,⍺)∨~((⎕DR ⍵)∊11 83 163 323):0 ∨/⍵≤0:0 1} :If defined[2] dataposn←rc 2⊃values :If 0=2 chk dataposn c←'Parameter "dataposn" requires two positive integers' ⋄ →0 :EndIf :Else dataposn←1 1 ⍝default defined[2]←1 :EndIf :If defined[9] ⍝datarowscols drc←9⊃values :If 0=2 chk drc c←'Parameter "datarowscols" requires two positive integers' ⋄ →0 :EndIf :ElseIf ~defined[1] ⍝data c←'Parameter "datarowscols" is required' ⋄ →0 :EndIf :If defined[7] ⍝labels :If ~defined[8] ⍝labelposn c←'Parameter labelposn is required for the labels' ⋄ →0 :EndIf :EndIf :If defined[10] ⍝legposn legposn←⊃(10⊃values)~' ' :If ~legposn∊'tblrc' c←'Parameter legposn (',(⍕legposn),') is invalid' ⋄ →0 :EndIf :EndIf :If defined[11] ⍝plotposn plotposn←11⊃values :If (2≠⍴,plotposn)∨∨/(10|∊⎕DR¨plotposn)∊0 2 c←'Parameter plotposn must have two elements (number or ⍬)' ⋄ →0 :EndIf :EndIf :If defined[12] ⍝null nullhandling←⊃12⊃values :AndIf ~nullhandling∊xl.(xlNotPlotted xlZero xlInterpolated) c←'Parameter null must be one of ',⍕xl.(xlNotPlotted xlZero xlInterpolated) ⋄ →0 :EndIf :If defined[1] ⍝data data←⊃values :If 0∊⍴data c←'Data array is empty' ⋄ →0 :ElseIf 1=⍴⍴data data←((⍴data),1)⍴data ⍝⍪data in V12 :EndIf drc←⍴data :If 0∊drc c←'The data array is empty' ⋄ →0 :ElseIf defined[2] data write dataposn :EndIf :EndIf type←0 :If 0=⎕NC'X' type←xl.xlLine :ElseIf (⎕DR X)∊83 163 :If X∊xl.XlChartType[;3] type←X :EndIf :ElseIf (10|⎕DR X)∊0 2 type←⊃((⊂X)≡¨'cc' 'sc' 'l' 'lm' 'pie')/xl.(xlColumnClustered,xlColumnStacked,xlLine,xlLineMarkers,xlPie) :EndIf :If type=0 c←'Invalid type: ',,(⍕X),' ' ⋄ →0 :EndIf (scatter pie doughnut bubble radar)←type∊¨(¯4169 72 73 74 75)(¯4102 5 68 69 70 71)(¯4120 80)(15 87)(¯4151 81 82) ⍝special plot types stktypes←88 89 90 91 :If type∊stktypes :AndIf drc[2]≠cols←(type=stktypes)/3 4 4 5 names←'xlStockHLC' 'xlStockOHLC' 'xlStockVHLC' 'xlStockVOHLC' c←'Data array for chart type ',(⊃names[stktypes⍳type]),' requires ',(⍕cols),' columns' ⋄ →0 :EndIf :If defined[13] ⍝yrange yrange←,13⊃values :If 3<⍴yrange c←'Yrange must be a vector of up to 3 elements: max value, min value and interval' ⋄ →0 :EndIf yrange,←(3-⍴yrange)⍴⊂⍬ :EndIf :If defined[14] ⍝scaling s←,14⊃values ⍝scaling: vector height & width factors and h&w origins ⍝[1 2]= height & width factor (default=1) ⍝[3 4]= origin: 0=top/left, 1=centre, 2=bottom/right :If (4<⍴s)∨~(⎕DR s)∊11 83 645 ⍝small ints + floats c←'Scaling must be a numeric vector of up to 4 elements: height factor, width factor, height origin (0/1/2), width origin' ⋄ →0 :EndIf scaling←1 0 0 1 0 0 ⍝defaults, with extra values req'd by Excel scaling[(⍴s)↑1 4 3 6]←s ⍝overlay defaults with input values ⍝scaling is ht fac, 0, ht origin, wi fac, 0, wi origin :EndIf ⍝-------- create the chart ----------------------------- ver←2⊃⎕VFI{(⍵⍳'.')↑⍵}xl.Version as←xl.ActiveSheet :If ver<12 xl.Charts.Add ⍬ c←xl.ActiveChart c.Location('Where'xl.xlLocationAsObject)('Name'as.Name) :Else c←(as.Shapes.AddChart ⍬).Chart :EndIf ⍝order of actions below is critical for certain plots but immaterial for others :If bubble∨scatter ⍝must set chart type before source data c.ChartType←type c.SetSourceData('Source'(as.Range xl.Cells.Item¨dataposn(dataposn+drc-1)))('PlotBy'xl.xlColumns) :Else ⍝must set source data before chart type for surface, stock c.SetSourceData('Source'(as.Range xl.Cells.Item¨dataposn(dataposn+drc-1)))('PlotBy'xl.xlColumns) c.ChartType←type :EndIf ⍝-------- apply attributes ----------------------------- :If defined[12] c.DisplayBlanksAs←nullhandling :EndIf :If defined[3] ⍝plot title c.HasTitle←1 ⍝HasTitle enables the definition of the associated title c.ChartTitle.Text←3⊃values ⍝nb: when is style ...Text←'={"title text"}' required? :EndIf :If defined[4]>pie∨doughnut∨radar ⍝x-axis title; n/a to pie, doughnut, radar c.(Axes xlCategory).HasTitle←1 c.(Axes xlCategory).AxisTitle.Text←4⊃values :EndIf :If defined[5]>pie∨doughnut∨radar ⍝y-axis title; n/a to pie, doughnut, radar c.(Axes xlValue).HasTitle←1 c.(Axes xlValue).AxisTitle.Text←5⊃values :EndIf :If defined[6]>pie∨doughnut ⍝legend n/a to pie & doughnut legend←6⊃values :If defined[10] ⍝legposn c.Legend.Position←(legposn='tbrlc')/c.(xlTop,xlBottom,xlRight,xlLeft,xlCorner) :EndIf :If (10|⎕DR legend)∊0 2 (c.SeriesCollection 1).Name←legend :ElseIf (⎕DR legend)=326 ⍝assign only as many legends as we have up to the number of series :If bubble ⍝# of series: ( # of cols = ⍳9)/1 1 1 2 2 3 3 4 4 eg n←(1⌈⌊drc[2]÷2)⌊⍴legend (⍳n){(c.SeriesCollection ⍺).Name←⍵}¨n↑legend :Else ⍝for scatter plot, subtract 1 because n cols of data leads to n-1 series ⍝but even if only 1 data col we still get a plot (x-axis is ⍳⊃⍴drc) n←1⌈(drc[2]-scatter)⌊⍴legend (⍳n){(c.SeriesCollection ⍺).Name←⍵}¨n↑legend :EndIf :EndIf :EndIf :If defined[8] ⍝labelposn labelposn←rc 8⊃values :If 0=2 chk labelposn c←'Parameter "labelposn" requires two positive integers' ⋄ →0 :EndIf :If ~defined[7] ⍝labels ⍝verify at least one label cell is defined (otherwise the graph gets screwed up) labels←,read labelposn((labelposn[1]+drc[1]-1),labelposn[2]) :If ∧/labels≡¨⎕NULL c←'No lables are defined at "labelposn"' ⋄ →0 :EndIf :EndIf :EndIf :If defined[7] ⍝x-axis labels labels←7⊃values :If 1=⍴⍴labels labels←((⍴labels),1)⍴⍕¨labels ⍝⍕ in case they are numeric :EndIf labels write labelposn :EndIf :If defined[8]>scatter∨bubble ⍝labelposn; a scatter plot may get buggered if XValues specified; labels not impl. for bubble ⍝specify range in '=Sheet!r1c1:r2c2' style frc←{'R',(⍕⊃⍵),'C',⍕2⊃⍵} xv←'=',(as.Name),'!',(frc labelposn),':',frc((labelposn[1]+drc[1]-1),labelposn[2]) (c.SeriesCollection 1).XValues←xv :EndIf :If defined[11] ⍝plotposn ok←~∊⍴∘⍴¨plotposn ⍝1 means non-empty :If ∧/ok c.ChartArea.(Left Top)←plotposn :ElseIf ok[1] c.ChartArea.Left←⊃plotposn :ElseIf ok[2] c.ChartArea.Top←2⊃plotposn :EndIf :EndIf :If defined[13]>pie∨doughnut ⍝y-axis range & intervals; n/a to pie & doughnut x←c.Axes xl.(xlValue,xlPrimary) ⍝xlPrimary uncessary but anticipates xlSecondary x{(,0)≢⍴⍵:⍺.MaximumScale←⍵}⊃yrange x{(,0)≢⍴⍵:⍺.MinimumScale←⍵}2⊃yrange x{(,0)≢⍴⍵:⍺.MajorUnit←⍵}3⊃yrange :EndIf :If defined[14] ⍝scaling c.##.ScaleHeight 3↑scaling c.##.ScaleWidth ¯3↑scaling :EndIf ∇ quitexcel←{ ⍵≡'?':'terminate Excel session and erase Excel objects in the current namespace; ⍵: empty vector' 0=⎕NC'xl':endexcel'' xl.Quit endexcel''} range←{ ⍵≡'?':'return range co-ordinates; ⍵: (1→5 elements): single cell, # of rows (+ or -) or ⍬, # of cols (+ or -) or ⍬ [, {u|d} [, {l|r}]]' ⍝⍵[]: (|≡⍵) ∊ 2 3: 1=number-pair or string, 2=number or ⍬, 3=number or ⍬, 4=char, 5=char ⎕IO←1 ⍝if ⍵[1] = string convert to co-ords; '' = active cell args←{(10|⎕DR⊃⍵)∊0 2:{''≡⊃⍵:(⊂xl.ActiveCell.(Row Column)),1↓⍵ (⊂rc⊃⍵),1↓⍵}⍵ ,⍵}⍵ ((y x)∆y ∆x diry dirx)←args,((⍴args)-5)↑⍬ ⍬'d' 'r' ⍝d, r = default directions diry←⊃diry,'d' ⋄ dirx←⊃dirx,'r' ⍝substitute defaults if empty r←{0=⍴,3⊃⍵:⊃(4⊃⍵)lastcell 2↑⍵ (+/⍵[1 3])-×⍵[3] }y x ∆y diry c←{0=⍴,3⊃⍵:2⊃(4⊃⍵)lastcell 2↑⍵ (+/⍵[2 3])-×⍵[3] }y x ∆x dirx (y,x)(1⌈(dim'')⌊r,c) } rc←{ ⍵≡'?':'return row-column co-ordinates of an address or address range; ⍵: string such as E5 or E5:H9' ⎕ML←1 ~(10|⎕DR ⍵)∊0 2:⍵ addr←⍵~' ' ⍝embedded space causes error ':'∊addr:rc¨1↓¨(1,addr=':')⊂':',addr (xl.Range addr).(Row Column)} rd←{ ⍵≡'?':'return a range as a string; ⍵: one or two co-ordinate pairs' ⎕ML←1 (10|⎕DR ⍵)∊0 2:⍵ ⍝allow (2 3) and (⊂2 3) ... 1=≡⍵:(xl.Cells.Item ⍵).Address'' ⍝single cell, ⍵ simple 1=⍴,⍵:(xl.Cells.Item⊃⍵).Address'' ⍝single cell, ⍵ nested ⍝range: (xl.Range xl.Cells.Item¨⍵).Address''} read←{ ⍵≡'?':'return data from sheet; ⍵: single cell or cell range' 6::⎕NULL ⍝Value Error occurs when reading a single empty cell 11::'Domain Error: probbably due to errors in the target range' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Value2 (xl.Range xl.Cells.Item¨c).Value2} readf←{ ⍵≡'?':'return formulas from sheet; ⍵: single cell or cell range' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Formula (xl.Range xl.Cells.Item¨c).Formula} readt←{ ⍵≡'?':'return text from sheet; ⍵: single cell or cell range' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).Text {(xl.Cells.Item ⍵).Text}¨cellrange c} rename←{ ⍵≡'?':'rename worksheets; ⍵: list of (sheet # or name, new name [, sheet # or name, new name [, ...]])' ⎕IO←⎕ML←1 names←,⍵ ((10|⎕DR names)∊0 2)∨(1=2|⍴names):'specify an even number of sheet names or numbers' names←((0.5×⍴⍵),2)⍴⍵ names[(names[;1]≡¨⊂'')/⍳⊃⍴names;1]←⊂xl.ActiveSheet.Name sh←xl.Sheets 11::'domain error: probably invalid name or number of existing sheet or invalid new name' sh[names[;1]].Name←names[;2] ⍝error if bad ⍺ or ⍵ } rjust←{ ⍵≡'?':'align right; ⍵: single cell or cell range' c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).HorizontalAlignment←xl.xlRight (xl.Range xl.Cells.Item¨c).HorizontalAlignment←xl.xlRight} ∇ z←{X}saveas Y;⎕ML;⎕IO;ff;exten;ffmt;ver;ffmt;ffmts;alerts;wbk ⎕ML←⎕IO←1 :If Y≡'?' z←'save a workbook under another name or format; ⍵[1]= name of open workbook (no path) or '''' for active workbook; ⍵[2]= file name to write (with path & suffix); ⍵[3] (optional) = file format number; ⍺: ''p''=prompt (default) or ''f''=force file-save and avoid a potential prompt (as where file already exists, eg)' →0 :EndIf :If 0=⎕NC'X' X←'p' :EndIf :If 2≠|≡Y 'Specify workbook and file name and file format (optional) in ⍵' ⋄ →0 ⍝⍵ m/b nested vec of at least length 2 :EndIf wbk←{0=⍴,⍵:xl.ActiveWorkbook wb[⊂⍵]}⊃Y ff←3⊃Y,⊂'' ⍝file format ver←2⊃⎕VFI xl.Version :If 0=⍴,ff ⍝no file format specified so use the format implied by the file extension, if any exten←{'.'∊⍵:(1-(⌽⍵)⍳'.')↑⍵ ''}2⊃Y :If ×⍴exten :If ver≥12 ffmts←'xls' 'csv' 'xml' 'html' 'xlt' 'xla' 'xlsb' 'xlsx' 'xlsm' 'xltx' 'xltm' 'xlam' ffmt←⊃((⊂exten)≡¨ffmts)/xl.(xlWorkbookNormal xlCSV xlXMLSpreadsheet xlHtml xlTemplate xlAddIn xlExcel12 xlOpenXMLWorkbook xlOpenXMLWorkbookMacroEnabled xlOpenXMLTemplate xlOpenXMLTemplateMacroEnabled xlOpenXMLAddIn) :Else ⍝version 11 and earlier ffmts←'xls' 'csv' 'xml' 'html' 'xlt' 'xla' ffmt←⊃((⊂exten)≡¨ffmts)/xl.(xlWorkbookNormal xlTemplate xlCSV xlXMLSpreadsheet xlHtml xlAddIn) :EndIf :If 0=ffmt z←'Please supply a valid file format as the third argument (see xl.XlFileFormats)' ⋄ →0 :EndIf :Else ⍝no file format nor extension so use current format ffmt←wbk.FileFormat :EndIf :Else ⍝ff specified :If (⎕DR ff)∊11 83 163 ffmt←(ff∊xl.XlFileFormat[;3])/ff :If 0=ffmt z←'Please supply a valid file format as the third argument (see xl.XlFileFormats)' ⋄ →0 :EndIf :Else z←'The file format (third argument) must be an Excel constant (see xl.XlFileFormats)' ⋄ →0 :EndIf :EndIf alerts←xl.DisplayAlerts xl.DisplayAlerts←X='p' :Trap 11 wbk.SaveAs(2⊃Y)('FileFormat'ffmt) ⍝Y[2] ? xl.DisplayAlerts←alerts :Else z←'Domain Error: probably invalid workbook name' xl.DisplayAlerts←alerts :EndTrap z←0 0⍴0 ∇ setbook←{ ⍵≡'?':'set the active workbook and return the book object; ⍵: the name of an open workbook (include the suffix but exclude the path)' 0=⍴,⍵:xl.ActiveWorkbook aw←wb.Item ⍵ ⍝Item is more tolerant than [] aw.Activate aw} setsheet←{ ⍵≡'?':'set active sheet and return sheet object; ⍵: sheet name in active workbook' 0=⍴,⍵:xl.ActiveSheet as←xl.Sheets.Item ⍵ ⍝Item is more tolerant than [] as.Activate as} sheets←{ ⍵≡'?':'return list of sheets in a workbook; ⍵: empty vector (= active workbook) else the name (excluding path) of any open workbook' ⎕IO←1 0=⍴,⍵:xl.(Sheets[⍳Sheets.Count]).Name sh←wb[⊂⍵].Sheets sh[⍳sh.Count].Name} ∇ z←{X}startexcel Y;a;b;⎕WX ⍝start excel :If Y≡'?' z←'start Excel; ⍵: '''' to use existing Excel session, if possible, or ''new'' to start new Excel sesssion; ⍺: 1=visible (default), 0=hidden' ⋄ →0 :EndIf :If 0=⎕NC'X' X←1 :EndIf :Trap 0 ⎕WX←1 :If 0=⍴Y ⍝use existing session if one exists; if already connected, do nothing :Trap 6 11 a←xl.Visible ⋄ b←wb.Count ⍝if no error, we're ok :Else ⎕EX¨'xl' 'wb' 'xl'⎕WC'oleclient' 'excel.application' :EndTrap :ElseIf Y≡'new' ⎕EX¨'xl' 'wb' 'xl'⎕WC'oleclient' 'excel.application'('instanceMode' 'new') :Else z←'argument not empty vector or ''new''' ⋄ →0 :EndIf xl.Visible←X wb←xl.Workbooks z←0 :Else 'error in ∇startexcel'⎕SIGNAL 500 :EndTrap ∇ ∇ test 'ns=',⎕CS'' ⎕CS⊃⎕NSI 'ns=',⎕CS'' ∇ usedrange←{ ⍵≡'?':'return matrix (if >1 defined cell) of all data in the worksheet; ⍵: empty vector (= the active sheet) or else the name of any sheet in the active workbook' 6::''(0 0⍴0) ⍝value error if no defined cells ⍝but let domain error occur if a cell has an error 0=⍴,⍵:(xl.ActiveSheet.UsedRange).((Address'')Value2) ⍝Item is more tolerant than [] below (xl.ActiveWorkbook.Sheets.Item ⍵).UsedRange.((Address'')Value2)} wrap←{ ⍵≡'?':'set or remove text wrapping; ⍵: single cell or cell range; ⍺: 1=set (default), 0=remove' ⍺←1 ⍝set wrap; 0=remove wrap c←rc ⍵ ⍝cell co-ords 1=≡c:(xl.Cells.Item c).WrapText←⍺ (xl.Range xl.Cells.Item¨c).WrapText←⍺} write←{ ⍵≡'?':'write data to sheet; ⍵: single cell (top left corner of target range); ⍺: data array of rank≤2 and (|depth)≤2' ⎕ML←⎕IO←1 (|≡⍵)>1:'⍵ must be row and column co-ordinates of a single cell' ⍝in case user specifies a range ⍝character scalar, vector or matrix: c←rc ⍵ ⍝cell co-ords (10|⎕DR ⍺)∊0 2:⍺{1≥⍴⍴⍺:(xl.Cells.Item c).Value2←clean ⍺ (xl.Range xl.Cells.Item¨⍵(⍵+(¯1+⊃⍴⍺),0)).Value2←clean¨,[1.5]↓⍺}⍵ 1=×/⍴⍺:(xl.Cells.Item c).Value2←clean ⍺ ⍝scalar number ⍝vector (treat as 1×n) or array: (xl.Range xl.Cells.Item¨c(c+¯1+¯2↑1,⍴⍺)).Value2←clean¨⍺} writef←{ ⍵≡'?':'write formulas and/or values to sheet; ⍵: single cell (top left corner of target range); ⍺: single formula or array of formulas with rank≤2 and (|depth)≤2' (|≡⍵)>1:'⍵ must be row and column co-ordinates of a single cell' ⍝in case user specifies a range ⎕ML←⎕IO←1 c←rc ⍵ ⍝cell co-ords (10|⎕DR ⍺)∊0 2:(xl.Cells.Item c).Formula←clean ⍺ ⍝single cell ⍝if ⍺ is an array (vector treated as n×1): cells←,cellrange c(c+¯1+¯2↑1,⍴⍺) (,⍺){(xl.Cells.Item ⍵).Formula←clean ⍺}¨cells} ∇ z←{X}xread Y;wbk;sheet;cells;flags;isOpen;c;a;type;addr;ur;sel;n;s;⎕ML;⎕IO ⍝Read data from Excel ⍝Y: workbook, worksheet, range (single cell, cell range or dynamic range) ⍝X: flags: [1]=Workbook (0/1/2/3), [2]=Excel (0/1/2) ⍝start Excel if necessary, open workbook if necessary ⎕ML←⎕IO←1 ⍝check Y: :If '?'≡Y '' 'Right argument: Workbook, Worksheet, Cell Range (3 items)' ' Workbook: name or number; name must include full path if it is not already open; empty vector implies active workbook' ' number is an index to "wb" (Workbooks Collection) - valid for an open workbook only; empty vector implies active workbook' ' Worksheet: name or number; empty vector implies active sheet; number is an index to xl.Sheets (Worksheets collection)' ' Cell Range: Option 1: standard format, such as ''B5'' or ''B5:C8''' ' Option 2: empty vector, implying the current selection' ' Option 3: ''*'' meaning UsedRange, ie, all the data in the sheet' ' Option 4: an argument allowed for the function range; eg,' ' a fixed range, such as (''C5'' 3 4), meaning 3 rows and 4 columns starting at ''C5''' ' a dynamic range, such as (''C5'' ⍬ ⍬), meaning all rows down to the first blank cell in column C' ' and all columns across to the first blank cell in row 5' ' Either or both of the number of rows and columns may be ⍬' ' For options 1 and 4, row-column co-ordinates are allowed; eg, (5 2) for "B5" and ((5 2)(8 3)) for "B5:C8"' ' The cell range must be enclosed if option 4 is used or if co-ordinates are specified for option 1' '' 'Optional left argument: any of the following characters to control disposition of Workbook and Excel objects:' ' "n": start a new Excel session' ' "c": close the workbook' ' "e": erase the Excel objects but leave the Excel session open' ' "q": erase the Excel objects and quit Excel' '' 'Result:' ' a scalar or string (single cell)' ' a simple or nested matrix (multiple cells)' →0 :ElseIf ((,3)≢⍴Y)∨(∨/(∊⍴∘⍴¨Y)>1)∨(~(|≡Y)∊⍳3) ⍝require 3 elements, each of rank ≤1; overall depth 1, 2 or 3 'Right argument has invalid structure'⎕SIGNAL 500 :EndIf (wbk sheet cells)←,¨Y ⍝prohibit scalars ⍝check X: :If ×⎕NC'X' :If ~(10|⎕DR X)∊0 2 'Left argument has invalid strucure' ⋄ →0 :ElseIf ∧/X∊'nceq ' flags←lc X :Else 'Allowed values for left argument are "n", "c", "e", "q"'⎕SIGNAL 500 :EndIf :Else flags←'' :EndIf a←1 startexcel('n'∊flags)/'new' ⍝start Excel if necessary ⍝open or activate the workbook: :If wb.Count=0 ⍝must open an existing book :If (0=⍴wbk)∨~(10|⎕DR wbk)∊0 2 ⍝error if `wbk empty or number 'The name and path of an existing workbook are required'⎕SIGNAL 500 :Else :Trap 11 wb.Open⊂wbk :Else ('Cannot open workbook "',wbk,'"')⎕SIGNAL 500 :EndTrap :EndIf :Else ⍝open or activate: :If ×⍴wbk ⍝no action needed for active workbook (`wbk empty) :If (10|⎕DR wbk)∊0 2 ⍝string :If ':'∊wbk :If '.'∊wbk ⍝extension included isOpen←(⊂lc wbk)∊lc¨wb[⍳wb.Count].FullName :Else ⍝no extension isOpen←(⊂lc wbk)∊{lc(¯1+⍵⍳'.')↑⍵}¨wb[⍳wb.Count].Name :EndIf :Else :If '.'∊wbk ⍝extension included isOpen←(⊂lc wbk)∊lc¨wb[⍳wb.Count].Name :Else ⍝no extension isOpen←(⊂lc wbk)∊{lc(¯1+⍵⍳'.')↑⍵}¨wb[⍳wb.Count].Name :EndIf :EndIf :If isOpen wb[⊂(1-(⌽wbk)⍳'\')↑wbk].Activate ⍝remove path, if any :Else :If ':'∊wbk ⍝full path specified :Trap 11 wb.Open⊂wbk :Else ('Cannot open workbook "',wbk,'"')⎕SIGNAL 500 :EndTrap :Else ('Cannot open workbook "',wbk,'"')⎕SIGNAL 500 :EndIf :EndIf :ElseIf 1=⍴wbk :If wbk∊⍳wb.Count ⍝number wb[⊃wbk].Activate :Else ('Cannot open workbook "',(⍕wbk),'"')⎕SIGNAL 500 :EndIf :Else 'Workbook specification is invalid'⎕SIGNAL 500 :EndIf :EndIf :EndIf ⍝activate the sheet: :If ×⍴sheet ⍝no action needed for active sheet :If (10|⎕DR sheet)∊0 2 ⍝is string :If (⊂lc sheet)∊lc¨xl.Sheets[⍳xl.Sheets.Count].Name xl.Sheets[⊂sheet].Activate :Else ('Sheet "',sheet,'" does not exist in book "',xl.ActiveWorkbook.Name,'"')⎕SIGNAL 500 :EndIf :ElseIf 1=⍴sheet :If sheet∊⍳xl.Sheets.Count xl.Sheets[⊃sheet].Activate :Else ('Sheet "',(⍕sheet),'" does not exist in book "',xl.ActiveWorkbook.Name,'"')⎕SIGNAL 500 :EndIf :Else 'Sheet specification is invalid'⎕SIGNAL 500 :EndIf :EndIf ⍝determine the cell range: :If 0=⍴cells ⍝use the current selection (possibly just the active cell) ⍝beware that if entire rows or columns are selected, we'll get too much ⍝so truncate the range by using endpoint of used range ⍝Address of Row range is "$6:$8", eg, and column range "$B:$F" addr←xl.Selection.Address'' type←2⊥(∨/⎕D∊addr),(∨/⎕A∊addr) ⍝1=col range, 2=row range, 3=rectangle :If type<3 ⍝row or column range selected ur←rc xl.ActiveSheet.UsedRange.Address'' ⍝just $A$1 for empty sheet :If 2=≡ur ur←2⊃ur ⍝just want last cell of used range :EndIf :If type=1 ⍝column range sel←xl.Columns[1↓¨{(1,⍵=':')⊂':',⍵}addr~'$'].Column ⍝col numbers in selection c←(1,sel[1])(ur[1],sel[2]) ⍝cell range: up to last row of used range :Else ⍝row range ((addr∊'$:')/addr)←' ' ⋄ sel←2⊃⎕VFI addr ⍝row #s in selection c←(sel[1],1)(sel[2],ur[2]) ⍝cell range: up to last col of used range :EndIf :If ≡/c ⍝one-cell range :Trap 6 11 z←{xl.Cells.(Item ⍵).Value2}⊃c :Else :If ⎕EN=6 z←⎕NULL ⍝single empty cell :Else ⍝error ('Cell ',(⍕⊃c),' contains an error and can`t be read')⎕SIGNAL 500 :EndIf :EndTrap :Else ⍝rectangular range :Trap 11 z←(xl.Range xl.Cells.Item¨c).Value2 ⍝remove trailing rows or cols that contain ⎕null (empty cells that have been formatted are included in used range): :If ×n←+/∧\⌽∧/[3-type]z≡¨⎕NULL :If n=(⍴z)[type] s←⍴z ⋄ s[type]←0 ⋄ z←s⍴0 ⍝needed because n↓n⍴⎕null ←→ nonce error :Else z←(-n)↓[type]z :EndIf :EndIf :Else 'The cell range contains an error and cannot be read'⎕SIGNAL 500 :EndTrap :EndIf :Else ⍝rectangular selection :Trap 6 11 z←xl.Selection.Value2 :Else c←rc xl.Selection.Address'' ⍝depth 1 for single cell, 2 for range :If ⎕EN=6 ⍝implies single empty cell z←⎕NULL :Else ⍝11=domain error 'Some cells contain errors and cannot be read'⎕SIGNAL 500 :EndIf :EndTrap :EndIf :ElseIf (,'*')≡cells ⍝read whole sheet :Trap 6 11 ⍝value error, domain error z←xl.ActiveSheet.UsedRange.Value2 :Else :If ⎕EN=6 ⍝value error; occurs if no defined cells z←0 0⍴0 :Else ⍝11=domain error 'Some cells contain errors and cannot be read'⎕SIGNAL 500 :EndIf :EndTrap :Else ⍝explicit cell(range) specified, string or co-ords, or argument for ∇range :If (10|⎕DR cells)∊0 2 ⍝string :Trap 11 c←rc cells :Else 'The cell range specification is invalid'⎕SIGNAL 500 :EndTrap :ElseIf (⎕DR cells)∊11 83 163 323 ⍝numeric :If 2=⍴cells ⍝single cell (row, column) c←cells :Else 'Cell specification is invalid'⎕SIGNAL 500 :EndIf :ElseIf 326=⎕DR cells ⍝cell range (2 pairs of co-ords) or ∇range arg ⎕ML←0 ⋄ type←∊cells ⋄ ⎕ML←1 :If (2=⍴cells)∧(type≡(0 0)(0 0)) ⍝eg, cells←(1 2)(8 7) c←cells :Else :Trap 0 c←range cells :Else 'The cell range specification is invalid (interpreted by ∇range)'⎕SIGNAL 500 :EndTrap :EndIf :Else 'The cell range specification is invalid'⎕SIGNAL 500 :EndIf :If (2=≡c)∧(,2)≡⍴c :AndIf ≡/c ⍝eg, (5 4)(5 4) c←⊃c :EndIf :If 1=≡c ⍝singlecell :Trap 6 11 z←(xl.Cells.Item c).Value2 :Else :If ⎕EN=6 ⍝value error occurs for single empty cell z←⎕NULL :Else 'The cell contains an error and cannot be read'⎕SIGNAL 500 :EndIf :EndTrap :Else ⍝rectangular range :Trap 11 z←(xl.Range xl.Cells.Item¨c).Value2 :Else 'The cell range contains an error and cannot be read'⎕SIGNAL 500 :EndTrap :EndIf :EndIf ⍝clean up: conditionally close workbook and/or excel ⍝close the workbook :If 'c'∊flags :If 0=xl.ActiveWorkbook.Saved ('Workbook "',wbk,'" has unsaved changes')⎕SIGNAL 500 :Else xl.ActiveWorkbook.Close'' :EndIf :EndIf ⍝end or close Excel :If 'q'∊flags quitexcel'' :ElseIf 'e'∊flags endexcel'' :EndIf ∇ ∇ {X}xwrite Y;err;wbk;sheet;cell;flags;data;isOpen;a;newFile;⎕ML;⎕IO;d;wbn ⍝Write data to Excel ⍝Y: workbook, worksheet, single cell, data ⍝X: flags: [1]=Workbook (0/1/2/3), [2]=Excel (0/1/2) ⍝start Excel if necessary, open workbook if necessary ⍝create workbook if it doesn't exist (implies do SaveAs) and ditto worksheet err←0 ⋄ ⎕ML←⎕IO←1 ⍝check Y: :If '?'≡Y '' 'Right argument: Workbook, Worksheet, Cell, Data (4 items)' ' Workbook: name or number; name must include full path if it is not already open; empty vector implies active workbook' ' number is an index to "wb" (Workbooks Collection) - valid for an open workbook only; empty vector implies active workbook' ' If the workbook does not exist, it will be created and saved' ' If Workbook≡''book'', Excel will created a workbook ''Bookn'', where n is a small integer, which will not be saved' ' Worksheet: name or number; empty vector implies active sheet; number is an index to xl.Sheets (Worksheets collection)' ' Cell specifies the top left corner of the target range' ' Option 1: standard format, such as ''B5''' ' Option 2: empty vector, implying the active cell' ' Option 3: nested row and column co-ordinates such as (3 4), ie, ''D3''' ' Data: an array of rank≤2 and (|depth)≤2; a character vector and each row of a character matrix is treated as a single entity' '' 'Optional left argument: any of the following characters for disposition of Workbook and Excel objects:' ' "n": start a new Excel session' ' "s": save the workbook' ' "c": save and close the workbook' ' "e": erase the Excel objects but leave the Excel session open' ' "q": erase the Excel objects and quit Excel' ' Beware of overrides:' ' If Workbook=''book'' the workbook will not be saved, even if either "c" or "s" is specified' ' If Workbook specifies a new file the workbook will be saved, even if neither "c" nor "s" is specified' '' →0 :ElseIf ((,4)≢⍴Y)∨~(|≡Y)∊2 3 ⍝4 items, overall depth 2 or 3 err←1 :ElseIf (∨/1<∊⍴∘⍴¨3↑Y)∨(2<⍴⍴4⊃Y) ⍝scalar/vector for first 3 items, rank ≤2 or last err←1 :EndIf :If err 'Right argument has invalid strucure'⎕SIGNAL 500 :EndIf (wbk sheet cell)←,¨3↑Y ⋄ data←4⊃Y ⍝no scalars in first 3 items ⍝check X: :If ×⎕NC'X' :If ~(10|⎕DR X)∊0 2 'Left argument has invalid strucure' ⋄ →0 :ElseIf ∧/X∊'ncseq ' flags←lc X :Else 'Allowed values for left argument are "n", "c", "s", "e", "q"'⎕SIGNAL 500 :EndIf :Else flags←'' :EndIf a←1 startexcel('n'∊flags)/'new' ⍝start Excel if necessary ⍝open, activate or create the workbook: newFile←0 :If 0=⍴wbk ⍝empty vector :If 0=wb.Count 'No workbooks are open; workbook "" is invalid'⎕SIGNAL 500 :EndIf :ElseIf (⎕DR wbk)∊11 83 163 ⍝number :If 0=wb.Count ('No workbooks are open; workbook "',(⍕wbk),'" is invalid')⎕SIGNAL 500 :Else :If wbk∊⍳wb.Count wb[⊃wbk].Activate :Else ('The workbook number must be in the range ⍳',⍕wb.Count)⎕SIGNAL 500 :EndIf :EndIf :ElseIf 'book'≡lc wbk wb.Add ⍬ ⍝create a new book; eg, Book1; this is now the active book newFile←2 :ElseIf ':'∊wbk ⍝file name including path ⍝validate the name d←⎕CMD'dir "',wbk,'"' ⍝⍴d: 0=bad name, 5=name ok but doesn't exist, 8=file exists, >8=directory or '*'=¯1↑wbk :If (0=⍴d)∨(8<⍴d) ('"',wbk,'" is not a valid file name')⎕SIGNAL 500 :ElseIf 5=⍴d ⍝valid but non-existant file name :If '.'∊wbk ⍝require extension for new file wb.Add ⍬ ⍝create a new book (eg, Book1); must do SaveAs later newFile←1 :Else ⍝no extension so try to match `wbk against open files first isOpen←0 :If wb.Count>0 isOpen←(⊂lc wbk)≡¨lc{(¯1+⍵⍳'.')↑⍵}¨wb[⍳wb.Count].FullName :If 0=+/isOpen isOpen←0 :ElseIf 1=+/isOpen wb[⊂(1-(⌽wbk)⍳'\')↑wbk].Activate ⍝discard path isOpen←1 :Else ⍝>1 match ('workbook "',wbk,'" is ambiguous')⎕SIGNAL 500 :EndIf :EndIf :If ~isOpen ⍝try to open anyway; `wbk could match a file with an Excel extension :Trap 11 wb.Open wbk :Else ('Cannot open "',wbk,'"; specify an extension in order to create it')⎕SIGNAL 500 :EndTrap :EndIf :EndIf :Else ⍝file exists; open or activate it :If wb.Count>0 isOpen←(⊂lc wbk)∊lc¨wb[⍳wb.Count].FullName ⍝assumes '.'∊wbk :Else isOpen←0 :EndIf :If isOpen wb[⊂(1-(⌽wbk)⍳'\')↑wbk].Activate ⍝discard path :Else :Trap 11 wb.Open wbk :Else ('Cannot open "',wbk,'"')⎕SIGNAL 500 :EndTrap :EndIf :EndIf :Else ⍝no path so match `wbk against open books; isOpen←0 :If 0=wb.Count ('Workbook "',wbk,'" is not open; specify the full path')⎕SIGNAL 500 :Else ⍝there are open workbooks :If '.'∊wbk ⍝with extension isOpen←(⊂lc wbk)∊lc¨wb[⍳wb.Count].Name :Else ⍝no extension isOpen←(⊂lc wbk)≡¨lc¨{(¯1+⍵⍳'.')↑⍵}¨wb[⍳wb.Count].Name :If 0=+/isOpen isOpen←0 :ElseIf 1=+/isOpen isOpen←1 :Else ⍝>1 match ('workbook "',wbk,'" is ambiguous')⎕SIGNAL 500 :EndIf :EndIf :If isOpen wb[⊂wbk].Activate :Else ('Workbook "',wbk,'" is not open')⎕SIGNAL 500 :EndIf :EndIf :EndIf ⍝activate the sheet: :If ×⍴sheet ⍝empty vector implies active sheet - no action needed :If (10|⎕DR sheet)∊0 2 ⍝is string :If (⊂lc sheet)∊lc¨xl.Sheets[⍳xl.Sheets.Count].Name xl.Sheets[⊂sheet].Activate :Else ('Sheet "',sheet,'" does not exist in book "',xl.ActiveWorkbook.Name,'"')⎕SIGNAL 500 :EndIf :ElseIf 1=⍴sheet :If sheet∊⍳xl.Sheets.Count xl.Sheets[⊃sheet].Activate :Else ('Cannot activate sheet "',(⍕sheet),'" in book "',xl.ActiveWorkbook.Name,'"')⎕SIGNAL 500 :EndIf :Else 'Sheet specification is invalid'⎕SIGNAL 500 :EndIf :EndIf ⍝write the data to the sheet using `cell as the top left corner: :If 0=⍴cell cell←xl.ActiveCell.(Row Column) :ElseIf ':'∊cell 'The cell range specification is invalid (specify a single cell)'⎕SIGNAL 500 :ElseIf (((⎕DR cell)∊11 83 163)∧(2≠⍴cell))∨(326=⎕DR cell) 'The cell range specification is invalid'⎕SIGNAL 500 :EndIf :Trap 11 data write cell :Else 'Cannot write data'⎕SIGNAL 500 :EndTrap ⍝clean up: conditionally close workbook and/or excel ⍝close and/or save the workbook wbn←xl.ActiveWorkbook.Name :If newFile=0 ⍝using existing workbook :If ∨/'sc'∊flags ⍝close implies save :If ':'∊xl.ActiveWorkbook.FullName :If xl.ActiveWorkbook.ReadOnly=1 ('workbook "',wbn,'" is Read Only and cannot be saved')⎕SIGNAL 500 :Else :Trap 11 xl.ActiveWorkbook.Save :Else 'Workbook "',wbn,'" cannot be saved"'⎕SIGNAL 500 :EndTrap :EndIf :Else ⍝lack of ':' in FullName implies unsaved workbook ('Workbook "',wbn,'" not saved; please use "saveas" to save it')⎕SIGNAL 500 :EndIf :If 'c'∊flags xl.ActiveWorkbook.Close'' :EndIf :EndIf :ElseIf newFile=1 ⍝book created, to be saved as `wbk :Trap 11 xl.ActiveWorkbook.SaveAs⊂wbk :Else ('Cannot save ',wbn,' as ',wbk)⎕SIGNAL 500 :EndTrap :If 'c'∊flags xl.ActiveWorkbook.Close'' :EndIf :Else ⍝newFile=2; file name is "Bookn" so no save or close :EndIf ⍝end or close Excel :If 'q'∊flags quitexcel'' :ElseIf 'e'∊flags endexcel'' :EndIf ∇ :EndNamespace