start working on formats for xlsx (still a bit broken)

2 files changed, 189 insertions, 18 deletions

diff --git a/lib/Spreadsheet/Template/Generator/Parser/Excel.pm b/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
index 2c43b40..0045ce4 100644
--- a/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
+++ b/lib/Spreadsheet/Template/Generator/Parser/Excel.pm
@@ -114,19 +114,25 @@ sub _parse_cell {
             3 => 'vjustify',
         );
 
-        $format_data->{size} = $format->{Font}{Height};
-        $format_data->{color} = '#' . Spreadsheet::ParseExcel->ColorIdxToRGB(
-            $format->{Font}{Color}
-        ) unless $format->{Font}{Color} == 8; # XXX
-        $format_data->{bg_color} = '#' . Spreadsheet::ParseExcel->ColorIdxToRGB(
-            $format->{Fill}[1]
-        ) unless $format->{Fill}[1] == 64;
-        $format_data->{align} = $halign{$format->{AlignH}}
-            unless $format->{AlignH} == 0;
-        $format_data->{valign} = $valign{$format->{AlignV}}
-            unless $format->{AlignV} == 2;
-        $format_data->{text_wrap} = JSON::true
-            if $format->{Wrap};
+        if (!$format->{IgnoreFont}) {
+            $format_data->{size} = $format->{Font}{Height};
+            $format_data->{color} = $self->_color(
+                $format->{Font}{Color}
+            ) unless $format->{Font}{Color} eq '8'; # XXX
+        }
+        if (!$format->{IgnoreFill}) {
+            $format_data->{bg_color} = $self->_color(
+                $format->{Fill}[1]
+            ) unless $format->{Fill}[1] eq '64'; # XXX
+        }
+        if (!$format->{IgnoreAlignment}) {
+            $format_data->{align} = $halign{$format->{AlignH}}
+                unless $format->{AlignH} == 0;
+            $format_data->{valign} = $valign{$format->{AlignV}}
+                unless $format->{AlignV} == 2;
+            $format_data->{text_wrap} = JSON::true
+                if $format->{Wrap};
+        }
         my $wb = $self->excel;
         $format_data->{num_format} = $wb->{FormatStr}{$format->{FmtIdx}}
             unless $wb->{FormatStr}{$format->{FmtIdx}} eq 'GENERAL';
@@ -148,6 +154,18 @@ sub _filter_cell_contents {
     return $contents;
 }
 
+sub _color {
+    my $self = shift;
+    my ($color) = @_;
+
+    if ($color =~ /^#/) {
+        return $color;
+    }
+    else {
+        return '#' . Spreadsheet::ParseExcel->ColorIdxToRGB($color);
+    }
+}
+
 no Moose::Role;
 
 1;
diff --git a/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm b/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
index 33f4f27..3189785 100644
--- a/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
+++ b/lib/Spreadsheet/Template/Generator/Parser/XLSX.pm
@@ -21,14 +21,17 @@ sub _fixup_excel {
     my ($excel) = @_;
 
     my $book_xml = $self->_parse_xml("xl/workbook.xml");
-    $self->_parse_selected_sheet($excel, $book_xml);
+    $self->_parse_selected_sheet($excel, $book_xml->root);
+
+    my $styles_xml = $self->_parse_xml("xl/styles.xml");
+    $self->_parse_styles($excel, $styles_xml->root);
 
     for my $sheet ($excel->worksheets) {
         my $sheet_xml = $self->_parse_xml("xl/$sheet->{path}");
 
-        $self->_parse_cell_sizes($sheet, $sheet_xml);
-        $self->_parse_formulas($sheet, $sheet_xml);
-        $self->_parse_sheet_selection($sheet, $sheet_xml);
+        $self->_parse_cell_sizes($sheet, $sheet_xml->root);
+        $self->_parse_formulas($sheet, $sheet_xml->root);
+        $self->_parse_sheet_selection($sheet, $sheet_xml->root);
     }
 }
 
@@ -42,6 +45,143 @@ sub _parse_selected_sheet {
     $excel->{SelectedSheet} = defined($selected) ? 0+$selected : 0;
 }
 
