One file to do them all

To write data to a file perform the following actions:

- 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 certain employees, their age and their salary. Execution script and the parameter script for such an example are represented below:

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");