NAME

Data::Table::Excel - Convert between Data::Table objects and Excel (xls/xlsx) files.

SYNOPSIS

  News: The package now includes "Perl Data::Table Cookbook" (PDF), which may serve as a better learning material.
  To download the free Cookbook, visit https://sites.google.com/site/easydatabase/

  use Data::Table::Excel qw (tables2xls xls2tables tables2xlsx xlsx2tables);

  # read in two CSV tables and generate an Excel .xls binary file with two spreadsheets
  my $t_category = Data::Table::fromFile("Category.csv");
  my $t_product = Data::Table::fromFile("Product.csv");
  # the workbook will contain two sheets, named Category and Product
  # parameters: output file name, an array of tables to write, and their corresponding names
  tables2xls("NorthWind.xls", [$t_category, $t_product], ["Category","Product"]);

  # read in NorthWind.xls file as two Data::Table objects
  my ($tableObjects, $tableNames)=xls2tables("NorthWind.xls");
  for (my $i=0; $i<@$tableNames; $i++) {
    print "*** ". $tableNames->[$i], " ***\n";
    print $tableObjects->[$i]->csv;
  }

  Outputs:
  *** Category ***
  CategoryID,CategoryName,Description
  1,Beverages,"Soft drinks, coffees, teas, beers, and ales"
  2,Condiments,"Sweet and savory sauces, relishes, spreads, and seasonings"
  3,Confections,"Desserts, candies, and sweet breads"
  ...
  
  *** Product ***
  ProductID,ProductName,CategoryID,UnitPrice,UnitsInStock,Discontinued
  1,Chai,1,18,39,FALSE
  2,Chang,1,19,17,FALSE
  3,Aniseed Syrup,2,10,13,FALSE
  ...

  # to deal with Excel 2007 format (.xlsx), use xlsx2tables instead.
  # since no table name is supplied, they will be named Sheet1 and Sheet2.
  # here we also provide custom colors for each sheet, color array is for [OddRow, EvenRow, HeaderRow]

  tables2xlsx("NorthWind.xlsx", [$t_category, $t_product], undef, [['silver','white','black'], [45,'white',37]]);
  # read in NorthWind.xlsx file as two Data::Table objects
  my ($tableObjects, $tableNames)=xlsx2tables("NorthWind.xlsx");
  # note: Spreadsheet::XLSX module is used to parse .xlsx file. Please make sure it is updated.

ABSTRACT

This perl package provide utility methods to convert between an Excel file and Data::Table objects. It then enables you to take advantage of the Data::Table methods to further manipulate the data and/or export it into other formats such as CSV/TSV/HTML, etc.

DESCRIPTION

To read and write Excel .xls (2003 and prior) format, we use Spreadsheet::WriteExcel and Spreadsheet::ParseExcel; to read and write Excel .xlsx (2007 format), we use Spreadsheet::XLSX and Excel::Writer::XLSX. If this module gives incorrect results, please check if the corresponding Perl modules are updated.

xls2tables ($fileName, $sheetNames, $sheetIndices)

xlsx2tables ($fileName, $sheetNames, $sheetIndices)

xls2tables is for reading Excel .xls files (binary, 2003 and prior), xlsx2table is for reading .xlsx file (2007, compressed XML format).

$fileName is the input Excel file. $sheetNames is a reference to an array of sheet names. $sheetIndices is a reference to an array of sheet indices. If neither $sheetNames or $sheetIndices is provides, all sheets are converted into table objects, one table per sheet. If $sheetNames is provided, only sheets found in the @$sheetNames array is converted. If $sheetIndices is provided, only sheets match the index in the @$sheetIndices array is converted (notice the first spreadsheet has an index of 1).

The method returns an array ($tableObjects, $tableNames). $tableObjects is a reference to an array of Data::Table objects. $tableNames is a reference to an array of sheet names, corresponding to $tableObjects.

  # print each of spreadsheet into an HTML table on the web
  ($tableObjects, $tableNames)=xls2tables("Tables.xls");
  foreach my $t (@$tableObjects) {
    print "<h1>", shift @$tableNames, "</h1><br>";
    print $t->html;
  }

  ($tableObjects, $tableNames)=xlsx2tables("Tables.xlsx", undef, [1]);

This will only read the first sheet. By providing sheet names or sheet indicies, you save time if you are not interested in all the sheets.

tables2xls ($fileName, $tables, $names, $colors, $portrait)

tables2xlsx ($fileName, $tables, $names, $colors, $portrait)

table2xls is for writing Excel .xls files (binary, 2003 and prior), xlsx2table is for writing .xlsx file (2007, compressed XML format).

$fileName is used to name the output Excel file. $tables is a reference to an array of Data::Table objects to be write into the file, one sheet per table. $names is a reference to an array of names used to name Spreadsheets, if not provided, it uses "Sheet1", "Sheet2", etc. $colors is a reference to an array of reference to a color array. Each color array has to contains three elements, defining Excel color index for odd rows, even rows and header row. Acceptable color index (or name) is defined by the docs\palette.html file in the CPAN Spreadsheet::WriteExcel package.

$portrait is a reference to an array of orientation flag (0 or 1), 1 is for Portrait (the default), where each row represents a table row. In landscape (0) mode, each row represents a column. (Similar to Data::Table::html and Data::Table::html2).

The arrays pointed by $names, $colors and $portraits should be the same length as that of $tables. these customization values are applied to each table objects sequentially. If a value is missing for a table, the method will use the setting from the previous table.

  tables2xls("TwoTables.xls", [$t_A, $t_B], ["Table_A","Table_B"], [["white","silver","gray"], undef], [1, 0]);

This will produce two spreadsheets named Table_A and Table_B for table $t_A and $t_B, respectively. The first table is colored in a black-white style, the second is colored by the default style. The first table is the default portrait oritentation, the second is in the transposed orientation.

AUTHOR

Copyright 2008, Yingyao Zhou. All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

Please send bug reports and comments to: easydatabase at gmail dot com. When sending bug reports, please provide the version of Data::Table::Excel.pm, the version of Perl.

SEE ALSO

  Data::Table.