Skip to main content
  • EN

FME Desktop 2019 2.3.3 (64-bit).

I'm making my first foray into using FME to format an Excel spreadsheet I'm writing. The output is the results of address matching. I want to highlight cells based on how good the match was. My first attempt was to assign a hex colour value e.g. #ffff00 to fme_fill_color then expose it as a format attribute in the Writer. This did nothing so I researched it and found the ExcelStyler.

Implementing this has slowed my Workspace down from 4 minutes to nearly 2.5 hours, and when I try to open the output XLSX, it says it's corrupt. Also, the XLSX jumps from 5Mb to 37Mb in size. I presume I'm doing something wrong. Can anyone help?

The first thing I do is set a colour value based on the match status/score:

CondVal

 

Then I set that as the background colour in the ExcelStyler (red in this screenshot because I've disconnected it):

ExcelStyler

 

Even after reading the documentation, I'm confused about Background Color and Pattern Color. Should I set one or the other or both?

These are the errors in the XLSX:

ERROR

 

repair

It's hard to say. Can you elaborate on a few things?

  • How many features are you processing?
  • What does the log window look like? Are there lots of messages being repeated there?

Usually I try stuff like this out with a smaller subset of data just to know I got it all right before letting it work on the full set, the Sampler can be useful in those cases.

It's hard to say. Can you elaborate on a few things?

  • How many features are you processing?
  • What does the log window look like? Are there lots of messages being repeated there?

Usually I try stuff like this out with a smaller subset of data just to know I got it all right before letting it work on the full set, the Sampler can be useful in those cases.

It's 1300 input records. Without the ExcelStyler it takes 4 mins to process. I'll plug it back in and see what the log reveals...

It's 1300 input records. Without the ExcelStyler it takes 4 mins to process. I'll plug it back in and see what the log reveals...

Okay, there's definitely something fishy going on. Before you start, probably best to use a Sampler and limit this to the first 20-30 records or so, saves you 2.5 hours of processing time. Also make sure you're overwriting the file rather than appending to it (which is the default for the Excel writer)

It's 1300 input records. Without the ExcelStyler it takes 4 mins to process. I'll plug it back in and see what the log reveals...

Thanks for the tip. I am truncating the sheet but not dropping it. I may try that. But the Excel styling worked when I only processed the first 100 records in the input XLSX.

 

I don't see any errors relating to the output, but there is this:

2020-08-10 12:11:44| 156.7| 0.0|INFORM|Excel Writer: No Handler for key 'CELL_BORDER_FORMATTING'

2020-08-10 12:11:44| 156.7| 0.0|INFORM|... Last line repeated 36 times ...

 

However it seems to do that even without the ExcelStyler.

It's 1300 input records. Without the ExcelStyler it takes 4 mins to process. I'll plug it back in and see what the log reveals...

I can't seem to reproduce the issue here, you could try setting the color in the ExcelStyler using a conditional value (I assume you're using an AttributeCreator or AttributeManager to do that beforehand), but I seriously doubt that that will have much of an effect.

 

It's 1300 input records. Without the ExcelStyler it takes 4 mins to process. I'll plug it back in and see what the log reveals...

Strange!

you could check out the 'xlsx_row_formatting' attribute to see if it looks weird at all. Another test is to set the attribute to a fixed color from the selector to see if there is any difference in performance of if the xlsx_row_formatting looks any different.

I had an issue with encoding when trying to use an AppearenceSetter - my input attribute was encoded in UTF-16 and the AppearenceSetter would just crash. It's quite a different process but there could be something weird going on. You did say it was working fine for 100 features so likely it not an issue like this but it's all I got... :-/

Reply


Community Stats

30,162
Posts
114,483
Replies
37,809
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.