Dear All,

I need help with formatting excel spreedsheet, I have this code;

#! c:/perl/bin/perl.exe
#
require 5.008;
use strict;
use warnings 'all';

use vars qw /%data %xls/;
use constant {TRUE  => -1,FALSE => 0};

use win32;
use Win32::OLE;
use Win32::OLE::Const;

use DBD::ODBC;

my $input = join (" ",@ARGV);
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32:
+:OLE->new('Excel.Application');
#
# if I add the following line;
# my $xlConst = Win32::OLE::Const->Load('Microsoft Excel 8.0 Object Li
+brary');
#
#I get this error:
#
#No type library matching "Microsoft Excel 8.0 Object Library" found a
+t U:\go\test_for_raj2.pl line 18
#Win32::OLE(0.1701): GetOleTypeLibObject() Not a Win32::OLE::TypeLib o
+bject at C:/Perl/site/lib/Win32/OLE/Const.pm line 45.
#
my $Book = $Excel->Workbooks->Add; 
my $Sheet1 = $Book->Worksheets(1);

$Sheet1->PageSetup->{Orientation} = 'xlLandscape'; # this is not worki
+ng - nothing happens
$Sheet1->PageSetup->{PaperSize} = 'xlPaperA3'; # this is not working -
+ nothing happens

$Sheet1->Range("A1:U1")->{MergeCells} = 1;

$Sheet1->Cells(1,1)->Interior->{ColorIndex} = 50;
$Sheet1->Cells(1,1)->Font->{bold} = 1;
$Sheet1->Cells(1,1)->Font->{size} = 12;
$Sheet1->Cells(1,1)->Font->{ColorIndex} = 6;
$Sheet1->Cells(1,1)->{value}= "Seating ( ".$input." ) Exam Analysis - 
+students seating Heat Map";

$Sheet1->Columns->AutoFit(); # this is not working - nothing happens

