aDriv4 - MANAGER

Edit File: webform_exporter_excel_xlsx.inc

<?php

/**
 * This exporter creates an XLSX file readable by newer versions of Excel.
 */
class webform_exporter_excel_xlsx extends webform_exporter {
  /**
   * Regular expression that checks for a valid ISO 8601 date/time.
   */
  const DATE_REGEX_ANY = '/^((\d{4})(-(\d{2}))(-(\d{2})))?(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';
  const DATE_REGEX_DATE = '/^((\d{4})(-(\d{2}))(-(\d{2})))$/';
  const DATE_REGEX_TIME = '/^(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';

/**
   * {@inheritdoc}
   */
  public function add_row(&$file_handle, array $data, $row_count) {
    $row = $row_count + 1;
    $col = 'A';

$output = '<row>';
    $utc_timezone = new DateTimeZone('UTC');
    foreach ($data as $key => $value) {
      // Strip UTF8 characters that are not legal in XML files.
      // See http://www.w3.org/TR/xml/#charsets
      // See http://stackoverflow.com/questions/3466035/how-to-skip-invalid-characters-in-xml-file-using-php
      // @code
      // Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
      // @endcode
      $value = preg_replace('/[^\x{0009}\x{000a}\x{000d}\x{0020}-\x{D7FF}\x{E000}-\x{FFFD}]+/u', '', $value);

$cell_position = $col . $row;
      if (strlen($value) === 0) {
        // Skip empty cells.
      }
      elseif (preg_match(self::DATE_REGEX_ANY, $value)) {
        // An Excel timestamp is the number of days since Jan 1, 1900, with
        // the decimal portion indicating the time (ddddd.tttttt).
        // To calculate, take the UNIX timestamp then add the number of days
        // between 1900 and 1970 (25568).
        $timestamp = date_timestamp_get(date_create($value, $utc_timezone));
        $excel_timestamp = ($timestamp / 86400) + 25568;

// 1900 is treated as a leap year, but it is not. So all dates after
        // Feb 28, 1900 have one extra day added. That is "59" should be
        // March 1, 1900, but it's considered Feb 29, 1900, which didn't exist.
        // So all dates greater than 59 have 1 extra day added.
        // See http://www.cpearson.com/excel/datetime.htm.
        if ($excel_timestamp >= 59) {
          $excel_timestamp = $excel_timestamp + 1.0;
        }

// Excel does not support dates prior to 0 (Jan 1, 1900). They have to
        // be represented as plain-text instead.
        if ($excel_timestamp <= 0) {
          $output .= '<c r="' . $cell_position . '" t="inlineStr"><is><t>';
          $output .= htmlspecialchars($value, ENT_QUOTES);
          $output .= '</t></is></c>';
        }
        // Now after calculating the Excel "timestamp", save it as a decimal
        // and point to a style formatter to make it appear as a date/time.
        else {
          // 1: Dates.
          // 2: Times.
          // 3: Date times.
          // These are tied to style definitions in the styles.xml file
          // generated by webform_exporter_excel_xlsx::xlsx_parts().
          if (preg_match(self::DATE_REGEX_DATE, $value)) {
            $style_format = 1;
          }
          elseif (preg_match(self::DATE_REGEX_TIME, $value)) {
            // Only take the time portion of time values.
            $excel_timestamp = $excel_timestamp - (int) $excel_timestamp;
            $style_format = 2;
          }
          else {
            $style_format = 3;
          }

$output .= '<c r="' . $cell_position . '" s="' . $style_format . '"><v>';
          $output .= $excel_timestamp;
          $output .= '</v></c>';
        }
      }
      else {
        $output .= '<c r="' . $cell_position . '" t="inlineStr"' . ($this->wrappable($row_count, $key, $value) ? ' s="4"' : '') . '><is><t>';
        $output .= htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
        $output .= '</t></is></c>';
      }

// Unbelievably, in PHP you can increment on letters. "Z"++ becomes "AA"
      // and "AA"++ becomes "AB", identical to Excel column names.
      $col++;
    }
    $output .= '</row>';
    $row++;

@fwrite($file_handle, $output);
  }

/**
   * Output space for the BOF.
   *
   * Our beginning of file needs to include unknown data (the number of columns
   * and rows) at this point. Instead of writing the true BOF, we output enough
   * empty space to fill in the BOF later. See
   * webform_exporter_excel_xlsx::eof().
   */
  public function bof(&$file_handle) {
    $output = str_repeat(' ', 1024);
    @fwrite($file_handle, $output . "\n");
  }

/**
   * Output the BOF and end the file.
   *
   * We output a chunk of empty data in webform_exporter_excel_xlsx::bof() to
   * leave room for our real header, which includes the important <dimension>
   * tag. This is required for proper importing into Google Docs.
   */
  public function eof(&$file_handle, $row_count, $col_count) {
    // Convert column count to letter representation.
    $col = 'A';
    for ($n = 1; $n < $col_count; $n++) {
      $col++;
    }

$bof = '';
    $bof .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
    $bof .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
    $bof .= '<dimension ref="A1:' . $col . $row_count . '"/>';
    $bof .= '<sheetData>';

@fseek($file_handle, 0);
    @fwrite($file_handle, $bof);

$eof = '';
    $eof .= '</sheetData>';
    $eof .= '</worksheet>';

fseek($file_handle, 0, SEEK_END);
    fwrite($file_handle, $eof);
  }

/**
   * {@inheritdoc}
   */
  public function post_process(&$results) {
    // Our download file is currently a single XML sheet file. We need to add
    // the peripheral XML files to make this into a XLSX directory, then zip it.
    $file_uri = $results['file_name'];
    $zip_uri = _webform_export_tempname();

// ZipArchive does not support stream wrappers, convert to filesystem path.
    $zip_filepath = drupal_realpath($zip_uri);
    $file_filepath = drupal_realpath($file_uri);
    $zip = new ZipArchive();
    if ($zip->open($zip_filepath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
      // Create a bare-bones Office Open XML format directory structure. This is
      // based on the sample simple XLSX file at
      // http://blogs.msdn.com/b/chrisrae/archive/2011/08/18/creating-a-simple-xlsx-from-scratch-using-the-open-xml-sdk.aspx
      $parts = $this->xlsx_parts();
      foreach ($parts as $file_name => $file_contents) {
        if (empty($file_contents)) {
          $zip->addEmptyDir($file_name);
        }
        else {
          $zip->addFromString($file_name, $file_contents);
        }
      }

// Add the actual export to the zip.
      $zip->addEmptyDir('xl/worksheets');
      $zip->addFile($file_filepath, 'xl/worksheets/sheet1.xml');

$zip->close();

// Switch the results file name to the new zip (xlsx) file.
      unlink($file_uri);
      if (!@rename($zip_uri, $file_uri)) {
        // The file could not be renamed, probably due to different stream
        // wrappers during drush wfx execution.
        copy($zip_uri, $file_uri);
        unlink($zip_uri);
      }
    }
  }

/**
   * {@inheritdoc}
   */
  public function set_headers($filename) {
    drupal_add_http_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    drupal_add_http_header('Content-Disposition', 'attachment; filename=' . $this->get_filename($filename));
    drupal_add_http_header('Pragma', 'public');
    drupal_add_http_header('Cache-Control', 'max-age=0');
  }

/**
   * {@inheritdoc}
   */
  public function get_filename($filename) {
    return $filename . '.xlsx';
  }

/**
   * Return all the parts needed to assemble a bare-bones XLSX package.
   */
  public function xlsx_parts() {
    $parts['_rels'] = '';
    $parts['_rels/.rels'] = <<<EOL
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>
EOL;

$parts['xl'] = '';
    $parts['xl/_rels'] = '';
    $parts['xl/_rels/workbook.xml.rels'] = <<<EOL
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>
EOL;
    $parts['xl/styles.xml'] = <<<EOL
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="1"><font><name val="Verdana"/></font></fonts>
  <fills count="1"><fill><patternFill patternType="none"/></fill></fills>
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="5">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="18" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="22" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
      <alignment wrapText="1"/>
    </xf>
  </cellXfs>
  <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
  <dxfs count="0"/>
  <tableStyles count="0"/>
</styleSheet>
EOL;
    $parts['xl/workbook.xml'] = <<<EOL
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <workbookPr />
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
  </sheets>
  <calcPr calcId="0"/>
  <fileRecoveryPr repairLoad="1"/>
</workbook>
EOL;

$parts['[Content_Types].xml'] = <<<EOL
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>
EOL;

return $parts;
  }

}