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

All,

Is there a way to write to excel from a query using the below logic and format each column uniquely? 

foreach my $stmt (@{$ary_ref}) 
{ 
   $worksheet->write_col(15,0,$ary_ref,$format); 
   last; 
}

[download]
I know you can set a column like so…I have tried the set_column before the write_col and after. The write_col function seems to always overrides it. 
$worksheet->set_column('J:J',19.57,$format);

[download]
$worksheet->set_column('J15:J300',19.57,$format);

[download]
Basically, my code takes three different queries and writes to excel using the write_col function to write the data in three different locations on one worksheet. I want to handle the formatting of each section independently. I can format the block with a general format during the write_col. But I can't rap a grid around one block and format individual columns with dollar sign, percent sign, colors, etc. Any help will be greatly appreciated...

Replies are listed 'Best First'.
Re: Formatting Excel blocks from query load
by tilly (Archbishop) on Apr 02, 2009 at 05:34 UTC
    There are two solutions I can recommend. The simple solution is to write it originally with the right formats. This is unfortunately not always very doable. The more complex solution is to create your own in memory editable representation of what you want the spreadsheet to be, and then write the whole thing in one go once you've got it ready.

    In the past when I had to solve this problem I took the second solution. Unfortunately I can't show you that code because it is owned by a past employer. But if you're competent with OO code, it isn't that hard to create an OO representation of a spreadsheet...

[reply]
Re: Formatting Excel blocks from query load
by flummoxer (Initiate) on Apr 02, 2009 at 07:11 UTC
    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]
[reply]
[d/l]
[select]

      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]

Back to Seekers of Perl Wisdom