Customizing JIRA Excel output

Microsoft Office 2000 supports Hypertext Markup Language (HTML) as a native file format, allowing Microsoft Excel to save and open HTML files as spreadsheets with all the document formatting preserved. The document can be formatted through a combination of HTML, XML and Cascading Style Sheets (CSS).

The format of the Excel output from JIRA can be modified by editing the template file searchrequest-excel-header.vm (located within src/etc/java/templates/plugins/searchrequestviews/ of your JIRA home folder). Editing this file is essentially editing a HTML template file with tables and styles.

By default the template file searchrequest-excel-header.vm should contain:

searchrequest-excel-header.vm

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

...
<style>
@page
{
mso-page-orientation:landscape;
margin:.25in .25in .5in .25in;
mso-header-margin:.5in;
mso-footer-margin:.25in;
mso-footer-data:"&R&P of &N";
mso-horizontal-page-align:center;
mso-vertical-page-align:center;
}

br
{
    mso-data-placement:same-cell;
}

td
{
    vertical-align: top;
}
</style>
...

The above formats the default excel output to have the page orientation as landscape, and multi-lined text will be kept within one cell.
Further examples are provided JIRA:below.

Microsoft Office HTML and XML Reference

Microsoft Office HTML and XML reference contains a thorough listing of available styles and details on how to use them. The reference manual is available for download from msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffxml/html/ofxml2k.asp 

Example 1. Customising Page Setup

Excel uses CSS and @page definitions to store some page setup settings and XML to store others. Below is a sample to setup the page with the following document format respectively.

• Set page orientation as landscape (default is portrait)
• Set the top, right, bottom and left margins as .75in .25in .75in .25in respectively
• Set header margin as .5in
• Set footer margin as .4in
• Set a custom footer displaying page number, date and time all centered.

1
2
3
4
5
6
7
8
9
10
11
12

<style>
...
@page
{
mso-page-orientation: landscape;
margin:.75in .25in .75in .25in;
mso-header-margin:.5in;
mso-footer-margin:.4in;
mso-footer-data:"&C Page &P of &N Date: &D Time: &T";
<!-- other @page styles ... -->
}
</style>

Example 2. Customising Cell Formatting

Both CSS and HTML are used to customise the cells. A global style that is applied to all table cells can be applied using TD definition.

1
2
3
4
5
6
7
8
9
10
11

<style>
...
TD
{
mso-numberformat:general;
text-align: left;
vertical-align: middle;
color:red
<!-- other TD styles ... -->
}
</style>

The above applies the following styles to all table cells respectively:

• Sets the number format of all table cells as general
• Horizontally aligns the text in the cell to the left
• Vertically aligns the text in the cell to the middle
• Set the text color to be red