Setting Excel cell formats

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

I have found the tutorial Using Win32::OLE and Excel - Tips and Tricks
by cacharbe on Mar 22, 2002 at 04:56 UTC extremely useful.
However I could not find in this or elsewhere how to format an individual cell or group of cells.
I want to display times as min:sec. When I do this I find I get, for example, 22:00:00
I guess that the : is being used by Excel in the normal way for a numeric cell.
I can see two solutions namely
1. making those cells simply text cells;
2. making the cell have one of the ‘standard’ time formats where the : separates the parts or the time.
How are cell formats set in the way?
I am using

use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE qw(in with);
[download]

on a Win XP PC to manipulate Excel spreadsheets.

Comment on Setting Excel cell formats Download Code

Replies are listed 'Best First'.
Re: Setting Excel cell formats by Samy_rio (Vicar) on Jul 26, 2007 at 10:31 UTC
Hi merrymonk, you can customize the cell as below: `$sheet->Cells($st, 1)->{NumberFormat} = "mm:ss"; $sheet->Cells($st, 1)->{Value} = "15:59:12";` [download] Regards, Velusamy R. eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@\|6%,53!-9@2~j';	[reply] [d/l] [select]
Re^2: Setting Excel cell formats by merrymonk (Hermit) on Jul 26, 2007 at 14:13 UTC
Many thanks to all. The codes line were just what I wanted. I do have another setting question that I posted yesterday. As I have not had any replies I have taken the liberty of repeating it below. I want to change the values for an Excel spreadsheet that are set manually with File->page set up. Below is part of the code I am using. All works fine except the $sheet->set_margins_LR which has not effect at all `$workbook = $excel->Workbooks->Add; $sheet = $workbook->Worksheets("Sheet1"); $sheet->Activate(); $sheet->{Name} = "Tool List"; $sheet_margin = 2; $sheet->set_margins_LR($sheet_margin);` [download] What should I be doing?	[reply] [d/l]
Re^3: Setting Excel cell formats by Samy_rio (Vicar) on Jul 27, 2007 at 04:08 UTC
Hi merrymonk, set the left and right margin value like below `$sheet1->PageSetup->{LeftMargin} = $Excel->InchesToPoints("0.19"); $sheet1->PageSetup->{RightMargin} = $Excel->InchesToPoints("0.54");` [download] In the same way, you can set the value of the remaining margin. `TopMargin BottomMargin HeaderMargin FooterMargin` [download] Regards, Velusamy R. eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@\|6%,53!-9@2~j';	[reply] [d/l] [select]
Re: Setting Excel cell formats by Corion (Patriarch) on Jul 26, 2007 at 11:13 UTC
I often do such stuff by the method I outlined in Re^3: Problems with Microsft's new Office 'XML' and Re: Convert word(.doc) file to html file. It's the easiest, most mechanic way of getting Office to do what I want.	[reply]
Re: Setting Excel cell formats by fmerges (Chaplain) on Jul 26, 2007 at 10:21 UTC
Hi, See: http://www.ozgrid.com/Excel/CustomFormats.htm http://www.geocities.com/davemcritchie/excel/colors.htm Have also a look at Spreadsheet::WriteExcel and Spreadsheet::ParseExcel. Regards, fmerges at irc.freenode.net	[reply]


Syntactic Confectionery Delight
	PerlMonks