NB. Excel OLE demo
NB.
NB. xldemo0 - loads Excel, writes some random data to a new
NB.           workbook and reads data back to J.
NB.
NB. xldemo1 - loads Excel and displays a chart of data from J.
NB.           The processing is mainly OLE function calls from J
NB.
NB. xldemo2 - more complex example - processing mainly in Excel macros.
NB.        ** NB. Before running this, open the DEMOFILE in Excel
NB.        ** and ensure the demo paths are set correctly.

DEMOFILE=: jpath '~system\examples\ole\excel\jdemo.xls'

require 'files numeric trig'
require jpath '~system\examples\ole\excel\xlutil.ijs'

NB. =========================================================
NB. xldemo0 - loads Excel, writes some random data to a new
NB.           workbook and reads data back to J.
xldemo0=: 3 : 0

NB. open excel OLE automation object:
xlopen''

NB. add a workbook:
NB. note the 'wb' object used here was defined in xlopen
xlcmd 'wb add'

NB. get the workbook name:
wbname=. xlget 'temp name'

NB. get the worksheets object for this workbook with id 'ws':
xlget 'temp worksheets'
xlid 'ws'

NB. get the worksheet name:
xlget 'ws item 1'
wsname=. xlget 'temp name'

NB. write random table:
NB. note names are not case-sensitive
(>:?5 3$10) xlwriter wbname,' ',wsname,' 2 1'

NB. read data back:
dat=. xlreadr wbname,' ',wsname,' 2 1 5 3'
msg=. 'data read back from Excel:',,LF,.":dat
msg=. msg,LF,LF,'to show the spreadsheet: ',LF,'   xlshow 1'
msg=. msg,LF,LF,'to quit the spreadsheet: ',LF,'   xlquit '''''
)

NB. =========================================================
NB. xldemo1    - run demo
xldemo1=: 3 : 0

NB. open excel OLE automation object:
xlopen''

NB. add a workbook:
NB. note the 'wb' object used here was defined in xldemo
xlcmd 'wb add'

NB. get the workbook name:
wbname=. xlget 'temp name'

NB. get the worksheets object for this workbook with id 'ws':
xlget 'temp worksheets'
xlid 'ws'

NB. set the item and change its name to JData:
NB. the alternate statement handles Excel97 changes:
xlcmd :: xlget 'ws item 1'

xlid 'wj'
xlset 'wj name JData'

NB. write random table:
NB. note names are not case-sensitive
(>:?.5 3$10) xlwriter wbname,' jdata 1 1'

NB. now get the charts object, with id 'wc':
xlget 'base charts'
xlid 'wc'

NB. add a chart sheet with id 'c1', named JChart:
xlcmd 'wc add'
xlid 'c1'
xlset 'c1 name JChart'

NB. set chart type (vertical bars)
xlset 'c1 type 3'

NB. set hastitle property on, and give it a title:
xlset 'c1 hastitle 1'
xlget 'c1 charttitle'
xlset 'temp text *J Data'

NB. size chart with window:
xlset 'c1 sizewithwindow 1'

NB. set chart data:
NB. xlsetchart 'jchart jdata!a1:z24'
xlsetchart 'jchart jdata!a1:c5'

NB. turn off grid lines:
xlget 'c1 axes'
xlcmd 'temp item 2'
xlset'temp hasmajorgridlines 0'

NB. turn off toolbars:
xlget 'base toolbars'
xlcmd :: xlget 'temp item 1'
xlset 'temp visible 0'

NB. set full screen display:
xlset 'base displayfullscreen 1'

NB. show it:
NB. xlshow 1 turns on, xlshow 0 turns off
xlshow 1

)

NB. =========================================================
NB. xldemo2   - run demo
NB. xlview    - view new data (run xldemo2 first)

DAT0=: <.100*sin */~ steps 0 3 30
DAT1=: <.100*sin */~ steps _2 2 30
DAT2=: <.100*(sin (% + 0: = ]) ]) (+/&.*:)/~ |steps _8 8 30

xldemo2=: 3 : 0

NB.  data to be charted:
(xlfmt DAT0) fwrite jpath '~temp\xl.txt'

xlopen''
xlcmd 'wb open ',DEMOFILE
xlget 'base charts'
xlcmd :: xlget 'temp item 1'
xlcmd 'temp select'
xlset 'base visible 0'
xlset 'base displayfullscreen 1'
xlcmd 'base run showchart'
xlshow 1
)

NB. =========================================================
NB. try xlview with DAT0 (original data), DAT1, DAT2
NB. view data
xlview=: 3 : 0
(xlfmt ,y.) fwrite jpath '~temp\xl.txt'
xlcmd 'base run showchart'
)
