class ViewsDataExportExporterExcelxlsx in Views data export 7.4
This exporter creates an XLSX file readable by newer versions of Excel.
Hierarchy
- class \ViewsDataExportExporter implements ViewsDataExportExporterInterface
Expanded class hierarchy of ViewsDataExportExporterExcelxlsx
2 string references to 'ViewsDataExportExporterExcelxlsx'
- views_data_export_views_plugins in ./
views_data_export.views.inc - Implementation of hook_views_plugins().
- XLSXExportViewsDataExportExporterTests::getExporterClassName in tests/
exporter_tests/ xlsx.test
File
- exporters/
views_data_export_exporter_excel_xlsx.inc, line 8
View source
class ViewsDataExportExporterExcelxlsx extends ViewsDataExportExporter {
/**
* 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}))?)?$/';
function __construct($options) {
$this->options = $options;
parent::__construct($options);
}
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);
}
function bof(&$file_handle) {
// 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().
$output = str_repeat(' ', 1024);
fwrite($file_handle, $output . "\n");
}
function eof(&$file_handle, $row_count, $col_count) {
// 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.
// 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);
}
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 = drupal_tempnam('temporary://', 'webform_');
// 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);
rename($zip_uri, $file_uri);
}
}
function get_headers($filename) {
$headers = parent::get_headers($filename);
$headers['Content-Type'] = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';
$headers['Content-Disposition'] = "attachment; filename={$filename}.xlsx";
return $headers;
}
/**
* Return all the parts needed to assemble a bare-bones XLSX package.
*/
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;
}
}