Tiny File Manager

Back Back Up Advanced Editor

use strict;
#use Date::Calc;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

our $workbook = Spreadsheet::WriteExcel->new("perl2.xls"); 
our %format = &get_excel_cell_formats;

our %worksheet;
# worksheet for text
$worksheet{text}   = $workbook->add_worksheet('text'); 
&write_text($worksheet{text});

# worksheet for dates
$worksheet{dates}   = $workbook->add_worksheet('dates'); 
&write_dates($worksheet{dates});

# worksheet for money
$worksheet{money}   = $workbook->add_worksheet('money'); 
&write_money($worksheet{money});

$workbook->close();
exit;

sub first_last_month {
	my ($year,$month,$day) = Date::Calc::Today;
	$month--;
	if ($month < 1) {
		$month += 12;
		$year--;
	}
	return ($year, $month, $day);
}

sub last_last_month {
	my ($year,$month,$day) = Date::Calc::Today;
	return Date::Calc::Add_Delta_Days( $year, $month, 1, -1);
}

sub get_excel_cell_formats {
	my $format;
	# bold
	$format{bold} = $workbook->add_format();
	$format{bold}->set_bold();
	# german date format
	$format{date_ger} = $workbook->add_format();
	$format{date_ger}->set_num_format('dd.mm.yyyy');
	# Euro-cells: 1.234,56 E
	$format{currency_euro} = $workbook->add_format();
	$format{currency_euro}->set_num_format( sprintf '0,000.00 "%s"', chr(128));
	# Euro-cells, with red values below zero
	$format{currency_euro_red} = $workbook->add_format();
	$format{currency_euro_red}->set_num_format( 
		sprintf '0,000.00 "%s";[Red]-0,000.00 "%s"', chr(128), chr(128));
	return %format;
}

sub write_text {
	my $worksheet = shift;
	my ($x,$y)=(0,0);
	# Simple text
	$worksheet->write($y++, $x, "Hi Excel!");
	# Simple text bold
	$worksheet->write($y++, $x, "Hi Excel!", $format{bold});
}

sub write_dates {
	my $worksheet = shift;
	my $y = 0;
	
	# Set width of column
	$worksheet->set_column(0, 0,  20);
	$worksheet->set_column(1, 1,  15);

# German date: 30.01.2002
	my ($year,$month,$day) = Date::Calc::Today;
	my $date = xl_date_list($year,$month,$day);
	$worksheet->write($y, 0, "Today" ); 
	$worksheet->write($y, 1, $date, $format{date_ger} ); 
	$y++;
	
	# First of this month
	my ($year,$month,$day) = Date::Calc::Today;
	my $date = xl_date_list($year, $month, 1);
	$worksheet->write($y, 0, "First of this month" ); 
	$worksheet->write($y, 1, $date, $format{date_ger} ); 
	$y++;
	
	# First last month
	my ($year,$month,$day) = &first_last_month;
	my $date = xl_date_list($year, $month, $day);
	$worksheet->write($y, 0, "First of last month" ); 
	$worksheet->write($y, 1, $date, $format{date_ger} ); 
	$y++;
	
	# Last last month
	my ($year,$month,$day) = &last_last_month;
	my $date = xl_date_list($year, $month, $day);
	$worksheet->write($y, 0, "Last of last month" ); 
	$worksheet->write($y, 1, $date, $format{date_ger} ); 
	$y++;
}

sub write_money {
	my $worksheet = shift;
	my ($x,$y) = (1,0);
	
	# some currency values in a row and sum it up
	# remember first and last cell to sum it up later
	# SUM works only with A1 not 0/0
	my $first = xl_rowcol_to_cell($y,$x);
	my $last;
	foreach (1 .. 10) {
		$worksheet->write($y, $x, rand()*90000 +1 - 40000, 
			$format{currency_euro_red} ); 
		# remember last row
		$last = xl_rowcol_to_cell($y,$x);
		$y++;
	}
	# Sum it up
	$worksheet->write($y, $x-1, "Sum" );
	$worksheet->write($y, $x, "=SUM($first:$last)", 
		$format{currency_euro_red} );
}