Export SQL data from database to Excel using PHPExcel

Question

I am trying to export data from my database to Excel using PHPExcel. I used the code from this question. Edited mysql to mysqli and changed the extension to xlsx format. But when I want to open the file I get the following error (I translated it into English so sorry for my bad English if it isn't right)

The file rapportage 29-05-2017.xlsx can't be opened because the file format or file extension is not valid. Check if the file isn't damaged and if the file extension matches with the format of the file.

This is the code I copied and changed:

<?php
require 'database.php';
require '../Classes/PHPExcel.php';

if (isset($_POST['exporteer'])) {

    $a = $_POST['organisatie_naam'];
    $b = $_POST['begin_datum'];
    $c = $_POST['eind_datum'];
    $datum = date("d-m-Y");

    $objPHPExcel = new PHPExcel();

    $query = "SELECT * FROM register WHERE organisatie_naam = '$a' AND (registratie_aansluit_datum BETWEEN '$b' AND '$c'";

    $result = mysqli_query($db, $query);

    $objPHPExcel = new PHPExcel();

    $rowCount = 1;

    while ($row = mysqli_fetch_array($result)) {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$rowCount, $row['registratie_id']);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$rowCount, $row['registratie_soort']);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$rowCount, $row['organisatie_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$rowCount, $row['apparaat_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('E'.$rowCount, $row['client_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('F'.$rowCount, $row['dienst_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('G'.$rowCount, $row['functie_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('H'.$rowCount, $row['kosten_soort']);
        $objPHPExcel->getActiveSheet()->setCellValue('I'.$rowCount, $row['status_naam']);
        $objPHPExcel->getActiveSheet()->setCellValue('J'.$rowCount, $row['registratie_aansluit_datum']);
        $objPHPExcel->getActiveSheet()->setCellValue('K'.$rowCount, $row['registratie_afsluit_datum']);
        $objPHPExcel->getActiveSheet()->setCellValue('L'.$rowCount, $row['registratie_omschr']);
        $rowCount++;
        pr($objPHPExcel);
    }

    header('Content-Type: application/vnd.openxmlformats-   officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="rapportage ' . $datum . '.xlsx"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    header("Location: ../rapportage.php");
}
?>

Anyone who knows if I maybe missed something? Thanks in advance!

And those spaces in the Content-Type header won't help either — Mark Baker
– Mark Baker, Commented May 29, 2017 at 10:02
Plus headers to redirect after the output has been sent to the browser should give warnings, that are probably being injected into the output and breaking it — Mark Baker
– Mark Baker, Commented May 29, 2017 at 10:03
I removed those spaces, that was a mistake. And also removed the header after the output. But I still get this warning.. @MarkBaker — Baspa
– Baspa, Commented May 29, 2017 at 10:24

imostafa · Accepted Answer · 2018-12-15 16:58:43Z

require('../phpexcel/PHPExcel.php');

require('../phpexcel/PHPExcel/Writer/Excel5.php');

$filename = 'userReport'; //your file name

$objPHPExcel = new PHPExcel();
/*********************Add column headings START**********************/
$objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A1', 'username')
            ->setCellValue('B1', 'city_name');

/*********************Add data entries START**********************/
//get_result_array_from_class**You can replace your sql code with this line.

$result = $get_report_clas->get_user_report();

//set variable for count table fields.
$num_row = 1;
foreach ($result as $value) {
  $user_name = $value['username'];
  $c_code = $value['city_name'];
  $num_row++;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A'.$num_row, $user_name )
                ->setCellValue('B'.$num_row, $c_code );
}

/*********************Autoresize column width depending upon contents START**********************/
foreach(range('A','B') as $columnID) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
}
$objPHPExcel->getActiveSheet()->getStyle('A1:B1')->getFont()->setBold(true);



//Make heading font bold
    /*********************Add color to heading START**********************/
    $objPHPExcel->getActiveSheet()
                ->getStyle('A1:B1')
                ->getFill()
                ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()
                ->setARGB('99ff99');

    $objPHPExcel->getActiveSheet()->setTitle('userReport'); //give title to sheet
    $objPHPExcel->setActiveSheetIndex(0);
    header('Content-Type: application/vnd.ms-excel');
    header("Content-Disposition: attachment;Filename=$filename.xls");
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');

Collectives™ on Stack Overflow

Export SQL data from database to Excel using PHPExcel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related