Give us feedback
officeutils
Service icon

OfficeUtils

Stable version 5.0.5 (Compatible with OutSystems 11)
Other versions available for 10 and Older
Published on 25 May by 
4.6
 (31 ratings)
officeutils

OfficeUtils

Documentation
5.0.5

The OfficeUtils component consists in two modules:

  1. An Extension, responsible for generating the xlsx and docx files, which uses the NPOI library for .NET and POI for JAVA stack.
  2. OfficeUtils espace, which provides a set of helper actions to specify the generation inputs.

Word generation: 

Word generation is based on a template with placeholders, which will be replaced using the available helpers (server actions).

Word generation steps:

  1. Provide a template document, and specify unique names for the placeholders that should be replaced. The template document is mandatory. Include your template document in Resources folder of your module. A template example is available for download in the demo.
    - To add a text or a picture, your placeholder should be like this example:

    - To add a table to the document, one cell with the placeholder inside should be created.

    (the styles of this cell will be applied to every cell of the table):

    Note: Please do not use the soft enter in your template. The NPOI library has a bug which causes soft enters to be replaced by page breaks. Use paragraph definitions to control the space between lines instead.

  2. Create a new local variable of WordFile type. WordFile is a structure that keeps the information about the template, and the list of all replacements to be performed using helpers. This variable must be updated after each helper usage, to add the last replacement made, to the list of replacements.
  3. Manipulate your document content, using the helper actions. Use  Word_Export_SetText, Word_Export_SetPictureWord_Export_SetTableWord_Export_SetAdvancedTable for specifying what should be replaced (and with what). The output of these actions is a new WordFile record. You can obtain the full list of helpers and an implementation example for each one, in the demo of this component. Using the helper actions is not mandatory. If it is more convenient, you can populate the WordFile record directly. 
  4. Use the helper action Word_Export_SetTemplate to set the template that you have created. The output of this action is a new WordFile record. The template is mandatory.
  5. Use the Word_Export_GenerateFile to generate the new document. The input of this action is a WordFile record that should have already the information about the template and the replacements to be performed. You have to pass your created and updated WordFile variable. The output is the binary content of the generated document.

Excel generation: 

  1. Provide an optional template document. The template can have cell styles and others formats to be applied to the generated document. A template example is available for download in the demo component
  2. Create a new local variable of ExcelFile type. ExcelFile is a structure that keeps the information about the template, and the list of all operations to be done on the spreadsheet. This variable must be updated after each helper usage, to add the last operation made, to the list of operations.
  3. Use the helper actions Excel_Export_SetCellTextExcel_Export_SetCellBoolean, Excel_Export_SetCellDate, Excel_Export_SetCellTime, Excel_Export_SetCellDateTime, Excel_Export_SetCellDecimalExcel_Export_SetCellIntegerExcel_Export_SetPictureExcel_Export_SetTable, Excel_Export_SetCellStyleExcel_Export_MergeCells to specify the operations to be done on the spreadsheet. Each action returns a NewExcelFile record, which is a representation of all operations. Each helper action has a common set of input parameters:
    • ExcelFile – record with previously added operations.
    • SheetName – Spreadsheet Sheet name. If the sheet does not exist then it will be created automatically.
    • SheetToClone – The name of the sheet on the template (template sheet) based on which the new one will be created. The original sheet will be deleted when the document is generated.
    • Row – Row number on the sheet where to place the information. Starts with 0.
    • Column – Column number on the sheet where to place the information. Starts with 0.
    • UseTemplateCellsDataFormat  Boolean to specify if the format of the cell should be taken from the provided template, or if instead the default (OutSystems) format should be used.
      Default format vs Template format changing UseTemplateCellsDataFormat value

      Using the helper actions is not mandatory. If more convenient you can populate the ExcelFile record directly.
  4. Use the helper action Excel_Export_SetTemplate to set the template. The output is a new ExcelFile Record.This step is optional, only necessary if a template must be specified.
  5. Use the Excel_Export_GenerateFile to generate the new spreadsheet. The input of this action is an ExcelFile record, that should have already the information about the template and the operations to be performed.


