Custom-made Excel/PDF: Customize your reports entirely!

Home / Tutorials/Custom-made Excel/PDF: Customize your reports entirely!

08Sep, 2016

0Comments

Categories / Tutorials By Sarah Marivaux

By default, the Excel list allows you to extract one or more data fields from the same Excel file.
However, it is possible to export a Kizeo Forms report in customise Excel format with the header, layout, font, style … that YOU want. Kizeo Forms gives you total freedom to write YOUR own Custom-made Excel/PDF. To do this, you will have to deal with some technical terms… but nothing serious! The 3 steps below will help you create a custom-made Excel/PDF !

Custom-made Excel/PDF: Step 1. Create a custom-made Excel.

You are going to create what we call a custom-made Excel. You will use Microsoft Excel software, 2007 or newer. If you do not have the Microsoft Office suite, you can download the trial version or buy the full version on our partner’s site.

To customise your report, you will also need to know the item tags of your form. The item tags represent the reference code for each element (ex. ##customer##). For more information on item tags, you may consult our tutorial on Tags Help.

Tips: You can download and take inspiration from the Excel example provided by Kizeo. We strongly advise you to proceed that way. The advantage of this example is that it includes all the item tags for your properly written form.

Go to the Exports tab.
Click on Add a new export -> Download example.

Open a blank Excel page
Build your document the way you want it by inserting (manually) the tags where you want them to display the information. For example, if you want the last name and first name of your client located on the same cell then you can simply place the item tags: Last name and First name side by side in your Excel file.

excel file

You also have the ability to include calculations, text, colours, images, etc.
Similarly, you can make certain items disappear in your reports. For example, information necessary to manage your data, but providing no advantage to the customer.

Tags for your excel

Tips for creating your template.

Standards :

##_last_name##: user’s surname
##_first_name##: user’s first name
##_phone##: user’s phone number
##_email##: user’s email
##_login##: user’s ID
##_answer_time##: date of reply
##_id##: unique identifier
##_record_number##: n° of response to this form (identifier)
##_answer_time##: date of response to the form.
##audio##: allows you to obtain a link that returns to the audio.
##attachment##: allows you to obtain a link that returns to the attachment.
##list:label##: allows you to increase the wording (especially for the custom-made Excel that displays the code by default).
##field_id[n]##: allows you to retrieve the entry of an element of a specified row (n) in a table. If you have a checkbox in your table, for example, and want to retrieve its value on line 3, you can now use the ##checkbox[3]## tag.
##field:caption##: allows you to retrieves the title of a field.

Date and time tags:

##datetime##: numeric format (01/01/2016).
##datetime:short##: short text format (1er Jan. 2016).
##datetime:medium##: medium text format (1 January 2016).
##datetime:long##: long text format ( Friday 1 January 2016).
##datetime:iso##: ISO format (2016-01-01). (available soon).

“datetime” corresponds to the tag of your date and time field.

Geolocation tags:

##geoloc##: allows you to obtain the coordinates.
##geoloc:latitude## and ##geoloc:longitude##: allow you to obtain latitude and longitude.
##geoloc:link##: allows you to obtain a link that returns to the map when clicked.
##geoloc:time##: allows you to obtain the time when the geolocation was taken.
##geoloc:map-z12-200x200##: allows you to display a map on you customised export. Please note that this is the letter “X” and not the multiplication sign.
– « -200x200 » corresponds to the dimension of the image.
– « z12 » corresponds to zoom in/out on the word map. To have an idea: 1 displays the word map and 18 displays the corresponding street. 12 displays more or less the neighborhood.

Photo, drawing and signature tags:

Define the size of your images (photo, drawing and signature):

##id_element##: if one doesn’t want to define the size (default size 250 pixel).
##id_element-heightxwidth##: if one wants to define the size.
##id_element-width##: if one wants to define just the width.
##id_element-xheight##: if one only wants to define the height.

/!\ To define the size of your images (photo, signature or drawing), you can choose to define the height and/or the width. The undefined value is automatically defined to conserve the image’s proportions. For example, if you want a signature that appears in 100 pixel (height), you need to insert ##id_element-x100##.

Define the size of your multiple photos, multiple drawings:

##id_element:n##: if one doesn’t want to define the size.
##id_element:n-widthxheight##: if one wants to define the size.
##id_element:n-widthx##: if one wants to define just the width.
##id_element:n-xheight##: if one wants to define just the height.

n corresponds to the image’s number. Numbering starts at 1.
Tip: You can use a table to have a better presentation of you multiple photos!

Display the date and time of your photos when they were taken:

##photo:time##: allows you to obtain the date and time when the picture was taken.
##photo:time:short##: allows you to obtain the date and time in the short text format (1 Jan. 2016).
##photo:time:medium##: allows you to obtain the date and time in the medium text format (1 January 2016).
##photo:time:long##: allows you to obtain the date and time in the long text format (Friday 1 January 2016).
##photo:time:iso##: allows you to obtain the date and time in the ISO format (2016-01-01).

Display the date and time of your signatures:

##signature:time##: allows you to obtain the date and time when signing your form.
##signature:time:short##: allows you to obtain the date and time in the short text format (1 Jan. 2016)
##signature:time:medium##: allows you to obtain the date and time in the medium text format (1 January 2016).
##signature:time:long##:allows you to obtain the date and time in the long text format (Friday 1 January 2016).
##signature:time:iso##: allows you to obtain the date and time in the ISO format (2016-01-01).

Geolocation display of your photos and signatures:

##photo:latitude## and ##photo:longitude##: allows you to obtain the latitude and longitude when taking a picture.

##signature:latitude## and ##signature:longitude##: allows you to obtain the latitude and longitude when signing your form.

Note: please, tick the “Get the location automatically” checkbox in the options of your field in the Advanced tab.

Display a link of your photos and signatures:

##photo:link##: allows you to obtain a link of your photo when clicked.

##signature:link##: allows you to obtain a link of your signature when signed.

Bar-code tags:

Bar code display in 128B format:

##barcode:128##: if one doesn’t want to define the size.
##barcode:128-width##: if one wants to define just the width.
##barcode:128-height##: if one wants to define just the height.

Bar code display in QR code format:

##barcode:qrcode##: if one doesn’t want to define the size.
##barcode:qrcode-widthxheight##: if one wants to define the size.
##barcode:time##: allows you to obtain the date and time when the information was taken.

Display your fields in 128B bar code or QR Code formats:

The following fields are compatible with the bar code format:

Text field
text area,
Slider,
NFC tag,
Calculation,
Reference,
Counter.

##field:128##: to make its fields in QR Code Format.
##field:qrcode##: to make its field in 128B bar code format.

List, slider and choice tags:

##list:break## and ##choice:break##: allow you to duplicate the row and maintain the chip format used.
##list:unordered## and ##choice:unordered##: to make an hyphen appears before list or choice field.
##list:ordered## and ##choice:ordered##: allow you to have your list or choice field in numerical order.

Display of your Hierarchical Lists path:

You want to display all or part of your Hierarchical list‘s path in your Excel report, the following tags allow you to do so:

As an example, let’s take the following hierarchical list:
FR-fr:France\83:Var\83700:Saint-Raphaël

The syntax to display the entire hierarchical path is: ##list:path##

In our example, « FR-fr / 83 » will be displayed on the Excel report.

The syntax to display the entire path as well as the label is: ##list:fullpath##

« FR-fr / 83 / 83700 » will be displayed on the Excel report.

The syntax to display each element of your list individually is: ##list:path-1##, ##list:path-2##, etc…

In your Excel report:

##list:path-1## will display « FR-fr »,
##list:path-2## will display « 83 ».

Tips:

It is possible to display the Code in all exports by using the following tags: ##list:pathcode-1## / ##list:fullpathcode##
It is possible to display the label in all exports by using the following tags: ##list:pathlabel-1## / ##list:fullpathlabel##

Tables.

In Excel, you have two possibilities to integrate a table: in rows or in columns.

Data in rows:

On every cell of the first row, name your columns.
On every cell of the second row, insert the item tags where you want the information to be displayed.

Item tag rows will display as many times as the entry rows.

insert the item tags where you want the information to be displayed in your excel

Data in columns

Insert a table with as many rows as necessary and two columns.
In the first column first cell, insert your table tags.
In the second column, insert the item tag of the table and all item tags.

Item tag columns will display as many times as the entry rows during entry.

insert the item tags where you want the information to be displayed in your excel

Tips: How to do line break in Excel:

On PC: alt + enter.
On Mac: cmd + alt + enter.

insert the item tags where you want the information to be displayed in your excel

Display the line number

If you want to display the line number in a table, you can use the tag:##_line_number:id_table##.

Retrieve the entry of an element of a line in a table.

The tag##id_field[n]## is used to retrieve the entry of an element of a specific line in a table. For example, if you have a check box in your table and want to retrieve its value in line 3, you can use the tag##check_box[3]##.

Stylize the tags.

If you want to color your data, put it in bold, etc. Just mark the tag. For example, I want my data to appear in bold, red, italic, and underlined, so I mark the tag as ##tag##.

Customise data:

An example:

Template Kizeo Forms excel

Tip: To get only one page, do not hesitate to hide the sheets that you do not want to appear in your personalized report!

Step 2. Import the Custom-made Excel template.

Return to your form.
Go to the Exports tab.
Click on Add a new export.
Load your Excel template by clicking on Drag your Excel template here.
Close the window by clicking on Save.

Tips :

You can analyze your template with the second part of the windows. In the example below, the template has only valid tags.

You can also edit the name of your export with tags.

Step 3. Export data.

Now, watch the results. Go to the History page of your menu and click on Custom-made Excel or PDF if needed.

You can also rename your export files. For more information, refer to our tutorial.

Do not hesitate to comment on this article!
All your questions, comments and suggestions are most welcome!

Custom-made Excel/PDF: Customize your reports entirely!

Custom-made Excel/PDF: Step 1. Create a custom-made Excel.