Welcome to the Wiki of the Mirrorlake Excel Importer!
Pls find more information at below links:
Download and Install: latest releases and install instructions.
Documentation: documentation about the importer features
How to Buy: License models and how to buy activation keys or license plugin
Issue Tracker: submit feature requests and bug reports here
If you want to know more about mirrorlake software, pls. visit our website: http://www.mirrorlake.eu
For any other question just write to support@mirrorlake.eu
Download and 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.
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
In the connect setup global settings for the connection to the Jira® Server are configured. The window looks like shown below:
Use the "Check for Updates" button to check if you are working with the latest Excel Addin.
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 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 info@mirrorlake.eu.
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.
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):
It is possible to define the format of imported date and time information.
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.
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.