#!/usr/bin/perl
use Excel::Writer::XLSX;
use Text::CSV;
use Switch;

my $csv_parser = Text::CSV->new({sep_char => ','});	# CSV parser for the config file
my $conf_file = $ARGV[0] || die("Please give a config file in CSV format\n");	#Config file name
open(CONF, "<:encoding(utf8)", $conf_file) || die("Unable to open config file \"$conf_file\": $!\n");
my %conf_table;	# Will hold all the config information we read in

# First, we will read the config file. It is a .csv file that consists of
# one-field rows giving the name of an Excel (.xlsx) file to create, each
# followed by one or more two-field rows each describing a sheet to be created
# in that file.  The fields here are first the name of the sheet, then the .csv
# file to put in it.
#
# These are placed into the hash %conf_table.  Each xlsx filename is a key in
# the hash, and its value is an array of arrays, with the outer array being the
# worksheets and each inner array being a two-element array with worksheet name
# and csv file as in the corresponding config file entry.

CONF_LINE: while(<CONF>)
{
	chomp;
	if(length == 0) {next}
	$csv_parser->parse($_) || die("Config file error, line $.: cannot parse line as csv.\n");
	my @conf_fields = $csv_parser->fields();
	my $num_fields = scalar @conf_fields;
	switch($num_fields)
	{
		case 0 {next CONF_LINE}
		case 1 {$filename = $conf_fields[0]}
		case 2 
		{
			if(!$filename) {die "Config file error, line $.: Must give a workbook name before listing worksheets to put in it.\n"}
			push(@{$conf_table{$filename}}, [@conf_fields]);
		}
		else {die("Config error, line $.: Expected 1 or 2 fields per line, got $num_fields.\n")}
	}
}
close(CONF);
foreach $filename (keys(%conf_table))
{
	print "Creating workbook '$filename'\n";
	my $xlsx_file = Excel::Writer::XLSX->new($filename);
	my @conf_worksheets = @{$conf_table{$filename}};
	my $title_format = $xlsx_file->add_format('bold' => 1);
	WORKSHEET: foreach $worksheet_fields (@conf_worksheets)
	{
		print "Creting sheet '@$worksheet_fields[0]' from file '@$worksheet_fields[1]'\n";
		my $sheet = $xlsx_file->add_worksheet(@$worksheet_fields[0]);
		if(!open(CSV, '<', @$worksheet_fields[1])){warn "Error opening csv file '@$worksheet_fields[1]': $!\n"; next WORKSHEET}
		if(my $firstline = <CSV>)
		{
			chomp $firstline;
			$csv_parser->parse($firstline);
			my @csv_fields = $csv_parser->fields();
			$sheet->write_row(0,0,\@csv_fields, $title_format);
		}
		while(<CSV>)
		{
			chomp;
			$csv_parser->parse($_);
			my @csv_fields = $csv_parser->fields();
			$sheet->write_row($.-1,0,\@csv_fields);
		}
		close(CSV);
	}
}
