Ok, see if this is closer
#!/usr/bin/perl
use strict;
use Excel::Writer::XLSX;
my $strExcelFilename= 'chart.xlsx';
my $book = Excel::Writer::XLSX->new($strExcelFilename);
my $format = $book->add_format();
my $sheet1 = $book->add_worksheet('Data');
my @header = ('User ID','Device','Browser','Application');
for my $col(0..$#header){
$sheet1->write(0,$col, $header[$col], $format);
}
my $row = 1;
my %pivot=(); # pivot table count
my %categ=();
my %app =();
while (<DATA>) {
chomp;
next unless /\S/;
my @f = split ';',$_;
my ($userid,$device,$browser,$app) = @f;
++$pivot{$app}{$device};
++$pivot{$app}{$browser};
++$categ{'device'}{$device};
++$categ{'browser'}{$browser};
++$app{$app};
$sheet1->write_row($row++,0, \@f);
}
# create summary pivot table
my $sheet2 = $book->add_worksheet('Summary');
my @device = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;
$sheet2->write_row(0,1,\@applist);
$row = 1;
for my $categ (@device,@browser){
my $col = 0;
$sheet2->write($row,$col++,$categ);
for my $app (@applist){
$sheet2->write($row,$col++,$pivot{$app}{$categ});
}
++$row
}
my $chart = $book->add_chart( type => 'column', embedded => 1 );
my $col = 'B';
for my $app (sort keys %app){
$chart->add_series(
name => $app,
categories => '=Summary!$A2:$A'.$row,
values => '=Summary!$'.$col.'$2:$'.$col.$row,
);
++$col;
}
$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Device/Browser' );
$chart->set_y_axis( name => 'User Count' );
#$chart->set_style( 11 );
# add chart
$sheet2->insert_chart( 'A'.($row+2), $chart, 0, 0 );
$book->close();
# user;device;browser;app
__DATA__
user1;laptop;Chrome;AppA
user2;laptop;IE11;AppB
user3;desktop;IE11;AppC
user4;laptop;Chrome;AppD
user5;laptop;Firefox;AppA
user6;laptop;IE11;AppC
user7;desktop;Safari;AppE
user8;desktop;Safari;AppE
user9;desktop;Safari;AppE
poj
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.