Excel Exporter: Several questions

0
Hi. I am currently building an project using the 6.8.1 version of the modeler and the latest version of the excel exporter. However, I am running into some issue of which I don't know if I am doing something wrong or that it are limitations and\or bugs. Maybe somebody with experience can give some feedback or maybe some clever ways around it? 1-When using static data to print a datetime attribute, the cell in the generated excel document is not set as 'date', but as 'standard'. This means not a date is showing but a number. 2-In the generated excel file, all column data cell types are set as 'standard', except the datetime cells. Isn't it possible to set the cell type for numbers\decimals to 'number' cell type (with 2 decimals)? 3-The result aggregation doesn't seem to work at all, and causes a crash in the excel generation. Mostly without an error, but I saw one time the error: "java.math.BigDecimal cannot be cast to java.lang.Double at XLSReport.GenerateReport (JavaAction : 'GenerateExcelDoc')"
asked
1 answers
2

Rene,

I've got the same problem and I solved this by changing the module a little bit. I've added the possibility to add a xls template in the configuration. So instead of starting with an empty xls file the export will use the updated file. In the xls file you can change all the format's you would like.

STEP 1) Create an new entity called TemplateFile -- with a reference to MxTemplate - Generalization "Filedocument"

STEP 2) Add an upload button on the form "TemplateEditExcel"

STEP 3) In the javaaction "GenerateExcelDoc" add/replace the following code (inside the xlsx case (line 82)

        log.trace("Use Excel export");
        if(filename == null)
        {
            filename = this.TemplateObject.getName().replace(" ", "_") + ".xlsx";
        }           

            /*ADDITION*/
            FileDocument templateDocument = TemplateObject.getMxTemplate_TemplateFile();

            if(templateDocument == null){
                 export = new ExportExcel(this.getContext(), this.TemplateObject, this.InputObject)
             }else{
                  /* NEW CONSTRUCTOR */
                  export = new ExportExcel(this.getContext(), this.TemplateObject, this.InputObject, templateDocument)
             }

STEP 4. Add this constructor to the ExportExcel.java file

public ExportExcel(IContext context, MxTemplate template, IMendixObject inputObject, FileDocument templateFile) throws CoreException, IOException
{
    super(context);
    InputStream fileIn = Core.getFileDocumentContent(context, templateFile.getMendixObject());

    //InputStream inp = new FileInputStream("workbook.xlsx");
    this.book = new HSSFWorkbook(fileIn);
    // Initialize all the styling items for the excel   
    this.styling = new Styling(template.getDateTimePresentation());
    this.styling.setAllStyles(context, template, this.book);    
    this.useStyling = false;
}

Step 5: Replace inside the buildExportFile the code where the sheet is created with the following

    // Create new sheet object in the excel doc.
    //ADDED RB
    Sheet sheet = null;
    if (this.book.getSheet(mxSheet.getName()) != null){
        sheet = this.book.getSheet(mxSheet.getName());
    }else{
        sheet = this.book.createSheet(mxSheet.getName());
    }
    //END ADDED RB

I never bother with the format ever again, i just asked the functional owner to update the template. That's it.

answered