sub xlscreator_delete_previous_xls {

my ($path) = @_ ;

opendir(DIR, "$path") or die "cant open Directory $path: $!\n";

while(defined($folder = readdir(DIR))) {
    if (length $folder > 2) {
        my ($file,$type) = split(/\./,$folder) ;
		
		# if ($match) { unless ($file =~ /$match/gi) { next ; } }
		
		# my ($j1,$j2,$accid,$date) = split(/\-/,$file) ;
		# if ($match) { unless ($accid eq $match) { next ; } }
		
        if (-e "$path/$file.xls"){
            unlink ("$path/$file.xls") or print "unable to unlink - $path/$file.xls : $!";
            }
        }
    }

closedir(DIR) ;

} #------------------------------------------------------------------------------------

sub xlscreator_create_xls {

my ($file) =  @_;

$workbook  = Spreadsheet::WriteExcel->new($file) or die "ERROR : $file : $!";

} #------------------------------------------------------------------------------------------

sub xlscreator_close_xls {

$workbook->close();

} #------------------------------------------------------------------------------------------

sub xlscreator_hide_gridlines_xls {

($worksheet_name) =  @_;

$worksheet{$worksheet_name}->hide_gridlines(1);

} #------------------------------------------------------------------------------------------

sub xlscreator_set_margins_xls {

($worksheet_name,$margin_width) =  @_;

$worksheet{$worksheet_name}->set_margins($margin_width); # Set all margins to the same value in inches

} #------------------------------------------------------------------------------------------

sub xlscreator_add_worksheet {

my ($worksheet_name) =  @_;

$worksheet{$worksheet_name} = $workbook->add_worksheet($worksheet_name);

#$worksheet{$worksheet_name}->add_write_handler(qr[\w], \&xlscreator_store_string_widths);

} #------------------------------------------------------------------------------------------

sub xlscreator_add_pie_chart {

my ($worksheet_name) =  @_;

$chart{$worksheet_name} = $workbook->add_chart( type => 'pie', embedded => 1 );

} #------------------------------------------------------------------------------------------

sub xlscreator_write_xls {

my ($worksheet_name,$row,$col,$token,$cellformat) = @_;

$token =~ s/\,//g;

$worksheet{$worksheet_name}->write($row,$col,$token,$cellformat);

#&xlscreator_autofit_columns($worksheet{$worksheet_name});

} #------------------------------------------------------------------------------------------

sub xlscreator_write_formula {

my ($worksheet_name,$row,$col,$formula,$cellformat) = @_;

$worksheet{$worksheet_name}->write_formula($row,$col,$formula,$cellformat);

} #------------------------------------------------------------------------------------------

sub xlscreator_freeze_panes {

my ($worksheet_name,$rows,$cols) = @_;

$worksheet{$worksheet_name}->freeze_panes($rows,$cols); # Freeze the first 5 rows

} #------------------------------------------------------------------------------------------

sub xlscreator_insert_image {

my ($worksheet_name,$row,$col,$file,$margin_right, $margin_bottom) = @_;

$worksheet{$worksheet_name}->insert_image($row,$col,$file,$margin_right,$margin_bottom);

} #------------------------------------------------------------------------------------------

