OnExcel Instruction

The powerful OnExcel instruction

The instruction OnExcel let you process a datatable in an Excel sheet very easily.

Data table structure

A data table is organized in two part: a header line and below are data rows.

      A       B
   +------+-------+
1  |  Id  | Value |
2  |   1  |   12  |
3  |   2  |       | 
4  |   3  |  234  |
5  |   4  |       | 
6  |   5  |  631  |

In main cases, the header take place in one row, the first one.

Build the script

First you have to define the Excel file you want to process.

The first sheet of the Excel file is used by default.

OnExcel "mydata.xlsx"

Then the ForEachRow/Next will scan automatically each datarow of the datatable, one by one.

The first row is considered to be the header of the datatable, so the first datarow to process is the second one, just below the header row.

ForEachRow
 do something on each row
Next

In the instruction ForEachRow you can put If-Then instruction to check and modify values as expected.

If A.Cell > 10 Then A.Cell= 10

The full script:

Save it in a text file, form example: script.lxrw

OnExcel "mydata.xlsx"
  ForEachRow
    If A.Cell > 10 Then A.Cell= 10
  Next
End OnExcel

Tune the script

Select files

If the script has to process all excel files, you can do it easily by using the instruction SelectFiles.

# select all excel files
files= SelectFiles("*.xlsx")

# process all datarows of all selected excel files
OnExcel files
  ForEachRow
    If A.Cell>10 Then A.Cell=10
  Next
End OnExcel

Modify the first data row

If you want to modify the first row of the datatable from row # 2 to another one, use the instruction FirstRow.

# datarow start at row#3
OnExcel "data.xlsx"
  FirstRow 3
  ForEachRow
   If A.Cell <= 10 Then A.Cell=12
  Next
End OnExcel

# define the FirstRow value using a variable
r=3

OnExcel "data.xlsx"
  FirstRow r
  ForEachRow
    If A.Cell <= 10 Then A.Cell=12
  Next
End OnExcel