#!/usr/bin/perl -w
my
$workbook
= Excel::Writer::XLSX->new(
'sales.xlsx'
);
my
$worksheet
=
$workbook
->add_worksheet(
'May Sales'
);
my
%heading
= (
bold
=> 1,
pattern
=> 1,
fg_color
=> 19,
border
=> 1,
align
=>
'center'
,
);
my
%total
= (
bold
=> 1,
top
=> 1,
num_format
=>
'$#,##0.00'
);
my
$heading
=
$workbook
->add_format(
%heading
);
my
$total_format
=
$workbook
->add_format(
%total
);
my
$price_format
=
$workbook
->add_format(
num_format
=>
'$#,##0.00'
);
my
$date_format
=
$workbook
->add_format(
num_format
=>
'mmm d yyy'
);
$worksheet
->freeze_panes( 1 );
$worksheet
->
write
(
'A1'
,
'Item'
,
$heading
);
$worksheet
->
write
(
'B1'
,
'Quantity'
,
$heading
);
$worksheet
->
write
(
'C1'
,
'Price'
,
$heading
);
$worksheet
->
write
(
'D1'
,
'Total'
,
$heading
);
$worksheet
->
write
(
'E1'
,
'Date'
,
$heading
);
$worksheet
->set_column(
'A:A'
, 25 );
$worksheet
->set_column(
'B:B'
, 10 );
$worksheet
->set_column(
'C:E'
, 16 );
my
@sales
;
foreach
my
$line
( <DATA> ) {
chomp
$line
;
next
if
$line
eq
''
;
my
@items
=
split
/,/,
$line
;
push
@sales
, \
@items
;
}
my
$row
= 1;
foreach
my
$sale
(
@sales
) {
$worksheet
->
write
(
$row
, 0,
@$sale
[0] );
$worksheet
->
write
(
$row
, 1,
@$sale
[1] );
$worksheet
->
write
(
$row
, 2,
@$sale
[2],
$price_format
);
my
$formula
=
'='
. xl_rowcol_to_cell(
$row
, 1 ) .
"*"
. xl_rowcol_to_cell(
$row
, 2 );
$worksheet
->
write
(
$row
, 3,
$formula
,
$price_format
);
my
$date
= xl_decode_date_US(
@$sale
[3] );
$worksheet
->
write
(
$row
, 4,
$date
,
$date_format
);
$row
++;
}
my
$total
=
'=SUM(D2:'
. xl_rowcol_to_cell(
$row
- 1, 3 ) .
")"
;
$worksheet
->
write
(
$row
, 3,
$total
,
$total_format
);