sub xlscreator_xls_format {

$format1 = $workbook->add_format();
$format1->set_font('Verdana');
$format1->set_size(8);

$format2 = $workbook->add_format();
$format2->copy($format1);
$format2->set_bold();  # Turns bold on
$format2->set_num_format('#,##0.00'); # 1,234.56

$format3 = $workbook->add_format();
$format3->copy($format1);
$format3->set_num_format('#,##0.00'); # 1,234.56

$format4 = $workbook->add_format();
$format4->copy($format1);
$format4->set_bold();  # Turns bold on
$format4->set_num_format('#,##0.00'); # 1,234.56
$format4->set_top('6');
$format4->set_bottom('6');

$format5 = $workbook->add_format();
$format5->copy($format1);
$format5->set_underline();   # Single underline

$format6 = $workbook->add_format();
$format6->copy($format1);
$format6->set_underline();   # Single underline
$format6->set_align('right');

$format7 = $workbook->add_format();
$format7->copy($format1);
$format7->set_align('right');
$format7->set_num_format('#,##0.00'); # 1,234.56

$format8 = $workbook->add_format();
$format8->copy($format1);
$format8->set_bold();  # Turns bold on
$format8->set_align('left');
$format8->set_num_format('#,##0.00'); # 1,234.56

$format9 = $workbook->add_format();
$format9->copy($format1);
$format9->set_bold();  # Turns bold on
$format9->set_align('right');
$format9->set_num_format('#,##0.00'); # 1,234.56

$format10 = $workbook->add_format();
$format10->copy($format1);
$format10->set_align('left');

$format11 = $workbook->add_format();
$format11->copy($format1);
$format11->set_align('center');
$format11->set_bg_color(0x16); #0x08 = 'black' ; #0x0C = 'blue' ; #0x10 = 'brown' ; #0x0F = 'cyan' ; #0x17 = 'gray' ; #0x11 = 'green' ; #0x0B = 'lime' ; #0x0E = 'magenta' ; #0x12 = 'navy' ; #0x35 = 'orange' ; #0x21 = 'pink' ; #0x14 = 'purple' ; #0x0A = 'red' ; #0x16 = 'silver' ; #0x09 = 'white' ; #0x0D = 'yellow' ;
$format11->set_top('1');
$format11->set_bottom('1');
$format11->set_left('1');
$format11->set_right('1');

$format12 = $workbook->add_format();
$format12->set_bg_color(0x16);
$format12->set_top('1');
$format12->set_bottom('1');
$format12->set_left('1');
$format12->set_right('1');

$format13 = $workbook->add_format();
$format13->copy($format1);
$format13->set_bg_color(0x16);
$format13->set_num_format('#,##0.00'); # 1,234.56
$format13->set_top('1');
$format13->set_bottom('1');
$format13->set_left('1');
$format13->set_right('1');

$format14 = $workbook->add_format();
$format14->copy($format1);
$format14->set_bold();  # Turns bold on
$format14->set_bg_color(0x16);
$format14->set_top('1');
$format14->set_bottom('1');
$format14->set_left('1');
$format14->set_right('1');

$format15 = $workbook->add_format();
$format15->copy($format1);
$format15->set_align('right');
$format15->set_num_format('#,##0.00'); # 1,234.56
$format15->set_top('1');
$format15->set_bottom('1');
$format15->set_left('1');
$format15->set_right('1');

$format16 = $workbook->add_format();
$format16->copy($format1);
$format16->set_bg_color(0x16);
$format16->set_num_format('#,##0.0000'); # 1,234.56
$format16->set_top('1');
$format16->set_bottom('1');
$format16->set_left('1');
$format16->set_right('1');

$format17 = $workbook->add_format();
$format17->copy($format1);
$format17->set_align('right');
$format17->set_bg_color(0x16);
$format17->set_num_format('#,##0.00'); # 1,234.56
$format17->set_top('1');
$format17->set_bottom('1');
$format17->set_left('1');
$format17->set_right('1');

$format18 = $workbook->add_format();
$format18->copy($format1);
$format18->set_bold();
$format18->set_align('center');

$format19 = $workbook->add_format();
$format19->copy($format1);
$format19->set_num_format('#,##0.00'); # 1,234.56
$format19->set_top('1');
$format19->set_bottom('6');

$format20 = $workbook->add_format();
$format20->copy($format1);
$format20->set_align('center');
$format20->set_num_format(0x0a); #0.00% (0x09 = 0%)

$format21 = $workbook->add_format();
$format21->copy($format1);
$format21->set_align('right');

$format22 = $workbook->add_format();
$format22->copy($format1);
$format22->set_bold();  # Turns bold on
$format22->set_align('right');
$format22->set_bg_color(0x16);
$format22->set_num_format('#,##0.00'); # 1,234.56

$format23 = $workbook->add_format();
$format23->copy($format1);
$format23->set_bold();  # Turns bold on
$format23->set_color(0x0A); # red

$format24 = $workbook->add_format();
$format24->copy($format1);
$format24->set_top('2');

$format25 = $workbook->add_format();
$format25->copy($format1);
$format25->set_bottom('3');

$format26 = $workbook->add_format();
$format26->copy($format1);
$format26->set_bold();  # Turns bold on
$format26->set_align('left');

$format27 = $workbook->add_format();
$format27->copy($format1);
$format27->set_bold();  # Turns bold on
$format27->set_align('right');

$format28 = $workbook->add_format();
$format28->set_underline(2);   # Single underline

$format29 = $workbook->add_format();
$format29->set_font('Verdana');
$format29->set_size(5);

$format30 = $workbook->add_format();
$format30->set_font('Verdana');
$format30->set_size(7);

$format31 = $workbook->add_format();
$format31->set_font('Verdana');
$format31->set_size(4);
$format31->set_align('right');

$format32 = $workbook->add_format();
$format32->set_font('Verdana');
$format32->set_size(6);

$format33 = $workbook->add_format();
$format33->copy($format10);

$format34 = $workbook->add_format();
$format34->copy($format1);
$format34->set_border(1);

$format35 = $workbook->add_format();
$format35->copy($format10);
$format35->set_text_wrap();
$format35->set_border(1);

my $light_blue   = $workbook->set_custom_color(40,'#D5FFFF');

$format36 = $workbook->add_format();
$format36->copy($format8);
$format36->set_bg_color($light_blue);

$format37 = $workbook->add_format();
$format37->copy($format10);
$format37->set_bg_color($light_blue);

$format38 = $workbook->add_format();
$format38->copy($format1);
$format38->set_align('center');
$format38->set_bg_color($light_blue);

$format39 = $workbook->add_format();
$format39->copy($format1);
$format39->set_bg_color($light_blue);

my $light_grey  = $workbook->set_custom_color(41,'#c6c6c6');

$format40 = $workbook->add_format();
$format40->copy($format1);
$format40->set_align('center');
$format40->set_bg_color($light_grey); 
$format40->set_border(1);

my $very_light_grey  = $workbook->set_custom_color(42,'#EEEEEE');

$format41 = $workbook->add_format();
$format41->copy($format10);
$format41->set_bg_color($very_light_grey); 
$format41->set_border(1);
$format41->set_italic();

# merged
$format42 = $workbook->add_format();
$format42->copy($format40);

$format43 = $workbook->add_format();
$format43->copy($format38);
$format43->set_border(1);

$format44 = $workbook->add_format();
$format44->copy($format41);
$format44->set_bg_color(0x09); 

$format45 = $workbook->add_format();
$format45->set_font('Verdana');
$format45->set_size(10);

my $green  = $workbook->set_custom_color(43,'#00b050');

$format46 = $workbook->add_format();
$format46->set_align('center');
$format46->copy($format45);
$format46->set_bg_color($green); 

my $light_blue_1  = $workbook->set_custom_color(44,'#b8cce4');

$format47 = $workbook->add_format();
$format47->copy($format45);
$format47->set_bg_color($light_blue_1); 
$format47->set_border(1);
$format47->set_border_color(0x09);

my $very_light_blue  = $workbook->set_custom_color(45,'#dce6f1');

$format48 = $workbook->add_format();
$format48->copy($format47);
$format48->set_bg_color($very_light_blue); 

$format49 = $workbook->add_format();
$format49->set_font('Arial');
$format49->set_size(12);
$format49->set_bold(); 

$format50 = $workbook->add_format();
$format50->set_font('Arial');
$format50->set_size(10);
$format50->set_bold(); 

$format51 = $workbook->add_format();
$format51->set_font('Arial');
$format51->set_size(9);

#-------------------------------------------------------- red highlighting

my $hred  = $workbook->set_custom_color(46,'#ff0000');

#40
$format52 = $workbook->add_format();
$format52->copy($format1);
$format52->set_align('center');
$format52->set_bg_color($light_grey); 
$format52->set_border(1);
$format52->set_color($hred); # red

#42
$format53 = $workbook->add_format();
$format53->copy($format52);
$format53->set_color($hred); # red

#35
$format54 = $workbook->add_format();
$format54->copy($format10);
$format54->set_text_wrap();
$format54->set_border(1);
$format54->set_color($hred); # red

#41
$format55 = $workbook->add_format();
$format55->copy($format10);
$format55->set_bg_color($very_light_grey); 
$format55->set_border(1);
$format55->set_italic();
$format55->set_color($hred); # red

#34
$format56 = $workbook->add_format();
$format56->copy($format1);
$format56->set_border(1);
$format56->set_color($hred); # red

#---------------------------------------------------

$format57 = $workbook->add_format();
$format57->copy($format1);
$format57->set_top('1');
$format57->set_bottom('6');
 
$format58 = $workbook->add_format();
$format58->copy($format1);
$format58->set_top('1');
$format58->set_bottom('6');
$format58->set_bold();
$format58->set_align('center');

my $ttl_light_blue  = $workbook->set_custom_color(47,'#DAEEF3');

$format59 = $workbook->add_format();
$format59->copy($format40);
$format59->set_bg_color($ttl_light_blue); 
$format59->set_bold();

$format60 = $workbook->add_format();
$format60->copy($format18);
$format60->set_bg_color($ttl_light_blue); 
$format60->set_border(1);

my $ttl_light_green  = $workbook->set_custom_color(48,'#EBF1DE');

$format61 = $workbook->add_format();
$format61->copy($format40);
$format61->set_bg_color($ttl_light_green); 
$format61->set_bold();

$format62 = $workbook->add_format();
$format62->copy($format18);
$format62->set_bg_color($ttl_light_green); 
$format62->set_border(1);

my $ttl_light_orange  = $workbook->set_custom_color(49,'#FDE9D9');

$format63 = $workbook->add_format();
$format63->copy($format40);
$format63->set_bg_color($ttl_light_orange); 
$format63->set_bold();

$format64 = $workbook->add_format();
$format64->copy($format18);
$format64->set_bg_color($ttl_light_orange); 
$format64->set_border(1);

$format65 = $workbook->add_format();
$format65->copy($format40);
$format65->set_align('vcenter');

$format66 = $workbook->add_format();
$format66->copy($format42);
$format66->set_align('vcenter');

$format67 = $workbook->add_format();
$format67->copy($format19);
$format67->set_bold();

$format68 = $workbook->add_format();
$format68->copy($format49);
$format68->set_bold();
$format68->set_text_wrap() ;
$format68->set_align('vcenter');
$format68->set_align('center');

my $ttl_light_yellow  = $workbook->set_custom_color(50,'#FFFFB7');

$format69 = $workbook->add_format();
$format69->copy($format40);
$format69->set_bg_color($ttl_light_yellow); 
$format69->set_bold();

$format70 = $workbook->add_format();
$format70->copy($format18);
$format70->set_bg_color($ttl_light_yellow); 
$format70->set_border(1);

$format71 = $workbook->add_format();
$format71->copy($format23);
$format71->set_bg_color($ttl_light_yellow); 

$format72 = $workbook->add_format();
$format72->copy($format3);
$format72->set_bg_color($ttl_light_yellow); 

$format73 = $workbook->add_format();
$format73->copy($format1);
$format73->set_bg_color($ttl_light_yellow); 

$format74 = $workbook->add_format();
$format74->copy($format69);
$format74->set_bold();
$format74->set_text_wrap() ;
$format74->set_align('vcenter');
$format74->set_align('center');

$format75 = $workbook->add_format();
$format75->copy($format40);
$format75->set_bold();
$format75->set_text_wrap() ;
$format75->set_align('vcenter');
$format75->set_align('center');

$format76 = $workbook->add_format();
$format76->copy($format1);
$format76->set_text_wrap() ;
# $format76->set_bold();
$format76->set_align('right');

} #------------------------------------------------------------------------------------------

