#!/usr/bin/perl -w

#
# Utility program to convert an Excel file into a Spreadsheet::WriteExcel
# program using Win32::OLE
#

#
# lecxe program
# by t0mas@netlords.net
#
# Version  0.01a    Initial release (alpha)


# Modules
use strict;
use Win32::OLE;
use Win32::OLE::Const;
use Getopt::Std;


# Vars
use vars qw(%opts);


# Get options
getopts('i:o:v',\%opts);


# Not enough options
exit &usage unless ($opts{i} && $opts{o});


# Create Excel object
my $Excel = new Win32::OLE("Excel.Application","Quit") or
        die "Can't start excel: $!";


# Get constants
my $ExcelConst=Win32::OLE::Const->Load("Microsoft Excel");


# Show Excel
$Excel->{Visible} = 1 if ($opts{v});


# Open infile
my $Workbook = $Excel->Workbooks->Open({Filename=>$opts{i}});


# Open outfile
open (OUTFILE,">$opts{o}") or die "Can't open outfile $opts{o}: $!";


# Print header for outfile
print OUTFILE <<'EOH';
#!/usr/bin/perl -w


use strict;
use Spreadsheet::WriteExcel;


use vars qw($workbook %worksheets %formats);


$workbook = Spreadsheet::WriteExcel->new("_change_me_.xls");


EOH


# Loop all sheets
foreach my $sheetnum (1..$Excel->Workbooks(1)->Worksheets->Count) {


        # Format sheet
        my $name=$Excel->Workbooks(1)->Worksheets($sheetnum)->Name;
        print "Sheet $name\n" if ($opts{v});
        print OUTFILE "# Sheet $name\n";
        print OUTFILE "\$worksheets{'$name'} = \$workbook->add_worksheet('$name');\n";


        # Get usedrange of cells in worksheet
        my $usedrange=$Excel->Workbooks(1)->Worksheets($sheetnum)->UsedRange;


        # Loop all columns in used range
        foreach my $j (1..$usedrange->Columns->Count){


                # Format column
                print "Col $j\n" if ($opts{v});
                my ($colwidth);
                $colwidth=$usedrange->Columns($j)->ColumnWidth;
                print OUTFILE "# Column $j\n";
                print OUTFILE "\$worksheets{'$name'}->set_column(".($j-1).",".($j-1).
                        ", $colwidth);\n";


                # Loop all rows in used range
                foreach my $i (1..$usedrange->Rows->Count){


                        # Format row
                        print "Row $i\n" if ($opts{v});
                        print OUTFILE "# Row $i\n";
                        do {
                                my ($rowheight);
                                $rowheight=$usedrange->Rows($i)->RowHeight;
                                print OUTFILE "\$worksheets{'$name'}->set_row(".($i-1).
                                        ", $rowheight);\n";
                        } if ($j==1);


                        # Start creating cell format
                        my $fname="\$formats{'".$name.'R'.$i.'C'.$j."'}";
                        my $format="$fname=\$workbook->add_format();\n";
                        my $print_format=0;

                        # Check for borders
                        my @bfnames=qw(left right top bottom);
                        foreach my $k (1..$usedrange->Cells($i,$j)->Borders->Count) {
                                my $lstyle=$usedrange->Cells($i,$j)->Borders($k)->LineStyle;
                                if ($lstyle > 0) {
                                        $format.=$fname."->set_".$bfnames[$k-1]."($lstyle);\n";
                                        $print_format=1;
                                }
                        }


                        # Check for font
                        my ($fontattr,$prop,$func,%fontsets,$fontColor);
                        %fontsets=(Name=>'set_font',
                                                Size=>'set_size');
                        while (($prop,$func) = each %fontsets) {
                                $fontattr=$usedrange->Cells($i,$j)->Font->$prop;
                                if ($fontattr ne "") {
                                        $format.=$fname."->$func('$fontattr');\n";
                                        $print_format=1;
                                }


                        }
                        %fontsets=(Bold=>'set_bold(1)',
                                                Italic=>'set_italic(1)',
                                                Underline=>'set_underline(1)',
                                                Strikethrough=>'set_strikeout(1)',
                                                Superscript=>'set_script(1)',
                                                Subscript=>'set_script(2)',
                                                OutlineFont=>'set_outline(1)',
                                                Shadow=>'set_shadow(1)');
                        while (($prop,$func) = each %fontsets) {
                                $fontattr=$usedrange->Cells($i,$j)->Font->$prop;
                                if ($fontattr==1) {
                                        $format.=$fname."->$func;\n" ;

                                        $print_format=1;
                                }
                        }
                        $fontColor=$usedrange->Cells($i,$j)->Font->ColorIndex();
                        if ($fontColor>0&&$fontColor!=$ExcelConst->{xlColorIndexAutomatic}) {
                                $format.=$fname."->set_color(".($fontColor+7).");\n" ;
                                $print_format=1;
                        }



                        # Check text alignment, merging and wrapping
                        my ($halign,$valign,$merge,$wrap);
                        $halign=$usedrange->Cells($i,$j)->HorizontalAlignment;
                        my %hAligns=($ExcelConst->{xlHAlignCenter}=>"'center'",
                                $ExcelConst->{xlHAlignJustify}=>"'justify'",
                                $ExcelConst->{xlHAlignLeft}=>"'left'",
                                $ExcelConst->{xlHAlignRight}=>"'right'",
                                $ExcelConst->{xlHAlignFill}=>"'fill'",
                                $ExcelConst->{xlHAlignCenterAcrossSelection}=>"'merge'");
                        if ($halign!=$ExcelConst->{xlHAlignGeneral}) {
                                $format.=$fname."->set_align($hAligns{$halign});\n";
                                $print_format=1;
                        }
                        $valign=$usedrange->Cells($i,$j)->VerticalAlignment;
                        my %vAligns=($ExcelConst->{xlVAlignBottom}=>"'bottom'",
                                $ExcelConst->{xlVAlignCenter}=>"'vcenter'",
                                $ExcelConst->{xlVAlignJustify}=>"'vjustify'",
                                $ExcelConst->{xlVAlignTop}=>"'top'");
                        if ($valign) {
                                $format.=$fname."->set_align($vAligns{$valign});\n";
                                $print_format=1;
                        }
                        $merge=$usedrange->Cells($i,$j)->MergeCells;
                        if ($merge==1) {
                                $format.=$fname."->set_merge();\n";

                                $print_format=1;
                        }
                        $wrap=$usedrange->Cells($i,$j)->WrapText;
                        if ($wrap==1) {
                                $format.=$fname."->set_text_wrap(1);\n";

                                $print_format=1;
                        }


                        # Check patterns
                        my ($pattern,%pats);
                        %pats=(-4142=>0,-4125=>2,-4126=>3,-4124=>4,-4128=>5,-4166=>6,
                                        -4121=>7,-4162=>8);
                        $pattern=$usedrange->Cells($i,$j)->Interior->Pattern;
                        if ($pattern&&$pattern!=$ExcelConst->{xlPatternAutomatic}) {
                                $pattern=$pats{$pattern} if ($pattern<0 && defined $pats{$pattern});
                                $format.=$fname."->set_pattern($pattern);\n";

                                # Colors fg/bg
                                my ($cIndex);
                                $cIndex=$usedrange->Cells($i,$j)->Interior->PatternColorIndex;
                                if ($cIndex>0&&$cIndex!=$ExcelConst->{xlColorIndexAutomatic}) {
                                        $format.=$fname."->set_bg_color(".($cIndex+7).");\n";
                                }
                                $cIndex=$usedrange->Cells($i,$j)->Interior->ColorIndex;
                                if ($cIndex>0&&$cIndex!=$ExcelConst->{xlColorIndexAutomatic}) {
                                        $format.=$fname."->set_fg_color(".($cIndex+7).");\n";
                                }
                                $print_format=1;
                        }


                        # Check for number format
                        my ($num_format);
                        $num_format=$usedrange->Cells($i,$j)->NumberFormat;
                        if ($num_format ne "") {
                                $format.=$fname."->set_num_format('$num_format');\n";
                                $print_format=1;
                        }


                        # Check for contents (text or formula)
                        my ($contents);
                        $contents=$usedrange->Cells($i,$j)->Formula;
                        $contents=$usedrange->Cells($i,$j)->Text if ($contents eq "");


                        # Print cell
                        if ($contents ne "" or $print_format) {
                                print OUTFILE "# Cell($i,$j)\n";
                                print OUTFILE $format if ($print_format);
                                print OUTFILE "\$worksheets{'$name'}->write(".($i-1).",".($j-1).
                                        ",'$contents'";
                                print OUTFILE ",$fname" if ($print_format);
                                print OUTFILE ");\n";
                        }
                }
        }
}


