Read and Write Data from a Downloaded XLSX File
Attaching the script along with the Screenshot to follow the steps
We need 3 different Service suite scripts
-
A common JS script
-
Filename JS: Where we are providing the Sheet name, Path of the repository where the file is being saved
-
Write JS: Here as well, we need to define the repository name and file path after making the write action, We also need to add the controller URL and AIQ username
and Password
Test Script
Sample Script: WriteExcel.ds
-
Testfile name: At step number 45, make sure to add the repository path and the repository name which the user cloned in the AIQ
-
After clicking on download, add a step store in locker and provide the sheet name, which is there in the download file
-
In Ds script at step 55 and 61, make sure to give the exact header name for the targetted column header
Write JS
Write JS: Here as well, we need to define the repository name and file path after making the write action, We also need to add the controller URL and AIQ username
and Password
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
function writeexcel(row, col, value)
{
var $excel= readExcel(filepathval);
var $sheetname = locker.get("sheet");
log($sheetname);
//var $sheetname="Sheet1";
var $sheet = $excel.getWorkbook().getSheet($sheetname); // Get sheet as an object HSSFSheet
$excel.getWorkbook().setActiveSheet($excel.getWorkbook().getSheetIndex($sheetname));//Change active sheet to the one specified
var $headers = $sheet.getRow(0);
var $lastExistingColumn = $headers.getLastCellNum(); // Get number of colum
locker.put("columnlength",$lastExistingColumn);
log($excel);
$excel.setValueAt(row, col, value);
writeExcel($excel, filepathval);
}
var filepathval=locker.get("filenameval");
var rowval=locker.get("rownum");
var colval=locker.get("colnum");
var actualval=locker.get("excelval");
log("servicesuite "+filepathval);
writeexcel(rowval,colval,actualval);
var $boundary = "--AIQ-DS-BOUNDARY";
var $repositoryName = "Appvance"; // CHANGE FOR YOUR REPO NAME, THIS CONF MUST BE IN THE USER ACCOUNT USED TO LOGIN
var $CONTROLLER = "http://localhost:8080"; // Controller URL
var $username = "appvance"; // username used to login to aiq
var $password = "appvance"; // password used to login to aiq
var $uploadPath = "Customer/VWR/ReadWriteXLS/"; // CHANGE FOR folder in repo where the modified file should be uploaded
include("{ds}/commonFunctions.js");
if (login($CONTROLLER)) {
uploadFile(fileName, fileName);
var $resp= get($CONTROLLER+"/AppvanceServer/rest/admin/logout");
log("logout: "+$resp.getDataString());
};
Write JS. At step 26 and 30, make sure to add the repository name and the repository path, respectively, which the user cloned in the AIQ
Filename JS
Filename JS:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
function columnlen(filepathvalcol)
{
//var filepathvalcol=locker.get("filenameval");
var $sheetname = locker.get("sheet");
log($sheetname);
log("reading excel from "+filepathvalcol);
var $excel= readExcel(filepathvalcol);
//var $sheetname="Sheet1";
var $sheet = $excel.getWorkbook().getSheet($sheetname); // Get sheet as an object HSSFSheet
$excel.getWorkbook().setActiveSheet($excel.getWorkbook().getSheetIndex($sheetname));//Change active sheet to the one specified
var $headers = $sheet.getRow(0);
var $lastExistingColumn = $headers.getLastCellNum(); // Get number of colum
locker.put("columnlength",$lastExistingColumn);
var headersArray = []
for (var col = 0; col < $lastExistingColumn; col++) {
var cell = $headers.getCell(col);
switch (cell.getCellType().toString()) {
case "STRING":
headersArray.push(cell.getStringCellValue());
break;
case "NUMERIC":
headersArray.push(cell.getNumericCellValue());
break;
default:
headersArray.push(cell.toString());
break;
}
}
locker.put("headers", headersArray);
}
var json = locker.get("fileDownloadedList");
// [{"path":"C:\Users\Administrator\TestDesigner\NetworkProxy\execution\Chrome10383680D\EnrollmentSearch_2024022217255349.xls","name":"EnrollmentSearch_2024022217255349.xls","length":4702}]
var filePath = ""+json.getJSONObject(0).getString("path");
var fileName1 = ""+json.getJSONObject(0).getString("name");
var fileName = fileName1.replace(/\s+/g, '_')
locker.put("repofilename",fileName);
locker.put("filenameval",fileName);
filePath = filePath.replace(/\\/g,"/");
// path to your repo
var pathToYourRepo = "REPOSITORY/Appvance/Customer/VWR/ReadWriteXLS/" + fileName; // Give the path pointing to the folder that has the script
log("servicefilename val "+pathToYourRepo);
locker.put("pathToYourRepoval",pathToYourRepo);
log("file saved at: " + pathToYourRepo);
var fileContentAsBytes = java.nio.file.Files.readAllBytes(java.nio.file.Paths.get(filePath));
writeBytes(fileName, fileContentAsBytes);
log(" | downloaded file size: "+fileContentAsBytes.length);
columnlen(fileName);
HEADING
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
function uploadFile(fileName, filePath) {
var data = createMultipartPayload(fileName, filePath);
var call = createPost($CONTROLLER + "/AppvanceServer/rest/file/uploadFile?&commitMessage=Appvance+skip+commit&path=REPOSITORY/" + $repositoryName + "/" + $uploadPath + fileName, data, "multipart/form-data", " boundary=" + $boundary);
var resp = process(call);
log(resp.getDataString())
}
function getSessionCookie() {
var $jsession = getCookieValue($CONTROLLER, "JSESSIONID");
var cookie = "JSESSION=" + $jsession;
locker.put("aiq-cookie", cookie);
addGlobalHeader("cookie", "JSESSION=" + $jsession);
}
function login() {
var $loginForm = formData();
$loginForm.put("username", $username);
$loginForm.put("password", $password);
var $resp = get($CONTROLLER+"/AppvanceServer/rest/admin/isLogged");
if ($resp.getDataString().indexOf("false")) {
var $call = createPost($CONTROLLER + "/AppvanceServer/rest/admin/logginForServices", $loginForm);
var $resp = process($call).getDataString();
if ($resp.indexOf("true") != -1) {
getSessionCookie();
log("Login successful");
return true;
} else {
log("Login was NOT successful: " + $resp)
return false;
}
} else {
getSessionCookie();
}
}
function createMultipartPayload(filename, path) {
var header = '--' + $boundary + '\n' +
'Content-Disposition: form-data; name="file"; filename="' + filename + '"\r\n' +
'Content-Type: application/octet-stream\r\n\r\n';
var headerBytes = new Array(header.length);
for (var i = 0; i < header.length; ++i) {
headerBytes[i] = header.charCodeAt(i);
}
var $data = readBytes(path);
var footer = '\r\n--' + $boundary + "--\r\n";
var footerBytes = new Array(footer.length);
for (var i = 0; i < footer.length; ++i) {
footerBytes[i] = footer.charCodeAt(i);
}
return headerBytes.concat($data).concat(footerBytes);
}
function getLastDownloadedFile(json) {
json.sort((a, b) => b.modifiedTime - a.modifiedTime);
return json[0];
}
Last updated: December 20, 2024
If you have any feedback on this topic, please contact us at documentation@appvance.ai