PHPExcel converting from a database to xlsx

Question

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)

But I would like to get something like this (photo 2)

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.

score 1 · Accepted Answer · edited May 23 '17 at 12:06

1

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.

edited May 23 '17 at 12:06

Community

1
1

answered Aug 16 '15 at 12:40

Brendan Rollinson

204
1
6

Thanks. It works. Now i get what i want but I still have problem with a new line. – Boky Aug 16 '15 at 13:00
1

Did you set your cell to wrap? [link]http://stackoverflow.com/questions/17899857/perserving-newline-characters-in-phpexcel[link] – Brendan Rollinson Aug 16 '15 at 13:05
That was the problem. I saw it. It works now like a charm. Thanks a lot :) – Boky Aug 16 '15 at 13:06
In your example, you don't seem to be using the email column. Could you make sure to take out korisnici.email as korMail if it's not used, ensure that you're grouping and group_concat-ing correctly as well? The first step is to validate that the query returns the grouped data correctly. However, it doesn't look like you've updated the query in the example, so it's hard to tell. – Brendan Rollinson Aug 17 '15 at 11:03
Comparing the current version of the two files, the number of rows and row grouping seems to be the same. The main difference I see is the addition of two columns (columns 2 and 3) in the second image. I can't tell what column two is (collapsed) but for column three, you could just repeat the header and content of column 1. – Brendan Rollinson Aug 17 '15 at 11:27
My bad. I didnt copy it right. The main difference is that I have in the second photo in column 3 next to each product the quantity of that product, but on the first photo, _that is what I get_, I have the quantity next to only one product. – Boky Aug 17 '15 at 11:35

score 0 · Answer 2 · answered Aug 16 '15 at 12:50

You can do something like this

$userHolder = '';
$shopHolder = ''; 
while ($row = mysqli_fetch_assoc($rezOrders)){

  if($userHolder != $value['user']){
    $list->setCellValue('A'.$rowcounter, $row['korNaam']);
    $userHolder = $value['user'];
    $shopHolder = '';
  }

  if($shopHolder != $value['shop']){
    $list->setCellValue('B'.$rowcounter, $row['shopNaam']);
    $shopHolder = $value['shop'];
  }
  $list->setCellValue('C'.$rowcounter, $row['prodNaam']);
  $rowcounter++;
}

Thanks. I tried Brendans answer and it worked, but now I have a new problem. I updated my question. — Boky, Aug 17 '15 at 07:10

PHPExcel converting from a database to xlsx

2 Answers2