public function webform_exporter_excel_xlsx::add_row in Webform 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.
Overrides webform_exporter::add_row
File
- includes/
exporters/ webform_exporter_excel_xlsx.inc, line 17
Class
- webform_exporter_excel_xlsx
- This exporter creates an XLSX file readable by newer versions of Excel.
Code
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>';
}
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);
}