Child pages
  • How to create a custom export
Skip to end of metadata
Go to start of metadata

Introduction

This article will describe how to create a custom Excel export using Squore Rest Api, Groovy script and POI library.
The use case is "How to export level E files from model projects to an Excel file?"

Notes:

  • Throughout this article, ${SquorePath} represents the absolute path to the Squore installation
  • Documentation can be found in two places:
    • The user guide for the Rest Api:  Squore/?/User Guide (${SquorePath}/api/documentation/api/index.html)
    • The Swagger documentation for the Rest Api: Squore/?/API Documentation (${SquorePath}/api/documentation/api.html)

Prerequisites

Squore

  • Squore (version: [19.0.0,)) installed with Software Analytics projects available (Getting Started: sample projects)
  • Create a custom highlight  (Getting Started: advanced highlights) with the following specification:
    • Select the "Earth" project in the Project Portfolio, create a new Highlight
      • Name: "Get all Level E Files"
      • Target Artefact Types: File (Alias)
      • Columns => Measure: Line Count
      • Additional Filters => Indicator: "Software Analytics" with Valid Levels: "Level E"

Tools to install

Development environment

Configure IntelliJ IDEA Project

  • File/Open/${SquorePath}/configuration


  • File/Project Structure
    • Configure Jdk 8



      Add dependencies: click "+" icon, select "1- Jars or directories", go to the folder and select libraries in ${SquorePath}/addons/lib

Custom Export

  • In existing ${SquorePath}/configuration/exports folder create new folder files_leve_e_excel
  • Create from_en.properties with this two properties FORM.GENERAL.NAME and FORM.GENERAL.DESCR
FORM.GENERAL.NAME  = Get all Level E Files
FORM.GENERAL.DESCR = Selected Model export
<?xml version="1.0" encoding="UTF-8"?>
<tags>
   <exec-phase id="export">
      <exec name="java">
         <!-- To add librairies in classpath -->
         <arg value="${javaClasspath(poi,groovy,jackson)}"/>
         <!--To run Groovy script-->
         <arg value="groovy.lang.GroovyShell" />
         <!-- Groovy script to execute-->
         <arg value="${getToolConfigDir(level_e_to_excel.groovy)}"/>
         <!--Groovy script to load in classpath. Contains utilities used by other Groovy scripts -->
         <arg value="${getSharedAddonsFile(GroovyScriptUtils.groovy)}"/>

         <!--Groovy script file to load in classpath. Contains utilities used by Groovy scripts that read/write to Excel files-->
         <arg value="-excelUtilsScript"/>
         <arg value="${getSharedAddonsFile(ExcelUtils.groovy)}" />

         <!--Groovy script file to load in classpath. Contains utilities used by Groovy scripts that call Squore Api Rest-->
         <arg value="-squoreApiUtils"/>
         <arg value="${getSharedAddonsFile(SquoreApiUtils.groovy)}" />

         <!--Groovy script to load in classpath. Contains utilities used by all export scripts-->
         <arg value="-importScript"/>
         <arg value="${getSharedAddonsFile(exports_utils.groovy)}" />

         <!--Result directory, by default squore-data/sessions/IdSession/export-->
         <arg value="-outputDirectory" />
         <arg value="${outputDirectory}" />

         <!--The identifier of the model that is currently selected in the Project Portfolios -->
         <arg value="-idModel" />
         <arg value="${idModel}"/>

         <!--The identifier of the model group that is currently selected in the Project Portfolios -->
         <arg value="-group" />
         <arg value="${group}"/>

         <!-- The base URL of the Squore server. -->
         <arg value="-serverUrl"/>
         <arg value="${localUrl}"/>

         <!--Is the auto-generated token for on the fly authentification to the API REST -->
         <arg value="-token"/>
         <arg value="${token}"/>

         <!-- Is the optional Excel template used to produce the output Excel file. If you omit this option, make sure you don't use -template ${templatePath} -->
         <arg value="-template"/>
         <arg value="${getConfigFile(template.xlsx)}" />
      </exec>
   </exec-phase>
