Spreadsheet::WriteExcel setting color in drop-down box

sushi2k has asked for the wisdom of the Perl Monks concerning the following question:

Hy perlmonks,

I've created a xls document with Spreadsheet::WriteExcel. I also used data-validation fields in some columns (like drop down boxes), so that a user can only put certain values in cells.

This is all going well, but I want to realise that a certain value gets a certain background color in his cell if he is choosen (like high => red, medium => orange, low => green).

Via the if-function I don't know how to do it, because I just can set the value of the cell either the condition is true or false. But I don't know how to set the format on a condition in a cell...

$worksheet->write_formula('A4', '=IF(A3>1,"Yes", "No")' );

Does anyone know how I can do that?

Comment on Spreadsheet::WriteExcel setting color in drop-down box

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel setting color in drop-down box by imrags (Monk) on Jul 17, 2009 at 10:42 UTC
If you are on Windows box, i'd highly recommend using Win32::OLE... Here's a very good link: Tek-Tips For WriteExcel formatting: You need to first add the format: `$footer = $excel->add_format(); $footer->set_font('Verdana'); $footer +->set_color('Blue'); $footer->set_size(9); $footer->set_bold(); $footer->set_top(); $fo +oter->set_left(); $footer->set_right(); $footer->set_bottom();` [download] Something similar to the above. Define this when the condition becomes true and then `$sheet->write($srow,$scol,'=FORMULA',$footer);` [download] Raghu	[reply] [d/l] [select]
Re: Spreadsheet::WriteExcel setting color in drop-down box by jrsimmon (Hermit) on Jul 17, 2009 at 11:56 UTC
This is the second time in two days that I will recommend Win32::OLE to you. I've never used Spreadsheet::WriteExcel because it does not do (or not well, at least) the things you are trying to do. Please spend some time with Win32::OLE -- you'll be much better off.	[reply]
Re: Spreadsheet::WriteExcel setting color in drop-down box by pdeshpan (Acolyte) on Jul 17, 2009 at 13:01 UTC
I suggest you to confirm that your work really needs excel. I have seen that .csv (comma separated values) file has many advantages; viz. apart from using facilities of excel the flexibility provided by editors can also be used. The excel related stuff like cell formatting etc. can be done once in a pre formatted excel file. This had worked for me pretty well.	[reply]
Re: Spreadsheet::WriteExcel setting color in drop-down box by biohisham (Priest) on Jul 17, 2009 at 15:49 UTC
Create a conditional format using the set_num_format()method, this enables you to distinguish between different cells values by assigning color backgrounds. I heard a lot of Monks mention things about WIN32::OLE module, I believe it has to be a strong one, so better try it out and learn it, I would want to learn it too....SpreadSheet::WriteExcel has some disadvantages I was not aware of until I have tried doing stuff and then crashing on the discovery that this module could not well do it satisfactorily or even not do it at all...here is a link to some more examples on SpreadSheet::WriteExcel that you might find useful http://www.koders.com/perl/fid2656ED1AA9FDA418F78129684CD3956AA785DC9D.aspx `$f_change->set_num_format('[color 10]0.00%; [Red]-0.00%;[Orange]0.0%') +; $worksheet->write(3,3,0,$f_change); $worksheet->write(2,3,-0.015,$f_change); worksheet->write(1,3,0.085,$f_change);` [download] using this set_num_format can allow you to give colors to positive percentage ranges, negative percentage ranges and zero...I have been there before, so this code, extracted from a program I wrote does not have bugs in it. Excellence is an Endeavor of Persistence. Chance Favors a Prepared Mind	[reply] [d/l]
Re: Spreadsheet::WriteExcel setting color in drop-down box by sushi2k (Novice) on Jul 20, 2009 at 15:07 UTC
Thx for all the answers. set_num_format can only be used to numbers (like the method name says it ;-) but I have to compare strings. And even if I replace high, medium and low with 1,0,-1 it only changes the font not the background color... Unfortunately Win32::OLE can only be used on Windows and I'm using Linux... So I have to look for another solutions. Thx anyway, and if I find something to solve my problem I will post it.	[reply]