nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Creating an Dynamic DataSet from ExcelSheet

domurada

Hello Guys,
 
 
I am pretty new to BIRT and my Job is to create some fancy Graphics/Charts from our Data which is saved in an ExcelSheet looking like this (It is saved on a Stages Webserver - it doesnt really matter , just for complete Information):

They are listed in Groups by Rows and I have to get each Group and do Graphics out of them.
Currently, I am simply trying to read each Group manual where it starts and till it ends.
 
Script :

//_______________________________________________________________________________________________ 
//Functions for value storage 
//_______________________________________________________________________________________________ 
function dumpValues(file) { 
 if (file !=null) { 
 var wb = new WorkbookFactory.create(file); // For XLSX / XLS Files 
 // var sheet = wb.getSheetAt(0); 
 var sheet = wb.getSheet("Sheet"); // Name of targeted sheet 
 var myRow = 1; 
 var myCol = 1; 
 var ProcessRow = 1; 
 // Getting filled columns 
 for (myCol = 1; cellValue(sheet,myRow,myCol) != "....." || !isCellEmpty(sheet,ProcessRow,myCol); myCol++){ 
 if(!isCellEmpty(sheet,ProcessRow,myCol) ){ 
 dataset.setColumnValue("debug",getStringValue(sheet,myRow,myCol).split("\\.")[0]); 
 
 dataset.setColumnValue("Date",getDateValue(sheet,myRow,myCol)); 
 dataset.setColumnValue("A#1",cellValue(sheet,ProcessRow,myCol)); dataset.setColumnValue("A#2",cellValue(sheet,ProcessRow+1,myCol)); 
 dataset.setColumnValue("A#3",cellValue(sheet,ProcessRow+1,myCol)); 
 
 dataset.storeResultRow(); 
 } 
 } 
 } 
 return; 
} 
// Stuff I need to read the XLSM File from Stages
<div>//______________________________________________________________________________</div>
<div>// Main</div>
<div>//______________________________________________________________________________</div>
<div>var ArtifactName = "DefaultMetrik"; // Name of "Work Product" ( Artifact Name )</div>
<div>var ArtifactInstanceName = "File.xlsm"; // Name of File in Work Products Artifact</div>
<div>var process = pkit.getCurrentProject().getEntities("containsProcess@SYSTEM");</div>
<div>if (process.length !=0 ) {</div>
<div>var artifacts = process[0].getEntities("containsArtifact@SYSTEM"); // Choose Process</div>
<div>if (artifacts[0].getProperty("DisplayName") == ArtifactName) {</div>
<div>var artifacts_instances = artifacts[0].getEntities("containsInstance@SYSTEM");</div>
<div> </div>
<div>var filestream = artifacts_instances[artifacts_instances.length-1].getProperty("FileStream");</div>
<div>dumpValues(filestream); </div>
<div> </div>
<div> </div>
<div>}</div>
<div>}</div>


The Functions used here are to read from an XLXS File

//_______________________________________________________________________________________________ 
// Functions for Excel sheets 
//_______________________________________________________________________________________________ 
function getNumericValue(sheet,row, col) { 
 Row = sheet.getRow(row); 
 if (Row != null) { 
 Cell = Row.getCell(col); 
 if (Cell != null) { 
 return(Cell.getNumericCellValue()); 
 } 
 } 
 return ""; 
} 
function getStringValue(sheet,row, col) { 
 Row = sheet.getRow(row); 
 Cell = Row.getCell(col); 
 if (Cell != null) { 
 if (Cell.getCellType() == Cell.CELL_TYPE_STRING) { 
 return Cell.getStringCellValue(); 
 } 
 } 
 return ""; 
} 
function getDateValue(sheet,row, col) { 
 Row = sheet.getRow(row); 
 Cell = Row.getCell(col); 
 if (Cell != null) { 
 if ((Cell.getCellType() == Cell.CELL_TYPE_NUMERIC) 
 || (Cell.getCellType() == Cell.CELL_TYPE_FORMULA)) { 
 return Cell.getDateCellValue(); 
 }else 
 if(Cell.getCellType()== Cell.CELL_TYPE_STRING){ 
 var date = new Date(); 
 var day = Cell.getStringCellValue().split("\\.")[0]; 
 var month = Cell.getStringCellValue().split("\\.")[1]; 
 var year = Cell.getStringCellValue().split("\\.")[2]; 
 date = new Date(year,month-1,day); 
 return date; 
 
 } 
 } 
 return ""; 
} 
/* taken vom <a data-ipb='nomediaparse' href='http://trephine.org/t/index.php?title=Extracting_Excel_data_with_Apache_POI_and_Rhino'>http://trephine.org/t/index.php?title=Extracting_Excel_data_with_Apache_POI_and_Rhino</a> */ 
function cellValue(sheet, CellRow, CellCol) { 
 row = sheet.getRow(CellRow); 
 if (row != null) { 
 cell = row.getCell(CellCol); 
 if (cell != null) { 
 var t = cell.cellType; 
 if (t == Cell.CELL_TYPE_NUMERIC) { 
 if (DateUtil.isCellDateFormatted(cell)) return new Date(cell.dateCellValue.time); 
 else return cell.numericCellValue + 0.0; 
 } 
 if (t == Cell.CELL_TYPE_STRING) return cell.stringCellValue + ''; 
 if (t == Cell.CELL_TYPE_FORMULA) { 
 try { 
 if (DateUtil.isCellDateFormatted(cell)) return new Date(cell.dateCellValue.time); 
 else return cell.numericCellValue + 0.0; 
 } catch(err) { } 
 try { return cell.stringCellValue + ''; } catch(err) { } 
 try { return cell.numericCellValue + 0.0; } catch(err) { } 
 try { return !!cell.booleanCellValue; } catch(err) { } 
 try { return cell.errorCellValue + 0; } catch(err) { } 
 return ''; 
 } 
 if (t == Cell.CELL_TYPE_BLANK) return ''; 
 if (t == Cell.CELL_TYPE_BOOLEAN) return !!cell.booleanCellValue; 
 if (t == Cell.CELL_TYPE_ERROR) return cell.errorCellValue + 0; 
 } 
 } 
 return null; 
} 
function isCellEmpty(sheet,row, col) { 
 Row = sheet.getRow(row); 
 if (Row != null) { 
 Cell = Row.getCell(col); 
 if (Cell != null) { 
 if (Cell.getCellType() != Cell.CELL_TYPE_BLANK) { 
 return false; 
 } 
 } 
 } 
 return true; 
}

This worked quite a while thou the Original File has about 600 Rows +- a bit. 
I builded myself a Script, that creates a Report with the needed DataSet's and JavaScritp. It works but changeing it (I am sorry) sucks ****. 
Now the File length has changed , and I got sick of changeing my Script. 
Question now : 
Is it possible, to read the Excel File, dynamicly build DataSets ( or just Columns ) , save the Data and done ?

Find more posts tagged with

Comments

kclark

Can you post an example report with some dummy data that we can look at? I think what you're trying to do might be able to be achieved using a scripted data set or building a simple POJO for your reports. Can you provide a couple examples of data and how it might change and I'll write up an example if I can get it working.

domurada

Sure thing.
TestData.xlsx is the old Data.
On TestDataChanged.xlsx you see the Yellow marked addings. These are mostly random and can add whenever or wherever they want new Values.
 
What I want to have access to would be, a Grouped List like I can choose from A->A#2 or from C->C#7 and of course the Date.

Also, i could be that the SheetName changes.
 
Would be nice and Thanks.