Creating Excel sheet from the database using PHPExcel in codeigniter
<p>I am trying to create an excel sheet from the database using PHPExcel in Codeigniter for generating reports. I have the main table <code>sales</code> and more than 4 tables from which am fetching data. I have written query using multiple JOINS between the tables.</p>

<p>Here is my scenario: </p>

<p>As am dealing with generating reports for sales, A customer may have purchased multiple products and this details are stored in another table <code>sale_items</code>. At present I have written a main query with joins for other tables and after getting the result inside the foreach loop I am writing another query to select the corresponding data from <code>sale_items</code> table. </p>

<p>I am trying to show in excel sheet for a single customer, if he has purchased multiple products I need to show these products separated by comma in excel sheet. I used a 2nd query to fetch the product names and imploded the result and tried to show in the corresponding row in excel. it works fine if am trying it with a single customer. If I try it for more than one customer, the page shows a server error. </p>

<p>Here is my code:</p>

<pre><code>    $dateTmp = "DATE_FORMAT(".$this-&gt;db-&gt;dbprefix('sales').".date,'%m/%Y')";
    $condition = "if(".$this-&gt;db-&gt;dbprefix('sales').".biller_id = 5, 'A', if(".$this-&gt;db-&gt;dbprefix('sales').".biller_id = 6, 'B',if(".$this-&gt;db-&gt;dbprefix('sales').".biller_id = 7,'C','D'  )))";       


    if ($pdf || $xls) {
    $this-&gt;db
    -&gt;select("date, ".$this-&gt;db-&gt;dbprefix('warehouses').".name, 
    CONCAT(".$this-&gt;db-&gt;dbprefix('warehouses').".name,'-',".$dateTmp.",'-', ".$condition.",".$this-&gt;db-&gt;dbprefix('sales').".invoice_no) as month," 
    ." biller, customer,
    scan_no, CONCAT(" . $this-&gt;db-&gt;dbprefix('sale_items') . ".product_name, '__', " . $this-&gt;db-&gt;dbprefix('sale_items') . ".quantity) as iname, 
    unit_price,if(tax_rate_id=2,item_tax,0) as taxId1,if(tax_rate_id=4,item_tax,0) as taxId2 ,
    tin,cin,cst,total_discount, item_tax, shipping,subtotal,payment_status,sales.id as saleid ", FALSE)
    -&gt;from('sales')
    -&gt;join('sale_items', 'sale_items.sale_id=sales.id', 'left')
    -&gt;join('companies','companies.id=sales.customer_id', 'left')
    -&gt;join('salersperson','salersperson.id=companies.salesperson_id', 'left')
    -&gt;join('hosipital_group','hosipital_group.id=companies.hospital_id', 'left')
    -&gt;join('doctor','doctor.id=companies.dr_id', 'left')
    -&gt;join('warehouses', 'warehouses.id=sales.warehouse_id', 'left')
    -&gt;group_by('sales.id')
    -&gt;order_by('sales.date desc');  //MAIN JOIN QUERY

    $q = $this-&gt;db-&gt;get();
    if ($q-&gt;num_rows() &gt; 0) {
    foreach (($q-&gt;result()) as $row) {
    $data[] = $row;
    }
    } else {
    $data = NULL;
    }

    if (!empty($data)) 
    {
    $this-&gt;load-&gt;library('excel');
    $this-&gt;excel-&gt;setActiveSheetIndex(0);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;setTitle(lang('sales_report'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('A1', lang('date'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('B1', lang('branch'));

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('C1', lang('invoice_no'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('D1', lang('biller'));

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('E1', lang('customer'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('F1', lang('tin'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('G1', lang('cin'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('H1', lang('cst'));

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('I1', lang('scan_no'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('J1', lang('product_qty'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('K1', lang('prod_price'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('L1', lang('vat1'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('M1', lang('vat2'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('N1', lang('discount'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('O1', lang('product_tax'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('P1', lang('shipping'));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('Q1', lang('total'));

    $row = 2;
    $total = 0;
    $paid = 0;
    $balance = 0;

    foreach ($data as $data_row) {
    $saleid=$data_row-&gt;saleid;
    $this-&gt;db-&gt;select("product_name")
    -&gt;from('sale_items')
    -&gt;where('sale_items.sale_id',$saleid); /*SECOND QUERY TO FETCH FROM SALE_ITEMS TABLE*/
    $q1 = $this-&gt;db-&gt;get();
    if ($q1-&gt;num_rows() &gt; 0) 
    {
    foreach (($q1-&gt;result()) as $row1) 
    {
    $prdtarray[] = $row1-&gt;product_name; 
    }
    $product=implode(',',$prdtarray); /*PRODUCTS NAME SEPARATED BY COMMA AND STORED IN A VARIABLE*/
    } 
    else 
    {
    $data1 = NULL;
    }

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('A' . $row, $this-&gt;sma-&gt;hrld($data_row-&gt;date));
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('B' . $row, $data_row-&gt;name);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('C' . $row, $data_row-&gt;month);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('D' . $row, $data_row-&gt;biller);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('E' . $row, $data_row-&gt;customer);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('F' . $row, $data_row-&gt;tin);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('G' . $row, $data_row-&gt;cin);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('H' . $row, $data_row-&gt;cst);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('I' . $row, $data_row-&gt;scan_no);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('J' . $row, $product); //THE COMMA SEPARATED PRODUCT NAMES
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('K' . $row, $data_row-&gt;unit_price);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('L' . $row, $data_row-&gt;taxId1);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('M' . $row, $data_row-&gt;taxId2);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('N' . $row, $data_row-&gt;total_discount);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('O' . $row, $data_row-&gt;item_tax);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('P' . $row, $data_row-&gt;shipping);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('Q' . $row, $data_row-&gt;subtotal);

    $total += $data_row-&gt;subtotal;
    $paid += $data_row-&gt;item_tax;
    // $balance += ($data_row-&gt;grand_total - $data_row-&gt;paid);
    $row++;
    }
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getStyle("L" . $row . ":M" . $row)-&gt;getBorders()
    -&gt;getTop()-&gt;setBorderStyle(PHPExcel_Style_Border::BORDER_MEDIUM);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('Q' . $row, $total);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('O' . $row, $paid);
    // $this-&gt;excel-&gt;getActiveSheet()-&gt;SetCellValue('H' . $row, $balance);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('A')-&gt;setWidth(20);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('B')-&gt;setWidth(20);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('C')-&gt;setWidth(20);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('D')-&gt;setWidth(30);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('E')-&gt;setWidth(15);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('F')-&gt;setWidth(15);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('G')-&gt;setWidth(15);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('H')-&gt;setWidth(20);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('I')-&gt;setWidth(20);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('J')-&gt;setWidth(20);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('K')-&gt;setWidth(15);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('L')-&gt;setWidth(30);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('M')-&gt;setWidth(30);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('N')-&gt;setWidth(15);

    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('O')-&gt;setWidth(30);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('P')-&gt;setWidth(30);
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getColumnDimension('Q')-&gt;setWidth(30);

    $filename = 'sales_report';
    $this-&gt;excel-&gt;getDefaultStyle()-&gt;getAlignment()-&gt;setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

    if ($xls) {
    $this-&gt;excel-&gt;getActiveSheet()-&gt;getStyle('E2:E' . $row)-&gt;getAlignment()-&gt;setWrapText(true);
    ob_clean();
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
    header('Cache-Control: max-age=0');
    ob_clean();
    $objWriter = PHPExcel_IOFactory::createWriter($this-&gt;excel, 'Excel5');
    $objWriter-&gt;save('php://output');
    exit();
    }

    }
    $this-&gt;session-&gt;set_flashdata('error', lang('nothing_found'));
    //redirect($_SERVER["HTTP_REFERER"]);

    } 
</code></pre>

<p>I don't know what happens when am trying it with more than one customer. May be Am wrong in my logic. Can anyone help me to find my mistake..?? Thanks in advance</p>