Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options
 
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 taking refuge in the Monastery: (7)
As of 2014-10-02 05:17 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    What is your favourite meta-syntactic variable name?














    Results (49 votes), past polls