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 !
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.
##_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.##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.
##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.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.
##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.
##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.
The following fields are compatible with the bar code format:
##field:128##: to make its fields in QR Code Format.
##field:qrcode##: to make its field in 128B bar code format.
##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.
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:
##list:pathcode-1## / ##list:fullpathcode####list:pathlabel-1## / ##list:fullpathlabel##In Excel, you have two possibilities to integrate a table: in rows or in columns. Data in rows:
Item tag rows will display as many times as the entry rows.
Item tag columns will display as many times as the entry rows during entry.
Tips: How to do line break in Excel:
##_line_number:id_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]##.
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##.
An example:
Tip: To get only one page, do not hesitate to hide the sheets that you do not want to appear in your personalized report!
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.
