Custom Field (Scripted Field from Script Runner) not showing up in Timesheet Report Excel Export

[Martin Kellner]

If I want to display a custom field in the timesheet report as an additional field, it is shown in the Browser view, but it is missing in the Excel Export. I appended two screen shots.

We are on Jira 7.1.7 and Timesheet report 3.0.7.1.

[Andriy Zhdanov]

Hi Martin,

Could you please check downloaded xls file source and see if corresponding value is empty or it's just can not be displayed by Excel?

Note, downloaded file is html, so you can see source in any text editor and locate corresponding table row.

Thank you.

[Martin Kellner]

Hi Andriy,

this seems to be the problem. Saving the file with a html extension let's me open it in my browser and all columns are visible. What can we do to make it work in Excel?

Regards Martin Kellner

[Andriy Zhdanov]

I will try to find a way to retrieve text/plain value for such fields for excel view.

[Martin Kellner]

Hi Andriy,

one more information. We have two scripted fields, that are configured in the same way. The scripted fields that makes the trouble returns html to give the field a coloured background in dependency of the value. The second field that works correctly, returns plain text. Both seem to work in the browser view of the report. The one with the html makes the trouble in excel.

Regards Martin Kellner

[Andriy Zhdanov]

I try similar scenario with Epic Link field. In browser it is displayed similarly with html like the following:

<a class="aui-label ghx-label-1" href="http://localhost:2990/jira/browse/TIME-5">
    Test Epic
</a>

And it is displayed in Excel as link, just without coloring.

Could you check if you can fix your scripted field to produce html that can be displayed in excel safely?

[Martin Kellner]

Hi Andriy, If I'm in Excel and change the font color to something different than white, the text comes back. I set the font color to white in my script field. Do you remove the background color and keep the font color when generating the html file for the excel link?

Regards Martin Kellner

[Andriy Zhdanov]

No, plugin does not remove anything, though you may see it generated html, does it have background specified? Or it is defined in css that is not included in xls?

[Martin Kellner]

Yes, sorry. Of course the color is ok, when viewing the file as html in a browser. If I write a excel file as HTML it generates a separate css file with the styles.

So, is there a specification what html is accepted by Excel? Or any examples without reverse engineering an Excel export?

[Andriy Zhdanov]

Sorry, don't get what you mean by "write excel file as HTML"?

Could you please copy paste HTML you see for the field in generated xls.

[Martin Kellner]

"write a excel file as HTML" means: I'm in Excel and save the file as HTML.

This is the cell that is displayed with white font on white background. The correct background color is green (the green from the first screen shot): <div style=white-space:pre;background-color:#008000;color:white> 152318 - ECS Kundenbetreuung 2016 </div>

[Andriy Zhdanov]

Well, that's strange that background-color is ignored, but font color is applied. Sorry, I don't know what html can be accepted by Excel, and I have just tried the same code in my Excel, but everything is ignored in my case, and text is displayed normally, though I'm using LibreOffice.

Though I've just found something useful: http://unixmonkey.net/?p=41

3) Certain colors and properties don't work:
If you use a color like #ECECEC (a light gray), it will come out white, If you use #CCCCCC (a darker gray) it will work. Stick to the basics like #000000 (black) if you can.

May beif you change background color to #00cc00 it will work?

Note, you may edit generated xls to quickly test the idea.

[Martin Kellner]

I tried it with a few color. it didn't work.

If I put the Background-color in the td-tag it works .... <td nowrap width="1%" style=background-color:#008000>

Strange Excel.

[Andriy Zhdanov]

Well, makes sense actually, background can be applied to the whole cell only in Excel, basing on the information we have. It's strange though font color is applied - it also should be applied to the cell itself.

May be you can change font color to something that Excel will ignore and display in black, but will work in html? E.g. #ECECEC (light gray)

[Martin Kellner]

e.g. #ECECEC is not ignored. This trick might have worked in 2010 ;-) So I will ask my colleagues to select the whole excel sheet and set the font color to "automatic".

[Andriy Zhdanov]

Well, ok, I wish I could help better.