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
- set up the template using reporting_setupTemplate routine
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");