Club Drupal

<?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) === 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=$filename.xlsx");

    drupal_add_http_header('Pragma', 'public');

    drupal_add_http_header('Cache-Control', 'max-age=0');

  }

  /**

   * 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;

  }

}