$strExcelFilename = "D:/PERL/" . "DEVICE-BROWSER-REPORT" . ".xlsx";
						
     		$workbook1 = Excel::Writer::XLSX->new($strExcelFilename);
		my $format_bold = $workbook1->add_format( bold => 1, top=>1, bottom=>1 ); 
        $worksheet1 = $workbook1->add_worksheet('Data');
		$row1= 1;
		
		#$chart = $workbook1->add_chart( type => 'column', embedded => 1 );
			
			foreach $worksheet1 ($workbook1->sheets()) {
		 if( $worksheet1->get_name() eq $SUCCESS_COUNT_SHEET_NAME ) {
			  $format = $workbook1->add_format(%header);
           $worksheet1->write(0,0, "User Id", $format);
		   $worksheet1->write(0,1, "User Display Name", $format);
		   $worksheet1->write(0,2, "User Email Id", $format);
		   $worksheet1->write(0,3, "Device", $format);
           $worksheet1->write(0,4, "Browser", $format);
           $worksheet1->write(0,5,"Application Name", $format);
           $worksheet1->write(0,6,"TimeStamp", $format);
		 	  }
	  }
	  for ($k = 0; $k <= $#responseid; $k++)  {
	
		 
        $worksheet1->write($row1,0, $responseid[$k]);
		$worksheet1->write($row1,1, $responsdisp[$k]);
		$worksheet1->write($row1,2, $responsalter[$k]);
		$worksheet1->write($row1,3, $responseclientdevice[$k]);
		$worksheet1->write($row1,4, $responseclientbrowser[$k]);
		$worksheet1->write($row1,5, $responseapps5[$k]);
		$worksheet1->write($row1,6, $estdate[$k]);
		$app = $responseapps5[$k];
		$device = $responseclientdevice[$k];
		$browser = $responseclientbrowser[$k];
		$userid = $responseid[$k];
		
		++$pivot{$app}{$device}{$browser};
		  ++$pivot{$app}{'TOTAL'}{$browser};
          ++$pivot{$app}{'TOTAL'}{'TOTAL'};
          ++$pivot{$app}{$device}{'TOTAL'};
          ++$pivot{'TOTAL'}{$device}{$browser};
		#++$pivot{$app}{$browser};
           ++$categ{'device'}{$device};
           ++$categ{'browser'}{$browser}; 
		   ++$app{$app};
    
	$row1++;
	#print Dumper $app;
	
	}
	
	$worksheet2 = $workbook1->add_worksheet('Summary');
	$worksheet2->set_column(0,0,20);
my @device  = sort keys %{$categ{'device'}};
my @browser = sort keys %{$categ{'browser'}};
my @applist = sort keys %app;

my $row2 = 0;
$worksheet2->write($row2,1,\@applist);
for my $device (@device){
   my $col2 = 0;
   $worksheet2->write(++$row2,$col2,$device,$format_bold);
   for my $app (@applist){
     $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{'TOTAL'},$format_bold);  
   }
   
   for my $bro (@browser){
     $col2 = 0;
     $worksheet2->write(++$row2,$col2,substr($device,0,3).'-'.$bro);   
     for my $app (@applist){
       $worksheet2->write($row2,++$col2,$pivot{$app}{$device}{$bro});  
     }     
   }  
   ++$row2;
}

# GRAND TOTAL
my $col2 = 0;
$worksheet2->write(++$row2,$col2,'GRAND TOTAL',$format_bold);
for my $app (@applist){
  $worksheet2->write($row2,++$col2,$pivot{$app}{'TOTAL'}{'TOTAL'},$format_bold);  
}



my $chart = $workbook1->add_chart( type => 'column', embedded => 1  );
$chart->set_size( width => 1000, height => 500 );
#my $col = 'B';
$col2 = 0;

for my $app (sort keys %app){
   ++$col2;
  $chart->add_series(
    name       => $app,	
    categories => ['Summary',1,$row2,0,0],
    values     => ['Summary',1,$row2,$col2,$col2]
  );
}

$chart->set_title ( name => 'Results of sample analysis' );
$chart->set_x_axis( name => 'Device/Browser' );
$chart->set_y_axis( name => 'User Count'  );

# add chart

$worksheet2->insert_chart( 22, 1 , $chart);
	$workbook1->close();