php - 使用php和phpexcel将两个表中的mysql数据导出到excel

Question

我正在尝试从 mysql 表获取数据并输出到单个 Excel 文件。数据需要并排出现。

但是输出的excel文件只包含特殊字符而不是mysql数据。请为我指出正确的方向。

这是我的代码:

<?php
    // connection with the database 
    $dbhost = "localhost"; 
    $dbuser = "root"; 
    $dbpass = "redhat"; 
    $dbname = "was"; 

    mysql_connect($dbhost,$dbuser,$dbpass); 
    mysql_select_db($dbname); 

    // require the PHPExcel file 
    require 'phpexcel/Classes/PHPExcel.php'; 
    //$q2 = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ".$dbname."";
    $sql = "SHOW TABLES"; $res = mysql_query($sql); $num_tables = mysql_num_rows($res);
    $table = $num_tables;
    $i = 1;
    echo $i;
    echo $table;
    // write all table names with a variable value like table1 table2 table3
    // simple query 
    while($i <= $table){
        //$query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report ORDER by id DESC"; 
        $query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos".$i."report"; 
        $headings = array('Service Date','Rec. Date','DOS to Rec. Lag','Cases Rec.','scan Date','Cases Entered','Cases Pending','Cases Count Diff','Entry Date','Rec. to Entry Lag'); 

        if ($result = mysql_query($query) or die(mysql_error())) { 
            // Create a new PHPExcel object 
            $objPHPExcel = new PHPExcel(); 
            $objPHPExcel->getActiveSheet()->setTitle('Report'); 

            $rowNumber = 8; 
            if ($i == 1) {
                $col = 'A';
            } elseif ($i == 2) {
                $col = 'L';
            } 
            //  $col = 'A'; 
            foreach($headings as $heading) { 
                $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
                $col++; 
            } 

            // Loop through the result set 
            $rowNumber = 9; 
            while ($row = mysql_fetch_row($result)) {
                if ($i == 1) {
                    $col = 'A';
                } elseif ($i == 2) {
                    $col = 'L';
                } 
                //  $col = 'A'; 
                //       $col = 'A'; 
                foreach($row as $cell) { 
                    $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
                    $col++; 
                }
                $rowNumber++; 

            } 
            // Freeze pane so that the heading line won't scroll 

            $objPHPExcel->getActiveSheet()->freezePane('A2'); 
            // Save as an Excel BIFF (xls) file 
            $i++;
        }
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="report.xls"');
        header('Cache-Control: max-age=0'); 
    }

    $objWriter->save('php://output'); 
    exit(); 
    echo 'a problem has occurred... no data retrieved from the database'; 

?> 

Answer 1

试试这个代码:

<?php
    // connection with the database 
    $dbhost = "localhost"; 
    $dbuser = "root"; 
    $dbpass = "redhat"; 
    $dbname = "was"; 

    mysql_connect($dbhost,$dbuser,$dbpass); 
    mysql_select_db($dbname); 

    // require the PHPExcel file 
    require 'phpexcel/Classes/PHPExcel.php'; 
    //$q2 = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ".$dbname."";
    $sql = "SHOW TABLES"; $res = mysql_query($sql); $num_tables = mysql_num_rows($res);
    $table = $num_tables;
    $i = 1;
    echo $i;
    echo $table;
    // write all table names with a variable value like table1 table2 table3
    // simple query 
    while($i <= $table){
        //$query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos1report ORDER by id DESC"; 
        $query = "SELECT servicedate,recdate,dostoreclag,casesrec,scandate,casesentered,casespending,casecountdiff,entrydate,rcvdtolag FROM hos".$i."report"; 
        $headings = array('Service Date','Rec. Date','DOS to Rec. Lag','Cases Rec.','scan Date','Cases Entered','Cases Pending','Cases Count Diff','Entry Date','Rec. to Entry Lag'); 

        if ($result = mysql_query($query) or die(mysql_error())) { 
            // Create a new PHPExcel object 
            $objPHPExcel = new PHPExcel(); 
            $objPHPExcel->getActiveSheet()->setTitle('Report'); 

            $rowNumber = 8; 
            if ($i == 1) {
                $col = 'A';
            } elseif ($i == 2) {
                $col = 'L';
            } 
            //  $col = 'A'; 
            foreach($headings as $heading) { 
                $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$heading); 
                $col++; 
            } 

            // Loop through the result set 
            $rowNumber = 9; 
            while ($row = mysql_fetch_row($result)) {
                if ($i == 1) {
                    $col = 'A';
                } elseif ($i == 2) {
                    $col = 'L';
                } 
                //  $col = 'A'; 
                //       $col = 'A'; 
                foreach($row as $cell) { 
                    $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell); 
                    $col++; 
                }
                $rowNumber++; 

            } 
            // Freeze pane so that the heading line won't scroll 

            $objPHPExcel->getActiveSheet()->freezePane('A2'); 
            // Save as an Excel BIFF (xls) file 
            $i++;
        }
        // $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 

        // header('Content-Type: application/vnd.ms-excel');
        // header('Content-Disposition: attachment;filename="report.xls"');
        // header('Cache-Control: max-age=0'); 

    }
        // Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
        $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel); 
        // Write the Excel file to filename some_excel_file.xlsx in the current directory
        $objWriter->save('some_excel_file.xlsx');


    // $objWriter->save('php://output'); 
    exit(); 
    echo 'a problem has occurred... no data retrieved from the database';
?> 

希望它运行顺利......:)