使用PHPExcel将SQL数据从数据库导出到Excel [英] Export SQL data from database to Excel using PHPExcel

问题描述

我正在尝试使用PHPExcel将数据从数据库导出到Excel.我使用了这个问题中的代码.将mysql编辑为mysqli，并将扩展名更改为xlsx格式.但是，当我想打开文件时，出现以下错误(我将其翻译成英语，如果英语不正确，请为我的英语不好对不起)

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)

无法打开文件和谐29-05-2017.xlsx，因为文件格式或文件扩展名无效.检查文件是否未损坏以及文件扩展名是否与文件格式匹配.

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!

推荐答案

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');

这篇关于使用PHPExcel将SQL数据从数据库导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！