From e26631072fcf0fe885ed1215b6d4c80b6a719e3c Mon Sep 17 00:00:00 2001 From: Jesse Luehrs Date: Mon, 6 May 2013 18:15:33 -0500 Subject: start working on formats for xlsx (still a bit broken) --- lib/Spreadsheet/Template/Generator/Parser/Excel.pm | 44 ++++-- lib/Spreadsheet/Template/Generator/Parser/XLSX.pm | 163 ++++++++++++++++++++- 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 { -- cgit v1.2.3-54-g00ecf