Demonstrations of excel automation
Launch jeforth.3hta (run 3hta.bat or double click jeforth.hta directly) and run the below command line:
include excel.f
That includes the excel.f module and it gets the excel application object ready to work for you. Observe an excel instance is highlighted or blinking on your computer. Please use that one, in case there were multiple excel instances running on your computer.
Now focus a cell on the excel active worksheet. jeforth.3hta can access that cell directly. Try:
cell@ .
to read the value of the active cell. Try:
1234 cell!
to write 1234 into the active cell.
Prepare a column of numbers like this:
We are going to add 1 to all of them, to see how excel.f module does that.
Try this command line on jeforth.3hta:
cell@ 1+ 1 0 offset :: value=pop()
You get the result to the right of the cell you worked on. 1 0 offset is to get the cell object offset 1 cell to the right of the active cell (the 8727) and offset 0 cell down to it. To the 8727 cell (B3), 1 1 offset is C4, 2 1 offset is D4, and -1 -1 offset is A2. So 1 0 offset, in this example, gets the C3 cell object and leaves it at the TOS (top of the data stack) of the jeforth VM. The following :: value=pop() is like pop().value=pop() while the first pop() is the cell object of C3 and the 2'nd pop() is the number 8728.
Result of the example is like this:
Now do the same thing to the entire column.
Try this command line on jeforth.3hta:
[begin] @?stop cell@ 1+ 1 0 offset :: value=pop() down 10 nap [again]
Where [begin] ... [again] is an interpret state infinit loop of jeforth. When running a loop it's good to always have some sleeps to allow JavaScript host to do its jobs. That's what the 10 nap is for to have 10 milliseconds sleep for every loop, although it may not be necessary in this small case.
@?stop checkes the active cell if it's empty then stop jeforth from going on executing the command line (the Forth TIB). And finally the down simply moves the active cell down a row.
This is the result:
There's another way, even better in flexibility, to work on multiple cells.
Like the figure shows, please select not all but only some of the cells. Then try this command line on jeforth.3hta:
0 [begin] i?stop cell@ 1+ 1 0 offset :: value=pop() 10 nap [again]
Where i?stop places the active cell at the position indicated by the given number at TOS, which is the 0 befroe [begin] at the beginning of the command line. If the given number is bigger than the count of the selected region then it stops the TIB command line. After placing the active cell, it increases the number at TOS by 1 so next loop will proceed to the next cell of the selected region. In this example, the down command is not used because i?stop already does its job.
This is the result:
-- END --
- H.C. Chen hcchen5600@gmail.com
- jeforth.3we http://github.com/hcchengithub/jeforth.3we
- FigTaiwan http://www.figtaiwan.org/