function ViewsDataExportExporterExcelxlsx::add_row in Views data export 7.4

Add a single row to the export file.

Parameters

$file_handle: A PHP file handle to the export file.

array $data: An array of formatted data for this row. One cell per item.

int $row_count: The current number of rows in the export file.

$field_titles:

Overrides ViewsDataExportExporter::add_row

File

exporters/views_data_export_exporter_excel_xlsx.inc, line 22

Class

ViewsDataExportExporterExcelxlsx

This exporter creates an XLSX file readable by newer versions of Excel.

Code

function add_row(&$file_handle, $data, $row_count, $field_titles) {
  $row = $row_count + 1;
  $col = 'A';
  $output = '<row>';
  $utc_timezone = new DateTimeZone('UTC');
  foreach ($data as $key => $value) {
    $cell_position = $col . $row;
    if (empty($value) && strlen($value) === 0) {

      // Skip empty cells.
    }
    elseif (is_numeric($value)) {
      $output .= '<c r="' . $cell_position . '"><v>';
      $output .= $value;
      $output .= '</v></c>';
    }
    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>';
      }
      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"' . (strpos($value, "\n") === FALSE ? '' : ' s="4"') . '><is><t>';
      $output .= htmlspecialchars($value, ENT_QUOTES);
      $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>';
  $output .= PHP_EOL;
  $row++;
  fwrite($file_handle, $output);
}