Read Excel File from ADS

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

Hi Freddy Barkhuizen,

I tried your above script, but when getting the folder I get "null": 

var fldADS = repADS.getFolder(sADSFolder);

Could you advise what is wrong?

Best Regards,