Jira2Excel Importer / Documentation
This documentation is fading out. Pls find the latest documentation in our new Mirrorlake Support Confluence
Table of content:
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.
These fields are only needed in case you buy a single PC activation key. For more information visit https://bitbucket.org/mirrorlake/jira2excel-importer/wiki/How%20to%20Buy#markdown-header-license-model-1-activation-key-valid-for-single-pc or contact us at email@example.com.
#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
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.
Hello World!prints in each cell in that colum the text "Hello World"
=R1C1is 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)
=RC[-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) and seven to the left ([C[-7]). This example is shown in the picture below.
=§keycreates 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 RC notation: when changing column order, all your formulas are still correct!
=if(RC[-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.
#Create Pivot Charts#
#Frequently asked Questions# ##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 (c) Tim Hall - https://github.com/VBA-tools/VBA-JSON
JSON Converter for VBA Errors: 10001 - JSON parse error
The MIT License (MIT)
Copyright (c) 2015 Tim Hall
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
Copyright (c) 2013, Ryo Yokoyama All rights reserved.
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.