使用PHPSpreadsheet导出.xlsx，读取创建HTML表的.php文件 [英] Export .xlsx with PHPSpreadsheet reading a .php file that creates an HTML table

问题描述

是否可以使用PHPSpreadsheet从创建HTML表的.php文件中创建 .xlsx 电子表格?.php文件从POST和DB值创建一个HTML表.我能够仅使用php标头说明成功创建.xls文件.我通过PHPSpreadsheet获得的最接近的内容是将.php文件读取为HTML，这会创建一个.xlsx文件，但还会在浏览器中生成有关该文件类型的php警告，并且.xlsx文件除了包含所有的php代码外，还包含生成的HTML表格(表格的静态标题位于电子表格中，而不是表格的动态php内容中).我在PHPSpreadsheet文档中找不到有关从.php文件读取的任何内容.我已经尝试过在php文件的开头使用text/html标头，但这对.xlsx文件的内容没有任何改变.

Is it possible to create an .xlsx spreadsheet using PHPSpreadsheet from a .php file that creates an HTML table? The .php file creates an HTML table from both POST and DB values. I'm able to successfully create an .xls file using just php header instructions. The closest I've gotten through PHPSpreadsheet is reading the .php file as HTML, which creates an .xlsx file but also generates php warnings in the browser about the filetype, and the .xlsx file contains all of the php code in addition to the generated HTML table (The static headers of the table are in the spreadsheet but not the dynamic php content of the table). I can't find anything in the PHPSpreadsheet documentation about reading from a .php file. I've tried using a text/html header at the beginning of the php file and that changes nothing about the content of the .xlsx file.

   <?php
    require_once("../urlparse.php");
    require_once("$passwordfile");
    require_once("dbfetchsiteoptions.php");
    $fullname = $_SESSION['fullname'];
    $userid = $_SESSION['userid'];
    $stmt = $PDO->prepare("SELECT * FROM siteoptions WHERE optionname='timesheetround'");
    $stmt->execute();
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
    $optionname = $data['optionname'];
    $roundoptions = array($data['optionvalue1'], $data['optionvalue2'], $data['optionvalue3']);
    $valuecurrent = $data['optionvaluecurrent'];
    $stmt = $PDO->prepare("SELECT * FROM payperiod WHERE id='1'");
    $stmt->execute();
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
    $payperiodstart = new DateTime($data['payperiodstart']);
    $payperiodinterval = new DateInterval($data['payperiodinterval']);
    while($payperiodstart <= $newdatetime) {
      $payperiodstartdate = $payperiodstart->format('m-d-Y');
      $payperiodstart->add($payperiodinterval);
      $payperiodstart->sub(new DateInterval('P01D'));
      $payperiodenddate = $payperiodstart->format('m-d-Y');
      $payperiodstart->add(new DateInterval('P01D'));
    }
    if(isset($payperiodstartdate))   echo '<table><tr><th>From</th><th>'.$payperiodstartdate.'<th>To</th><th>'.$payperiodenddate.'</th></tr>';
    $stmt = $PDO->prepare("SELECT * FROM timesheet WHERE userid='$userid' ORDER BY id ASC");
    $stmt->execute(); ?>

    <table><tr><th>Date</th><th>Time-In</th><th>Time-Out</th><th>Hours</th></tr>

    <?php
    while($data = $stmt->fetch(PDO::FETCH_ASSOC)) {
      $date = new DateTime($data['date']);
      $date = $date->format('m-d-Y');
      if($date >= $payperiodstartdate AND $date <= $payperiodenddate) {
        $hours[] = $data['hours'];
        echo '<tr><td>'.$date.'</td><td>'.date("g:i a", strtotime($data['timein'])).'</td>';
        if($data['timeout'] == NULL) {
          echo '<td>Working...</td>';
        }else{
          echo '<td>'.date("g:i a", strtotime($data['timeout'])).'</td>';
        }
        echo '<td>'.$data['hours'].'</td></tr>';
      }
    } ?>

    </table>

    <?php
    if(empty($hours)){$hours = '00:00';}
    $counter = new times_counter($hours);
    class times_counter {
      private $hou = 0;
      private $min = 0;
      private $sec = 0;
      private $totaltime = '00:00';
      public function __construct($times){
        if(is_array($times)){
          $length = sizeof($times);
          for($x=0; $x <= $length; $x++){
            $split = explode(":", @$times[$x]);
            if(!empty($split[0])) {$this->hou += @$split[0];}
            $this->min += @$split[1];
          }
          $seconds = $this->sec % 60;
          $minutes = $this->sec / 60;
          $minutes = (integer)$minutes;
          $minutes += $this->min;
          $hours = $minutes / 60;
          $minutes = $minutes % 60;
          $hours = (integer)$hours;
          $hours += $this->hou;
          if($minutes < '10') {$minutes = sprintf("%02d", $minutes);}
          if($hours < '10') {$hours = sprintf("%02d", $hours);}
          if($hours >= 80) {$othours = $hours - 80; $hours = 80;}
          if(isset($othours)) {$this->totaltime = $hours.':00<br><h3>Overtime</h3>'.$othours.':'.$minutes;}else{$this->totaltime = $hours.":".$minutes;}
        }
      }
      public function get_total_time(){
          return $this->totaltime;
      }
    }
    ?>

    <table><tr><th></th><th></th><th></th><th>Total Hours</th></tr><tr><td></td><td></td>
<td></td><td><?php echo $counter->get_total_time(); ?></td></tr>

</table>

推荐答案

您有2种方法可以做到这一点:

You have 2 ways to do this :

• 继续从php脚本生成的HTML生成xlsx.

通过这种方式，您的PHP脚本在磁盘上生成了(例如)export.html文件，然后phpspreadsheet代码使用"HTML Reader"读取该文件并生成xslx.

In this way, your PHP script generates (for instance) an export.html file on the disk, and then phpspreadsheet code reads this file (with "HTML Reader") and generates the xslx.

使用PHPExcel(phpspreadsheet的旧名称)，我们做到了:

With PHPExcel (prior name of phpspreadsheet), we did like this :

$inputFileType = 'HTML';
$inputFileName = './myHtmlFile.html';
$outputFileType = 'Excel2007';
$outputFileName = './myExcelFile.xlsx';

$objPHPExcelReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objPHPExcelReader->load($inputFileName);

$objPHPExcelWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,$outputFileType);
$objPHPExcel = $objPHPExcelWriter->save($outputFileName);

• 您可以为您的PHP代码生成CSV，而不是生成HTML，然后将其发送到phpspreadsheet，请参见此处的文档 https://phpspreadsheet.readthedocs.io/en/develop/topics/reading-files/

这篇关于使用PHPSpreadsheet导出.xlsx，读取创建HTML表的.php文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！