$Sheet1->Range("A2:A7")->{MergeCells} = 1;
$Sheet1->Cells(2,1)->Interior->{ColorIndex} = 20;
$Sheet1->Cells(2,1)->{HorizontalAlignment} = 'xlHAlignCenter'; # this 
+is not working
# also want to make this cell text alignment vertical Center, horizont
+al left and wrap text
$Sheet1->Cells(2,1)->Font->{bold} = 1;
$Sheet1->Cells(2,1)->Font->{size} = 12;
$Sheet1->Cells(2,1)->BorderAround('xlBorderLineStyleContinuous','xlThi
+n',1); # this is not working - nothing happens
$Sheet1->Cells(2,1)->{value}= "Function Groups";

$Sheet1->Range("B2:B7")->{MergeCells} = 1;
$Sheet1->Cells(2,2)->Interior->{ColorIndex} = 20;
# want to make this cell text alignment vertical Center, horizontal le
+ft and wrap text 
# AND text orientation at 74 degrees
$Sheet1->Cells(2,2)->Font->{bold} = 1;
$Sheet1->Cells(2,2)->Font->{size} = 12;
$Sheet1->Cells(2,2)->BorderAround('xlBorderLineStyleContinuous','xlThi
+n',1); # this is not working - nothing happens
$Sheet1->Cells(2,2)->{value}= "Seats";

$Excel->{Visible} = 1;
[download]

The problems I am having (also documented within the code);

1- I don't know how to use Win32::OLE qw\With in\ to format the contects of cells as required above.
2- Cannnot import Excel constants to use for formatting the Cells.

Your help is highly appreciated

UPDATE: I found the code below which I wrote ages ago and thought that I have lost it. I have what I need as examples in the code.

#! c:/perl/bin/perl.exe
# FSL-SDS 21 <Frozen Server List-Servers Disk Space> by Blackadder Oct
+ 2001
use strict;
use Win32;
use Time::localtime;
use Win32::NetAdmin;
use Win32::AdminMisc;
use WIn32::Lanman;
use Win32::OLE qw ( in with );
use Win32::OLE::Const 'Microsoft Excel';
my @servers_list;
my ( $fsl_row, $sds_row, $fail_row ) = ( 1, 1, 1 );
my $no_errors = 1;
my $app = 'Excel.Application';
my $path = '\\\\srevershr04\\sa199800073\\uk_servers3.lis';
my $xls = Win32::OLE -> GetActiveObject ( $app ) || Win32::OLE -> new 
+( $app );
$xls -> { Visible } = 1;
$xls -> { SheetInNewWorkbook } = 3;
die "\nCannot start $app! : $!\n" unless $xls;
my $xls_book  = $xls -> Workbooks -> Add ( );
sub get_date
{
    my $tm = localtime;
    my ( $day, $mon, $year ) = ( $tm -> mday, $tm -> mon, $tm -> year 
+);
    ($day) . '-' . ($mon +=1) . '-' . ($year += 1900);
}
sub get_analyst
{
    my $usr = 'USER_FULL_NAME';
    my $usr_id = Win32::LoginName ( );
    my %attrib = ( );
    ( $attrib { $usr } ) if ( Win32::AdminMisc::UserGetMiscAttributes 
+( "\\\\sxmad05", $usr_id, \ %attrib ) );
}
sub check_dup
{
    my @list = @_;
    my ( %seen, @uniq ) = ( );
    for my $item ( @list )
    {
        $item =~ s/^[\W]+|\s*$//g;
        push ( @uniq, uc ( $item ) ) unless $seen{$item}++;
    }
    return @uniq;
}
sub xls_sheet
{
    my ( $sheet, $name, $width ) = @_;
    $sheet = 'Sheet' . $sheet;
    my $handle = $xls_book -> Worksheets ( "$sheet" );
    $handle -> { Name } =  "$name";
    $handle -> Range ( "a:a" ) -> Columns -> { ColumnWidth } = "$width
+";
    $handle -> Range ( "b:k" ) -> Columns -> { ColumnWidth } = "12";
    $handle -> Range ( "b:k" ) -> Columns -> { NumberFormat } =  "#,##
+0.00_);[Red](#,##0.00)";
    return $handle;
}
sub xls_str
{
    my ( $row, $col, $sheet, $font, $color, $str ) = @_;
    $sheet -> Cells ( $row, $col ) -> Font -> { FontStyle } = "$font" 
+if $font;
    $sheet -> Cells ( $row, $col ) -> Font -> { ColorIndex } = "$color
+" if $color;
    $sheet -> Cells ( $row, $col ) -> { Value } = "$str";
}
sub xls_box
{
    my ( $row, $col, $sheet, $line, $type, $color ) = @_;
    my @edges = qw ( xlEdgeBottom xlEdgeRight xlEdgeLeft xlEdgeTop xlI
+nsideHorizontal xlInsideVertical );
                        for my $edge ( @edges )
                        {
                            with ( my $borders = $sheet -> Cells ( $ro
+w, $col ) -> Borders ( eval ( $edge ) ),
                                        LineStyle        =>     $line,
                                        Waight            =>    $type,
                                        ColorIndex     =>    $color   
+ );
                        }
}
sub xls_title
{
    my ( $row, $sheet, $color,  $titles ) = @_;
    my @titles = split ( /,/, $titles );
    my $col = 1;
    for my $title ( @titles )
    {
        xls_str ( $row, $col, $sheet, 'bold', $color, "$title" );
        xls_box ( $row, $col++, $sheet, xlContinuous, xlThick ,  );
    }
}
# Splitting @ARGV to get lis files and xls template or to produce help
+.
push ( @ARGV, $path ) if ( ! scalar @ARGV );
Win32::MsgBox("Using : @ARGV",32,"FSL");
for my $list ( @ARGV )
{
    die "\nLST : $!\n" unless open ( LST, "$list" );
    chomp ( my ( @servers ) = <LST> );
    push ( @servers_list , @servers )
}
# For futur development: new data structure constructs will go in here
@servers_list = check_dup ( @servers_list );
my $row_adjust =0;
my $hsf =0;
my $fsl = xls_sheet ( 1, 'FSL', 22 );
my $sds = xls_sheet ( 2, 'SDS', 22 );
my $fail = xls_sheet ( 3, 'Failed', 55 );
xls_str ( $fsl_row, 1, $fsl, '', '', 'Analyst: ' . get_analyst );
xls_str ( $fsl_row++, 2, $fsl, '', '', 'Date: ' . get_date );
xls_title ( $fsl_row, $fsl, 1, 'Server Name, L o g i c a l     D r i v
+ e s' );
xls_title ( $sds_row, $sds, 1, 'Server Name, Disk, Size Mb, Free, Used
+, %Free' );
xls_title ( $sds_row, $fail, 1, 'Server Name : Error Message.....' );
for my $server ( sort @servers_list )
{
    next if ( $server eq '' );
    my ( $fsl_col, $sds_col, $fail_col     )    = ( 1, 1, 1 );
    if ( Win32::NetAdmin::GetServerDisks ( $server, \ my @ disks ) )
    {
        $row_adjust = scalar @disks;
        if ( $row_adjust > $hsf )
        {
            $hsf = $row_adjust;
            $hsf = $hsf - 3;
        }
        print "\nServer Name : $server \n";
        xls_str ( ++$fsl_row, $fsl_col++, $fsl, '', '', "$server" );
        xls_str ( ++$sds_row, $sds_col++, $sds, '', '', "$server" );
        for my $disk ( @disks )
        {
            next if ( ( lc $disk ) =~ /^a|^c|^d|^s/ );
            xls_str ( $fsl_row, $fsl_col, $fsl, '', '', "$disk" );
            xls_str ( $sds_row, $sds_col++, $sds, '', '', "$disk" );
            $disk =~ s/:/\$/;
            my $unc_path = "\\\\" . $server . "\\" . $disk . "\\";
            my ( $size, $free ) = ( Win32::AdminMisc::GetDriveSpace ( 
+"$unc_path" ) );
            if ( $size !=  0 )
            {
                $size = $size/1024/1024;
                $free = $free/1024/1024;
                my $used = $size - $free;
                my $pcent = ( ( $free / $size ) * 100 );
                xls_str ( $sds_row, $sds_col++, $sds, '', '', "$size" 
+);
                xls_str ( $sds_row, $sds_col++, $sds, '', '', "$free" 
+);
                xls_str ( $sds_row, $sds_col++, $sds, '', '', "$used" 
+);
                if ( $pcent < 50 )
                {
                    xls_str ( $sds_row++, $sds_col++, $sds, 'bold', '3
+', "$pcent" );
                    $fsl -> Cells ( $fsl_row, $fsl_col ) -> Interior -
+> { colorIndex } =3;
                }
                else
                {
                    xls_str ( $sds_row++, $sds_col++, $sds, 'bold', ''
+, "$pcent" );
                    $fsl -> Cells ( $fsl_row, $fsl_col ) -> Interior -
+> { colorIndex } =4;
                }
                opendir ( DIR, "$unc_path" ) || die "\nDIR : $!\n";
                while ( my $file = readdir ( DIR ) )
                {
                    if  ( ( lc $file ) =~ /^fsl/ )
                    {
                        open ( F,"$unc_path"."$file")||die "\n$! : $fi
+le\n";
                        chomp ( my $tag=<F>);
                        $fsl -> Cells ( $fsl_row, $fsl_col ) -> Interi
+or -> { colorIndex } =6;
                        $disk =~ s/\$//;
                        xls_str ( $fsl_row, $fsl_col, $fsl, '', '', "$
+disk : $tag" );
                        xls_box ( $fsl_row, $fsl_col, $fsl, xlContinuo
+us, xlThick, 1 );
                    }
                }
                close ( DIR );
                $fsl_col++;
            }
            $sds_col = 2;
        }
    }
    else
    {
        $no_errors = 0;
        xls_str ( ++$fail_row, $fail_col++, $fail, '', '', "$server : 
+$!" );
    }
}
$fsl -> Range ( $fsl -> Cells ( 2, 2 ) , $fsl -> Cells ( 2, $hsf +1) )
+ -> { MergeCells } = 1;
$fsl -> Cells ( 2, 2 ) -> { HorizontalAlignment } = xlHAlignCenter;
for (my $ount=2;$count<=$hsf+1; $count++)
{
    xls_box ( 2, $count, $fsl, xlContinuous, xlThick ,  );
}
$fsl_row = $fsl_row + 2;
$fsl -> Cells ( ++$fsl_row, 1 ) -> { Value } = "Chart Legend";
$fsl -> Cells ( $fsl_row, 2 ) -> { Value } = "FROZEN";
$fsl -> Cells ( $fsl_row, 2 ) -> Interior -> { Colorindex } = 3;
$fsl -> Cells ( $fsl_row, 3 ) -> { Value } = "Available";
$fsl -> Cells ( $fsl_row, 3 ) -> Interior -> { Colorindex } = 4;
$fsl -> Cells ( $fsl_row, 4 ) -> { Value } = "Reserved";
$fsl -> Cells ( $fsl_row, 4 ) -> Interior -> { Colorindex } = 6;
$fsl -> Cells ( $fsl_row, 5 ) -> { Value } = "FutureCap";
$fsl -> Cells ( $fsl_row, 5 ) -> Interior -> { Colorindex } = 5;
my $save = "@ARGV" . "FSL " . get_date . ".xls";
$xls -> ActiveWorkbook -> SaveAs( $save );
if ($no_errors)
{
    print "\nNo Errors\n";
    Win32::MsgBox (    Win32::FormatMessage(Win32::Lanman::GetLastMess
+age())
                                ."\nExcel file was saved, Path: $save"
+,
                                MB_ICONINFORMATION );
}
else
{
    print "Operation Completed. But there was some errors\nPlease chec
+k the 'Failed' tab. ";
    Win32::MsgBox (    Win32::FormatMessage(Win32::Lanman::GetLastMess
+age())
                                ."\nExcel file was saved, Path:$save",
                                MB_ICONINFORMATION );
}
[download]

Thanks for listening.