phpspreadsheet create a new worksheet on salesperson name change

kat35601 · June 14, 2019

I am creating a spreadsheet with phpspreadsheet using sql data. After the first loop that gets all the data I want to loop through the data again and create new worksheets for each salesperson(ARJSALESEMPLOYEEID).

This code gets me all the data in a worksheet how would I then run through it again and create the worksheets "createSheets($row[ARJSALESEMPLOYEEID])" for each salesperson?

<?php
//call the autoload
require 'vendor/autoload.php';
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;

//styling arrays
//table head style
$tableHead = [
    'font'=>[
        'color'=>[
            'rgb'=>'FFFFFF'
        ],
        'bold'=>true,
        'size'=>11
    ],
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => '538ED5'        
        ]
    ]
];
//even row
$evenRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => 'F2F2F2'
        ]
    ]
];
//odd row
$oddRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
            'rgb' => 'FFFFFF'
        ]
    ]
];

//styling arrays end
$exrow=3;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//get current active sheet (first sheet)
$sheet = $spreadsheet->getActiveSheet();

//set default font
$spreadsheet->getDefaultStyle()
    ->getFont()
    ->setName('Arial')
    ->setSize(10);

//heading
$spreadsheet->getActiveSheet()
    ->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:P1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(25);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(10);


//header text//current row
$spreadsheet->getActiveSheet()
    ->setCellValue('A2',"INVOICE DATE")
    ->setCellValue('B2',"SALESPERSON")
    ->setCellValue('C2',"INVOICE ID")
    ->setCellValue('D2',"ORDER ID")
    ->setCellValue('E2',"ORDER DATE")
    ->setCellValue('F2',"CUSTOMER ID")
    ->setCellValue('G2',"NAME")
    ->setCellValue('H2',"GROUP ID")
    ->setCellValue('I2',"STATE")
    ->setCellValue('J2',"QTY")
    ->setCellValue('K2',"PART")
    ->setCellValue('L2',"UOM")
    ->setCellValue('M2',"GROUP")
    ->setCellValue('N2',"GROSS")
    ->setCellValue('O2',"DISCOUNT")
    ->setCellValue('P2',"NET");
    

//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A2:P2')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect(removed);
if(!$connect) {
    exit("Connection Failed: " . $connect);
}

//the sql query
$sql="SELECT 
ARPINVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,ompCreatedDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE
,ARLSALESORDERID

,case when arlPartGroupID = 'PROMO' then 4.00 
when arlPartGroupID = 'DC' then 2.00 
when cmocustomergroupid = 'LIST' then 5.00 
when cmocustomergroupid = 'VOL' then 4.00
when cmocustomergroupid = 'VOL2' then 4.00
when cmocustomergroupid = 'VOL2' then 5.00
else 5.00 end as comission_percent

FROM M1_KF.dbo.ARInvoices
INNER JOIN M1_KF.dbo.ARInvoiceLines ON arpARInvoiceID = arlARInvoiceID
LEFT OUTER JOIN M1_KF.dbo.organizations ON ARPCUSTOMERORGANIZATIONID = cmoorganizationid
LEFT OUTER JOIN M1_KF.dbo.arinvoicesalespeople ON arparinvoiceid = arjarinvoiceid
left outer join M1_KF.dbo.SalesOrders on ompSalesOrderID=UARPSALESORDERNUMBER

where arlsalesorderid !='' and arpPostedDate >='05-01-2019'and arpPostedDate <'06-01-2019'
and arlPartGroupID not in('FRT')
ORDER BY ARJSALESEMPLOYEEID,arpARInvoiceID";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

 while ($row = odbc_fetch_array($result))
{
//loop through the data

    $spreadsheet->getActiveSheet()
        ->setCellValue('A'.$exrow ,$row['ARPINVOICEDATE'])
        ->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
        ->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
        ->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
        ->setCellValue('E'.$exrow ,$row['ompCreatedDate'])
        ->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
        ->setCellValue('G'.$exrow ,$row['CMONAME'])
        ->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
        ->setCellValue('I'.$exrow ,$row['cmoState'])
        ->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
        ->setCellValue('K'.$exrow ,$row['ARLPARTID'])
        ->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
        ->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
        ->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
        ->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
        ->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE']);
        
        
        
    //set row style
    if( $exrow % 2 == 0 ){
        //even row
        $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':P'.$exrow)->applyFromArray($evenRow);
    }else{
        //odd row
        $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':P'.$exrow)->applyFromArray($oddRow);
    }
    //increment row
    $exrow++;
}


//define first row and last row
$firstRow=2;
$lastRow=$exrow-1;
//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":P".$lastRow);

odbc_close($connect);


//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="result.xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');

maxxd · June 14, 2019

Create a variable called "$currentSalesRep". At the start of your while loop, grab the current record's name field and compare the value to $currentSalesRep. If the values are different, create a new worksheet then store the current record's name value in $currentSalesRep.

Barand · June 14, 2019

An alternative approach is to create an array during your first pass of the data. This array would contain arrays of records for each rep. EG

$salesdata = [
                 'Sales_rep1'  => [
                                      0 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ],
                                      1 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ],
                                      2 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ]
                                  ],
                 'Sales_rep2'  => [
                                      0 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ],
                                      1 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ],
                                      2 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ],
                                      3 => [ 'Col1',
                                             'Col2',
                                             'Col3'
                                           ]
                                  ],
                                  
             ];

You can then loop through the array creating your sheets for each rep.

foreach ($salesdata as $rep => $sales) {
    create new sheet
    foreach ($sales as $recno => $recdata) {
        add new row
        foreach ($recdata as $col) {
            output column
        }
    }
}

kat35601 · June 21, 2019

Creating the array is give me great trouble could you elaborate for me just a bit.

thanks

Barand · June 21, 2019

Not rocket science

     $salesdata = [];  // initialize the array

     while ($row = odbc_fetch_array($result))
     {
         $salesdata[$row['ARJSALESEMPLOYEEID']][] = $row;
         
         // restof your current processing in the loop
         // goes here
     }

... assuming you want a sheet for each ARJSALESEMPLOYEEID value;

kat35601 · July 11, 2019

Finally got time to work on this again. The foreach Statement works for the First salesperson "A" but stops after that with an error that it does not see the "Use PhpOffice\PhpSpreadsheet\Style\Fill"

<?php

//call the autoload
require 'vendor/autoload.php';
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;


//styling arrays
//table head style
$tableHead = [
	'font'=>[
		'color'=>[
			'rgb'=>'FFFFFF'
		],
		'bold'=>true,
		'size'=>11
	],
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => '538ED5'		
		]
	]
];
//even row
$evenRow = [
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => 'F2F2F2'
		]
	]
];
//odd row
$oddRow = [
	'fill'=>[
		'fillType' => Fill::FILL_SOLID,
		'startColor' => [
			'rgb' => 'FFFFFF'
		]
	]
];

$salesperson=array('A',
'B',
'C',
'D',
'F',
'G',
'H',
'I');
foreach($salesperson as $salesId){

$commission_percent=0;
$price=0;
$commission=0;
//styling arrays end
$exrow=6;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//get current active sheet (first sheet)
$sheet = $spreadsheet->getActiveSheet();

//set default font
$spreadsheet->getDefaultStyle()
	->getFont()
	->setName('Arial')
	->setSize(10);

//heading
$spreadsheet->getActiveSheet()
	->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:R1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(45);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(10);


//header text//current row
$spreadsheet->getActiveSheet()
	->setCellValue('A5',"INVOICE DATE")
	->setCellValue('B5',"SALESPERSON")
	->setCellValue('C5',"INVOICE ID")
	->setCellValue('D5',"ORDER ID")
	->setCellValue('E5',"ORDER DATE")
	->setCellValue('F5',"CUSTOMER ID")
	->setCellValue('G5',"NAME")
	->setCellValue('H5',"GROUP ID")
	->setCellValue('I5',"STATE")
	->setCellValue('J5',"QTY")
	->setCellValue('K5',"PART")
	->setCellValue('L5',"UOM")
	->setCellValue('M5',"GROUP")
	->setCellValue('N5',"GROSS")
	->setCellValue('O5',"DISCOUNT")
	->setCellValue('P5',"NET")
	->setCellValue('Q5',"CommPrcnt")
	->setCellValue('R5',"Commission");
	



//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A5:R5')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

//the sql query
$sql="select INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,createdDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLSALESORDERID
,CONVERT(DECIMAL(10,2),ARLFULLEXTENDEDPRICEBASE)  as ARLFULLEXTENDEDPRICEBASE
,CONVERT(DECIMAL(10,2),ARLEXTENDEDDISCOUNTBASE) as ARLEXTENDEDDISCOUNTBASE
,CONVERT(DECIMAL(10,2),ARLEXTENDEDPRICEBASE) as ARLEXTENDEDPRICEBASE

,commission_percent
--,sum(commission_percent*ARLEXTENDEDPRICEBASE) as commission
from

(SELECT 

convert(varchar, ARPINVOICEDATE, 101) as INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,convert(varchar, ompCreatedDate, 101) as CreatedDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE


,ARLSALESORDERID

,case when arlPartGroupID = 'PROMO' then .04 
when arlPartGroupID = 'DC' then .02 
when cmocustomergroupid = 'LIST' then .05
when cmocustomergroupid = 'VOL' then .04
when cmocustomergroupid = 'VOL2' then .04
when cmocustomergroupid = 'VOL4' then .05
else .05 end as commission_percent

FROM M1_KF.dbo.ARInvoices
INNER JOIN M1_KF.dbo.ARInvoiceLines ON arpARInvoiceID = arlARInvoiceID
LEFT OUTER JOIN M1_KF.dbo.organizations ON ARPCUSTOMERORGANIZATIONID = cmoorganizationid
LEFT OUTER JOIN M1_KF.dbo.arinvoicesalespeople ON arparinvoiceid = arjarinvoiceid
left outer join M1_KF.dbo.SalesOrders on ompSalesOrderID=UARPSALESORDERNUMBER

where  arpPostedDate >='06-01-2019'and arpPostedDate <'07-01-2019'
and ARJSALESEMPLOYEEID='$salesId'
and arlPartGroupID not in('FRT')) as test


group by
 INVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,createdDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLSALESORDERID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE
,commission_percent


ORDER BY ARJSALESEMPLOYEEID,arpARInvoiceID";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

 while ($row = odbc_fetch_array($result))
{
//loop through the data
$curentsalesrep=$row['ARJSALESEMPLOYEEID'];
//sum(commission_percent*ARLEXTENDEDPRICEBASE) as commiss

$prtcnt=floatval($row['commission_percent']);
$commission_percent=number_format($prtcnt, 2);
$price=floatval($row['ARLEXTENDEDPRICEBASE']);
$commission=$commission_percent*$price;
	$spreadsheet->getActiveSheet()
		->setCellValue('A'.$exrow ,$row['INVOICEDATE'])
		->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
		->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
		->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
		->setCellValue('E'.$exrow ,$row['createdDate'])
		->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
		->setCellValue('G'.$exrow ,$row['CMONAME'])
		->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
		->setCellValue('I'.$exrow ,$row['cmoState'])
		->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
		->setCellValue('K'.$exrow ,$row['ARLPARTID'])
		->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
		->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
		->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
		->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
		->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE'])
		->setCellValue('Q'.$exrow ,$row['commission_percent'])
		->setCellValue('R'.$exrow ,$commission);
	
		
		
	//set row style
	if( $exrow % 2 == 0 ){
		//even row
		$spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($evenRow);
	}else{
		//odd row
		$spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($oddRow);
	}
	//increment row
	$exrow++;
}


//define first row and last row
$firstRow=5;
$lastRow=$exrow-1;
$totalrow=$exrow+1;
$percentrow=$totalrow+1;



//SUBtotal and SUM rows
$spreadsheet->getActiveSheet()->setCellValue('J2', "=SUBTOTAL(9,J6:J$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('N2', "=SUBTOTAL(9,N6:N$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('O2', "=SUBTOTAL(9,O6:O$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('P2', "=SUBTOTAL(9,P6:P$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('R2', "=SUBTOTAL(9,R6:R$lastRow)");
// Total All
$spreadsheet->getActiveSheet()->setCellValue('J3', "=SUM(J6:J$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('N3', "=SUM(N6:N$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('O3', "=SUM(O6:O$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('P3', "=SUM(P6:P$lastRow)");
$spreadsheet->getActiveSheet()->setCellValue('R3', "=SUM(R6:R$lastRow)");
//Percent
$spreadsheet->getActiveSheet()->setCellValue('J4', "=SUM(J2/J3)*100");
$spreadsheet->getActiveSheet()->setCellValue('N4', "=SUM(N2/N3)*100");
$spreadsheet->getActiveSheet()->setCellValue('O4', "=SUM(O2/O3)*100");
$spreadsheet->getActiveSheet()->setCellValue('P4', "=SUM(P2/P3)*100");
$spreadsheet->getActiveSheet()->setCellValue('R4', "=SUM(R2/R3)*100");
//Format currency 
$spreadsheet->getActiveSheet()->getStyle('N'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('O'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('P'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('R'.$exrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('N'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('O'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('P'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
	$spreadsheet->getActiveSheet()->getStyle('R'.$totalrow)->getNumberFormat()
	->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
//
$spreadsheet->getActiveSheet()
->setCellValue('G2',"Filter Total")
->setCellValue('G3',"Grand Total")
->setCellValue('G4',"Percent");

$spreadsheet->getActiveSheet()->getStyle('G2')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('G3')->getFont()->setSize(12);
$spreadsheet->getActiveSheet()->getStyle('G4')->getFont()->setSize(12);

$spreadsheet->getActiveSheet()->getStyle("R".$firstRow.":R".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $spreadsheet->getActiveSheet()->getStyle("P".$firstRow.":P".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
    $spreadsheet->getActiveSheet()->getStyle("N".$firstRow.":N".$totalrow)->getNumberFormat()
    ->setFormatCode('#,##0.00');
//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":R".$totalrow);
odbc_close($connect);


//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="'.$salesId.'".xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');
}
echo "done";
?>

Psycho · July 11, 2019

That is NOT what Barand suggested. You don't create some arbitrary array of salespeople. You use your query results to dynamically create the array BEFORE you start creating your output. You should really break up your functionality into distinct functions/files/etc. It will make things so much easier. Try adding better organization to your code and you know exactly where the problem is based on the error.

Anyway, I did my best to rewrite the code using Barand's suggestion and adding a better structure. Obviously I can't test it as I don't have your DB, but hopefully thi swill work or you can fix whatever minor errors I may have introduced

	<?php
	
//call the autoload
require 'vendor/autoload.php';
//load phpspreadsheet class using namespaces
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//call iofactory instead of xlsx writer
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
	############### Begin styling arrays ###############
//table head style
$tableHead = [
    'font'=>[
        'color'=>['rgb'=>'FFFFFF'],
        'bold'=>true,
        'size'=>11
    ],
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
        'rgb' => '538ED5'        
        ]
    ]
];
//even row
$evenRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
        'rgb' => 'F2F2F2'
        ]
    ]
];
//odd row
$oddRow = [
    'fill'=>[
        'fillType' => Fill::FILL_SOLID,
        'startColor' => [
        'rgb' => 'FFFFFF'
        ]
    ]
];
############### End styling arrays ###############
	############### Begin Data Abstraction ###############
// Create DB connection
$connect =odbc_connect(removed);
if(!$connect) {
    exit("Connection Failed: " . $connect);
}
	//the sql query
$sql="SELECT 
ARPINVOICEDATE
,ARJSALESEMPLOYEEID
,ARPARINVOICEID 
,UARPSALESORDERNUMBER
,ompCreatedDate
,ARPCUSTOMERORGANIZATIONID
,CMONAME
,CMOCUSTOMERGROUPID
,cmoState
,ARLINVOICEQUANTITY
,ARLPARTID
,ARLUNITOFMEASURE
,ARLPARTGROUPID
,ARLFULLEXTENDEDPRICEBASE
,ARLEXTENDEDDISCOUNTBASE
,ARLEXTENDEDPRICEBASE
,ARLSALESORDERID
	,case when arlPartGroupID = 'PROMO' then 4.00 
when arlPartGroupID = 'DC' then 2.00 
when cmocustomergroupid = 'LIST' then 5.00 
when cmocustomergroupid = 'VOL' then 4.00
when cmocustomergroupid = 'VOL2' then 4.00
when cmocustomergroupid = 'VOL2' then 5.00
else 5.00 end as comission_percent
	FROM M1_KF.dbo.ARInvoices
INNER JOIN M1_KF.dbo.ARInvoiceLines ON arpARInvoiceID = arlARInvoiceID
LEFT OUTER JOIN M1_KF.dbo.organizations ON ARPCUSTOMERORGANIZATIONID = cmoorganizationid
LEFT OUTER JOIN M1_KF.dbo.arinvoicesalespeople ON arparinvoiceid = arjarinvoiceid
left outer join M1_KF.dbo.SalesOrders on ompSalesOrderID=UARPSALESORDERNUMBER
	where arlsalesorderid !='' and arpPostedDate >='05-01-2019'and arpPostedDate <'06-01-2019'
and arlPartGroupID not in('FRT')
ORDER BY ARJSALESEMPLOYEEID,arpARInvoiceID";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
	//Process the data into an array
$salesData = array();
while ($row = odbc_fetch_array($result))
{
    //loop through the data
    $salesData[$row['ARJSALESEMPLOYEEID']][$row['arpARInvoiceID'] = $row;
}
//Close connection
odbc_close($connect);
############### End Data Abstraction ###############
	############### Begin Create Spreadsheet ###############
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//get current active sheet (first sheet)
	//set default font
$spreadsheet->getDefaultStyle()
    ->getFont()
    ->setName('Arial')
    ->setSize(10);
	//Loop through records for each sales data
$lastSalesID = false;
foreach($salesData as $salesId => $salesData)
{
    //Check if this is a different sales id than the last
    if($salesId !== $lastSalesID || )
    {
        //If not the first salesID, create a new worksheet
        if($lastSalesID !== false)
        {
            $spreadsheet->createSheet();
        }
        //Set new last sales ID
        $lastSalesID = $salesId;
        
        //Create the worksheet header data
        //heading
        $spreadsheet->getActiveSheet()->setCellValue('A1',"Comission");
        //merge heading
        $spreadsheet->getActiveSheet()->mergeCells("A1:P1");
        // set font style
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        // set cell alignment
        $spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
	        //setting column width
        $spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(30);
        $spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(10);
        $spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(10);
	        //header text//current row
        $spreadsheet->getActiveSheet()
            ->setCellValue('A2',"INVOICE DATE")
            ->setCellValue('B2',"SALESPERSON")
            ->setCellValue('C2',"INVOICE ID")
            ->setCellValue('D2',"ORDER ID")
            ->setCellValue('E2',"ORDER DATE")
            ->setCellValue('F2',"CUSTOMER ID")
            ->setCellValue('G2',"NAME")
            ->setCellValue('H2',"GROUP ID")
            ->setCellValue('I2',"STATE")
            ->setCellValue('J2',"QTY")
            ->setCellValue('K2',"PART")
            ->setCellValue('L2',"UOM")
            ->setCellValue('M2',"GROUP")
            ->setCellValue('N2',"GROSS")
            ->setCellValue('O2',"DISCOUNT")
            ->setCellValue('P2',"NET");
            
        //set font style and background color
        $spreadsheet->getActiveSheet()->getStyle('A2:P2')->applyFromArray($tableHead);
        
        //Output invoice records
        $lastInvID = false;
        $exrow = 3; //Set starting row for current sheet
        foreach($salesData as $invId => $invData)
        {
            $spreadsheet->getActiveSheet()
                ->setCellValue('A'.$exrow ,$data['ARPINVOICEDATE'])
                ->setCellValue('B'.$exrow ,$data['ARJSALESEMPLOYEEID'])
                ->setCellValue('C'.$exrow ,$data['ARPARINVOICEID'])
                ->setCellValue('D'.$exrow ,$data['UARPSALESORDERNUMBER'])
                ->setCellValue('E'.$exrow ,$data['ompCreatedDate'])
                ->setCellValue('F'.$exrow ,$data['ARPCUSTOMERORGANIZATIONID'])
                ->setCellValue('G'.$exrow ,$data['CMONAME'])
                ->setCellValue('H'.$exrow ,$data['CMOCUSTOMERGROUPID'])
                ->setCellValue('I'.$exrow ,$data['cmoState'])
                ->setCellValue('J'.$exrow ,$data['ARLINVOICEQUANTITY'])
                ->setCellValue('K'.$exrow ,$data['ARLPARTID'])
                ->setCellValue('L'.$exrow ,$data['ARLUNITOFMEASURE'])
                ->setCellValue('M'.$exrow ,$data['ARLPARTGROUPID'])
                ->setCellValue('N'.$exrow ,$data['ARLFULLEXTENDEDPRICEBASE'])
                ->setCellValue('O'.$exrow ,$data['ARLEXTENDEDDISCOUNTBASE'])
                ->setCellValue('P'.$exrow ,$data['ARLEXTENDEDPRICEBASE']);
                
            //set row style
            $rowStyle = ( $exrow % 2 == 0 ) ? $evenRow : $oddRow;
            $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':P'.$exrow)->applyFromArray($rowStyle);
	            //increment row
            $exrow++;
            
        }
        
        //Create filter for current sheet
        //define first row and last row
        $firstRow=2;
        $lastRow=$exrow-1;
        //set the autofilter
        $spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":P".$lastRow);
    }
	}
	######## OUTPUT THE FILE ################
//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="result.xlsx"');
	//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');

kat35601 · August 14, 2019

Sorry it has taken me so long to get back. I can not get this to work as described above PHPSPREADSHEET does have some memory limitations and trying to do it in one file runs PHPSPREADSHEET out of memory even with the setting set to the max. So I need to create one file per salespersonID but the foreach does not loop through as expected with this code I get a file with the first salespersonID and then it stops.

$salesperson=array('1name',
'2name',
'3name',
'4name',
'5name');
foreach($salesperson as $salesId ){

$commission_percent=0;
$price=0;
$commission=0;
//styling arrays end
$exrow=6;
//make a new spreadsheet object
$spreadsheet = new Spreadsheet();
//set default font
$spreadsheet->getDefaultStyle()
   ->getFont()
   ->setName('Arial')
   ->setSize(10);

//heading
$spreadsheet->getActiveSheet()
   ->setCellValue('A1',"Comission");

//merge heading
$spreadsheet->getActiveSheet()->mergeCells("A1:R1");

// set font style
$spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);

// set cell alignment
$spreadsheet->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

//setting column width



//header text//current row
$spreadsheet->getActiveSheet()
   ->setCellValue('A5',"INVOICE DATE")
   ->setCellValue('B5',"SALESPERSON")
   ->setCellValue('C5',"INVOICE ID")
   ->setCellValue('D5',"ORDER ID")
   ->setCellValue('E5',"ORDER DATE")
   ->setCellValue('F5',"CUSTOMER ID")
   ->setCellValue('G5',"NAME")
   ->setCellValue('H5',"GROUP ID")
   ->setCellValue('I5',"STATE")
   ->setCellValue('J5',"QTY")
   ->setCellValue('K5',"PART")
   ->setCellValue('L5',"UOM")
   ->setCellValue('M5',"GROUP")
   ->setCellValue('N5',"GROSS")
   ->setCellValue('O5',"DISCOUNT")
   ->setCellValue('P5',"NET")
   ->setCellValue('Q5',"CommPrcnt")
   ->setCellValue('R5',"Commission");




//set font style and background color
$spreadsheet->getActiveSheet()->getStyle('A5:R5')->applyFromArray($tableHead);
// the connection 
$connect =odbc_connect("removed");
if(!$connect) {
   exit("Connection Failed: " . $connect);
}

//the sql query
$sql="sql statement that gets data for each $salesId ";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}

while ($row = odbc_fetch_array($result))
{


$prtcnt=floatval($row['commission_percent']);
$commission_percent=number_format($prtcnt, 2);
$price=floatval($row['ARLEXTENDEDPRICEBASE']);
$commission=$commission_percent*$price;
   $spreadsheet->getActiveSheet()
       ->setCellValue('A'.$exrow ,$row['INVOICEDATE'])
       ->setCellValue('B'.$exrow ,$row['ARJSALESEMPLOYEEID'])
       ->setCellValue('C'.$exrow ,$row['ARPARINVOICEID'])
       ->setCellValue('D'.$exrow ,$row['UARPSALESORDERNUMBER'])
       ->setCellValue('E'.$exrow ,$row['createdDate'])
       ->setCellValue('F'.$exrow ,$row['ARPCUSTOMERORGANIZATIONID'])
       ->setCellValue('G'.$exrow ,$row['CMONAME'])
       ->setCellValue('H'.$exrow ,$row['CMOCUSTOMERGROUPID'])
       ->setCellValue('I'.$exrow ,$row['cmoState'])
       ->setCellValue('J'.$exrow ,$row['ARLINVOICEQUANTITY'])
       ->setCellValue('K'.$exrow ,$row['ARLPARTID'])
       ->setCellValue('L'.$exrow ,$row['ARLUNITOFMEASURE'])
       ->setCellValue('M'.$exrow ,$row['ARLPARTGROUPID'])
       ->setCellValue('N'.$exrow ,$row['ARLFULLEXTENDEDPRICEBASE'])
       ->setCellValue('O'.$exrow ,$row['ARLEXTENDEDDISCOUNTBASE'])
       ->setCellValue('P'.$exrow ,$row['ARLEXTENDEDPRICEBASE'])
       ->setCellValue('Q'.$exrow ,$row['commission_percent'])
       ->setCellValue('R'.$exrow ,$commission);



   //set row style
   if( $exrow % 2 == 0 ){
       //even row
       $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($evenRow);
   }else{
       //odd row
       $spreadsheet->getActiveSheet()->getStyle('A'.$exrow.':R'.$exrow)->applyFromArray($oddRow);
   }
   //increment row
   $exrow++;
}
odbc_close($connect);

//define first row and last row
$firstRow=5;
$lastRow=$exrow-1;
$totalrow=$exrow+1;
$percentrow=$totalrow+1;

//SUBtotal and SUM rows

// Total All

//Percent

//Format currency 

//
$spreadsheet->getActiveSheet()
->setCellValue('G2',"Filter Total")
->setCellValue('G3',"Grand Total")
->setCellValue('G4',"Percent");

//set the autofilter
$spreadsheet->getActiveSheet()->setAutoFilter("A".$firstRow.":R".$totalrow);

//set the header first, so the result will be treated as an xlsx file.
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

//make it an attachment so we can define filename
header('Content-Disposition: attachment;filename="'.$salesId.'".xlsx"');

//create IOFactory object
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
//save into php output
$writer->save('php://output');

}

ginerjm · August 14, 2019

I'm curious as to why you have this need to create individual spreadsheet (files) when you already have the data in a database from which you are able to draw this info from. Are you creating these files for others to use outside of this data-gathering app? And do they do updates within these spreadsheets that you then have to somehow apply to your nice-and-neat already-maintained database?

Edited August 14, 2019 by ginerjm

Psycho · August 14, 2019

3 hours ago, ginerjm said:

I'm curious as to why you have this need to create individual spreadsheet (files) when you already have the data in a database from which you are able to draw this info from. Are you creating these files for others to use outside of this data-gathering app? And do they do updates within these spreadsheets that you then have to somehow apply to your nice-and-neat already-maintained database?

I am curious as well. Why not just create each spreadsheet when requested by a user. I.e. User selects to see the data for Salesperson X - create the spreadsheet for Salesperson X only at that time.

In any event, you have a lot of loops going on with many "expensive" operations being repeated in those loops. For example, you're still running individual queries for each salesperson. Run ONE query to get all the data - THEN create your output for each spreadsheet from that query. You're also recreating the $spreadsheet object for each salesperson. Not sure, but that may not release any memory for the previous object. You shoudl create the object ONCE then create multiple spreadsheets using that object.

kat35601 · August 14, 2019

We will us the spreadsheet as statements each month for each Salesperson. The spreedsheet allows the salespeople to filter by item and/or by customer etc...etc.. so they can see what items work good in there area do what if's create there on reports for their sales teams. We send out 60 statements each month. I will add a function to the end of this script to email each Salesperson there spreadsheet automatically each month. I did try create object once and then create the spreadsheets from that but memory was a problem.These spreadsheet can be thousands of rows in size. I would really like to Create spreadsheet for salesperson 1name and then email it and then create spreadsheet for salesperson 2name and email it and so on. Thanks for you help.

Barand · August 14, 2019

1 hour ago, Psycho said:

Why not just create each spreadsheet when requested by a user.

I agree with Psycho.

Instead of creating all the spreadsheets and emailing them to each salesperson, let them request their data for the month and download the spreadsheet.

Psycho · August 14, 2019

2 hours ago, kat35601 said:

I did try create object once and then create the spreadsheets from that but memory was a problem.These spreadsheet can be thousands of rows in size. I would really like to Create spreadsheet for salesperson 1name and then email it and then create spreadsheet for salesperson 2name and email it and so on. Thanks for you help.

Then get rid of all the nested loops! That is what is sucking up all your memory. It would also help if you read the documentation. The documentation for the spreadsheet code you are using mentioins there is a memory problem and how to resolve it. Based on their recommendations, I would follow Barand's earlier advise to put the data into an array first.

	<?php
	//Run ONE Query to get the data for ALL the salespeople
$sql="SELECT x, y, z . .  ";
//run the query
$result =odbc_exec($connect,$sql);
if(!$result){
    exit("Error in SQL");
}
	//Create array to hold the data
$salesData = array();
	//Iterate through the data and populate a structured array
while ($row = odbc_fetch_array($result))
{
    $salesData[$row['salespersonID']][] = $row;
}
	
//Iterate over the structured array to create the spreadsheets
foreach($salesData as $salesPersonID => $salesPersonData)
{
    //make a new spreadsheet object
    $spreadsheetObj = new Spreadsheet();
	    //Add all the standard setting and content (e.g. headers)
    
    //Iterate over the records for the current salesperson and all to the spreadsheet
    foreach($salesPersonData as $salesData)
    {
        //Refernece the records using the $salesData array
    }
    
    //I assume there would be a step here to write the current sheet to disk
    
    ###########################
    ## Disconnect and unset the spreadsheet object
    ###########################
    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);
}

Sign In

phpspreadsheet create a new worksheet on salesperson name change

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information