We advise to download and install the component demo, as it provides implementation examples of the several OfficeUtils usage scenarios:


5.0.4

The OfficeUtils component consists in two modules:

  1. An Extension, responsible for generating the xlsx and docx files, which uses the NPOI library for .NET and POI for JAVA stack.
  2. OfficeUtils espace, which provides a set of helper actions to specify the generation inputs.

Word generation: 

Word generation is based on a template with placeholders, which will be replaced using the available helpers (server actions).

Word generation steps:

  1. Provide a template document, and specify unique names for the placeholders that should be replaced. The template document is mandatory. Include your template document in Resources folder of your module. A template example is available for download in the demo.
    - To add a text or a picture, your placeholder should be like this example:

    - To add a table to the document, one cell with the placeholder inside should be created.

    (the styles of this cell will be applied to every cell of the table):

    Note: Please do not use the soft enter in your template. The NPOI library has a bug which causes soft enters to be replaced by page breaks. Use paragraph definitions to control the space between lines instead.

  2. Create a new local variable of WordFile type. WordFile is a structure that keeps the information about the template, and the list of all replacements to be performed using helpers. This variable must be updated after each helper usage, to add the last replacement made, to the list of replacements.
  3. Manipulate your document content, using the helper actions. Use  Word_Export_SetText, Word_Export_SetPicture, Word_Export_SetTable, Word_Export_SetAdvancedTable for specifying what should be replaced (and with what). The output of these actions is a new WordFile record. You can obtain the full list of helpers and an implementation example for each one, in the demo of this component. Using the helper actions is not mandatory. If it is more convenient, you can populate the WordFile record directly. 
  4. Use the helper action Word_Export_SetTemplate to set the template that you have created. The output of this action is a new WordFile record. The template is mandatory.
  5. Use the Word_Export_GenerateFile to generate the new document. The input of this action is a WordFile record that should have already the information about the template and the replacements to be performed. You have to pass your created and updated WordFile variable. The output is the binary content of the generated document.

Excel generation: 

  1. Provide an optional template document. The template can have cell styles and others formats to be applied to the generated document. A template example is available for download in the demo component
  2. Create a new local variable of ExcelFile type. ExcelFile is a structure that keeps the information about the template, and the list of all operations to be done on the spreadsheet. This variable must be updated after each helper usage, to add the last operation made, to the list of operations.
  3. Use the helper actions Excel_Export_SetCellText, Excel_Export_SetCellBoolean, Excel_Export_SetCellDate, Excel_Export_SetCellTime, Excel_Export_SetCellDateTime, Excel_Export_SetCellDecimal, Excel_Export_SetCellInteger, Excel_Export_SetPicture, Excel_Export_SetTable, Excel_Export_SetCellStyle, Excel_Export_MergeCells to specify the operations to be done on the spreadsheet. Each action returns a NewExcelFile record, which is a representation of all operations. Each helper action has a common set of input parameters:
    • ExcelFile – record with previously added operations.
    • SheetName – Spreadsheet Sheet name. If the sheet does not exist then it will be created automatically.
    • SheetToClone – The name of the sheet on the template (template sheet) based on which the new one will be created. The original sheet will be deleted when the document is generated.
    • Row – Row number on the sheet where to place the information. Starts with 0.
    • Column – Column number on the sheet where to place the information. Starts with 0.
    • UseTemplateCellsDataFormat  Boolean to specify if the format of the cell should be taken from the provided template, or if instead the default (OutSystems) format should be used.
      Default format vs Template format changing UseTemplateCellsDataFormat value

      Using the helper actions is not mandatory. If more convenient you can populate the ExcelFile record directly.
  4. Use the helper action Excel_Export_SetTemplate to set the template. The output is a new ExcelFile Record.This step is optional, only necessary if a template must be specified.
  5. Use the Excel_Export_GenerateFile to generate the new spreadsheet. The input of this action is an ExcelFile record, that should have already the information about the template and the operations to be performed.


We advise to download and install the component demo, as it provides implementation examples of the several OfficeUtils usage scenarios: