php - 如何使用 php 在 excel 表上重复输出

Question

请给我下面的一些代码块，将数据从数据库填充到 excel 工作表并下载到一个设备中。它对我来说工作得很好，除了我想重复从数据库中出来的每个数据 3x，即 John John John 在其行中，即 A1、A2、A3 等，用于检索到的所有数据。请帮忙，因为我尝试的所有方法都失败了，例如代码块之间的“IF 条件”...谢谢

             require_once "Classes/PHPExcel.php";
     //create PHPExcel object
     $excel = new PHPExcel();
     //database connection here
     include_once "db.php";
     //insert some data to PHPExcel object
     $excel->setActiveSheetIndex(0);

     $query =mysqli_query($mysqli,"select * from student  WHERE class='".$class."' ");
     $row=4;
     $count=0;
            while($data=mysqli_fetch_object($query)){
              if($count<2)
            {
            $excel->getActiveSheet()

                    ->setCellValue('A'.$row , $data->name)

                     ->setCellValue('B'.$row , $class)   

             ->setCellValue('C'.$row , $data->email)
             ->setCellValue('D'.$row , 'Exam');

     }


     else
     {

     }

             $row++;

             }           $count++;
     // set column dimension/width

     $excel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
     $excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
     $excel->getActiveSheet()->getColumnDimension('C')->setWidth(20);

     //make table headers
     $excel -> getActiveSheet()
     ->setCellValue('A1', 'TABLE LISTS/DATA') //this is a title
     ->setCellValue('A3', 'NAME')
     ->setCellValue('B3', 'CLASS')
     ->setCellValue('C3', 'EMAIL')


     //merging the title
     $excel->getActiveSheet()->mergeCells('A1:D1');

     //aligning
     $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal('center');
     //styling
     $excel->getActiveSheet()->getStyle('A1')->applyFromArray(
     array(
     'font'=>array(
     'size'=>24,
     )
     )
     );

     $excel->getActiveSheet()->getStyle('A3:C3')->applyFromArray(
     array(
     'font'=>array(
     'bold'=>true
     ),
     'borders'=>array(
     'allborders'=>array(
     'style'=> PHPExcel_Style_Border::BORDER_THIN
     )
     )
     )
     );

     //redirect to browser (download) instead of saving the result as a file

     //this is for MS Office Excel 2007 xlsx format
     header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
     header("Content-Disposition: attachment; filename=$class._Resultsheet.xlsx");

     //this is for MS Office Excel 2003 xls format
     //header('Content-Type: application/vnd.ms-excel');
     //header('Content-Disposition: attachment; filename="test.xlsx"');


     header('Cache-Control: max-age=0');

     //write the result to a file
     //for excel 2007 format
     $file = PHPExcel_IOFactory::createWriter($excel,'Excel2007');

     //for excel 2003 format
     //$file = PHPExcel_IOFactory::createWriter($excel,'Excel5');

     //output to php output instead of filename
     $file->save('php://output');

 ?>
 ```

Answer 1

编辑:

对不起，我误解了你的问题，你可以用一个技巧来重复这些行

select * 
from student cross join 
(select "" union all select "" union all select "") repeater
order by student.id

sqlfiddle http://sqlfiddle.com/#!9/e3349b/269

原始答案

It works just fine for me, except that i want to repeat each data coming out from database 3x, i.e. John John John in its rows i.e A1,A2,A3 etc, for all the datas retrieved.

然后我建议您保持您的 PHP 代码不变，并使用 MySQL 在较低级别从 MySQL 获得您需要的结果 concat()

而不是那样做

select * from student

这样做

select
    concat(col1, ',', col1, ',', col1) as col1,
    concat(col2, ',', col2, ',', col2) as col2,
    concat(col3, ',', col3, ',', col3) as col3,
    concat(col4, ',', col4, ',', col4) as col4
    --  .....
from student

(将您喜欢的列的分隔符从 "," 更改为空格 "")

IMO，我认为选择在数据库级别而不是 PHP 级别执行操作总是更好。这使您的数据库更具可移植性并且更独立于应用程序代码，例如，您可以将在 PHP 应用程序下工作的 MySQL 数据库移动到 JAVA 应用程序下工作，而无需新的 JAVA 开发人员做太多工作。