in reply to Spreadsheet::WriteExcel, rotate text


Merging cells is possible with Spreadsheet::WriteExcel and so is rotating text. However, strictly speaking it isn't possible to do both in the same cell or group of cells.

This is due to the fact that Spreadsheet::WriteExcel is currently based on the Excel5 file format where merging and rotation are mutually exclusive.

However Excel 97 and Excel 2000 ignore this fact and you can get the effect that you want using the code shown below. 

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;


    my $workbook  = Spreadsheet::WriteExcel->new('test.xls');
    my $worksheet = $workbook->addworksheet();

    my $format    = $workbook->addformat(
                                            border   => 1,
                                            color    => 'blue',
                                            merge    => 1,
                                            rotation => 1,
                                        );

    $worksheet->write('B2', 'Hello', $format);
    $worksheet->write('C2', '',      $format);
    $worksheet->write('D2', '',      $format);

[download]

However, you should consider this to be a kludge. ;-) Hopefully it will be possible to do this more robustly when the module moves to the Excel97+ format.
--
John.

Replies are listed 'Best First'.
Re: Re: Spreadsheet::WriteExcel, rotate text
by gnu@perl (Pilgrim) on Jul 11, 2002 at 16:48 UTC
    Thank you for your help. I have found another problem and then subsequently a solution for the writing vertically in a merged column. The trick is to write what you want to the destination cell and then merge the cells. If you merge them first and then write the data to the beginning cell of the merged column it only registers as being written to that cell and it is treated as though it is in a single cell rather than a merged column. Here is the working code: 
    #!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

# Variables
   my $file_name = 'switch_report.xls';
   my $sheet_title='Traffic Estimations Report -May 2002';
   my @array=(    ['Switch 1','','above cell blank','456','789'],
        ['Switch 2','DEF','GHI']
           );
   #print $1;
   #my $array_ref=\$1;
   my $array_ref=\@array;



# Create a new Excel workbook
   my $workbook = Spreadsheet::WriteExcel->new($file_name);

# Add a worksheet
   my $worksheet = $workbook->addworksheet();
   $worksheet->fit_to_pages(1,1);
   $worksheet->set_landscape();
   $worksheet->set_margins(.25);
# Add formats
   my $cb_format = $workbook->addformat(); # Add a format
   my $rotation_format = $workbook->addformat();

# Define Formats
#  $cb_format
   $cb_format->set_bold();
   $cb_format->set_italic();
   $cb_format->set_underline();
   $cb_format->set_align('center');
   $cb_format->set_size(18);

# $rotation_format
   $rotation_format->set_rotation(2);
   $rotation_format->set_bold();
   $rotation_format->set_align('vcenter');


# Write to the worksheet
   $worksheet->write(0,1,$sheet_title, $cb_format);
   $worksheet->merge_cells(0,1,2,29);
   $worksheet->merge_cells(3,1,3,29);

#$worksheet->write_row(11,1,$array_ref);

   $worksheet->write(4,0,"Day of the month",$rotation_format);
   $worksheet->merge_cells(4,0,37,0);


# write_switch_data
   my $row = 4;
   my $col = 1;
   $worksheet->write_row($row, $col, $array_ref);

    [download]
[reply]
[d/l]

      That's interesting. If you don't mind I'll add it to the Spreadsheet::WriteExcel documentation.

      It doesn't work with Excel 97 but it is still very useful.

      Thanks.
      --
      John.

[reply]
        Thanks, that would be great. I would love to see it added as a tip. I know that if I could not have done it I would have been completely stuck. I am automating a process that is done by hand at this time and (for the sake of the endusers) has to look the same as it did before.
[reply]

In Section Seekers of Perl Wisdom