</tags>
  • Create the level_e_to_excel.groovy file
import com.fasterxml.jackson.annotation.JsonIgnoreProperties
import org.apache.poi.xssf.streaming.SXSSFWorkbook
import org.apache.poi.xssf.usermodel.XSSFWorkbook

class LevelEToExcel {
    /*
    Firs method used when the groovy script is executed
     */
    static void main(String[] args) {

        //Find the script GroovyScriptUtils.groovy defined in form.xml or the first parameter in CLI command
        def commonUtils = getGroovyScriptUtils(args)
        //transforms all parameters into Map<String,String>
        def mapArgs = commonUtils.argsParsing(args, '-')

        def toExcelEngine = new LevelEToExcelEngine()
        //Call the method to generate export
        toExcelEngine.generateExcelFinalFile(commonUtils, mapArgs)
    }

    /*
    Load GroovyScriptUtils.groovy in Classpath and return an instance of object
     */
    private static Object getGroovyScriptUtils(args) {
        def commonSrcipt
        args.each {
            if (it.contains('GroovyScriptUtils.groovy')) {
                commonSrcipt = new GroovyClassLoader().parseClass(it as File).newInstance()
            }
        }
        return commonSrcipt
    }
}

class LevelEToExcelEngine {

    def static IMPORT_SCRIPT = '-importScript'
    def static TEMPLATE = '-template'
    def static EXCEL_UTILS_SCRIPT = '-excelUtilsScript'
    def commonUtil
    def excelUtil
    def squoreApiUtils

    def generateExcelFinalFile(commonUtil, mapArgs) {
        println("[INFO] Start processing Excel !")
        this.commonUtil = commonUtil
        try {

            //Final File name
            def outputName = "highlightsModels.xlsx";
            //The file in ${SquorePath}addons/exports/Shared/exports_utils.groovy will be transformed in Groovy Class
            def exportUtil = commonUtil.getGroovyClass(mapArgs.get(IMPORT_SCRIPT))

            //The file ${SquorePath}addons/shared/ExcelUtils.groovy will be transformed in Groovy Class
            excelUtil = commonUtil.getGroovyClass(mapArgs.get(EXCEL_UTILS_SCRIPT))

            //The file  ${SquorePath}addons/shared/SquoreApiUtils.groovy will be transformed in Groovy Class
            squoreApiUtils = commonUtil.getGroovyClass(mapArgs.get(exportUtil.SQUORE_API_UTILS))

            //The output directory where the data.properties file will be created
            commonUtil.initGlobalData(mapArgs.get(exportUtil.OUTPUT_DIRECTORY));

            //Final file in data.properties
            commonUtil.addGlobalData("filename", outputName, mapArgs.get(exportUtil.OUTPUT_DIRECTORY));

            //Init de output file path and name
            def fnout = mapArgs.get(exportUtil.OUTPUT_DIRECTORY) + "/" + outputName;

            commonUtil.removeFileIfExits(fnout)

            //Check if a template is configured
            def templatePath = mapArgs.get(TEMPLATE)

            //Defined the API URL
            def apiUrl = mapArgs.get(exportUtil.SERVER_URL) + '/api'

            //Is the auto-generated token for on the fly authentification to the API REST
            def token =  mapArgs.get(exportUtil.TOKEN)

            //Api URL that will return the list of model projects
            def group = commonUtil.isEmptyString(mapArgs.get(exportUtil.GROUP)) ? '' : '?group=' + URLEncoder.encode(mapArgs.get(exportUtil.GROUP), "UTF-8")
            def modelProjectsUrl = apiUrl + '/models/' + mapArgs.get(exportUtil.ID_MODEL) + '/projects'+group

            //Api Rest Service call to get model context information
            def modelFull = squoreApiUtils.getObjectByApi(Model.class, apiUrl + '/models/' + mapArgs.get(exportUtil.ID_MODEL),token)

            //Api Rest Service call to get the list of model projects can be filtered by group
            def model = squoreApiUtils.getObjectByApi(Model.class, modelProjectsUrl,token)

            modelFull.setProjects(model.projects)

            //Create Excel Sheets by POI library
            def workbook = buildWorkbook(modelFull, templatePath, apiUrl, token)

            //generate the output excel file
            def outputStream = null;
            try {
                outputStream = new FileOutputStream(fnout);
                workbook.write(outputStream);
            } finally {
                outputStream?.close()
            }
        } catch (Exception e) {
            System.err.println("[ERROR] An error occurred " + e.getMessage() + " stackTrace " + e.getStackTrace())
        }
        println("[INFO] End processing !")
    }


