hi,
here is some example how to read excel file and calculate sum of id’s
TEST CASE
import com.kms.katalon.core.util.KeywordUtil
import org.apache.commons.collections4.MultiValuedMap;
import org.apache.commons.collections4.multimap.ArrayListValuedHashMap;
KeywordUtil log = new KeywordUtil()
List<String> excelValues = new ArrayList<String>();
List<String> headerValues = new ArrayList<String>();
String path = System.getProperty("user.dir")+"\\ExcelFiles\\SmoobuBookings-2020-sheet2.xlsx";
println path
List <String> idArray = new ArrayList<>()
List <String> amountArray = new ArrayList<>()
int counter = 1
int counter2 = 1
int sheet = 1
def count = CustomKeywords.'readExcelRows.ReadRows.getCountOfRows'(path, sheet)
excelValues = CustomKeywords.'readExcelRows.ReadRows.readExcelRows'(1, count+1, path, sheet)
for (String temp : excelValues) {
counter++
if (counter % 2 != 0){
amountArray.add(temp)
counter = 1
}
else{
idArray.add(temp)
counter2++
counter2 = 1
}
}
//combine two list and get keys/value from map without duplicates
Map<String,String> map = combineListsIntoOrderedMap2 (idArray, amountArray);
//create multimap where all duplicate keys added
MultiValuedMap<String,String> map0 = combineListsIntoOrderedMap3 (idArray, amountArray);
int ind = 0
List<String> list = new ArrayList<>()
//get count of keys
for (Map.Entry<String,String> entry : map.entrySet()){
list.add(entry.getKey())
println("keys: "+entry.getKey())
}
//create list of lists to add all values by key
ArrayList[] lis = new ArrayList[list.size()];
for(int i=0; i<list.size(); i++)
{
lis[i] = new ArrayList<String>();
lis[i] = (Collection<String>) map0.get(map.keySet().toArray()[i])
}
println("values in excel file "+lis)
ArrayList[] doub = new ArrayList[list.size()]; //size 3
List<Double> sum = new ArrayList<>()
for (int i = 0; i < list.size(); i++ ){
doub[i] = new ArrayList<Double>();
for (int x=0;x<lis[i].size();x++){
doub[i] += Double.parseDouble(lis[i].get(x))
}
}
def val = 0.0
//sum list values
for (int x = 0; x < doub.size(); x++){
val = 0.0
for (int i = 0; i < doub[x].size(); i++){
val += doub[x][i]
}
sum.add("key : "+list.get(x)+" sum "+ val)
}
println("sum of each keys: "+sum)
public <K, V> Map<K, V> combineListsIntoOrderedMap2 (Iterable<K> keys, Iterable<V> values) {
Map<K, V> map = new LinkedHashMap<>();
Iterator<V> vit = values.iterator();
for (K k: keys) {
if (!vit.hasNext())
throw new IllegalArgumentException ("Less values than keys.");
map.put(k, vit.next());
}
return map;
}
MultiValuedMap<String,String> combineListsIntoOrderedMap3 (List<String> keys, List<String> values) {
if (keys.size() != values.size())
throw new IllegalArgumentException ("Cannot combine lists with dissimilar sizes");
MultiValuedMap<String, String> map = new ArrayListValuedHashMap<String, String>();
int index = 0
for (String s : keys){
map.put(keys.get(index), values.get(index));
index++
}
return map;
}
KEYWORD
package readExcelRows
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import com.kms.katalon.core.annotation.Keyword
public class ReadRows {
//public String SAMPLE_XLSX_FILE_PATH = "C:\\Users\\fitim\\Desktop\\data\\readExcelSheet.xls";
Workbook workbook
@Keyword
public int getCountOfRows(String path, int sheetNum){
// Creating a Workbook from an Excel file (.xls or .xlsx)
workbook = WorkbookFactory.create(new File(path));
//Sheet sheet = workbook.getSheet("sheet0");
Sheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum()
int used = sheet.getRow(0).getPhysicalNumberOfCells();
println "rows used "+used
int last = sheet.getRow(0).getLastCellNum();
println "rows last "+last
return lastRowNum;
}
@Keyword
public List<String> readExcelRows(int start, int end, String path, int sheetNum) throws IOException, InvalidFormatException{
List<String> excelValues = new ArrayList<String>();
// Creating a Workbook from an Excel file (.xls or .xlsx)
workbook = WorkbookFactory.create(new File(path));
// Retrieving the number of sheets in the Workbook
// Getting the Sheet at index
Sheet sheet = workbook.getSheetAt(sheetNum);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
boolean last = false
boolean actualValue;
int lastRowNum = sheet.getLastRowNum()
lastRowNum++
if (start == lastRowNum){
last = true
}
int startRow = 0
int endRow = 0;
for (Row row: sheet) {
for(Cell cell: row) {
if (startRow >= start && endRow < end && last==false){
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
excelValues.add("**no value**");
}else{
String cellValue = dataFormatter.formatCellValue(cell);
//System.out.print(cellValue + ",");
excelValues.add(cellValue);
//println("********************* Is there added anything ****************")
}
}
if (last){
String cellValue = dataFormatter.formatCellValue(cell);
//System.out.print("last "+cellValue + ",");
excelValues.add(cellValue);
//println("********************* Is there added anything ****************")
}
//}
}
startRow++;
endRow++;
}
return excelValues;
}
}
result
keys: 101
keys: 100
keys: 102
values in excel file [[200, 201, 202, 203, 204], [150, 150, 151, 152], [300, 301, 302, 303]]
sum of each keys: [key : 101 sum 1010.0, key : 100 sum 603.0, key : 102 sum 1206.0]
