Hello @Anonymous
Your requirement #1 is a bit difficult because there is no such preset action, while #2 is easy.
If you set [Retrieve] as "All available values from worksheet" in action "Read from Excel worksheet", you will get Datatable which having basically the same layout as Excel worksheet.
For example, my source Excel worksheet has data in between B3 and F6 as below:
The result of Datatable as below if you use "All available values from worksheet" option to read from Excel:
So far, you can retreive properties %ExcelData.RowsCount% and %ExcelData.Columns.Count% both get 6 in the example above.
To identify the first cell with the data (which isn't empty), you have to create a nested For Each loop. One for row direction and one for column direction. As a result of loops below, you will get variable %MinDataRowIndex% = 3 and %MinDataColumnIndex% = 2 in this example.
Then you can convert from row index and column index to cell address by using the ADDRESS() function in Excel formulas.
ADDRESS function (microsoft.com)
BEFORE:
AFTER:
You can copy & paste the example code below into your PAD Flow Designer.
System.TerminateProcessByName ProcessName: $'''EXCEL'''
Excel.LaunchAndOpen Path: $'''C:\\Users\\User01\\Documents\\Customers.xlsx''' Visible: False ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
Excel.Close Instance: ExcelInstance
# Get address of the first cell having data (is not empty)
SET RowIndex TO 1
SET ColumnIndex TO 1
SET MaxDataRowIndex TO ExcelData.RowsCount
SET MaxDataColumnIndex TO ExcelData.Columns.Count
LOOP FOREACH CurrentDatarow IN ExcelData
LOOP FOREACH CurrentItem IN CurrentDatarow
IF IsNotEmpty(CurrentItem) THEN
SET MinDataRowIndex TO RowIndex
SET MinDataColumnIndex TO ColumnIndex
EXIT LOOP
END
Variables.IncreaseVariable Value: ColumnIndex IncrementValue: 1 IncreasedValue=> ColumnIndex
END
IF (MinDataRowIndex <> 0 AND MinDataColumnIndex <> 0) = $'''TRUE''' THEN
EXIT LOOP
END
Variables.IncreaseVariable Value: RowIndex IncrementValue: 1 IncreasedValue=> RowIndex
SET ColumnIndex TO 1
END
Display.ShowMessage Message: $'''MIN : [%MinDataRowIndex%][%MinDataColumnIndex%]
MAX : [%MaxDataRowIndex%][%MaxDataColumnIndex%]''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
# Get cell address using ADDRESS() formula in Excel
Excel.Launch Visible: False LoadAddInsAndMacros: False Instance=> ExcelInstance
Excel.WriteCell Instance: ExcelInstance Value: $'''=ADDRESS(%MinDataRowIndex%,%MinDataColumnIndex%,4)''' Column: 1 Row: 1
Excel.WriteCell Instance: ExcelInstance Value: $'''=ADDRESS(%MaxDataRowIndex%,%MaxDataColumnIndex%,4)''' Column: 2 Row: 1
Excel.ReadCell Instance: ExcelInstance StartColumn: 1 StartRow: 1 ReadAsText: False CellValue=> MinCellAddress
Excel.ReadCell Instance: ExcelInstance StartColumn: 2 StartRow: 1 ReadAsText: False CellValue=> MaxCellAddress
Excel.Close Instance: ExcelInstance
Display.ShowMessage Message: $'''MIN : %MinCellAddress%
MAX : %MaxCellAddress%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
Thank you.
