phpphpexcelphpexcelreaderphpexcel-1.8.0

How to write php excel code in phpexcel format and print dynamic label?


I have a dynamic cell coming from a database. How can I print it into Phpexcel format?

$out_table = '';
$k=0;
    mysql_data_seek($query_result1,0);
    while($row1=mysql_fetch_array($query_result1,MYSQL_ASSOC)){
            $out_table .= "<th valign=\"top\">".$labels[$k]." (value1)</th>";
            $out_table .= "<th valign=\"top\">".$labels[$k]." (value2)</th>";
            $k++;   
    }

    echo $out_table;
    //print_r($out_table);die;
}

Solution

  • I strongly suggest you to use PHPSPREEDSHEET the evolution of PHPEXCEL, here's an example:

    require 'DB.php';
    require 'vendor/autoload.php';
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx as xlsx; // Instead PHPExcel_Writer_Excel2007
    use PhpOffice\PhpSpreadsheet\Worksheet\Drawing as drawing; // Instead PHPExcel_Worksheet_Drawing
    use PhpOffice\PhpSpreadsheet\IOFactory as io_factory; // Instead PHPExcel_IOFactory
    
    
    $query = 'SELECT * FROM ? WHERE ?';
    $result = $mysqli->query($query);
    
    $objPHPExcel    =   new Spreadsheet();
    
    
    $objPHPExcel->setActiveSheetIndex(0);
    //NAME WORKSHEET
    $objPHPExcel->getActiveSheet()->setTitle("Test");
    
    
    $objPHPExcel->getActiveSheet()
        ->getPageSetup()
        ->setOrientation(PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
    
    $objPHPExcel->getActiveSheet()
        ->getPageSetup()
        ->setPaperSize(PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_A4);
    
    $objPHPExcel->getActiveSheet()->SetCellValue('A12', 'ROW1');
    $objPHPExcel->getActiveSheet()->SetCellValue('B12', 'ROW2');
    
    $rowCount   =   1;
    
    }
    while($row  =   $result->fetch_assoc()){
    
    
          //BORDER  
          $objPHPExcel->getActiveSheet()->getStyle('A'.$rowCount)->applyFromArray($styleArray);
          $objPHPExcel->getActiveSheet()->getStyle('B'.$rowCount)->applyFromArray($styleArray);
    
    
          //VALUE
          $objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['row1']);
          $objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['row2']);
    
    
        $rowCount++; //ADD COUNT 
    }
    
    $objWriter = io_factory::createWriter($objPHPExcel, 'Xlsx');
    
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=EXAMPLE.xlsx");
    header("Content-Transfer-Encoding: binary ");
    
    ob_end_clean();
    ob_start();
    $objWriter->save('php://output');
    exit;