+sub _parse_styles {
+    my $self = shift;
+    my ($excel, $root) = @_;
+
+    my %halign = (
+        none   => 0,
+        left   => 1,
+        center => 2,
+        right  => 3,
+        # XXX ...
+    );
+
+    my %valign = (
+        top    => 0,
+        center => 1,
+        bottom => 2,
+        # XXX ...
+    );
+
+    my $rels_xml = $self->_parse_xml("xl/_rels/workbook.xml.rels");
+    my ($theme_file) = map {
+        $_->att('Target')
+    } grep {
+        $_->att('Type') =~ m{/theme$};
+    } $rels_xml->root->find_nodes('//Relationships/Relationship');
+
+    my $theme_xml = $self->_parse_xml("xl/$theme_file");
+
+    my @colors = map {
+        $_->name eq 'a:sysClr' ? $_->att('lastClr') : $_->att('val')
+    } $theme_xml->root->find_nodes('//a:clrScheme/*/*');
+
+    my @fills = map {
+        my $fgcolor_node = $_->first_child('fgColor');
+        my $fgcolor = 64; # XXX
+        if ($fgcolor_node) {
+            $fgcolor = '#' . $colors[$fgcolor_node->att('theme')]
+                if defined $fgcolor_node->att('theme');
+            $fgcolor = $fgcolor_node->att('indexed')
+                if defined $fgcolor_node->att('indexed');
+        }
+        [
+            0, # XXX
+            $fgcolor,
+            0, # XXX
+        ]
+    } $root->find_nodes('//fills/fill/patternFill');
+
+    $excel->{FormatStr} = {
+        0 => 'GENERAL',
+        map {
+            $_->att('numFmtId') => $_->att('formatCode')
+        } $root->find_nodes('//numFmts/numFmt')
+    };
+
+    $excel->{Font} = [
+        map {
+            my $iHeight = 0+$_->first_child('sz')->att('val');
+            my $color_node = $_->first_child('color');
+            my $color = defined($color_node->att('theme'))
+                ? $colors[$color_node->att('theme')]
+                : substr($color_node->att('rgb'), 2, 6);
+            my $sFntName = $_->first_child('name')->att('val');
+
+            Spreadsheet::ParseExcel::Font->new(
+                Height         => $iHeight,
+                # Attr           => $iAttr,
+                Color          => "#$color",
+                # Super          => $iSuper,
+                # UnderlineStyle => $iUnderline,
+                Name           => $sFntName,
+
+                # Bold      => $bBold,
+                # Italic    => $bItalic,
+                # Underline => $bUnderline,
+                # Strikeout => $bStrikeout,
+            )
+        } $root->find_nodes('//fonts/font')
+    ];
+
+    # XXX what do applyFont, applyFill, and applyAlignment mean?
+    $excel->{Format} = [
+        map {
+            my $alignment = $_->first_child('alignment');
+
+            my $iFnt = $_->att('fontId');
+            my $iIdx = $_->att('numFmtId');
+            my $iAlH = $alignment
+                ? $halign{$alignment->att('horizontal') || 'none'}
+                : 0;
+            my $iWrap = $alignment
+                ? $alignment->att('wrapText')
+                : 0;
+            my $iAlV = $alignment
+                ? $valign{$alignment->att('vertical') || 'bottom'}
+                : 2;
+
+            Spreadsheet::ParseExcel::Format->new(
+                IgnoreFont      => !$_->att('applyFont'),
+                IgnoreFill      => !$_->att('applyFill'),
+                IgnoreBorder    => !$_->att('applyBorder'),
+                IgnoreAlignment => !$_->att('applyAlignment'),
+
+                FontNo => $iFnt,
+                Font   => $excel->{Font}[$iFnt],
+                FmtIdx => $iIdx,
+
+                # Lock     => $iLock,
+                # Hidden   => $iHidden,
+                # Style    => $iStyle,
+                # Key123   => $i123,
+                AlignH   => $iAlH,
+                Wrap     => $iWrap,
+                AlignV   => $iAlV,
+                # JustLast => $iJustL,
+                # Rotate   => $iRotate,
+
+                # Indent  => $iInd,
+                # Shrink  => $iShrink,
+                # Merge   => $iMerge,
+                # ReadDir => $iReadDir,
+
+                # BdrStyle => [ $iBdrSL, $iBdrSR,  $iBdrST, $iBdrSB ],
+                # BdrColor => [ $iBdrCL, $iBdrCR,  $iBdrCT, $iBdrCB ],
+                # BdrDiag  => [ $iBdrD,  $iBdrSD,  $iBdrCD ],
+                Fill     => $fills[$_->att('fillId')],
+            )
+        } $root->find_nodes('//cellXfs/xf')
+    ];
+
+    for my $sheet ($excel->worksheets) {
+        my $sheet_xml = $self->_parse_xml("xl/$sheet->{path}");
+
+        $self->_parse_sheet_formats($excel, $sheet, $sheet_xml->root);
+    }
+}
+
 sub _parse_cell_sizes {
     my $self = shift;
     my ($sheet, $root) = @_;
@@ -93,6 +233,19 @@ sub _parse_sheet_selection {
     $sheet->{Selection} = [ $self->_cell_to_row_col($cell) ];
 }
 
+sub _parse_sheet_formats {
+    my $self = shift;
+    my ($excel, $sheet, $root) = @_;
+
+    for my $cell ($root->find_nodes('//c')) {
+        my $idx = $cell->att('s');
+        next unless defined $idx;
+        my $cell_id = $cell->att('r');
+        my ($row, $col) = $self->_cell_to_row_col($cell_id);
+        $sheet->get_cell($row, $col)->{Format} = $excel->{Format}[$idx];
+    }
+}
+
 sub _parse_xml {
     my $self = shift;
     my ($subfile) = @_;
@@ -109,7 +262,7 @@ sub _parse_xml {
     my $xml = XML::Twig->new;
     $xml->parse($contents);
 
-    return $xml->root;
+    return $xml;
 }
 
 sub _cell_to_row_col {