Solved

FME writes data to Excel with white font color ignoring Excel-Template

  • 12 October 2017
  • 9 replies
  • 9 views

Badge +7

Since I upgraded to latest Build 17650 I first had problems with a corrupt FME Installation where e.g. StatisticsCalculator outputted only NULL-values (see this thread).

Now I executed all our statistics batches finding out FME is now writing data in white font color with white borderlines, you can say as "white boxes". It ignores formatting of the template-Excelfile.

 

The data itself is there, but not visible because of white color. The XLS-Template used (since 4 years unchanged) has standard formating in black with light grey borderlines...

Are there any known bugs in the excel writer since Build 17291?

If you change font color and borderline color manually you can see the values:

icon

Best answer by andreaatsafe 13 October 2017, 20:49

View original

9 replies

Userlevel 3
Badge +13

Hi @reservoirdog, did this workspace work properly for you in 2017.0 (17291)? Are you able to share the workspace here or via a support case? I'd like to take a look to see what is going on.

Badge +7

Hey @TiaAtSafe,

Yes, Everything was working perfectly on 2017.0 (17291/17259).

 

Attached you can find the workspace (statistik-wa.zip), the Excel-Template (wa-vorlage.xlsx), current problematic Excel-Export (wa-stats-2017-10-12.xlsx) as well as an old correct Excel-Export (wa-stats-2017-10-03.xlsx)

Badge +7

In the meantime, I could get the fonts to be black again by re-assigning correct font colors in the template. Is it possible that Excel Writer in newer FME builds (from 17291 on) don't use standard font colors? Instead it uses white fonts, when nothing particular is assigned to the cells?

What I cannot fix at this moment, are the borderlines. They stay white no matter what I change them

 

in the template file... wa-stats-2017-10-13.xlsx

Badge +10

Hi @reservoirdog,

Thanks for sending along your workspace and the template file. After doing some tests and looking into this a little more, this is an existing issue with template files where cell font and background colours are ignored when using Microsoft "Theme" colours. (PR#59453) - we'll update here when the PR is resolved.

A way to work around this: In your template file, if you choose a standard colour or a custom colour/more colour for the background it appears to work as expected. For the cell font colour, you can select it to be the automatic colour, a standard colour or custom colour/more colour.

Let me know if you are still running into any issues.

Badge +7

Hi @AndreaEAtSafe

Thanks for your information, but I still can't get it to work.

 

I defined custom coloured backgrounds in the template wa-vorlage.xlsx, but FME still overwrites with

 

white backgrounds. What bothers me the most, are the missing cell borders. No matter how I define them in the template, they are always missing in the export... wa-stats-2017-10-16.xlsx

 

(ignore the first sheet, it just takes values of the other sheets, so FME doesn't write anything in the first sheet "Zusammenfassung")
Badge +10

Hi @AndreaEAtSafe

Thanks for your information, but I still can't get it to work.

 

I defined custom coloured backgrounds in the template wa-vorlage.xlsx, but FME still overwrites with

 

white backgrounds. What bothers me the most, are the missing cell borders. No matter how I define them in the template, they are always missing in the export... wa-stats-2017-10-16.xlsx

 

(ignore the first sheet, it just takes values of the other sheets, so FME doesn't write anything in the first sheet "Zusammenfassung")
@reservoirdog

 

 

I took a look at your updated template file and it looks like that the sheet “Wasserverteilung” had cells that were still being formatted as a theme (“40% - Accent3"). When I formatted the cells as a normal cell, and reformatted the area with the suggestions I made above (font and background colour) and added in the cell borders, the data was outputted properly.

 

 

I’ve attached an updated template file. Hopefully this works for you now!

 

wa-vorlage-update.xlsx

I am having similar issues since upgrading to FME 2017.1.0.0 build 17539 where FME overwrites the template format, is there a work around?

Badge +10

I am having similar issues since upgrading to FME 2017.1.0.0 build 17539 where FME overwrites the template format, is there a work around?

Hi @grattop, the work around is to set up your template file where the cell backgrounds are a standard colour or a custom colour/more colour. The same thing for the cell font colour, where you need to select the automatic colour button, or choose a standard or custom colour.

 

 

If you are still experiencing problems would you mind sharing your workspace and data? If you can't share it here, please submit a support ticket and I'll look into the problem.

 

Hi @grattop, the work around is to set up your template file where the cell backgrounds are a standard colour or a custom colour/more colour. The same thing for the cell font colour, where you need to select the automatic colour button, or choose a standard or custom colour.

 

 

If you are still experiencing problems would you mind sharing your workspace and data? If you can't share it here, please submit a support ticket and I'll look into the problem.

 

A ticket was submitted, see case number C133185

 

 

Reply