Reading MS Excel Files

The used Java library POI Version 3.8 is able to read MS Excel files up to Version 2010.

1. Create a Report with a Stages Data Source

2. Create an Artifact and create and Artifact Instance (File) in Stages by uploading a MS Excel file.

3. Create a Data Set with the following result columns.

4. Copy the example and the POI Javascript wrapper function into the Data Collection script editor of the Data Set.

5. Include a Current metric like metric]Dashboard

6. Click on the Data Set on Preview Results

Exampel MS Excel Extraction Script

function dumpValues(file) {
 if (file !=null) {
 var wb = new WorkbookFactory.create(file);
 var sheet = wb.getSheet("Tabelle1");
 for (myrow = 1; !isCellEmpty(sheet, myrow, 0); myrow++) {
 dataset.setColumnValue("A",getNumericValue(sheet,myrow,0));
 dataset.setColumnValue("B",getDateValue(sheet,myrow,1));
 dataset.setColumnValue("C",getStringValue(sheet,myrow,2));
 dataset.storeResultRow();
 }
 }
 return;
}
var ArtifactName = "Excel Metric Example";
var ArtifactInstanceName = "Excel_Metric_example.xlsx";
var process = stages.getCurrentProcess();
var artifacts = process.getEntities("containsArtifact@SYSTEM");
for each (artifact in artifacts) {
 if (artifact.getProperty("DisplayName") == ArtifactName) {
 var artifacts_instances =
 artifact.getEntities("containsInstance@SYSTEM");
 for each (artifacts_instance in artifacts_instances) {
 if (artifacts_instance.getProperty("LogicalName") ==
 ArtifactInstanceName) {
 var filestream = artifacts_instance.getProperty("FileStream");
 dumpValues(filestream);
 }
 }
}
}

If you want to read Microsoft Excel 2007/2010 files from your existing dataset scripts and you have used the POI library directly in your dataset scripts to access these files, you have to perform the following steps to upgrade your scripts:

1. Add the following highlighted Java package to your PKitConfig.xml

<property name="metrics.dataCollector.importPackages"
 value="java.util,
 java.lang,
 java.text,
 java.io,
 java.math,
 java.sql,
 java.net,
 ...
 org.apache.poi.ss.usermodel
 ...
 "/>

2. Replace the following statement in your Data Set script.

var wb = new HSSFWorkbook(file);

with

var wb = new WorkbookFactory.create(file);

3. Replace each occurrence of the following statement in your Data Set script

HSSFCell

with

Cell