sub xlscreator_set_column_width {

my ($worksheet_name,$from,$to,$width) = @_;

$worksheet{$worksheet_name}->set_column("$from".":"."$to", $width);

} #------------------------------------------------------------------------------------------

sub xlscreator_autofit_columns {

my $worksheet = shift;
my $col       = 0;

for my $width (@{$worksheet->{__col_widths}}) {

	$worksheet->set_column($col, $col, $width) if $width;
	$col++;
	}

} #------------------------------------------------------------------------------------------

# The following function is a callback that was added via add_write_handler()
# above. It modifies the write() function so that it stores the maximum
# unwrapped width of a string in a column.

sub xlscreator_store_string_widths {

    my $worksheet = shift;
    my $col       = $_[1];
    my $token     = $_[2];

    # Ignore some tokens that we aren't interested in.
    return if not defined $token;       # Ignore undefs.
    return if $token eq '';             # Ignore blank cells.
    return if ref $token eq 'ARRAY';    # Ignore array refs.
    return if $token =~ /^=/;           # Ignore formula

    # Ignore numbers
    #return if $token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/;

    # Ignore various internal and external hyperlinks. In a real scenario
    # you may wish to track the length of the optional strings used with
    # urls.
    return if $token =~ m{^[fh]tt?ps?://};
    return if $token =~ m{^mailto:};
    return if $token =~ m{^(?:in|ex)ternal:};

    # We store the string width as data in the Worksheet object. We use
    # a double underscore key name to avoid conflicts with future names.
    #
    my $old_width    = $worksheet->{__col_widths}->[$col];
    my $string_width = xlscreator_string_width($token);

    if (not defined $old_width or $string_width > $old_width) {
        # You may wish to set a minimum column width as follows.
        #return undef if $string_width < 10;

        $worksheet->{__col_widths}->[$col] = $string_width;
    }


    # Return control to write();
    return undef;
} #------------------------------------------------------------------------------------------

# Very simple conversion between string length and string width for Arial 10.
# See below for a more sophisticated method.

sub xlscreator_string_width {

return 1.2 * length $_[0];

} #------------------------------------------------------------------------------------------

1;