Question

ExcelStyler: Format cells or columns of XLSX Writer

  • 27 September 2018
  • 6 replies
  • 34 views
D

Hi there!

I tried to use ExcelStyler in order to lock cells and change background colors according to the value of an attribute (I have 4 types). For one type all columns must be locked, for an other one only a few, etc. But it doesn't work, my Excel remain without formatting. Also when I try to lock the cells in the User attributes / Manual / Edit formatting of my writer.

When I went to Writers / Update Feature Type / select my XLSX / Parameters, I saw 3 tickboxes (Read formulas, Read hyperlinks, Read formatting) that I tried to activate. Here I get a message "No matching feature type(s) found in the selected dataset."

Any idea what I should do?

Additionnal questions: do you know if there is a way to insert an Excel formula in a field? to put a text string (like "Link") with an hyperlink behind (http://...) ?

I hope you can help me!

Best regards

6 replies

H
Badge +2

Hi @davidvieille

Re - locked cells the transformer doc specifies: If set to Yes, the cell will not be editable if worksheet protection has been turned on in Microsoft Excel.

Can you confirm this is the case?

For your second question the answer to this post: https://knowledge.safe.com/questions/3266/create-hyperlinks-in-excel.html explains how you can insert hyperlinks or other formulas into Excel. You need to make an additional attribute with the same name as the attribute holding the visible value with an extension such as .hyperlink or .formula which holds the underlying link/sum.

D

Hi @davidvieille

Re - locked cells the transformer doc specifies: If set to Yes, the cell will not be editable if worksheet protection has been turned on in Microsoft Excel.

Can you confirm this is the case?

For your second question the answer to this post: https://knowledge.safe.com/questions/3266/create-hyperlinks-in-excel.html explains how you can insert hyperlinks or other formulas into Excel. You need to make an additional attribute with the same name as the attribute holding the visible value with an extension such as .hyperlink or .formula which holds the underlying link/sum.

Hi @hollyatsafe. Thank you for your reply.

Unfortunately, it seems like my Writer (XLSXW2) doesn't allow any formatting, whether it is with hyperlinks or locking/styling cells. I couldn't have turned on any protection in Excel since my workflow produces the Excel Writer. Isn't there an option somewhere to enable formatting? Maybe I do sthg wrong when I create the Excel writer?

H
Badge +2

Hi @hollyatsafe. Thank you for your reply.

Unfortunately, it seems like my Writer (XLSXW2) doesn't allow any formatting, whether it is with hyperlinks or locking/styling cells. I couldn't have turned on any protection in Excel since my workflow produces the Excel Writer. Isn't there an option somewhere to enable formatting? Maybe I do sthg wrong when I create the Excel writer?

Hi @davidvieille,

 

There should be an option in the Excel Writer to set this up. There is a Protect Sheet box that you will need to check - you can leave the password blank and then set the Protection Level to 'Specific Permissions' and select the options you wish to apply.

 

D

Hi @davidvieille

Re - locked cells the transformer doc specifies: If set to Yes, the cell will not be editable if worksheet protection has been turned on in Microsoft Excel.

Can you confirm this is the case?

For your second question the answer to this post: https://knowledge.safe.com/questions/3266/create-hyperlinks-in-excel.html explains how you can insert hyperlinks or other formulas into Excel. You need to make an additional attribute with the same name as the attribute holding the visible value with an extension such as .hyperlink or .formula which holds the underlying link/sum.

Hi @hollyatsafe,

I just created a new Excel Writer with Sheet protection as you suggested (blank password, Specific permission, select all items), and it already allowed me to edit formatting via the Feature Type Column formatting (see screenshot), which I couldn't do before. Thank you for the hint!

But neither the Excel Styler nor the hyperlink work (although I did exactly what was described in the suggested post in your first answer). What am I doing wrong?

D

Hi @hollyatsafe,

I just created a new Excel Writer with Sheet protection as you suggested (blank password, Specific permission, select all items), and it already allowed me to edit formatting via the Feature Type Column formatting (see screenshot), which I couldn't do before. Thank you for the hint!

But neither the Excel Styler nor the hyperlink work (although I did exactly what was described in the suggested post in your first answer). What am I doing wrong?

Sorry I forgot the screenshot:

 

H
Badge +2

Sorry I forgot the screenshot:

 

Hi @davidvieille,

 

Please find attached a workspace which covers adding hyperlinks, formulas and formatting (including locked cells) and using either the writer or ExcelStylers to achieve this. Please review it and let me know if I have missed anything.

 

Some points to mention:

 

  • The hyperlink and formula are created using the same attribute name as the cell you wish it to occupy plus the extension name and the value is the corresponding hidden link/sum.
  • Once you have protected the sheet you will need to tell it both the cells that are locked and unlocked - otherwise from my tests it seems to consider all cells locked.
  • For Colour fill of cells you need to choose the pattern type as well as colour (e.g. solid)
  • If you choose to use Excel stylers route then notice this adds another attribute with the .formatting extension - if these are not including in the writer then no styling will be applied.
FMW file: excelstyling.fmw

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
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.