#!/usr/bin/perl 
#~
#~ Copyleft 2010 VelC.
#~ This program is free software; you can redistribute it and/or
#~ modify it under the same terms as Perl itself.
#~
#~ This program was originally written in french, therefore a lot of 
#~ variables have french names. Do not hesitate to change them if you
#~ like.
#~
#~ For the CSS I choose to write it directly in each markup. That's
#~ ugly and usually to ban but it was much more easier for me to do
#~ so, since I don't know which cells have the same style, and so
#~ which cells should have the same class.
#~ 
#~ This program was encoded in ISO-8859-1 (Western), so that it could
#~ produce HTML files encoded in ISO-8859-1, as written in the header
#~ I wrote. To change this, simply copy-paste the source code in a
#~ UTF-8 file (for example) and change the HTML charset at line 199
#~ (look for "charset" in the file and you'll find the relevant line).
#~
#~ See http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.57/lib/Spreadsheet/ParseExcel.pm
#~ for further details about the possibilities of SpreadSheet:ParseExcel
#~

use strict;
use Spreadsheet::ParseExcel qw (new Parse worksheets);
use Getopt::Std;
use Encode;
use File::Basename;

#~ The -s option define the normal font-size used in the page. For 
#~ example, -s 10 will define that the Excel font-size 10 equals 1em, 
#~ and thus that a font-size equals to 12 will be represented by 1.2em.
#~ If left empty, the normal font-size will be 14.

#~ The -n option define the sheets that must be treated. If left empty 
#~ or equals to zero, all sheets will be treated. If this value 
#~ contains greater numbers than the real number of sheets in the 
#~ Excel File, these numbers will be ignored. The first sheet is
#~ the sheet 1 and not 0.

#~ xls2html -n 1,2 file.xls file.html; # feuilles 1 et 2
#~ xls2html -n 1..3,5 file.xls file.html; # feuilles 1 à 3 et 5
#~ xls2html -n 2.. file.xls file.html; # toutes les feuilles à partir de la 2e
#~ xls2html -n ..4 file.xls file.html; # les 4 premières feuilles
#~ xls2html -n -1 file.xls file.html; # la dernière feuille

#~ xls2html -n 1,2 file.xls file.html; # sheets 1 and 2
#~ xls2html -n 1..3,5 file.xls file.html; # sheets 1 to 3 and 5
#~ xls2html -n 2.. file.xls file.html; # every sheets from 2nd
#~ xls2html -n ..4 file.xls file.html; # sheets 1 to 4
#~ xls2html -n -1 file.xls file.html; # last sheet

our($opt_s, $opt_n);
getopts("s:n:");

my $nbarg = @ARGV;
($nbarg == 2) || die "Usage : xls2html.pl [-s number][-n number] File_in.xls File_out.html\n";
my $fileIn = shift;
($fileIn =~ /^.*\.xls$/) || die "Usage : xls2html.pl [-s number][-n number] File_in.xls File_out.html\n";
my $fileOut = shift;
my $fileOutSuffix = '';
if($fileOut =~ /^(.*)\.(html)$/ || $fileOut =~ /^(.*)\.(htm)$/)
{
	$fileOut = $1;
	$fileOutSuffix = $2;
}
else
{
	die "Usage : xls2html.pl [-s number][-n number] File_in.xls File_out.html\n";
}

my $normal_size = 10;
my @n_sheets;
my $nb_sheets = 0;
my $parser = Spreadsheet::ParseExcel -> new();
my $workbook = $parser -> parse($fileIn);
my @worksheets = $workbook -> worksheets();
my $sheets_count = $workbook -> worksheet_count();

if(defined($opt_s))
{
	($opt_s =~ /^(\d+\.?\d*|\.\d+)$/ && $opt_s > 0 && $opt_s < 100) || die "The -s option has to be followed by a valid number !";
	$normal_size = $opt_s;
}

