Welcome

Welcome to the Documentation of the Mirrorlake Excel Importer!

For questions that are not answered here just write to support@mirrorlake.eu.

How to install

Installing the Excel add-in

Download the latest version here: https://mirrorlake.atlassian.net/wiki/spaces/MSS/pages/2825453570
Unzip Mirrorlake_Excel_Importer_vx.x.x.zip. The archive contains the importer add-in Mirrorlake_Jira2Excel.xlam
Save the Mirrorlake_Excel.xlam file to a local folder on your PC.
Open Microsoft Excel
Click "File tab" -> "Options" -> "Add-Ins" category
Select Excel-Add-Ins at manage field and click Go to open the Add-ins dialog box.
Click on browse and select the unzipped Mirrorlake_Excel.xlam

More help on how to manage add-ins in excel can be found in the Microsoft® Office support pages.

In case the add-in disappears after quit+restart of excel, look to the solution of this known issue cause by a Microsoft® Windows® security update.

That's it. Now your Importer should be visible in Excel®:

In case the add-in disappears after quit+restart of excel, look to the solution of this known issue cause by a Microsoft® Windows® security update.

Installing the license add-on to your Jira® server

Note: this step is not needed, if you just want to evaluate the tool or if you want to buy an single-PC activation key.

download the Jira® plugin from the Atlassian® marketplace here
follow the install instructions from the Atlassian® marketplace

Connect Setup

In the connect setup global settings for the connection to the Jira® Server are configured. The window looks like shown below:

Updates

Use the "Check for Updates" button to check if you are working with the latest Excel Addin.

Server Config File

You can store the connection configuration in a dedicated file. Using multiple configuration files is very useful in case you are accesing one of several possibel Jira® servers. The location of the currently used configuration is stored with your excel file. This way you can easily setup multiple excel tables for your import work - each for one Jira® server.

Jira Server Connection

In this field you need to configure basic setting required for accessing your Jira® server:

Jira Base URL: contains the first part of your Jira® server address. Look to your browser URL: e.g. when you search for your own open issues the URL will look similar to this: <baseURL>/issues/?filter=-1. Example: http://localhost:8080/issues/?filter=-1 means that the base address is http://localhost:8080.
Jira User: containes the login name of your Jira® user
Jira Password: contains the Jira® user password
Store password: when selected, the password is stored as encrypted information on your PC. Note, that the encrpytion level is not very high. Encryption is implemented mainly to avoid storing human readable information on your harddisk.

If all information was entered correctly, press Connect to Jira. The "connect information" field will show helpful information about the connect result.

Activation Key

These fields are only needed in case you buy a single PC activation key. For more information contact us at support@mirrorlake.eu.

Import Setup

Issue Selection

Using this configuration area you can define, which issue to be imported:

importing issues selected by a favourite filter
importing all issues of a project
importing issue of a given JQL query

Field Selection

Select available fiels and click the "arrow-right" button to add it to the list of fields to be imported. You can adjust the order in which fields are shown in the import-table (up/down arrows).

Adding your own custom columns to your table during import

You can add your own columns to your table of imported data. These colums can contain any text or even excel formulas! This way you can add new data columns that calulate their content based on references to other imported data columns. Sounds complicated? This feature can be extremely useful as there are lot of possibilities how you can extend your tables with additional data coming from your own excel formulars.

How to add FormulaR1C1 fields:

At first, go into the import setup. Then create your non-Jirac-fields by clicking "Create New". A new fields is shown in the list of selected fields. Now you can modify your field by giving it your preferred name and the FormulaR1C1 content. Remark: your own fields always look like <yourfield>. This way you can disinguish later easily between Jira® and non-Jira® fields.

Examples

Hello World! prints in each cell in that colum the text "Hello World"
=R1C1 is an absolute reference to a cell of your Jira® data sheet. This example prints in each cell of your column the content of cell A1 (Row1/Colum1)
=R[0]C[-7] is a relative reference to a cell in your Jira® data sheet. This example writes the cells content which is looked up in same row (R[0]) and seven to the left ([C[-7]). This example is shown in the picture below.
=§key creates same result as described in example above by using a column name reference! Column names are field ID's (shown in [] behind field names in import setup). Use prefix "§" before the field ID to refer to a cell in a different column. Advantage compared to R[]C[] notation: when changing column order, all your formulas are still correct!
=if(R[0]C[-3]="High","TOPPRIO","noPrio") is a statement using the excel "if" command. It will look to the content of a cell 3 columns to the left. If the status is "High" it will write "TOPPRIO" to the column, otherwise "noPrio".

How to find out more?

An easy way to experiment with FormulaR1C1 is to change (temporarily) the way, excel displays formulars in your worksheet: Goto file->formular and checkmark "R1C1 Formular"
Another very nice way is to record a macro: start recording, insert a formular, stop recording. Then check how the formular was recorded by Excel in formularR1C1 format.

Below picture explains R1C1 index mechanism (see examples above):

Date and Time format

It is possible to define the format of imported date and time information.

Worklog mode and chart update

Chart update after data import: refreshes all pivot tables/charts after a new import
Worklog mode: uses one line for each worklog entry. If not selected, only a limited amount of worklog entries (20) is shown.

Import Issues

Click on "Import Issues" to start the import.

Frequently asked Questions

Where can I submit feature requests or bug reports?

Pls use our service desk portal: https://mirrorlake.atlassian.net/servicedesk/customer/portal/1

Why does the add-in disappear after quit+restart of excel?

In case the add-in disappears after quit and restart of microsoft excel, pls here a guideline how to solve that issue: https://www.excelcampus.com/vba/add-in-ribbon-disappears

Do I need to install the Jira® Plugin?

No. The Jira® add on is only needed, if multiple users want to use the excel2jira tool and you want to by a server based license. For single PC licenses you just need to buy an activation key for your PC.

Is there a maximum number of tickets I can import?

No, not anymore.

Third Party IP used in Mirrorlake Excel Importer

The followig IP was used in parts or completely in Mirrorlake Excel Importer.

VBA-JSON v2.0.1

VBA-JSON v2.0.1 (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON

JSON Converter for VBA Errors: 10001 - JSON parse error

The MIT License (MIT)

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Based originally on vba-json (with extensive changes) BSD license included below

JSONLib,

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: * Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. * Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. * Neither the name of the <organization> nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.