Search code examples
phpexport-to-excel

PHPExcel converting from a database to xlsx

I have next code :

    <?php
        require_once 'PHPExcel/Classes/PHPExcel.php';
        include "PHPExcel/Classes/PHPExcel/Writer/Excel2007.php"; 
        session_start();
        include("config.php");
        global $kon;
        ob_start();
        $excel = new PHPExcel;
        $excel->getProperties()->setCreator('Boris Jelic');
        $excel->getProperties()->setLastModifiedBy('Boris Jelic');
        $excel->getProperties()->setTitle('Orders');
        $excel->removeSheetByIndex(0);


        $cols = array('tijd' => 'A', 'shop' => 'B', 'products' => 'C', 'naam' => 'D', 'adres' => 'E', 'gemeente' => 'F', 'telefoonnummer' => 'G', 'email' => 'H', 'leeggoed' => 'I');
        $list = $excel->createSheet();
        $list->setTitle('Users');
        $list->getColumnDimension('A')->setWidth(20);
        $list->getColumnDimension('B')->setWidth(25);
        $list->getColumnDimension('C')->setWidth(40);
        $list->getColumnDimension('D')->setWidth(40);
        $list->getColumnDimension('E')->setWidth(40);
        $list->getColumnDimension('F')->setWidth(20);
        $list->getColumnDimension('G')->setWidth(15);
        $list->getColumnDimension('H')->setWidth(40);
        $list->getColumnDimension('I')->setWidth(40);
        $list->setCellValue('A1', 'Tijd');
        $list->setCellValue('B1', 'Shop');
        $list->setCellValue('C1', 'Products');
        $list->setCellValue('D1', 'Naam en voornaam');
        $list->setCellValue('E1', 'Adres');
        $list->setCellValue('F1', 'Gemeente');
        $list->setCellValue('G1', 'Telefoonnummer');
        $list->setCellValue('H1', 'Email');
        $list->setCellValue('I1', 'Leeggoed');


        //za background
        $excel->getActiveSheet()->getStyle('A1:I1')->getFill()
        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
        ->getStartColor()->setARGB('FFE8E5E5');

        //stavljamo naslove bold
        $excel->getActiveSheet()->getStyle('A1:I1')->getFont()->setBold(true);

        //povecavamo velicinu slova
        $excel->getActiveSheet()->getStyle('A1:I1')->getFont()->setSize(13);


        //moramo prvo uzeti sve orders sa statusom 1
        $rezOrders = mysqli_query($kon, "SELECT 
                                         shops.naam as shopNaam, 
                                         GROUP_CONCAT(producten.naam SEPARATOR '\r') as prodNaam, GROUP_CONCAT(producten.id SEPARATOR '\r') as prodID,
                                         order_details.aantal as kolicina, order_details.leeggoed as leeggoed, order_details.id as ordDetId, SUM(order_details.aantal) as prodCount, 
                                         korisnici.ime as korNaam, korisnici.email as korMail, korisnici.id as korId, korisnici.prezime as korPrez, korisnici.mobitelBroj as broj, korisnici.mjesto as adres,
                                         korisnici.huisnummer as hn,
                                         korisnici.gemeente as gemeente, 
                                         orders.besteld_op as tijd 
                                         FROM orders
                                         INNER JOIN korisnici ON korisnici.id = orders.user_id 
                                         INNER JOIN order_details ON order_details.order_id = orders.id
                                         INNER JOIN product_shop_tt ON order_details.product_shop_tt_id = product_shop_tt.id
                                         INNER JOIN producten ON producten.id = product_shop_tt.product_id
                                         INNER JOIN shops ON shops.id = product_shop_tt.shop_id 
                                         WHERE orders.status = 1 GROUP BY korisnici.id");

        $rowcounter = 2;

        while ($row = mysqli_fetch_assoc($rezOrders)){


           $prod_id = $row['prodID'];
           $prodKol = $row["prodNaam"] . " -- Aantal : " . $row["kolicina"] . "x";
           echo "Product + quantity : " . $prodKol . "<br />";


          //Da uzmemo aantal za taj product


          /*echo "Product i kolicina : " . $redAan["productNaam"] . " - " . $redAan["kolicina"] . "<br />";*/
          $tijd = $row["tijd"];
          $ime = $row["korNaam"] . " " . $row["korPrez"];
          $email = $row["korMail"];
          $telNummer = $row["broj"];
          $gemeente = $row["gemeente"];
          $adresa = $row["adres"] . " " . $row["hn"];
          $leegoed = $row["leeggoed"];

          $list->setCellValue('A'.$rowcounter, $tijd);
          $list->setCellValue('B'.$rowcounter, $row['shopNaam']);
          $list->setCellValue('C'.$rowcounter, $prodKol."\r");
          $list->getStyle('C'.$rowcounter)->getAlignment()->setWrapText(true);
          $list->setCellValue('D'.$rowcounter, $ime);
          $list->setCellValue('E'.$rowcounter, $adresa);
          $list->setCellValue('F'.$rowcounter, $gemeente);
          $list->setCellValue('G'.$rowcounter, $telNummer);
          $list->setCellValue('H'.$rowcounter, $email);
          $list->setCellValue('I'.$rowcounter, $leegoed);
          $rowcounter++;
        }


        $writer = new PHPExcel_Writer_Excel2007($excel);
        if($writer->save('files/users.xlsx')){
            echo "Konvertovan je."; 
        }
   ?>

I save results in an xlsx file, everything works fine, but i'm trying to get a little bit different display of the contents within my excel file.

Now I get something like this withing my excel file(photo 1) enter image description here

But I would like to get something like this (photo 2) enter image description here

Thus I want to do something like group by the shops. If one user has ordered more products from the same shop that they all displayed within one row in the excel file, not like now, it is created a new row for each product.

UPDATED When i add GROUP_CONCAT(producten.naam SEPARATOR '\r') as prodNaam and GROUP BY korisnici.id I get than the right result in my excel file. Exactly like op the photo 2. But then I have the next problem. I can't get order_details.aantal value for each product, I get than values for only first product for each user. But if I put GROUP BY order_details.id then I get the right order_details.aantal value for each product but then I get my excel file like on the photo 1.

Solution

  • One simple way to do this would be to use GROUP BY and GROUP_CONCAT in you SQL statement.

    In particular, group your query by the fields that represent "User" and "Shop", and instead of simply selecting producten.naam as prodNaam, use GROUP_CONCAT(producten.naam, '\r').

    For more information about newlines in PHPExcel, check here: how to make New lines in a cell using phpexcel.