    private def buildWorkbook(model, templatePath, apiUrl, token) {
        //Init workbook
        def workbook = excelUtil.getWorkbook(templatePath)

        def sheetNum = 0;
        def helper = workbook.getCreationHelper();

        //cell style for hyperlinks
        //by default hyperlinks are blue and underlined
        def hlink_style = excelUtil.getHlinkStyle(workbook);

        //Create the firs Sheet Context if there is a template file defined
        contextSheet(templatePath, workbook, model, apiUrl + '/versions/', token)

        workbook = new SXSSFWorkbook(workbook, 10000);
        helper = workbook.getCreationHelper();
        def styles = [:];

        model.projects.each { project ->
            def jsonQuery = '{"artefactTypes":["FILES"],"columns":[{"type":"MEASURE","id":"LC"}],"filters":[{"type":"indicator","id":"ANALYTICS","data":{"checked":["LEVELE"]}}]}'
            def highlightUrl = apiUrl + '/artefacts/' + project.artefactId + '/query'
            def high = squoreApiUtils.getObjectByApiAndQuery(Highlight.class, highlightUrl, jsonQuery, token)
            sheetNum++;
            workbook.createSheet(project.name).with { sheet ->
                excelUtil.createHightlightSheet(commonUtil, workbook, sheet, project, high, helper, hlink_style, styles)
            }
        }
        return workbook;
    }

    /*
    Create the first Sheet that contains model context. It's base on template.xlsx
     */
    private void contextSheet(templatePath, XSSFWorkbook workbook, model, apiUrlVersion, token) {
        if (templatePath) {
            // The first tab of excel file
            def rowNum = 11;
            workbook.getSheetAt(0).with { sheet ->
                getRow(5).with { row ->
                    createCell(3).with { cell ->
                        setCellValue(model.name)
                    }
                }
                def projectCellStyle = getRow(11).getCell(1).getCellStyle()
                model.projects.each { project ->
                    def version = squoreApiUtils.getObjectByApi(Version.class, apiUrlVersion + project.versionId, token)
                    createRow(rowNum).with { row ->
                        excelUtil.applyStyleforCell(row, 3, projectCellStyle)
                        getCell(2).with {
                            cellValue = 'Name'
                        }
                        createCell(3).with {
                            setCellValue(project.name)
                        }
                    }
                    rowNum++
                    createRow(rowNum).with { row ->
                        excelUtil.applyStyleforCell(row, 3, projectCellStyle)
                        getCell(2).with {
                            cellValue = 'Version'
                        }
                        createCell(3).with {
                            setCellValue(version.name)
                        }
                    }
                    rowNum++
                    createRow(rowNum).with { row ->
                        excelUtil.applyStyleforCell(row, 2, projectCellStyle)
                    }
                    rowNum++
                }
            }
        }
    }
}

/*
List of all Objects needed to transform Json result
 */
@JsonIgnoreProperties(ignoreUnknown = true)
class Project {
    String name
    int id
    String artefactId
    int versionId
}

@JsonIgnoreProperties(ignoreUnknown = true)
class Model {
    String name
    String externalId
    String id
    List<Project> projects
}

class Values {
    Object value
    String formatted
    Object[] bgColor
}

class Columns {
    String externalId
    String id
    String name
    String description
    String kind
    String format
    String suffix
}

