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

Question

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 "[e-mail]"');
   //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);
  }
}

Answer 1

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();