Exporting tickets to MS Excel

In the eighth installment of our blog series Step by Step to a Ticket System, you saw how to create work assignments directly from the ticket form with a button. Today you will learn two ways to export your tickets to MS Excel and to continue editing the information there. In the first variant you can edit the tickets in Excel and then update in-STEP BLUE.

Export view contents into a MS Excel file

Log in as the administrator of your ticket system in in-STEP BLUE. As you know, you can simply export the contents of a view with one click via the context menu.

Present contents of view in MS Excel

If you don’t want to export the data into an unformatted Excel file, just create a template and allocate it using the properties of the view.

Create and allocate templates

Create an Excel file with the desired formatting template and, if necessary, the static content.

Create a template

Open the view Templates in your in-STEP BLUE ticket system view group Administration and move the new Excel file into the folder English using drag and drop. Determine the state and if necessary the version number.

Include MS Excel template in system

Open the properties dialog via the context menu of the view All Tickets. Change to the Template tab and configure the template settings like this:

Configure the view All tickets

So that your Excel template can only have a worksheet, you can leave the field Name of the worksheet empty. If you put a tick in the box for the setting Default view in MS Excel, then the Excel file will be generated and opened automatically when you click on the view entry in the view group.

The tickets list in MS Excel

Export and edit form data to MS Excel

To generate form data into an Excel-based product and then be able to edit the data in forms in Excel, three things are needed:

1. Template for the Excel file
2. Product type for the Excel file
3. Commands to generate the file

Template for Excel file

Open the view Templates again in the view group Administration and create a new Excel file  (Ticketlist.xlsx) in the folder German. Enter the column headings into the columns to be created. Apart from that, the cells of the have to be displayed like this: isp_<Name of the category property>. If you want to edit the properties in Excel, supplement the comment with the note :editable.

When checking in the Excel file, you can also send the results of the state machines of individual tickets. For that you just need a column where you can select results. In the example, the column action is identified with the comment isp_EVENT.

The rich Excel template

Product type for Excel files

Now open the view product types in the view group Administration and define a new product type Ticket list for your Excel file.

The ticket list under the product type

The settings of the product types can be edited like this:

Determine the properties of the ticket list

Please make sure that GENERATED EXCEL DOCUMENT is selected as the instance category and that the template that was just created is selected.

Connection of the necessary features from the ExcelAdapter.dll

Register the command server … for configurable MS Excel documents and the check in plugin Update forms from MS Excel work maps, to integrate the functionalities to configure and to update the Excel file. To do this, open the dialog for component management via the menu organizational unit / components and register the file ExcelAdapter.dll from the in-STEP BLUE installation directory and select the corresponding components.

Just register the components

Configuration adjustment of the view files/products

So that the command to configure and generate an Excel file will be offered in the context menu of the file, the properties of the view Files / Products has to be customized. To do that, open the properties dialog of the view and select the … for configurable MS Excel documents under the command server on the tab menu item.

Adapt the properties to the view

Small steps to finish

Now open the view Files / Products. In the folder files, create a new product with the type ticket list. And configure the product directly in the context menu command Configure …

Navigate to configuration

… and configure the work map

For the entry Directory, set the folder of your tickets. If you only want to export certain tickets, just enter the view that contails the desired tickets as a result – for example, tickets with a certain condtion that are allocated to the logged-in user.

If you want to use the form to create tickets, select the product type of the ticket product type to create.

To also edit the tickets after they are exported, select the entry check out form under actions for forms. If you select no actions, you will not be able to edit the tickets when they are exported to MS Excel. If you select send result to form, then a corresponding result will be sent to the tickets when the file is generated. This can then – depending on the state machine configuration – for example, changed into the state generate in file.

The result

Now you can use the content menu command Update work map to export your tickets to MS Excel. When updating, not just the Excel file but all the displayed tickets will be checked out. The changes that are made in the file will be taken over when checking the files back in. The column action can be used to trigger the state change for the tickets.

Working with tickets in Excel

That’s all for today. In the next article in the blog series, we will work with MS Excel again. You will see how you can export the contents of multiple views into one Excel file to edit it further with macro. Just follow along to come step by step to your own ticket system. And if you have any questions or suggestions, get in touch, as always, at service@microtool.de or 030/467086-20. We are looking forward to your ideas.