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

Hi All,

I am writing into excel using cgi/perl. When I write a number into a cell it is right aligned, though I want the left alignment. The choices I have are either format the cell left aligned or the whole worksheet formatting to text. Either of them is ok with me. How do I do this through perl? I am using Win32::OLE. Please find the code below. In summary, I'm opening a worksheet and then writing into the cells. Afterwards I am saving as CSV (Comma Separated) and closing the excel sheet. When I write into a cell, even if the value entered is number (i.e., 555) I want it to be left aligned instead of right aligned (which is the default for numbers) If I do the following, this is giving error. Think I am missing something :( 
       my    $format = $book->addformat(); # Add a format
       $format->set_align('right');
Below code is ok. I need to integrate the above one. 
my    $book    = $Excel->Workbooks->Add;
my    $sheet   = $book->Worksheets(1);

$sheet->Cells(1,1)->{Value} = $name;
$sheet->Cells(1,2)->{Value} = $address;
$sheet->Cells(1,3)->{Value} = $job;

$book->SaveAs( {FileName => "file1.csv", FileFormat =>6 , CreateBackup
+ => 0} );
$book->Close( {SaveChanges => 0} );

[download]
Thanks in advance, Raj.

Replies are listed 'Best First'.
Re: Formatting Excel cell using Perl
by Kanji (Parson) on May 06, 2002 at 22:10 UTC

    Error aside (and it would be helpful if you told us what the error was), you do realize that CSV has no provision for storing meta-data like formatting, right? Any attempt to do so will be lost as soon as you save and close.

        --k.


[reply]
Re: Formatting Excel cell using Perl
by particle (Vicar) on May 06, 2002 at 22:15 UTC
    Kanji has an excellent point.

    i believe something like this will do it... as long as you save as an excel file.

    $Sheet->Range($range)->{HorizontalAlignment} = xlHAlignCenter;

    i stole this snippet from cacharbe's great tutorial you'll find at Using Win32::OLE and Excel - Tips and Tricks

    ~Particle *accelerates*

[reply]
[d/l]
      Hi Kanji & Particle, 
      Thank you guys for the prompt reply. It did offer me good insight 
      into the way things happening. As Particle mentioned I able to 
      align in Excel but in CSV that should not matter.

      Thanks,
      Raj.
[reply]
        I've found that if I'm writing a script to generate an Excel-compatible spreadsheet with formatting, it's often easiest to generate an HTML table. HTML contains all of the formatting markup you could ever need, and Excel is smart enough to parse in the table structure and interpret it as spreadsheet cells.

        This may not help you with your current question, but for others that happen upon this thread, it might.

[reply]
Re: Formatting Excel cell using Perl
by strat (Canon) on May 07, 2002 at 12:28 UTC
    When I started playing around a bit with Excel and Perl, Excel's macro recorder helped me quite a lot finding out which methods and properties are available for a certain object.

    At first, I told excel to record everything I do from now on as a macro, and then manually did the changes I was interested in finding out how it worked, e.g. formatting a certain cell. Then I stopped the recording, and threw a look into the VbScriptcode generated by the macrorecorder, and tried to translate it somehow to perl. The only problems I had were so called standard properties that didn't work with perl (I just had to find out excactly which property is used), and constants that were not known in Win32::OLE. But by trying to output the constant's value in Excel, I usually found an integer I could use.

    Best regards,
    perl -le "s==*F=e=>y~\*martinF~stronat~=>s~[^\w]~~g=>chop,print"

[reply]

Back to Seekers of Perl Wisdom