One File To Do Them All

All you need to do in order to write data to a file is to:

- set up the template using reporting_setupTemplate routine
- set up the template's sheets using reporting_setupSheet routine
- add the data to the coresponding sheet using reporting_addData routine
- write the data added to a temporary file using reporting_writeWorkbook routine
- return the file created

You can do this all by creating a file like the one below:

SER_funcs.incl

struct SheetProp{
    string sheet;
    string[] heading;
    string[] data;
    string[] dataTypes;
}
function ser_writeDataInExcel(string reportFileName, boolean useRunnerLog, SheetProp[] sheetProps) {
    if(useRunnerLog) {
        runnerLog("Setting up report at " + formatDate(currentDate(), "HH:mm:ss"),100);
    }
    reporting_setupTemplate(reportFileName);
    
    for(SheetProp sheetProp in sheetProps){
        reporting_setupSheet(sheetProp.sheet, sheetProp.heading, sheetProp.dataTypes);
        if(useRunnerLog) {
            runnerLog("Writing data at " + formatDate(currentDate(), "HH:mm:ss"),100);
        }
        reporting_addData(sheetProp.sheet, sheetProp.data);
    }
    
    if(useRunnerLog) {
        runnerLog("Preparing file at " + formatDate(currentDate(), "HH:mm:ss"),100);
    }
    string filename = reporting_writeWorkbook();
    
    if(useRunnerLog) {
        runnerLog("Done at " + formatDate(currentDate(), "HH:mm:ss"),100);
    }
    return filename;
}

It defines the structure of one sheet: with its name, its header, the data it contains and the data types of the columns. 

The file above can be included into another SIL files by using this syntax:

include "SER_funcs.incl";

An easy example using this file would be one that returns some employees, their age and their salary. Below are the execution script and the parameter script for such an example.

exec.sil

include "SER_funcs.incl";
string templateFile = gadget_getSingleValue(argv, "Template file");
string sheetName = gadget_getSingleValue(argv, "Sheet name");
date startDate = gadget_getDateValue(argv, "Start Date");

//setting up headers and column types
string[] headers = {"Name", "Position", "Age", "Wage"};
number[] columnTypes = {0, 0, 1, 1};

string[] data = {"John Doe", "Developer", "30", "1300.50", 
                 "Paul Smith", "Project Manager", "41", "2500.00",
                 "Simon Desharnais", "Tester", "35", "900.20"};

SheetProp sheetProp;
sheetProp.sheet = sheetName;
sheetProp.heading = headers; 
sheetProp.data = data;
sheetProp.dataTypes = columnTypes;

return ser_writeDataInExcel(templateFile, true, {sheetProp});

params.sil

gadget_createInput("Template file", "template.xlsx", true, "Excel template file");
gadget_createInput("Sheet name", "Data", true, "Data Worksheet name in the template file");
gadget_createDatePicker("Start Date", currentDate(), true, "Choose a start date");