dsgdfh302506
2017-09-05 23:22
浏览 127
已采纳

如何使用PHPExcel读取XLSX邮件附件?

I am connecting through IMAP to get the XLSX attachments from a mailbox so I can convert these spreadsheets to PHP array data with PHPExcel, but when I load the file and convert it to array, the array is empty.

This is the function that gets the attachments from the mails matching the criteria:

public function getEmailAttachments($criteria){
  $emails = imap_search($this->inbox, $criteria);
  $email_attachments = [];
  if($emails) {
    rsort($emails);
    foreach ($emails as $email_number) {
      $attachments = [];
      //get Email structure
      $structure = imap_fetchstructure($this->inbox, $email_number);
      /* if any attachments found... */
      if(isset($structure->parts) && count($structure->parts))
      {
          for($i = 0; $i < count($structure->parts); $i++)
          {
              $attachments[$i] = array(
                  'is_attachment' => false,
                  'filename' => '',
                  'name' => '',
                  'attachment' => ''
              );

              if($structure->parts[$i]->ifdparameters)
              {
                  foreach($structure->parts[$i]->dparameters as $object)
                  {
                      if(strtolower($object->attribute) == 'filename')
                      {
                          $attachments[$i]['is_attachment'] = true;
                          $attachments[$i]['filename'] = $object->value;
                      }
                  }
              }

              if($structure->parts[$i]->ifparameters)
              {
                  foreach($structure->parts[$i]->parameters as $object)
                  {
                      if(strtolower($object->attribute) == 'name')
                      {
                          $attachments[$i]['is_attachment'] = true;
                          $attachments[$i]['name'] = $object->value;
                      }
                  }
              }

              if($attachments[$i]['is_attachment'])
              {
                  $attachments[$i]['attachment'] = imap_fetchbody($this->inbox, $email_number, $i+1);
                  /* 3 = BASE64 encoding */
                  if($structure->parts[$i]->encoding == 3)
                  {
                      $attachments[$i]['attachment'] = base64_decode($attachments[$i]['attachment']);
                  }
                  /* 4 = QUOTED-PRINTABLE encoding */
                  elseif($structure->parts[$i]->encoding == 4)
                  {
                      $attachments[$i]['attachment'] = quoted_printable_decode($attachments[$i]['attachment']);
                  }
              }
          }
      }
      $email_attachments = array_merge($email_attachments, array_filter($attachments, function($attachment){return $attachment['is_attachment'] == 1;}));
    }
  }
  imap_close($this->inbox);
  return $email_attachments;
}

Then this is the function that writes the XLSX files to the server and loads them with PHPExcel to convert them to Array data:

public function getEmailReports(){
  $this->loadPhpExcel();
  $attachments = parent::getEmailAttachments('FROM "example@example.com"');
   //iterate through each attachment and save it
  foreach($attachments as $attachment){
    $filename = $attachment['name'];
    if(empty($filename)) $filename = $attachment['filename'];
    $folder = "data/excel";
    $file_path = "./". $folder ."/". time() . "-" . $filename;
    $fp = fopen($file_path, "w");
    fwrite($fp, $attachment['attachment']);
    $excelReader = PHPExcel_IOFactory::createReaderForFile($file_path);
    $excelObj = $excelReader->load($file_path);
    foreach ($excelObj->getWorksheetIterator() as $worksheet) {
      $worksheets[$worksheet->getTitle()] = $worksheet->toArray();
    }
    fclose($fp);
    print_r($worksheets); //ARRAY IS EMPTY HERE
    //unlink($file_path);
  }
}
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • donglu9872 2017-09-06 15:45
    已采纳

    I opened the XLSX file with WinRAR and found out that the xl/worbooks.xml file has a "S" namespace:

    <?xml version="1.0"?>
    <s:workbook 
    xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <s:workbookPr/>
    <s:bookViews>
     <s:workbookView activeTab="0"/>
    </s:bookViews>
    <s:sheets>
     <s:sheet r:id="rId1" sheetId="1" name="Sheet1"/>
     <s:sheet r:id="rId2" sheetId="2" name="Sheet2"/>
     <s:sheet r:id="rId3" sheetId="3" name="Sheet3"/>
     <s:sheet r:id="rId4" sheetId="4" name="Sheet4"/>
    </s:sheets>
    <s:definedNames/>
    <s:calcPr fullCalcOnLoad="1" calcId="124519"/>
    </s:workbook>
    

    Then I found this issue on PHPExcel GitHub repo https://github.com/PHPOffice/PHPExcel/issues/571

    So all I did was create a class extending from PHPExcel_Reader_Excel2007 to remove the "s" namespace

    <?php
    class PHPExcel_Reader_Excel2007_XNamespace extends 
    PHPExcel_Reader_Excel2007
    {
    
     public function securityScan($xml)
     {
         $xml = parent::securityScan($xml);
         return str_replace(['<s:', '</s:'], ['<', '</'], $xml);
     }
    
    }
    

    and finally replaced this:

    $excelReader = PHPExcel_IOFactory::createReaderForFile($file_path);
    

    for this:

    $excelReader = new PHPExcel_Reader_Excel2007_XNamespace();
    
    打赏 评论

相关推荐 更多相似问题