Script samples

Here are some samples of script.

More samples can be found in tests: https://github.com/Pierlam/Lexerow/tree/main/2-Tests/Lexerow.Core.Tests/15-Scripts

# basic case, check int value, set a new int value
OnExcel "data.xlsx"
  ForEachRow
   If A.Cell>10 Then A.Cell=10
  Next
End OnExcel

# select many excel files
files= SelectFiles("*.xlsx")
OnExcel files
  ForEachRow
    If A.Cell>10 Then A.Cell=10
  Next
End OnExcel

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

# define here the FirstRow value
r=3
OnExcel "data.xlsx"
  FirstRow r
  ForEachRow
    If A.Cell <= 10 Then A.Cell=12
  Next
End OnExcel

# check double value
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell > 12.5 Then A.Cell=13.1
  Next
End OnExcel

# work with negative values
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell< -7 Then A.Cell=-7
  Next
End OnExcel

# By default string comparison is case insensitive
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell="Hello" Then A.Cell="Bonjour"
  Next
End OnExcel

# By default string comparison is case insensitive
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <>"bye" Then A.Cell="chao"
  Next
End OnExcel

# to force string comparison to be case sensitive
$StrCompareCaseSensitive=true

OnExcel "data.xlsx"
  ForEachRow
    If A.Cell="Hello" Then A.Cell="Bonjour"
  Next
End OnExcel

# check date value, set a new date value
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <= Date(2023,11,14) Then A.Cell=Date(2025,3, 12)
  Next
End OnExcel

# check date value, using a variable
a=Date(2025,12, 30)
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <= Date(2019,11,14) Then A.Cell=a
  Next
End OnExcel

# change the display format of the date of cells modified
$DateFormat="yyyy-mm-dd"

OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <= Date(2019,11,14) Then A.Cell=Date(2025,12, 30)
  Next
End OnExcel

# check if cell are blank, if cell are null, return true 
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell=blank Then A.Cell=0
  Next
End OnExcel

# check if cell are null, (not blank)
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell=null Then A.Cell=0
  Next
End OnExcel

# If condition more complex
OnExcel "data.xlsx"
  ForEachRow
     If A.Cell >10 And B.Cell< 20 And C.Cell=17 Then A.Cell=137
   Next
End OnExcel

# If condition more complex
OnExcel "data.xlsx"
  ForEachRow
     If (A.Cell >10 And B.Cell< 20) Or C.Cell=17 Then A.Cell=137
   Next
End OnExcel

# many then instructions
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <= 10 Then 
      A.Cell=12.3
      B.Cell=Blank
      C.Cell="Y"
    End If
  Next
End OnExcel

# Many ifThen instructions:
OnExcel "data.xlsx"
  ForEachRow
    If A.Cell <= 8 Then A.Cell=12
    If B.Cell="X" Then B.Cell=Blank
    If C.Cell=9.55 Then C.Cell=10
  Next
End OnExcel

# copy rows to another excel file, at it after the last row
file=SelectFiles("data.xlsx")

# create a result excel file to pu selected from the source excel file
fileRes=CreateExcel("result.xlsx")
CopyHeader(file, fileRes)

OnExcel file
  ForEachRow
    # push the current row to the result excel file
    If C.Cell >= 10 Then CopyRow(fileRes)
  Next
End OnExcel