Hi all,
I am writing a script to maintain historical data which I need to use in a dashboard (e.g. active users over a period of time). I've managed to build the code for generating the daily data and storing the data in an excel sheet in ADS. My issue now is to expand the script to (A) read the existing excel sheet from ADS, (B) append the new data to existing sheet (daily) and then (C) store the sheet again in ADS. Can anybody help please with steps A & B. I can't seem to find a way to do that.
Thanks.
Hi Nicola,
Create a folder called "Test" in your ADS and import the attached spreadsheet called "test.xlsx".
Import the attached script and run it from any DB context.
The script will read the spreadsheet from ADS, insert a new row with incrementing number and write back the updated spreadsheet to ADS.
var sADSFolder = "Test";
var sADSFile = "test.xlsx";
var cADS = Context.getComponent("ADS");
var repADS = cADS.getADSRepository("portal");
var fldADS = repADS.getFolder(sADSFolder);
var docExcel = repADS.getDocument(fldADS, null, sADSFile);
var isExcel = docExcel.getDocumentContent();
var baExcel = Packages.org.apache.commons.io.IOUtils.toByteArray(isExcel);
var wbExcel = Context.createExcelWorkbook(sADSFile, baExcel);
var shtExcel = wbExcel.getSheetAt(0);
var nLastRowNo = shtExcel.getLastFilledRowNum();
var cellUpdate = shtExcel.cell(nLastRowNo+1,0);
cellUpdate.setCellValue(Number(shtExcel.getCell(nLastRowNo,0).getCellValue())+1);
wbExcel.write();
var fExcel = Context.getFile(sADSFile, Constants.LOCATION_OUTPUT);
var metaDoc = repADS.createDocumentMetaInfo("", sADSFile, "");
docExcel = repADS.createAndOverwriteExistingDocument(fldADS, metaDoc, new Packages.java.io.ByteArrayInputStream(fExcel));
if(docExcel!=null){
Dialogs.MsgBox("Updated");
}
Regards,
Freddy