NAME

Excel::CloneXLSX::Format - Convert Spreadsheet::ParseXLSX formats to Excel::Writer::XLSX

SYNOPSIS

use Excel::CloneXLSX::Format qw(translate_xlsx_format);
use Excel::Writer::XLSX;
use Safe::Isa;
use Spreadsheet::ParseXLSX;

my $old_workbook  = Spreadsheet::ParseXLSX->new->parse('t/data/sample.xlsx');
my $old_worksheet = $old_workbook->worksheet('Sheet1');

open my $fh, '>', 't/data/converted.xlsx'
    or die "Can't open output: $!";
my $new_workbook  = Excel::Writer::XLSX->new( $fh );
my $new_worksheet = $new_workbook->add_worksheet();

my ($row_min, $row_max) = $old_worksheet->row_range();
my ($col_min, $col_max) = $old_worksheet->col_range();
for my $row ($row_min..$row_max) {
    for my $col ($col_min..$col_max) {

        my $old_cell   = $old_worksheet->get_cell($row, $col);
        my $old_format = $old_cell->$_call_if_object('get_format');
        my $fmt_props  = translate_xlsx_format( $old_format );
        my $new_format = $new_workbook->add_format(%$fmt_props);
        $new_worksheet->write(
            $row, $col, ($old_cell->$_call_if_object('unformatted') || ''),
            $new_format
        );
    }
}

$new_workbook->close;

DESCRIPTION

CPAN has great modules for reading XLS/XLSX files (Spreadsheet::ParseExcel / Spreadsheet::ParseXLSX), and a great module for writing XLSX files (Excel::Writer::XLSX), but no module for editing XLSX files. This module... won't do that either. It will convert Spreadsheet::ParseExcel-style cell formats to a structure that Excel::Writer::XLSX will understand.

My hope is to eventually release an Excel::CloneXLSX module that will create a copy of a ::Parse* object, with hooks to modify the content.

USAGE

translate_xlsx_format( $cell->get_format() )

Takes the hashref returned from Spreadsheet::ParseExcel::Cell's get_format() method and returns a hashref that can be fed to Excel::Writer::XLSX's new_format() method.

What's Supported

• Font (Family, Style, Size, {Super,Sub}script)
• Background Color
• Alignment
• Border Style and Color

What isn't

• Foreground Color

  Trying to set the foreground color produces weird results. I think it might be a bug in Excel::Writer::XLSX, but I haven't yet investigated.

• Everything else

LICENSE

Copyright (C) Fitz Elliott.

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

AUTHOR

Fitz Elliott [email protected]