How can I read an XLSX mail attachment file with PHPExcel?

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 "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);
  }
}

@rtfm The XLSX file is being written to the server successfully, the part that does not work is when loading the XLSX file from the server and converting it to Array — Josué Morales, Sep 05 '17 at 23:25
@MarkBaker there are no errors shown or logs written, what I did notice is that after the XLSX file is being downloaded if I open it with MS Excel and then Save it replacing the file itself, I am able to open and load the file with PHPExcel. Am I missing something while writing the file? Am I missing any other steps after decoding with base64_decode(...)? — Josué Morales, Sep 06 '17 at 13:39

score 1 · Accepted Answer · answered Sep 06 '17 at 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();

How can I read an XLSX mail attachment file with PHPExcel?

1 Answers1