Skip to main content
Question

ExcelStyler adds 2 hours to run time and 30 Mb to output! What am I doing wrong?!

tim_wood
Contributor
Forum|alt.badge.img+8

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

6 replies

redgeographics
Celebrity
Forum|alt.badge.img+47

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.

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • August 10, 2020
redgeographics wrote:

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...

redgeographics
Celebrity
Forum|alt.badge.img+47
tim_wood wrote:

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)

tim_wood
Contributor
Forum|alt.badge.img+8
  • tim_woodContributor
  • Author
  • Contributor
  • August 10, 2020
tim_wood wrote:

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.

redgeographics
Celebrity
Forum|alt.badge.img+47
tim_wood wrote:

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.

 

virtualcitymatt
Celebrity
Forum|alt.badge.img+34
tim_wood wrote:

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


Most helpful members this week

Community Stats

30,859
Posts
116,996
Replies
38,689
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.