Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
72:custom_reports_accessing_data_reading_msexcel [2019/02/27 18:26] – created ext-bkkr | 72:custom_reports_accessing_data_reading_msexcel [2019/02/27 19:02] – ext-bkkr | ||
---|---|---|---|
Line 1: | Line 1: | ||
====== Reading MS Excel Files ====== | ====== 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. | ||
+ | |||
+ | ^Name^Type| | ||
+ | |A|Integer| | ||
+ | |B|Date| | ||
+ | |C|String| | ||
+ | |||
+ | 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 [[[Software Project: | ||
+ | |||
+ | 6. Click on the Data Set on **Preview Results** | ||
+ | |||
+ | **Exampe MS Excel Extraction Script** | ||
+ | < | ||
+ | function dumpValues(file) { | ||
+ | if (file !=null) { | ||
+ | var wb = new WorkbookFactory.create(file); | ||
+ | // var sheet = wb.getSheetAt(0); | ||
+ | var sheet = wb.getSheet(" | ||
+ | for (myrow = 1; !isCellEmpty(sheet, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | } | ||
+ | } | ||
+ | | ||
+ | } | ||
+ | var ArtifactName = "Excel Metric Example"; | ||
+ | var ArtifactInstanceName = " | ||
+ | var process = | ||
+ | | ||
+ | if (process.length !=0 ) { | ||
+ | var artifacts = process[0].getEntities(" | ||
+ | for each (artifact in artifacts) { | ||
+ | if (artifact.getProperty(" | ||
+ | var artifacts_instances = | ||
+ | | ||
+ | for each (artifacts_instance in artifacts_instances) { | ||
+ | if (artifacts_instance.getProperty(" | ||
+ | | ||
+ | var filestream = artifacts_instance.getProperty(" | ||
+ | | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | ==== Upgrade to Read Microsoft Excel 2007/2010 (xlsx) files ==== | ||
+ | |||
+ | 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 | ||
+ | |||
+ | < | ||
+ | < | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | ... | ||
+ | | ||
+ | ... | ||
+ | "/> | ||
+ | </ | ||
+ | |||
+ | 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 | ||
+ | </ | ||
+ | |||
+ | \\ | ||