@JsonIgnoreProperties(ignoreUnknown = true)
class Highlight {
    String name
    List<Columns> columns
    List<Artefact> artefacts
    boolean hidePath
    boolean hideRating   
}

@JsonIgnoreProperties(ignoreUnknown = true)
class Artefact {
    String name
    String path
    String id
    String url
    List<Values> values
    Type type
    Level level
	String viewPath
}

class Type {
    String name;
    String externalId
    String id
}

@JsonIgnoreProperties(ignoreUnknown = true)
class Version {
    int id
    String name
}

class Level {
    String id
    String icon
    Object[] color
    String mnemonic
}
  • Add template.xlsx in project
  • Add the new Export reference to models/software_analytics/Exports/import.xml

<Package xmlns:xi="http://www.w3.org/2001/XInclude" >
   <ExportDef name="highlights_excel" onArtefactTypes="MODEL;APPLICATION" />
   <ExportDef name="highlights_json" onArtefactTypes="MODEL;APPLICATION" />
   <ExportDef name="highlights_xml" onArtefactTypes="MODEL;APPLICATION" />
   <ExportDef name="highlights_csv" onArtefactTypes="MODEL;APPLICATION" />
   <ExportDef name="files_level_e_excel" onArtefactTypes="MODEL" />
</Package>
  • IntelliJ Project structure should automatically reflect these changes

Test the New Export

by GUI

  • In Squore GUI Administration/Reload Configuration
  • Select the "Software Analytics" node in the Project Portfolio
    Reports/Exports select "Get all Level E Files" click "Create"
  • Save highlightsModels.xlsx
  • Compare Excel sheet of projects with the highlight created in Prerequisites part

by CLI

  • Run the following command, where:
    • ${PathResult}/toExcel is the absolute path of result folder. It has to exist before running the command line
    • ${idModel} is the Model ID to use for the export, it can be retrieved by using the REST API
    • ${apiToken} is the Token for the REST API
    • ${templatePath} is the Excel template used to produce the output Excel file.
java -classpath .:${SquorePath}/addons/lib/* groovy.lang.GroovyShell \
${SquorePath}/configuration/exports/files_level_e_excel/level_e_to_excel.groovy \
${SquorePath}/addons/shared/GroovyScriptUtils.groovy  \
-importScript ${SquorePath}/addons/exports/Shared/exports_utils.groovy \
-excelUtilsScript ${SquorePath}/addons/shared/ExcelUtils.groovy \
-squoreApiUtils ${SquorePath}/addons/shared/SquoreApiUtils.groovy
-outputDirectory ${PathResult}/toExcel \
-idModel ${idModel} \
-serverUrl ${ServerHost}/SQuORE_Server \
-token ${apiToken} \
-template ${templatePath}
  • Compare Excel sheet of projects with the highlight created in Prerequisites part

For Windows, the classpath separtor is ';' => .;${SquorePath}/addons/lib/*

by IntelliJ IDEA

Groovy Scripts can be run and debug (JetBrain Documentation - Debug Groovy) in IntelliJ IDEA.

  • Click on "Add Configuration"
  • Click on "+" then select Groovy template
    • Script Path: the absolute path of level_e_to_excel.groovy file
    • Program arguments: ${SquorePath}/addons/shared/GroovyScriptUtils.groovy -importScript ${SquorePath}/addons/exports/Shared/exports_utils.groovy -excelUtilsScript ${SquorePath}/addons/shared/ExcelUtils.groovy -squoreApiUtils ${SquorePath}/addons/shared/SquoreApiUtils.groovy -outputDirectory ${PathResult}/toExcel -idModel ${idModel} -serverUrl ${ServerHost} -token ${apiToken} -template ${templatePath}
    • Working directory: ${SquorePath}/configuration
    • Module: configuration
    • JRE: Default (1.8 - SDK of 'configuration' module)
  • Click On "Apply"
  • Run the Script
  • Compare ${PathResult}/toExcel/hightlightsModels.xlsx project sheet with with the highlight created in Prerequisites part
  • Run in


  • No labels