⟵ hear
  • Quick start
  • Install MIT
  • Install PRO
  • Updating
  • Optimization
  • Update v4-v6
  • Backups
  • Console utility bin/totum
  • Basics for users
  • Interface and Layout
  • Tables and their parameters
  • Prefilter
  • Fields and their parameters
  • Syntax
  • Code, actions, formatting
  • Relational relationships
  • Calculation order and calculation units
  • Auto-complete calculations and timing
  • Duplicate rows and cycles
  • Comparisons
  • Functions
  • Debugging
  • Print and CSV
  • API
  • Roles and users
  • Notifications
  • Scheduled Actions
  • System tables
  • Trees
  • Anonymous tables
  • External Forms
  • Exporting and importing tables
  • [PRO] MeiliSearch
  • [PRO] Databases
  • [PRO] Custom CSS
  • [PRO] Custom docs
  • [PRO] LDAP AD
  • [PRO] File versions
  • [PRO] List-unsubscribe
  • [PRO] Dynamic fields
  • [PRO] Only Office
  • [PRO] Auth Tokens
  • [PRO] 2FA
  • [PRO] Superlang
  • [PRO] Daemons
  • [PRO] Profiler
  • Connecting functions
  • [SRV] Installation and Connection
  • [SRV] Export, PDF, Upload, and Preview
  • [SRV] XLSX/DOCX Generators
  • How to generate xlsx based on table data and write it to a File type field
  • How to generate xlsx based on a template
  • How to add images instead of one of the variables
  • How to generate docx based on a template
  • How to generate multiple files from one template with different data
  • How to generate files directly in PDF
  • How to send generated files for download in the browser
  • How to send generated files via email
  • SERVICES Generation of XLSX and DOCX documents


    youtube

    Video on YouTube ⟹


    How to generate xlsx based on table data and write it to a File type field

    To generate xlsx, the serviceXlsxGenerator function is used. It has two mandatory parameters:

    • template – you need to pass the name of the file on the server with the xlsx template or indicate that a blank template should be used.

      • *NEW* — use a blank template.

      • #file_field[0][file] — get the file name on the server based on the File field.

    • data — array for replacement.

    When using *NEW* in data, you need to pass rowList, obtained by any method.

    • titles — if you need to add column headers to the generated file when using *NEW* in the template — pass a list with headers to this parameter.

      Example of this operation:

    = : set(table: $#ntn; field: 'result' = $list; where: 'id' = #id; log: true)
    
        list: listCreate(item: $from_services)
        //file: rowCreate(field: "name" = "some_name.xlsx"; field: "filestring" = $from_services)
    
    from_services: serviceXlsxGenerator(template: "*NEW*"; data: #data; answertype: "filerow"; name: "some_name"; titles: json`["Title A","Title B","Title C"]`)
    

    To make a set in the File field, you need to pass rowList (even if the File field has the multi parameter turned off). In this rowList, two columns are mandatory — name and file or filestring. Services cannot return file because it is the name on the Totum server, and when responding from the Service, the file in Totum has not yet been created, so the filestring key (file content) is used.

    = : set(table: $#ntn; field: 'result' = $list; where: 'id' = #id; log: true)
    
        list: listCreate(item: $file)
            file: rowCreate(field: "name" = "some_name.xlsx"; field: "filestring" = $from_services)
    
    from_services: serviceXlsxGenerator(template: "*NEW*"; data: #data_row_list)
    

    If the answertype parameter is not set, the service function by default returns the file content — filestring. However, we can specify other values in answertype:

    • filerow — in this case, a ready row with the keys name and filestring will be returned.

      To set a controlled name, you need to add the corresponding parameter (see the first example on this page). It can be set with or without an extension. If the extension is not set, Totum will substitute it based on the function's logic.

    How to generate xlsx based on a template

    To make a replacement based on a template, you need to set variables for replacement in the template in the form — ${variable_name}.

    • Variables must be passed, otherwise, they will be displayed as text.

    • If you want to hide a variable — pass an empty value — "".

    Variables can be replaced one by one, or you can add several rows. In this case, you need to take the first variable in the row and pass a rowList containing the variables for replacement:

    = : set(table: $#ntn; field: 'result' = $list; where: 'id' = #id; log: true)
    
    list: listCreate(item: $from_services)
    
    from_services: serviceXlsxGenerator(template: #template[0][file]; data: $data; answertype: "filerow"; name: "some_name")
    
    data: rowCreate(field: "title" = "Some title"; field: "value" = "Some value"; field: "field_1" = $rowList)
    
    rowList: rowListCreate(field: "field_1" = json`[1,2,3]`; field: "field_2" = json`["A","B","C"]`; field: "field_3" = json`["A1","B2","C3"]`; field: "field_4" = "")
    

    How to add images instead of one of the variables

    As a replacement for a variable, you can pass an image that will be implemented in the document.

    To do this, you need to pass an array in the variable in the form:


    data:
    rowCreate(field: "title" = "Some title"; field: "image" = $imagerow) imagerow: rowCreate(field: "type" = "image"; field: "height" = 100; field: "width" = 100; field: "imagetype" = "png"; field: "filestring" = $base64) base64: strBaseEncode(str: $getcontent) getcontent: fileGetContent(file: #h_image[0][file])

    The composition of this row:

    • type — value image

    • height and/or width — size values in pixels.

    • imagetype — image type (must be correct!). It can be automatically obtained from the ext key of the File field #image[0][ext].

    • filestring — image binary packed in base64

      • The binary is obtained by the function fileGetContent(file: #h_image[0][file])

      • base64 by the function strBaseEncode(str: )

    If you are passing images to fill multiple rows, you should have a rowList in which each row contains information about its own image:

    = : set(table: $#ntn; field: 'result' = $list; where: 'id' = #id; log: true)
    
    list: listCreate(item: $from_services)
    
    from_services: serviceXlsxGenerator(template: #template[0][file]; data: $data; answertype: "filerow"; name: "some_name")
    
    data: rowCreate(field: "title" = "Some title"; field: "value" = "Some value"; field: "field_1" = $rowList)
    
    rowList: rowListCreate(field: "field_1" = json`[1,2,3]`; field: "field_2" = json`["A","B","C"]`; field: "field_3" = json`["A1","B2","C3"]`; field: "field_4" = $imagerowlist)
    
    imagerowlist: listReplace(list: $list_of_files; action: $imagerow; value: "image_var")
                list_of_files: selectList(table: $#ntn; field: 'image'; order: 'id' asc)
        imagerow: rowCreate(field: "type" = "image"; field: "height" = 100; field: "imagetype" = $#image_var[0][ext]; field: "filestring" = $base64)
            base64: strBaseEncode(str: $getcontent)
                getcontent: fileGetContent(file: $#image_var[0][file])
    

    How to generate docx based on a template


    youtube

    Video on YouTube ⟹


    Variables in the template are designated similarly to xlsx templates.

    To perform a template replacement, you need to set variables for replacement in the template in the form — ${variable_name}.

    • Variables must be provided; otherwise, they will be displayed as text.

    • If you want to hide a variable, pass an empty value — "".

    The serviceXlsxGenerator function is used for generation — it works similarly to xlsx.

    You can also add multiple rows to a table inserted into a docx document.

    How to generate multiple files from one template with different data

    1. Use answertype: "filerowlist" — in this case, serviceDocxGenerator and serviceXlsxGenerator will return a rowList with keys name and filestring.

    2. To use different data for one template, pass a rowlist in data — where each row will contain data for a separate file.

    3. To provide correct names for the created files, pass a list of file names in name.

    In fact, template can also be a list of different templates — substitution will go 1 to 1 with the order in data.

    Multiple generation is for convenience. Requests to the server will be sent separately for each row in data.

    = : set(table: $#ntn; field: 'result' = $from_services; where: 'id' = #id; log: true)
    
    from_services: serviceDocxGenerator(template: #template[0][file]; data: $data_list; answertype: "filerowlist"; name: $list_names; pdf: true)
    
    data_list: listCreate(item: $data_1; item: $data_2)
    
        data_1: rowCreate(field: "var_1" = "Some title"; field: "var_2" = "Some value"; field: "field_1" = $rowList_1)
            rowList_1: rowListCreate(field: "field_1" = json`[1,2,3]`; field: "field_2" = json`["A","B","C"]`; field: "field_3" = json`["A1","B2","C3"]`)
    
        data_2: rowCreate(field: "var_1" = "Some title 2"; field: "var_2" = "Some value 2"; field: "field_1" = $rowList_2)
            rowList_2: rowListCreate(field: "field_1" = json`[4,5,6]`; field: "field_2" = json`["D","F","Y"]`; field: "field_3" = json`["D1","F2","Y3"]`)
    
    list_names: listCreate(item: "some_name_1"; item: "some_name_2")
    

    How to generate files directly in PDF

    Enable the pdf: true parameter in the serviceDocxGenerator or serviceXlsxGenerator function.

    In this case, in addition to generating the document from the template, the final file will be converted to .pdf.

    How to send generated files for download in the browser


    youtube

    Video on YouTube ⟹


    The function linkToFileDownload handles sending files for download in the browser.

    Its feature is that, in addition to the keys name and file/filestring, you need to pass the mime-type in the type key.

    A good list of mime-type can be found on the documentation page for mozilla.

    If using the return type filerowlist:

    = : linkToFileDownload(files: $add_mimetype)
    
        add_mimetype: listReplace(list: $from_services; action: "type" = "application/vnd.openxmlformats-officedocument.wordprocessingml.document")
    
        // for row when you use answertype: "filerow"
        row: rowAdd(row: $from_services; field: "type" = "application/vnd.openxmlformats-officedocument.wordprocessingml.document")
    
    from_services: serviceDocxGenerator(template: #template[0][file]; data: $data_list; answertype: "filerowlist"; name: "some name")
    
    data_list: listCreate(item: $datarow)
        datarow: "SOME DATA ROW FOR YOUR FILE"
    
    

    If using the return type filerow:

    = : linkToFileDownload(files: $row)
    
        row: rowAdd(row: $from_services; field: "type" = "application/vnd.openxmlformats-officedocument.wordprocessingml.document")
    
    from_services: serviceDocxGenerator(template: #template[0][file]; data: $data; answertype: "filerow"; name: "some name")
    
    data: "SOME DATA ROW FOR YOUR FILE"
    
    

    How to send generated files via email

    = : emailSend(to: "totum@totum.online"; title: "some title"; body: "some body"; files: $from_services)
    
    from_services: serviceDocxGenerator(template: #template[0][file]; data: $data_list; answertype: "filerowlist"; name: "some name")
    
    data_list: listCreate(item: $datarow)
        datarow: "SOME DATA ROW FOR YOUR FILE"