if(defined($opt_n))
{
	for(my $i = 0 ; $i < $sheets_count ; $i++)
	{
		$n_sheets[$i] = 0;
	}
	
	my @regtab;
	my $i = 0;
	
	while($opt_n =~ /(^\.\.\d+)|(\d+\.\.$)|(\d+\.\.\d+)|(\d+)|,/g)
	{
		$regtab[$i++]=$&;
	}
	
	if(@regtab)
	{
		for(my $j = 0 ; $j < $i ; $j++)
		{
			if($regtab[$j] =~ /^\.\.(\d+)$/)
			{
				for(my $k = 0 ; $k < $1 ; $k++)
				{
					$n_sheets[$k] = 1;
				}
				$nb_sheets += $1;
			}
			elsif($regtab[$j] =~ /^(\d+)\.\.$/)
			{
				for(my $k = $1 - 1 ; $k < $sheets_count ; $k++)
				{
					$n_sheets[$k] = 1;
				}
				$nb_sheets += $sheets_count - $1 + 1;
			}
			elsif($regtab[$j] =~ /^(\d+)\.\.(\d+)$/)
			{
				for(my $k = $1 - 1 ; $k < $2 ; $k++)
				{
					$n_sheets[$k] = 1;
				}
				$nb_sheets += $2 - $1 + 1;
			}
			elsif($regtab[$j] =~ /^(\d+)$/)
			{
				$n_sheets[$1-1] = 1;
				$nb_sheets++;
			}
			elsif($regtab[$j] =~ /^,$/)
			{
				#~ does nothing, but ',' is a correct value
			}
			else
			{
				die "An error occured, incorrect option -n.";
			}
			
		}
	}
	else
	{
		die "The -n option has to be followed by a valid format ! Example : ..2,4,6..8,10..";
	}
}
else
{
	for(my $i = 0 ; $i < $sheets_count ; $i++)
	{
		$n_sheets[$i] = 1;
		$nb_sheets++;
	}
}

my $filename;
my $worksheet;

for(my $k = 0 ; $k < $sheets_count ; $k++)
{
	if($n_sheets[$k])
	{
		$worksheet = $workbook -> worksheet($k);
		my $ligmax = $worksheet -> row_range();
		my $colmax = $worksheet -> col_range();
		
		if($fileIn =~ /^(.*)\.xls$/ && $nb_sheets == 1)
		{
			$filename = $1;
		}
		else
		{
			$filename = $worksheet->get_name();
		}
		
		#~ File where to write the result
		#~ If there's only one sheet to produce, the name of this sheet
		#~ isn't include in the name of the output file. Otherwise, it is
		#~ for each produced sheet.
		if($nb_sheets == 1)
		{
			open(FO,'>'.$fileOut.'.'.$fileOutSuffix) || die "Cannot open file: $!\n";
		}
		else
		{
			open(FO,'>'.$fileOut.'-'.$filename.'.'.$fileOutSuffix) || die "Cannot open file: $!\n";
		}

		#~ Beginning of the HTML file
		print FO '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">'."\n".
		'<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="fr" >'."\n".
		'	<head>'."\n".
		'		<title>'.$filename.'</title>'."\n".
		'		<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />'."\n".
		'	</head>'."\n".
		'	<body>'."\n".
		'		<table>'."\n";

		#~ Iteration on each line and each column of the worksheet, writing down each cell from left to right and from top to bottom

		my $bloc = '';
		my @cells = $worksheet->{Cells};
		for(my $i = 0 ; $i <= $ligmax ; $i++)
		{
			print FO "			<tr>\n";
			for(my $j = 0 ; $j <= $colmax ; $j++)
			{
				my $cell = $cells[0][$i][$j];
				if(defined($cell))
				{
					my $contenu = $cell -> value();
					my $format = $cell -> get_format();
					my $gras;
					if($format->{Font}->{Bold})
					{
						$gras = "bold";
					}
					else
					{
						$gras = "normal";
					}
					my $italique;
					if($format->{Font}->{Italic})
					{
						$italique = "italic";
					}
					else
					{
						$italique = "normal";
					}
					my %font = ("nom" => $format->{Font}->{Name},
								"gras" => $gras,
								"italique" => $italique,
								"taille" => $format->{Font}->{Height},
								"couleur" => $format->{Font}->{Color}
								);
					my $alignH = $format -> {AlignH};
					
					#~ 0 => No alignment
					#~ 1 => Left
					#~ 2 => Center
					#~ 3 => Right
					#~ 4 => Fill
					#~ 5 => Justify
					#~ 6 => Center across
					#~ 7 => Distributed/Equal spaced

					if($alignH == 3)
					{
						$alignH = "right";
					}
					elsif($alignH == 2 || $alignH == 	6)
					{
						$alignH = "center";
					}
					elsif($alignH == 5 || $alignH == 4)
					{
						$alignH = "justify";
					}
					else
					{
						$alignH = "left";
					}
					my @fill = $format -> {Fill};
					my $couleur = $parser -> ColorIdxToRGB($fill[0][1]);
					print FO '				<td style="font-family:'.$font{"nom"}.';'.
														'font-size:'.($font{"taille"}/$normal_size).'em;'.
														'color:'.$font{"couleur"}.';'.
														'font-weight:'.$font{"gras"}.';'.
														'font-style:'.$font{"italic"}.';'.
														'text-align:'.$alignH.';'.
														'background:#'.$couleur.';'.
														'">'."\n";
					print FO '					'.$contenu."\n";
					print FO "				</td>\n";
				}
			}
			print FO "			</tr>\n";
		}

		print FO "	</body>\n</html>";

		close FO || die "An error while closing $fileIn\n";
	}
}
print "\nHTML successfully generated !\n";