in reply to Formatting Excel blocks from query load

I'm not 100% sure I'm understanding your problem, cant you make multiple formats and supply the one you want when you write that column?

It may be terrible but I've like to go though spreadsheets cell by cell and format based on whatever that cell wants, this kinda demonstrates both. 

my $ssw      = Spreadsheet::WriteExcel->new('file.xls');
my $ss       = $ssw->add_worksheet($thing);
my $ssformat = $ssw->add_format();
my $ssborder = $ssw->add_format();
$ssformat->set_text_wrap();
$ssformat->set_border();

$ss->set_column( 'B:B', 21 ); 
for ( my $r = 0; $r <= $#ss; $r++ ) {
  for ( my $c = 0; $c <= $#{ $ss[$r] }; $c++ ) {
    if ( $c == 4 ) {
      $ss->write_string( $r, $c, ${ $ss[$r] }[$c], $ssformat );
    }
    else {
      $ss->write_string( $r, $c, ${ $ss[$r] }[$c], $ssborder );
    }
  }
}

[download]
In your code you're always writing to the same column, it's not clear how you get to different columns so I'm guessing at this.. 
for my $stmt (@{$ary_ref}) { 
   my $format = $stmt->{col} == 1 ? $format_a : $format_b;
   $worksheet->write_col(15,$col,$ary_ref,$format); 
   last; 
}

[download]

Replies are listed 'Best First'.
Re^2: Formatting Excel blocks from query load
by cocl04 (Sexton) on Apr 02, 2009 at 19:53 UTC

    I really appreciate your help. I figured it out. I did not use the cell by cell method. The data in a column can be handled the same. The most helpful thing was the write_string function. Instead of dumping the data into excel and formating all the data generally...I used the below method. It works great. Sometimes I just need a nug or idea...Again Thanks!!!

    this works...I set the formats by column... 

    ###############################
#
#  insert Data into spreadsheet
#
###############################

my $row = 1;
my $col = 0;


  while (
    my ( $TOTAL, $type, $calendar_date, $location, $rto_store,
         $change, $remodel_date, $LYTD_START_DATE) = $query->fetchrow_
+array() 
    ) {
      $row++;
      $col=0;
      $worksheet->write_string( $row, $col++, $TOTAL,$text_format );
      $worksheet->write_string( $row, $col++, $type,$text_format );
      $worksheet->write_date_time( $row, $col++, $calendar_date, $date
+_format );
      $worksheet->write_string( $row, $col++, $location,$text_format )
+;
      $worksheet->write_string( $row, $col++, $rto_store,$store_format
+);
      $worksheet->write_string( $row, $col++, $change,$text_format );
      $worksheet->write_date_time( $row, $col++, $remodel_date, $date_
+format );
      $worksheet->write_date_time( $row, $col++, $LYTD_START_DATE, $da
+te_format );
  $row++;

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom