Jump to content
kat35601

phpspreadsheet create a new worksheet on salesperson name change

Recommended Posts

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');

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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
        }
    }
}            

 

  • Like 1

Share this post


Link to post
Share on other sites

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

 

thanks 

Share this post


Link to post
Share on other sites

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;

Share this post


Link to post
Share on other sites

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";
?>

 

Share this post


Link to post
Share on other sites

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');
	

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.