# Famous last words...
print OUTFILE "\$workbook->close();\n";


# Close outfile
close (OUTFILE) or die "Can't close outfile $opts{o}: $!";


####################################################################
sub usage {
        printf STDERR "usage: $0 [options]\n".
                "\tOptions:\n".
                "\t\t-v       \tverbose mode\n" .
                "\t\t-i <name>\tname of input file\n" .
                "\t\t-o <name>\tname of output file\n";
}


####################################################################
sub END {
        # Quit excel
        do {
                $Excel->{DisplayAlerts} = 0;
                $Excel->Quit;
        } if (defined $Excel);
}


__END__


=head1 NAME


lecxe - A Excel file to Spreadsheet::WriteExcel code converter


=head1 DESCRIPTION


This program takes an MS Excel workbook file as input and from
that file, produces an output file with Perl code that uses the
Spreadsheet::WriteExcel module to reproduce the original
file.


=head1 STUFF


Additional hands-on editing of the output file might be neccecary
as:


* This program always names the file produced by output script
  _change_me_.xls


* Users of international Excel versions will have som work to do
  on list separators and numeric punctation characters.


=head1 SEE ALSO


L<Win32::OLE>, L<Win32::OLE::Variant>, L<Spreadsheet::WriteExcel>


=head1 BUGS


* Picks wrong color on cells sometimes.


* Probably a few other...


=head1 DISCLAIMER


I do not guarantee B<ANYTHING> with this program. If you use it you
are doing so B<AT YOUR OWN RISK>! I may or may not support this
depending on my time schedule...


=head1 AUTHOR


t0mas@netlords.net


=head1 COPYRIGHT


Copyright 2001, t0mas@netlords.net


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