package Spreadsheet::XML_to_XLS;
use strict;
use warnings;
our $VERSION = '0.02';
use XML::DOM;
use Spreadsheet::WriteExcel;
my $parser;
my $doc;
my $book;
my %styles;
my %styleStore;
my @colorPalette = ();
my %colorStore = ();
sub build {
my (undef, $inputFile, $outputFile) = @_;
die "Cen't open $inputFile" unless -r $inputFile;
$parser = new XML::DOM::Parser;
$doc = $parser->parsefile($inputFile);
$book = Spreadsheet::WriteExcel->new($outputFile);
%styles = &parse_styles($doc);
%styleStore = ();
%colorStore = ();
$colorPalette[8] = 'black';
$colorPalette[9] = 'white';
$colorPalette[10] = 'red';
$colorPalette[11] = 'lime';
$colorPalette[12] = 'blue';
$colorPalette[13] = 'yellow';
$colorPalette[14] = 'magenta';
$colorPalette[15] = 'cyan';
$colorPalette[16] = 'brown';
$colorPalette[17] = 'green';
$colorPalette[18] = 'navy';
$colorPalette[20] = 'purple';
$colorPalette[22] = 'silver';
$colorPalette[23] = 'gray';
$colorPalette[53] = 'orange';
foreach my $worksheet ($doc->getElementsByTagName("worksheet")) {
my $wsheet = {};
my $attr = $wsheet->{'attr'} = &get_attributes($worksheet);
&extend_style($styles{"default"}, $wsheet->{'attr'}) if exists $styles{"default"};
$attr->{'col'} = 0 unless exists $attr->{'col'} and $attr->{'col'} =~ /^\d+$/;
$attr->{'row'} = 0 unless exists $attr->{'row'} and $attr->{'row'} =~ /^\d+$/;
$attr->{'type'} = 'col' unless exists $attr->{'type'};
$attr->{'name'} = '' unless exists $attr->{'name'};
$wsheet->{'tag'} = 'worksheet';
$wsheet->{'data'} = [];
$wsheet->{'top'} = $wsheet;
$wsheet->{'mapCells'} = {};
$wsheet->{'widthCols'} = {};
$wsheet->{'heightRows'} = {};
$wsheet->{'images'} = [];
$wsheet->{'charts'} = [];
$wsheet->{'namedsets'} = {};
&parse_a_set($worksheet, $wsheet);
&calc_position($wsheet);
&write_excel($wsheet);
}
$book->close();
}
sub calc_position {
my $wsheet = shift;
my $attr = $wsheet->{'attr'};
$attr->{'col'} = 0 unless exists $attr->{'col'};
$attr->{'row'} = 0 unless exists $attr->{'row'};
$attr->{'maxcol'} = 0;
$attr->{'maxrow'} = 0;
if ($wsheet->{'tag'} eq 'cell') {
$attr->{'maxcol'} = $attr->{'mincol'} = $attr->{'col'};
$attr->{'maxrow'} = $attr->{'minrow'} = $attr->{'row'};
if (exists $attr->{'colspan'} and $attr->{'colspan'} =~ /^(\d+)$/) {
my $num = $1;
if ($num > 1) {
$attr->{'maxcol'} += $num - 1;
delete $attr->{'align'} if exists $attr->{'align'};
$attr->{'center_across'} = 1;
for (my $col = $attr->{'mincol'} + 1; $col <= $attr->{'maxcol'}; $col++) {
my $hash = {};
$hash->{'top'} = $wsheet->{'top'};
$hash->{'tag'} = 'cell';
$hash->{'col'} = $col;
$hash->{'row'} = $attr->{'maxrow'};
%{$hash->{'attr'}} = %$attr;
$hash->{'attr'}->{'blank'} = 1;
$hash->{'top'}->{'mapCells'}->{$attr->{'minrow'}}->{$col} = $hash;
}
}
}
$wsheet->{'top'}->{'mapCells'}->{$attr->{'minrow'}}->{$attr->{'mincol'}} = $wsheet;
}
if (exists $wsheet->{'data'}) {
unless (exists $attr->{'type'}) {
if ($wsheet->{'parent'}->{'attr'}->{'type'} eq 'col') {
$attr->{'type'} = 'row';
} else {
$attr->{'type'} = 'col';
}
}
my $curcol = $attr->{'col'};
my $currow = $attr->{'row'};
foreach my $child (@{$wsheet->{'data'}}) {
my $cattr = $child->{'attr'};
&calc_start_for_child($cattr, 'col', $curcol);
&calc_start_for_child($cattr, 'row', $currow);
&calc_position($child);
$attr->{'maxcol'} = $cattr->{'maxcol'} if $attr->{'maxcol'} < $cattr->{'maxcol'};
$attr->{'maxrow'} = $cattr->{'maxrow'} if $attr->{'maxrow'} < $cattr->{'maxrow'};
if ($attr->{'type'} eq 'col') {
$currow = $attr->{'maxrow'} + 1;
} else {
$curcol = $attr->{'maxcol'} + 1;
}
}
}
$attr->{'mincol'} = $attr->{'maxcol'} unless exists $attr->{'mincol'};
$attr->{'minrow'} = $attr->{'maxrow'} unless exists $attr->{'minrow'};
if (exists $wsheet->{'data'}) {
foreach my $child (@{$wsheet->{'data'}}) {
my $cattr = $child->{'attr'};
$attr->{'mincol'} = $cattr->{'mincol'} if $attr->{'mincol'} > $cattr->{'mincol'};
$attr->{'minrow'} = $cattr->{'minrow'} if $attr->{'minrow'} > $cattr->{'minrow'};
}
}
if ($wsheet->{'tag'} eq 'set') {
my %sattr = &attr_for_skipped_cell($wsheet);
my $map = $wsheet->{'top'}->{'mapCells'};
if (keys %sattr > 0) {
for (my $row = $attr->{'minrow'}; $row <= $attr->{'maxrow'}; $row++) {
for (my $col = $attr->{'mincol'}; $col <= $attr->{'maxcol'}; $col++) {
next if exists $map->{$row}->{$col};
my $hash = {};
$hash->{'tag'} = 'cell';
%{$hash->{'attr'}} = %sattr;
$map->{$row}->{$col} = $hash;
}
}
}
if (exists $attr->{'border_common'}) {
my $border_common = $attr->{'border_common'};
my $mincol = $attr->{'mincol'};
my $maxcol = $attr->{'maxcol'};
my $minrow = $attr->{'minrow'};
my $maxrow = $attr->{'maxrow'};
for (my $col = $mincol; $col <= $maxcol; $col++) {
$map->{$minrow}->{$col}->{'attr'}->{'border_top'} = $border_common;
$map->{$maxrow}->{$col}->{'attr'}->{'border_bottom'} = $border_common;
}
for (my $row = $minrow; $row <= $maxrow; $row++) {
$map->{$row}->{$mincol}->{'attr'}->{'border_left'} = $border_common;
$map->{$row}->{$maxcol}->{'attr'}->{'border_right'} = $border_common;
}
delete $attr->{'border_common'};
}
if (exists $attr->{'border_common_color'}) {
my $border_common_color = $attr->{'border_common_color'};
my $mincol = $attr->{'mincol'};
my $maxcol = $attr->{'maxcol'};
my $minrow = $attr->{'minrow'};
my $maxrow = $attr->{'maxrow'};
for (my $col = $mincol; $col <= $maxcol; $col++) {
$map->{$minrow}->{$col}->{'attr'}->{'border_top_color'} = $border_common_color;
$map->{$maxrow}->{$col}->{'attr'}->{'border_bottom_color'} = $border_common_color;
}
for (my $row = $minrow; $row <= $maxrow; $row++) {
$map->{$row}->{$mincol}->{'attr'}->{'border_left_color'} = $border_common_color;
$map->{$row}->{$maxcol}->{'attr'}->{'border_right_color'} = $border_common_color;
}
delete $attr->{'border_common_color'};
}
if ($wsheet->{'parent'}->{'tag'} eq 'worksheet') {
foreach my $name (keys %{$wsheet->{'parent'}->{'namedsets'}}) {
my $set = $wsheet->{'parent'}->{'namedsets'}->{$name};
my $attr = $set->{'attr'};
next if exists $attr->{'mincol'};
if (exists $attr->{'col'} and not exists $attr->{'row'}) {
$attr->{'col'} = $set->{'parent'}->{'attr'}->{'mincol'} + $1 if $attr->{'col'} =~ /^\+(\d+)$/;
$attr->{'mincol'} = $attr->{'maxcol'} = $attr->{'col'};
$attr->{'minrow'} = $set->{'parent'}->{'attr'}->{'minrow'};
$attr->{'maxrow'} = $set->{'parent'}->{'attr'}->{'maxrow'};
}
if (not exists $attr->{'col'} and exists $attr->{'row'}) {
$attr->{'row'} = $set->{'parent'}->{'attr'}->{'minrow'} + $1 if $attr->{'row'} =~ /^\+(\d+)$/;
$attr->{'minrow'} = $attr->{'maxrow'} = $attr->{'row'};
$attr->{'mincol'} = $set->{'parent'}->{'attr'}->{'mincol'};
$attr->{'maxcol'} = $set->{'parent'}->{'attr'}->{'maxcol'};
}
}
}
}
}
sub calc_start_for_child {
my ($cattr, $type, $cur) = @_;
if (exists $cattr->{$type}) {
unless ($cattr->{$type} =~ /^\d+$/) {
if ($cattr->{$type} =~ /^\+(\d+)$/) {
$cattr->{$type} = $cur + $1;
} else {
$cattr->{$type} = $cur;
}
}
} else {
$cattr->{$type} = $cur;
}
}
sub parse_a_set {
my ($elem, $parent) = @_;
my $hash = &parse_a_node($elem, $parent, 'set');
$hash->{'data'} = [];
my @childs = &get_childs($elem);
if (@childs > 0) {
push @{$parent->{'data'}}, $hash;
foreach my $item (@childs) {
&parse_a_set($item->{'child'}, $hash) if lc $item->{'tag'} eq 'set';
&parse_a_cell($item->{'child'}, $hash) if lc $item->{'tag'} eq 'cell';
&parse_a_image($item->{'child'}, $hash) if lc $item->{'tag'} eq 'img';
&parse_a_chart($item->{'child'}, $hash) if lc $item->{'tag'} eq 'chart';
}
}
if (exists $hash->{'attr'}->{'name'}) {
my $name = $hash->{'attr'}->{'name'};
if ($name) {
$hash->{'top'}->{'namedsets'}->{$name} = $hash if $hash->{'parent'}->{'tag'} eq 'set';
}
}
}
sub parse_a_cell {
my ($elem, $parent) = @_;
my $hash = &parse_a_node($elem, $parent, 'cell');
push @{$parent->{'data'}}, $hash;
$hash->{'value'} = &get_value($elem);
if (exists $hash->{'attr'}->{'comment'}) {
my $comment = {};
$comment->{'data'} = $hash->{'attr'}->{'comment'};
$comment->{'attr'} = {};
$hash->{'attr'}->{'comment'} = $comment;
}
foreach my $item (&get_childs($elem)) {
&parse_a_comment($item->{'child'}, $hash) if lc $item->{'tag'} eq 'comment';
&parse_a_image($item->{'child'}, $hash) if lc $item->{'tag'} eq 'img';
}
}
sub parse_a_node {
my ($elem, $parent, $tag) = @_;
my $hash = {};
$hash->{'parent'} = $parent;
$hash->{'top'} = $parent->{'top'};
$hash->{'tag'} = $tag;
if ($parent->{'tag'} eq 'worksheet') {
$hash->{'attr'} = $parent->{'attr'};
} else {
$hash->{'attr'} = &get_attributes($elem);
}
&parse_pos($hash->{'attr'}) if exists $hash->{'attr'}->{'pos'};
my $style = &read_style($parent->{'attr'});
if (exists $hash->{'attr'}->{'style'} ) {
my $sname = $hash->{'attr'}->{'style'};
$style = $styles{$sname};
}
&extend_style($style, $hash->{'attr'});
return $hash;
}
sub parse_a_comment {
my ($elem, $parent) = @_;
my $hash = {};
$hash->{'attr'} = &get_attributes($elem);
$hash->{'data'} = &get_value($elem);
$parent->{'attr'}->{'comment'} = $hash;
}
sub parse_a_image {
my ($elem, $parent) = @_;
my $hash = {};
$hash->{'attr'} = &get_attributes($elem);
$hash->{'parent'} = $parent;
&parse_pos($hash->{'attr'}) if exists $hash->{'attr'}->{'pos'};
push(@{$parent->{'top'}->{'images'}}, $hash);
}
sub parse_a_chart {
my ($elem, $parent) = @_;
my $hash = {};
$hash->{'attr'} = &get_attributes($elem);
if (exists $hash->{'attr'}->{'col'} and exists $hash->{'attr'}->{'row'}) {
&get_pos($hash->{'attr'});
}
$hash->{'data'} = [];
return unless exists $hash->{'attr'}->{'pos'};
foreach my $item (&get_childs($elem)) {
push @{$hash->{'data'}}, &get_attributes($item->{'child'}) if lc $item->{'tag'} eq 'data';
$hash->{'title'} = &get_attributes($item->{'child'}) if lc $item->{'tag'} eq 'title';
$hash->{'x_axis'} = &get_attributes($item->{'child'}) if lc $item->{'tag'} eq 'x_axis';
$hash->{'y_axis'} = &get_attributes($item->{'child'}) if lc $item->{'tag'} eq 'y_axis';
}
push(@{$parent->{'top'}->{'charts'}}, $hash);
}
# Save result in Excel doc
sub write_excel {
my $wsheet = shift;
my $sheet = $book->add_worksheet($wsheet->{'attr'}->{'name'});
foreach my $row (keys %{$wsheet->{'top'}->{'mapCells'}}) {
foreach my $col (keys %{$wsheet->{'top'}->{'mapCells'}->{$row}}) {
next unless exists $wsheet->{'top'}->{'mapCells'}->{$row}->{$col};
my $cell = $wsheet->{'top'}->{'mapCells'}->{$row}->{$col};
next unless exists $cell->{'attr'};
if (exists $cell->{'attr'}->{'width'}) {
my $width = $cell->{'attr'}->{'width'};
delete $cell->{'attr'}->{'width'};
if ($width > 0) {
unless (exists $wsheet->{'widthCols'}->{$col}) {
$wsheet->{'widthCols'}->{$col} = $width;
} else {
$wsheet->{'widthCols'}->{$col} = $width if $width > $wsheet->{'widthCols'}->{$col};
}
}
}
if (exists $cell->{'attr'}->{'height'}) {
my $height = $cell->{'attr'}->{'height'};
delete $cell->{'attr'}->{'height'};
if ($height > 0) {
unless (exists $wsheet->{'heightRows'}->{$row}) {
$wsheet->{'heightRows'}->{$row} = $height;
} else {
$wsheet->{'heightRows'}->{$row} = $height if $height > $wsheet->{'heightRows'}->{$row};
}
}
}
}
}
foreach my $col (keys %{$wsheet->{'widthCols'}}) {
$sheet->set_column($col, $col, $wsheet->{'widthCols'}->{$col});
}
foreach my $row (keys %{$wsheet->{'heightRows'}}) {
$sheet->set_row($row, $wsheet->{'heightRows'}->{$row});
}
foreach my $row (sort {$a <=> $b} keys %{$wsheet->{'top'}->{'mapCells'}}) {
foreach my $col (sort {$a <=> $b} keys %{$wsheet->{'top'}->{'mapCells'}->{$row}}) {
my $item = $wsheet->{'mapCells'}->{$row}->{$col};
my $attr = $item->{'attr'};
my $value = $item->{'value'};
my $style = &read_style($item->{'attr'});
&customise_style($style);
my $id = &add_format($style);
if (exists $item->{'attr'}->{'blank'}) {
$sheet->write_blank($row, $col, $id);
} else {
$sheet->write($row, $col, $value, $id);
}
if (exists $attr->{'comment'}) {
my $hash = $attr->{'comment'};
$sheet->write_comment($attr->{'row'}, $attr->{'col'}, $hash->{'data'}, %{$hash->{'attr'}});
}
}
}
foreach my $image (@{$wsheet->{'images'}}) {
my $cell = $image->{'parent'};
my $attr = $image->{'attr'};
my $row = (exists $attr->{'row'}) ? $attr->{'row'} : $cell->{'attr'}->{'row'};
my $col = (exists $attr->{'col'}) ? $attr->{'col'} : $cell->{'attr'}->{'col'};
my $src = $attr->{'src'};
my $x = (exists $attr->{'x'} and $attr->{'x'} > 0) ? $attr->{'x'}: 0;
my $y = (exists $attr->{'y'} and $attr->{'y'} > 0) ? $attr->{'y'}: 0;
$sheet->insert_image($row, $col, $src, $x, $y);
}
foreach my $chart (@{$wsheet->{'charts'}}) {
my $attr = $chart->{'attr'};
my $pos = $chart->{'attr'}->{'pos'};
my $x = (exists $chart->{'attr'}->{'x'}) ? $chart->{'attr'}->{'x'} : 0;
my $y = (exists $chart->{'attr'}->{'y'}) ? $chart->{'attr'}->{'y'} : 0;
my $width = (exists $chart->{'attr'}->{'width'}) ? $chart->{'attr'}->{'width'} : 1;
my $height = (exists $chart->{'attr'}->{'height'}) ? $chart->{'attr'}->{'height'} : 1;
next unless exists $chart->{'attr'}->{'type'};
my $type = $chart->{'attr'}->{'type'};
&get_pos($chart->{'attr'}) if exists $chart->{'attr'}->{'col'} and exists $chart->{'attr'}->{'row'};
next unless exists $chart->{'attr'}->{'pos'};
my $cattr = {};
$cattr->{'embedded'} = 1;
$cattr->{'type'} = $type;
$cattr->{'name'} = $chart->{'attr'}->{'name'} if exists $chart->{'attr'}->{'name'};
my $ch = $book->add_chart(%$cattr);
foreach my $data (@{$chart->{'data'}}) {
next unless exists $data->{'values'};
$data->{'values'} = &check_if_named_set($data->{'values'}, $wsheet);
$data->{'categories'} = &check_if_named_set($data->{'categories'}, $wsheet) if $data->{'categories'};
$ch->add_series(%$data);
}
$ch->set_title(%{$chart->{'title'}}) if exists $chart->{'title'};
$ch->set_x_axis(%{$chart->{'x_axis'}}) if exists $chart->{'x_axis'};
$ch->set_y_axis(%{$chart->{'y_axis'}}) if exists $chart->{'y_axis'};
$sheet->insert_chart($pos, $ch, $x, $y, $width, $height);
}
}
sub check_if_named_set() {
my ($values, $wsheet) = @_;
if (exists $wsheet->{'top'}->{'namedsets'}->{$values}) {
$values = &get_size_of_set($wsheet->{'top'}->{'namedsets'}->{$values});
}
return $values;
}
# Subroutines to parse XML::DOM tree
sub get_attributes {
my $elem = shift;
my %hash = %{$elem->getAttributes()};
delete $hash{''} if exists $hash{''};
my %attrs = map { $_ => $elem->getAttribute($_) } keys %hash;
return \%attrs;
}
sub get_value {
my $elem = shift;
my $value = '';
foreach my $child ($elem->getChildNodes()) {
next unless $child->getNodeType() eq XML::DOM::TEXT_NODE;
$value = $value . $child->getData();
}
my $attr = &get_attributes($elem);
if (exists $attr->{'trunc'} and &parse_boolean($attr->{'trunc'}) eq 1 ) {
$value = $` if $value =~ /\s+$/;
$value = $' if $value =~ /^\s+/;
$value =~ s/\n/ /g;
}
return $value;
}
sub get_childs {
my $elem = shift;
my @childs = ();
foreach my $child ($elem->getChildNodes()) {
next unless $child->getNodeType() eq XML::DOM::ELEMENT_NODE;
my $tag = $child->getNodeName();
push @childs, {'tag'=>$tag, 'child'=>$child};
}
return @childs;
}
# Service subroutines
sub parse_pos {
my $ptr = shift;
my $pos = uc $ptr->{'pos'};
if ($pos =~ /([A-Z]{1,2})(\d+)/) {
my $row = $2 - 1;
my $col = $1;
if ($col =~ /(\S)(\S)/) {
$col = (ord($1) - ord('A') + 1) * 26 + ord($2) - ord('A');
} else {
$col = ord($col) - ord('A');
}
$ptr->{'row'} = $row;
$ptr->{'col'} = $col;
delete $ptr->{'pos'};
}
}
sub get_pos {
my $ptr = shift;
$ptr->{'pos'} = &rowcol_to_pos($ptr->{'row'}, $ptr->{'col'}, 'pos');
delete $ptr->{'row'};
delete $ptr->{'col'};
}
sub rowcol_to_pos {
my ($row, $col, $type) = @_;
my $pos;
$row++;
my $up = int($col / 26);
my $low = $col % 26;
$low = chr($low + ord('A'));
$low = chr($up + ord('A') - 1).$low if $up > 0;
if ($type eq 'set') {
$pos = '$'.$low.'$'.$row;
} else {
$pos = $low.$row;
}
return $pos;
}
sub get_size_of_set {
my $ptr = shift;
my $mincol = $ptr->{'attr'}->{'mincol'};
my $maxcol = $ptr->{'attr'}->{'maxcol'};
my $minrow = $ptr->{'attr'}->{'minrow'};
my $maxrow = $ptr->{'attr'}->{'maxrow'};
my $minpos = &rowcol_to_pos($minrow, $mincol, 'set');
my $maxpos = &rowcol_to_pos($maxrow, $maxcol, 'set');
return "$minpos:$maxpos";
}
sub read_style {
my $attr = shift;
my @styletags = (
'font', 'size', 'color', 'bold', 'italic', 'underline',
'bg_color', 'fg_color',
'width', 'height',
'align', 'valign',
'indent', 'wrap',
'border', 'border_color',
'border_top', 'border_bottom', 'border_left', 'border_right',
'border_top_color', 'border_bottom_color', 'border_left_color', 'border_right_color',
'center_across'
);
my $hash = {};
foreach my $tag (@styletags) {
$hash->{$tag} = $attr->{$tag} if exists $attr->{$tag};
}
return $hash;
}
sub attr_for_skipped_cell {
my $wsheet = shift;
my %sattr = ();
my @styletags = (
'bg_color', 'fg_color',
'width', 'height',
'border', 'border_color',
'border_top', 'border_bottom', 'border_left', 'border_right',
'border_top_color', 'border_bottom_color', 'border_left_color', 'border_right_color'
);
foreach my $tag (@styletags) {
$sattr{$tag} = $wsheet->{'attr'}->{$tag} if exists $wsheet->{'attr'}->{$tag};
}
return %sattr;
}
sub customise_style {
my $style = shift;
$style->{'bold'} = &parse_boolean($style->{'bold'}) if exists $style->{'bold'};
$style->{'italic'} = &parse_boolean($style->{'italic'}) if exists $style->{'italic'};
$style->{'underline'} = &parse_boolean($style->{'underline'}) if exists $style->{'underline'};
$style->{'wrap'} = &parse_boolean($style->{'wrap'}) if exists $style->{'wrap'};
delete $style->{'wrap'} if exists $style->{'wrap'} and $style->{'wrap'} ne 1;
$style->{'color'} = &parse_color($style->{'color'}) if exists $style->{'color'};
$style->{'bg_color'} = &parse_color($style->{'bg_color'}) if exists $style->{'bg_color'};
$style->{'fg_color'} = &parse_color($style->{'fg_color'}) if exists $style->{'fg_color'};
$style->{'border_color'} = &parse_color($style->{'border_color'}) if exists $style->{'border_color'};
$style->{'valign'} = 'vcenter' if exists $style->{'valign'} and $style->{'valign'} eq 'middle';
}
sub extend_style {
my ($style, $attr) = @_;
my %hash = %{$style};
foreach my $key (keys %{&read_style($attr)}) {
$hash{$key} = $attr->{$key};
}
foreach my $key (keys %hash) {
$attr->{$key} = $hash{$key};
}
}
sub add_format {
my $style = shift;
return if keys %$style == 0;
my $key = join(' ', map {$_."='".$style->{$_}."'"} sort keys %$style);
my $id;
if (exists $styleStore{$key}) {
$id = $styleStore{$key};
} else {
my $text_wrapping = 0;
my $border_top = 0;
my $border_bottom = 0;
my $border_left = 0;
my $border_right = 0;
my $border_top_color;
my $border_bottom_color;
my $border_left_color;
my $border_right_color;
if (exists $style->{'wrap'} ) {
$text_wrapping = 1;
delete $style->{'wrap'};
}
if (exists $style->{'border_top'} ) {
$border_top = $style->{'border_top'};
delete $style->{'border_top'};
}
if (exists $style->{'border_bottom'} ) {
$border_bottom = $style->{'border_bottom'};
delete $style->{'border_bottom'};
}
if (exists $style->{'border_left'} ) {
$border_left = $style->{'border_left'};
delete $style->{'border_left'};
}
if (exists $style->{'border_right'} ) {
$border_right = $style->{'border_right'};
delete $style->{'border_right'};
}
if (exists $style->{'border_top_color'} ) {
$border_top_color = $style->{'border_top_color'};
delete $style->{'border_top_color'};
}
if (exists $style->{'border_bottom_color'} ) {
$border_bottom_color = $style->{'border_bottom_color'};
delete $style->{'border_bottom_color'};
}
if (exists $style->{'border_left_color'} ) {
$border_left_color = $style->{'border_left_color'};
delete $style->{'border_left_color'};
}
if (exists $style->{'border_right_color'} ) {
$border_right_color = $style->{'border_right_color'};
delete $style->{'border_right_color'};
}
$id = $book->add_format(%$style);
$id->set_text_wrap() if $text_wrapping;
$id->set_top($border_top) if $border_top;
$id->set_bottom($border_bottom) if $border_bottom;
$id->set_left($border_left) if $border_left;
$id->set_right($border_right) if $border_right;
$id->set_top_color($border_top_color) if $border_top_color;
$id->set_bottom_color($border_bottom_color) if $border_bottom_color;
$id->set_left_color($border_left_color) if $border_left_color;
$id->set_right_color($border_right_color) if $border_right_color;
$styleStore{$key} = $id;
}
return $id;
}
#<!ENTITY %boolean "(0|1|n|y|no|yes|false|true) 0">
sub parse_boolean {
my $value = lc shift;
my $res = 0;
$res = 0 if $value eq 'n' or $value eq 'no' or $value eq 'false' or $value eq 'off';
$res = 1 if $value eq 'y' or $value eq 'yes' or $value eq 'true' or $value eq 'on' or $value eq 1;
return $res;
}
sub parse_color {
my $color = lc shift;
# Fill color table is the first time
if (keys %colorStore == 0) {
for (my $i = 8; $i < 64; $i++) {
next unless $colorPalette[$i];
$colorStore{$i} = $colorPalette[$i];
}
}
return $color unless $color =~ /^\#[0-9a-f]{6}$/;
return $colorStore{$color} if exists $colorStore{$color};
for (my $i = 56; $i > 7; $i--) {
next if $colorPalette[$i];
$color =~ /^\#(\S\S)(\S\S)(\S\S)/;
my $red = hex $1;
my $green = hex $2;
my $blue = hex $3;
my $id = $book->set_custom_color($i, $red, $green, $blue);
$colorPalette[$id] = $color;
$colorStore{$color} = $id;
return $id;
}
return 'red';
}
sub parse_styles {
my $doc = shift;
my %styles = ();
foreach my $styleNode ($doc->getElementsByTagName("style")) {
my $attr = &get_attributes($styleNode);
next unless exists $attr->{"name"};
$styles{$attr->{"name"}} = $attr;
}
return %styles unless keys %styles > 0;
my $styleflag;
do {
$styleflag = 0;
foreach my $name (keys %styles) {
next unless exists $styles{$name}->{'extend'};
my $sname = $styles{$name}->{'extend'};
next if exists $styles{$sname}->{'extend'};
&extend_style($styles{$sname}, $styles{$name});
delete $styles{$name}->{'extend'};
$styleflag = 1;
}
} while ($styleflag);
foreach my $name (keys %styles) {
$styles{$name} = &read_style($styles{$name});
}
return %styles;
}
# Category Description Property Method Name
# -------- ----------- -------- -----------
# Font Font type font set_font()
# Font size size set_size()
# Font color color set_color()
# Bold bold set_bold()
# Italic italic set_italic()
# Underline underline set_underline()
# Strikeout font_strikeout set_font_strikeout()
# Super/Subscript font_script set_font_script()
# Outline font_outline set_font_outline()
# Shadow font_shadow set_font_shadow()
#
# Number Numeric format num_format set_num_format()
#
# Protection Lock cells locked set_locked()
# Hide formulas hidden set_hidden()
#
# Alignment Horizontal align align set_align()
# Vertical align valign set_align()
# Rotation rotation set_rotation()
# Text wrap text_wrap set_text_wrap()
# Justify last text_justlast set_text_justlast()
# Center across center_across set_center_across()
# Indentation indent set_indent()
# Shrink to fit shrink set_shrink()
#
# Pattern Cell pattern pattern set_pattern()
# Background color bg_color set_bg_color()
# Foreground color fg_color set_fg_color()
#
# Border Cell border border set_border()
# Bottom border bottom set_bottom()
# Top border top set_top()
# Left border left set_left()
# Right border right set_right()
# Border color border_color set_border_color()
# Bottom color bottom_color set_bottom_color()
# Top color top_color set_top_color()
# Left color left_color set_left_color()
# Right color right_color set_right_color()
1;