Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Setting Excel cell formats

by merrymonk (Friar)
on Jul 26, 2007 at 09:52 UTC ( #628886=perlquestion: print w/ replies, xml ) Need Help??
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);
on a Win XP PC to manipulate Excel spreadsheets.

Comment on Setting Excel cell formats
Download Code
Re: Setting Excel cell formats
by fmerges (Chaplain) on Jul 26, 2007 at 10:21 UTC
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";

    Regards,
    Velusamy R.


    eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@|6%,53!-9@2~j';

      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);
      What should I be doing?

        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");

        In the same way, you can set the value of the remaining margin.

        TopMargin BottomMargin HeaderMargin FooterMargin

        Regards,
        Velusamy R.


        eval"print uc\"\\c$_\""for split'','j)@,/6%@0%2,`e@3!-9v2)/@|6%,53!-9@2~j';

Re: Setting Excel cell formats
by Corion (Pope) on Jul 26, 2007 at 11:13 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://628886]
Approved by clinton
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2014-08-30 16:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (293 votes), past polls