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

 

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.