ExcelTableWriter

Convenience object for writing a table into an Excel worksheet.

ExcelTableWriter does not manage the excel file, and instead takes parameters of the workbook and worksheet objects to use. This allows quite a bit of flexibility.

  my $xls= Excel::Writer::XLSX->new($fh);
  
  my $tw= RapidApp::Spreadsheet::ExcelTableWriter->new(
    wbook => $xls,
    wsheet => $xls->add_worksheet("MyData"),
    columns => [ 'Foo', 'Bar', 'Baz' ]
  );
  
  my $tw= RapidApp::Spreadsheet::ExcelTableWriter->new(
    wbook => $xls,
    wsheet => $xls->add_worksheet("MyData"),
    columns => [
      { name => 'foo_1', label => 'Foo', isString => 0 },
      { name => 'bar', label => 'Bar', format => $xls->add_format(bold => 1) },
      { name => 'baz', label => 'BAAAAZZZZZ!' },
    ],
    headerFormat => $xls->add_format(bold => 1, underline => 1, italic => 1),
  );
  
  $tw->writePreamble("Some descriptive text at the top of the file");
  $tw->writePreamble;
  $tw->writeHeaders;    # optional so long as writeRow gets called
  
  $tw->writeRow(1, 'John Doe', '1234 Reading Rd');
  $tw->writeRow( [ 2, 'Bob Smith', '1234 Eagle Circle');
  $tw->writeRow( { foo_1 => 3, bar => 'Rand AlThor', baz => 'Royal Palace, Cairhien' } );
  
  $tw->autosizeColumns;

curRow

Returns the next row that will be written by a call to writePreamble, writeHeadrs, or writeRow.

This value is read-only

excelColIdxToLetter

  print RapidApp::Spreadsheet::ExcelTableWriter->excelColIdxToLetter(35);
  # prints AM
  print $tableWriter->excelColIdxToLetter(0);
  # prints A

writePreamble

writePreamble writes each of its arguments into an Excel cell from left to right, and then increments the current row.

The only purpose of this routine is to conveniently increment the starting row while writing various bits of text at the start of the worksheet.

writeHeaders

writeHeaders takes no parameters and returns nothing. It simply writes out the column header row in the current headerFormat, and changes the state of the object to "writing rows".

writeheaders can only be called once. No more writePreamble calls can be made after writeHeaders.

writeRow

  $tableWriter->writeRow( \@rowdata );
  $tableWriter->writeRow( { col1_name => col1_val, col2_name => col2_val ... } );
  $tableWriter->writeRow( @rowData );

Arguments: \@rowdata or \%rowhash or @rowdata

Returns: true

The most optimal parameter is an array of elements in the same order as the columns were defined.

Alternatively, a hash can be used, with the name of the columns as keys.

If the first parameter is not a array/hash reference, the argument array is treated as the data array.

autosizeColumns

  $tableWriter->writeRow
  $tableWriter->writeRow
  $tableWriter->writeRow
  ...
  $tableWriter->autosizeColumns

Arguments: none

Returns: none

AutosizeColumns should be called after all data has been written. As each row is written, a max width is updated per column. Calling autosizeColumns sets the excel column width to these maximum values.