1 |
a45e4bc1
|
Assos Assos
|
<?php
|
2 |
|
|
|
3 |
|
|
/**
|
4 |
|
|
* @file
|
5 |
|
|
* This exporter creates an XLSX file readable by newer versions of Excel.
|
6 |
|
|
*/
|
7 |
|
|
|
8 |
|
|
class webform_exporter_excel_xlsx extends webform_exporter {
|
9 |
|
|
/**
|
10 |
|
|
* Regular expression that checks for a valid ISO 8601 date/time.
|
11 |
|
|
*/
|
12 |
|
|
const DATE_REGEX_ANY = '/^((\d{4})(-(\d{2}))(-(\d{2})))?(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';
|
13 |
|
|
const DATE_REGEX_DATE = '/^((\d{4})(-(\d{2}))(-(\d{2})))$/';
|
14 |
|
|
const DATE_REGEX_TIME = '/^(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';
|
15 |
|
|
|
16 |
|
|
function add_row(&$file_handle, $data, $row_count) {
|
17 |
|
|
$row = $row_count + 1;
|
18 |
|
|
$col = 'A';
|
19 |
|
|
|
20 |
|
|
$output = '<row>';
|
21 |
|
|
$utc_timezone = new DateTimeZone('UTC');
|
22 |
|
|
foreach ($data as $key => $value) {
|
23 |
|
|
// Strip UTF8 characters that are not legal in XML files.
|
24 |
|
|
// See http://www.w3.org/TR/xml/#charsets
|
25 |
|
|
// See http://stackoverflow.com/questions/3466035/how-to-skip-invalid-characters-in-xml-file-using-php
|
26 |
|
|
// Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
|
27 |
|
|
$value = preg_replace('/[^\x{0009}\x{000a}\x{000d}\x{0020}-\x{D7FF}\x{E000}-\x{FFFD}]+/u', '', $value);
|
28 |
|
|
|
29 |
|
|
$cell_position = $col . $row;
|
30 |
|
|
if (strlen($value) === 0) {
|
31 |
|
|
// Skip empty cells.
|
32 |
|
|
}
|
33 |
|
|
elseif (is_numeric($value) && $value[0] !== '+') {
|
34 |
|
|
$output .= '<c r="' . $cell_position . '"><v>';
|
35 |
|
|
$output .= $value;
|
36 |
|
|
$output .= '</v></c>';
|
37 |
|
|
}
|
38 |
|
|
elseif (preg_match(self::DATE_REGEX_ANY, $value)) {
|
39 |
|
|
// An Excel timestamp is the number of days since Jan 1, 1900, with
|
40 |
|
|
// the decimal portion indicating the time (ddddd.tttttt).
|
41 |
|
|
// To calculate, take the UNIX timestamp then add the number of days
|
42 |
|
|
// between 1900 and 1970 (25568).
|
43 |
|
|
$timestamp = date_timestamp_get(date_create($value, $utc_timezone));
|
44 |
|
|
$excel_timestamp = ($timestamp / 86400) + 25568;
|
45 |
|
|
|
46 |
|
|
// 1900 is treated as a leap year, but it is not. So all dates after
|
47 |
|
|
// Feb 28, 1900 have one extra day added. That is "59" should be
|
48 |
|
|
// March 1, 1900, but it's considered Feb 29, 1900, which didn't exist.
|
49 |
|
|
// So all dates greater than 59 have 1 extra day added.
|
50 |
|
|
// See http://www.cpearson.com/excel/datetime.htm.
|
51 |
|
|
if ($excel_timestamp >= 59) {
|
52 |
|
|
$excel_timestamp = $excel_timestamp + 1.0;
|
53 |
|
|
}
|
54 |
|
|
|
55 |
|
|
// Excel does not support dates prior to 0 (Jan 1, 1900). They have to
|
56 |
|
|
// be represented as plain-text instead.
|
57 |
|
|
if ($excel_timestamp <= 0) {
|
58 |
|
|
$output .= '<c r="' . $cell_position . '" t="inlineStr"><is><t>';
|
59 |
|
|
$output .= htmlspecialchars($value, ENT_QUOTES);
|
60 |
|
|
$output .= '</t></is></c>';
|
61 |
|
|
}
|
62 |
|
|
// Now after calculating the Excel "timestamp", save it as a decimal
|
63 |
|
|
// and point to a style formatter to make it appear as a date/time.
|
64 |
|
|
else {
|
65 |
|
|
// 1: Dates.
|
66 |
|
|
// 2: Times.
|
67 |
|
|
// 3: Date times.
|
68 |
|
|
// These are tied to style definitions in the styles.xml file
|
69 |
|
|
// generated by webform_exporter_excel_xlsx::xlsx_parts().
|
70 |
|
|
if (preg_match(self::DATE_REGEX_DATE, $value)) {
|
71 |
|
|
$style_format = 1;
|
72 |
|
|
}
|
73 |
|
|
elseif (preg_match(self::DATE_REGEX_TIME, $value)) {
|
74 |
|
|
// Only take the time portion of time values.
|
75 |
|
|
$excel_timestamp = $excel_timestamp - (int) $excel_timestamp;
|
76 |
|
|
$style_format = 2;
|
77 |
|
|
}
|
78 |
|
|
else {
|
79 |
|
|
$style_format = 3;
|
80 |
|
|
}
|
81 |
|
|
|
82 |
|
|
$output .= '<c r="' . $cell_position . '" s="' . $style_format . '"><v>';
|
83 |
|
|
$output .= $excel_timestamp;
|
84 |
|
|
$output .= '</v></c>';
|
85 |
|
|
}
|
86 |
|
|
}
|
87 |
|
|
else {
|
88 |
|
|
$output .= '<c r="' . $cell_position . '" t="inlineStr"' . ($this->wrappable($row_count, $key, $value) ? ' s="4"' : '') . '><is><t>';
|
89 |
|
|
$output .= htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
|
90 |
|
|
$output .= '</t></is></c>';
|
91 |
|
|
}
|
92 |
|
|
|
93 |
|
|
// Unbelievably, in PHP you can increment on letters. "Z"++ becomes "AA"
|
94 |
|
|
// and "AA"++ becomes "AB", identical to Excel column names.
|
95 |
|
|
$col++;
|
96 |
|
|
}
|
97 |
|
|
$output .= '</row>';
|
98 |
|
|
$row++;
|
99 |
|
|
|
100 |
|
|
@fwrite($file_handle, $output);
|
101 |
|
|
}
|
102 |
|
|
|
103 |
|
|
function bof(&$file_handle) {
|
104 |
|
|
// Our beginning of file needs to include unknown data (the number of
|
105 |
|
|
// columns and rows) at this point. Instead of writing the true BOF, we
|
106 |
|
|
// output enough empty space to fill in the BOF later.
|
107 |
|
|
// See webform_exporter_excel_xlsx::eof().
|
108 |
|
|
$output = str_repeat(' ', 1024);
|
109 |
|
|
@fwrite($file_handle, $output . "\n");
|
110 |
|
|
}
|
111 |
|
|
|
112 |
|
|
function eof(&$file_handle, $row_count, $col_count) {
|
113 |
|
|
// We output a chunk of empty data in webform_exporter_excel_xlsx::bof()
|
114 |
|
|
// to leave room for our real header, which includes the important
|
115 |
|
|
// <dimension> tag. This is required for proper importing into Google Docs.
|
116 |
|
|
|
117 |
|
|
// Convert column count to letter representation.
|
118 |
|
|
$col = 'A';
|
119 |
|
|
for ($n = 1; $n < $col_count; $n++) {
|
120 |
|
|
$col++;
|
121 |
|
|
}
|
122 |
|
|
|
123 |
|
|
$bof = '';
|
124 |
|
|
$bof .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
|
125 |
|
|
$bof .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
|
126 |
|
|
$bof .= '<dimension ref="A1:' . $col . $row_count . '"/>';
|
127 |
|
|
$bof .= '<sheetData>';
|
128 |
|
|
|
129 |
|
|
@fseek($file_handle, 0);
|
130 |
|
|
@fwrite($file_handle, $bof);
|
131 |
|
|
|
132 |
|
|
$eof = '';
|
133 |
|
|
$eof .= '</sheetData>';
|
134 |
|
|
$eof .= '</worksheet>';
|
135 |
|
|
|
136 |
|
|
fseek($file_handle, 0, SEEK_END);
|
137 |
|
|
fwrite($file_handle, $eof);
|
138 |
|
|
}
|
139 |
|
|
|
140 |
|
|
function post_process(&$results) {
|
141 |
|
|
// Our download file is currently a single XML sheet file. We need to add
|
142 |
|
|
// the peripheral XML files to make this into a XLSX directory, then zip it.
|
143 |
|
|
$file_uri = $results['file_name'];
|
144 |
|
|
$zip_uri = _webform_export_tempname();
|
145 |
|
|
|
146 |
|
|
// ZipArchive does not support stream wrappers, convert to filesystem path.
|
147 |
|
|
$zip_filepath = drupal_realpath($zip_uri);
|
148 |
|
|
$file_filepath = drupal_realpath($file_uri);
|
149 |
|
|
$zip = new ZipArchive;
|
150 |
|
|
if ($zip->open($zip_filepath, ZipArchive::CREATE) === TRUE) {
|
151 |
|
|
// Create a bare-bones Office Open XML format directory structure. This is
|
152 |
|
|
// based on the sample simple XLSX file at
|
153 |
|
|
// http://blogs.msdn.com/b/chrisrae/archive/2011/08/18/creating-a-simple-xlsx-from-scratch-using-the-open-xml-sdk.aspx
|
154 |
|
|
$parts = $this->xlsx_parts();
|
155 |
|
|
foreach ($parts as $file_name => $file_contents) {
|
156 |
|
|
if (empty($file_contents)) {
|
157 |
|
|
$zip->addEmptyDir($file_name);
|
158 |
|
|
}
|
159 |
|
|
else {
|
160 |
|
|
$zip->addFromString($file_name, $file_contents);
|
161 |
|
|
}
|
162 |
|
|
}
|
163 |
|
|
|
164 |
|
|
// Add the actual export to the zip.
|
165 |
|
|
$zip->addEmptyDir('xl/worksheets');
|
166 |
|
|
$zip->addFile($file_filepath, 'xl/worksheets/sheet1.xml');
|
167 |
|
|
|
168 |
|
|
$zip->close();
|
169 |
|
|
|
170 |
|
|
// Switch the results file name to the new zip (xlsx) file.
|
171 |
|
|
unlink($file_uri);
|
172 |
|
|
if (!@rename($zip_uri, $file_uri)) {
|
173 |
|
|
// The file could not be renamed, probably due to different stream wrappers during drush wfx execution.
|
174 |
|
|
copy($zip_uri, $file_uri);
|
175 |
|
|
unlink($zip_uri);
|
176 |
|
|
}
|
177 |
|
|
}
|
178 |
|
|
}
|
179 |
|
|
|
180 |
|
|
function set_headers($filename) {
|
181 |
|
|
drupal_add_http_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
|
182 |
|
|
drupal_add_http_header('Content-Disposition', "attachment; filename=$filename.xlsx");
|
183 |
|
|
drupal_add_http_header('Pragma', 'public');
|
184 |
|
|
drupal_add_http_header('Cache-Control', 'max-age=0');
|
185 |
|
|
}
|
186 |
|
|
|
187 |
|
|
/**
|
188 |
|
|
* Return all the parts needed to assemble a bare-bones XLSX package.
|
189 |
|
|
*/
|
190 |
|
|
function xlsx_parts() {
|
191 |
|
|
$parts['_rels'] = '';
|
192 |
|
|
$parts['_rels/.rels'] = <<<EOL
|
193 |
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
194 |
|
|
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
|
195 |
|
|
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
|
196 |
|
|
</Relationships>
|
197 |
|
|
EOL;
|
198 |
|
|
|
199 |
|
|
$parts['xl'] = '';
|
200 |
|
|
$parts['xl/_rels'] = '';
|
201 |
|
|
$parts['xl/_rels/workbook.xml.rels'] = <<<EOL
|
202 |
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
203 |
|
|
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
|
204 |
|
|
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
|
205 |
|
|
<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
|
206 |
|
|
</Relationships>
|
207 |
|
|
EOL;
|
208 |
|
|
$parts['xl/styles.xml'] = <<<EOL
|
209 |
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
210 |
|
|
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
|
211 |
|
|
<fonts count="1"><font><name val="Verdana"/></font></fonts>
|
212 |
|
|
<fills count="1"><fill><patternFill patternType="none"/></fill></fills>
|
213 |
|
|
<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
|
214 |
|
|
<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
|
215 |
|
|
<cellXfs count="5">
|
216 |
|
|
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
|
217 |
|
|
<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
|
218 |
|
|
<xf numFmtId="18" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
|
219 |
|
|
<xf numFmtId="22" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
|
220 |
|
|
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
|
221 |
|
|
<alignment wrapText="1"/>
|
222 |
|
|
</xf>
|
223 |
|
|
</cellXfs>
|
224 |
|
|
<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
|
225 |
|
|
<dxfs count="0"/>
|
226 |
|
|
<tableStyles count="0"/>
|
227 |
|
|
</styleSheet>
|
228 |
|
|
EOL;
|
229 |
|
|
$parts['xl/workbook.xml'] = <<<EOL
|
230 |
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
231 |
|
|
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
|
232 |
|
|
<workbookPr />
|
233 |
|
|
<sheets>
|
234 |
|
|
<sheet name="Sheet1" sheetId="1" r:id="rId1"/>
|
235 |
|
|
</sheets>
|
236 |
|
|
<calcPr calcId="0"/>
|
237 |
|
|
<fileRecoveryPr repairLoad="1"/>
|
238 |
|
|
</workbook>
|
239 |
|
|
EOL;
|
240 |
|
|
|
241 |
|
|
$parts['[Content_Types].xml'] = <<<EOL
|
242 |
|
|
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
|
243 |
|
|
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
|
244 |
|
|
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
|
245 |
|
|
<Default Extension="xml" ContentType="application/xml"/>
|
246 |
|
|
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
|
247 |
|
|
<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
|
248 |
|
|
<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
|
249 |
|
|
</Types>
|
250 |
|
|
EOL;
|
251 |
|
|
|
252 |
|
|
return $parts;
|
253 |
|
|
}
|
254 |
|
|
|
255 |
|
|
} |