Project

General

Profile

Paste
Download (10.7 KB) Statistics
| Branch: | Revision:

root / drupal7 / sites / all / modules / webform / includes / exporters / webform_exporter_excel_xlsx.inc @ 7b2d1845

1
<?php
2

    
3
/**
4
 * This exporter creates an XLSX file readable by newer versions of Excel.
5
 */
6
class webform_exporter_excel_xlsx extends webform_exporter {
7
  /**
8
   * Regular expression that checks for a valid ISO 8601 date/time.
9
   */
10
  const DATE_REGEX_ANY = '/^((\d{4})(-(\d{2}))(-(\d{2})))?(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';
11
  const DATE_REGEX_DATE = '/^((\d{4})(-(\d{2}))(-(\d{2})))$/';
12
  const DATE_REGEX_TIME = '/^(([T \s]?(\d{2}))(:(\d{2}))(:(\d{2}))?)?$/';
13

    
14
  /**
15
   * {@inheritdoc}
16
   */
17
  public function add_row(&$file_handle, array $data, $row_count) {
18
    $row = $row_count + 1;
19
    $col = 'A';
20

    
21
    $output = '<row>';
22
    $utc_timezone = new DateTimeZone('UTC');
23
    foreach ($data as $key => $value) {
24
      // Strip UTF8 characters that are not legal in XML files.
25
      // See http://www.w3.org/TR/xml/#charsets
26
      // See http://stackoverflow.com/questions/3466035/how-to-skip-invalid-characters-in-xml-file-using-php
27
      // @code
28
      // Char ::= #x9 | #xA | #xD | [#x20-#xD7FF] | [#xE000-#xFFFD] | [#x10000-#x10FFFF]
29
      // @endcode
30
      $value = preg_replace('/[^\x{0009}\x{000a}\x{000d}\x{0020}-\x{D7FF}\x{E000}-\x{FFFD}]+/u', '', $value);
31

    
32
      $cell_position = $col . $row;
33
      if (strlen($value) === 0) {
34
        // Skip empty cells.
35
      }
36
      elseif (preg_match(self::DATE_REGEX_ANY, $value)) {
37
        // An Excel timestamp is the number of days since Jan 1, 1900, with
38
        // the decimal portion indicating the time (ddddd.tttttt).
39
        // To calculate, take the UNIX timestamp then add the number of days
40
        // between 1900 and 1970 (25568).
41
        $timestamp = date_timestamp_get(date_create($value, $utc_timezone));
42
        $excel_timestamp = ($timestamp / 86400) + 25568;
43

    
44
        // 1900 is treated as a leap year, but it is not. So all dates after
45
        // Feb 28, 1900 have one extra day added. That is "59" should be
46
        // March 1, 1900, but it's considered Feb 29, 1900, which didn't exist.
47
        // So all dates greater than 59 have 1 extra day added.
48
        // See http://www.cpearson.com/excel/datetime.htm.
49
        if ($excel_timestamp >= 59) {
50
          $excel_timestamp = $excel_timestamp + 1.0;
51
        }
52

    
53
        // Excel does not support dates prior to 0 (Jan 1, 1900). They have to
54
        // be represented as plain-text instead.
55
        if ($excel_timestamp <= 0) {
56
          $output .= '<c r="' . $cell_position . '" t="inlineStr"><is><t>';
57
          $output .= htmlspecialchars($value, ENT_QUOTES);
58
          $output .= '</t></is></c>';
59
        }
60
        // Now after calculating the Excel "timestamp", save it as a decimal
61
        // and point to a style formatter to make it appear as a date/time.
62
        else {
63
          // 1: Dates.
64
          // 2: Times.
65
          // 3: Date times.
66
          // These are tied to style definitions in the styles.xml file
67
          // generated by webform_exporter_excel_xlsx::xlsx_parts().
68
          if (preg_match(self::DATE_REGEX_DATE, $value)) {
69
            $style_format = 1;
70
          }
71
          elseif (preg_match(self::DATE_REGEX_TIME, $value)) {
72
            // Only take the time portion of time values.
73
            $excel_timestamp = $excel_timestamp - (int) $excel_timestamp;
74
            $style_format = 2;
75
          }
76
          else {
77
            $style_format = 3;
78
          }
79

    
80
          $output .= '<c r="' . $cell_position . '" s="' . $style_format . '"><v>';
81
          $output .= $excel_timestamp;
82
          $output .= '</v></c>';
83
        }
84
      }
85
      else {
86
        $output .= '<c r="' . $cell_position . '" t="inlineStr"' . ($this->wrappable($row_count, $key, $value) ? ' s="4"' : '') . '><is><t>';
87
        $output .= htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
88
        $output .= '</t></is></c>';
89
      }
90

    
91
      // Unbelievably, in PHP you can increment on letters. "Z"++ becomes "AA"
92
      // and "AA"++ becomes "AB", identical to Excel column names.
93
      $col++;
94
    }
95
    $output .= '</row>';
96
    $row++;
97

    
98
    @fwrite($file_handle, $output);
99
  }
100

    
101
  /**
102
   * Output space for the BOF.
103
   *
104
   * Our beginning of file needs to include unknown data (the number of columns
105
   * and rows) at this point. Instead of writing the true BOF, we output enough
106
   * empty space to fill in the BOF later. See
107
   * webform_exporter_excel_xlsx::eof().
108
   */
109
  public function bof(&$file_handle) {
110
    $output = str_repeat(' ', 1024);
111
    @fwrite($file_handle, $output . "\n");
112
  }
113

    
114
  /**
115
   * Output the BOF and end the file.
116
   *
117
   * We output a chunk of empty data in webform_exporter_excel_xlsx::bof() to
118
   * leave room for our real header, which includes the important <dimension>
119
   * tag. This is required for proper importing into Google Docs.
120
   */
121
  public function eof(&$file_handle, $row_count, $col_count) {
122
    // Convert column count to letter representation.
123
    $col = 'A';
124
    for ($n = 1; $n < $col_count; $n++) {
125
      $col++;
126
    }
127

    
128
    $bof = '';
129
    $bof .= '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' . "\n";
130
    $bof .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">';
131
    $bof .= '<dimension ref="A1:' . $col . $row_count . '"/>';
132
    $bof .= '<sheetData>';
133

    
134
    @fseek($file_handle, 0);
135
    @fwrite($file_handle, $bof);
136

    
137
    $eof = '';
138
    $eof .= '</sheetData>';
139
    $eof .= '</worksheet>';
140

    
141
    fseek($file_handle, 0, SEEK_END);
142
    fwrite($file_handle, $eof);
143
  }
144

    
145
  /**
146
   * {@inheritdoc}
147
   */
148
  public function post_process(&$results) {
149
    // Our download file is currently a single XML sheet file. We need to add
150
    // the peripheral XML files to make this into a XLSX directory, then zip it.
151
    $file_uri = $results['file_name'];
152
    $zip_uri = _webform_export_tempname();
153

    
154
    // ZipArchive does not support stream wrappers, convert to filesystem path.
155
    $zip_filepath = drupal_realpath($zip_uri);
156
    $file_filepath = drupal_realpath($file_uri);
157
    $zip = new ZipArchive();
158
    if ($zip->open($zip_filepath, ZipArchive::CREATE | ZipArchive::OVERWRITE) === TRUE) {
159
      // Create a bare-bones Office Open XML format directory structure. This is
160
      // based on the sample simple XLSX file at
161
      // http://blogs.msdn.com/b/chrisrae/archive/2011/08/18/creating-a-simple-xlsx-from-scratch-using-the-open-xml-sdk.aspx
162
      $parts = $this->xlsx_parts();
163
      foreach ($parts as $file_name => $file_contents) {
164
        if (empty($file_contents)) {
165
          $zip->addEmptyDir($file_name);
166
        }
167
        else {
168
          $zip->addFromString($file_name, $file_contents);
169
        }
170
      }
171

    
172
      // Add the actual export to the zip.
173
      $zip->addEmptyDir('xl/worksheets');
174
      $zip->addFile($file_filepath, 'xl/worksheets/sheet1.xml');
175

    
176
      $zip->close();
177

    
178
      // Switch the results file name to the new zip (xlsx) file.
179
      unlink($file_uri);
180
      if (!@rename($zip_uri, $file_uri)) {
181
        // The file could not be renamed, probably due to different stream
182
        // wrappers during drush wfx execution.
183
        copy($zip_uri, $file_uri);
184
        unlink($zip_uri);
185
      }
186
    }
187
  }
188

    
189
  /**
190
   * {@inheritdoc}
191
   */
192
  public function set_headers($filename) {
193
    drupal_add_http_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
194
    drupal_add_http_header('Content-Disposition', 'attachment; filename=' . $this->get_filename($filename));
195
    drupal_add_http_header('Pragma', 'public');
196
    drupal_add_http_header('Cache-Control', 'max-age=0');
197
  }
198

    
199
  /**
200
   * {@inheritdoc}
201
   */
202
  public function get_filename($filename) {
203
    return $filename . '.xlsx';
204
  }
205

    
206
  /**
207
   * Return all the parts needed to assemble a bare-bones XLSX package.
208
   */
209
  public function xlsx_parts() {
210
    $parts['_rels'] = '';
211
    $parts['_rels/.rels'] = <<<EOL
212
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
213
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
214
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
215
</Relationships>
216
EOL;
217

    
218
    $parts['xl'] = '';
219
    $parts['xl/_rels'] = '';
220
    $parts['xl/_rels/workbook.xml.rels'] = <<<EOL
221
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
222
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
223
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
224
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
225
</Relationships>
226
EOL;
227
    $parts['xl/styles.xml'] = <<<EOL
228
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
229
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
230
  <fonts count="1"><font><name val="Verdana"/></font></fonts>
231
  <fills count="1"><fill><patternFill patternType="none"/></fill></fills>
232
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
233
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
234
  <cellXfs count="5">
235
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
236
    <xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
237
    <xf numFmtId="18" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
238
    <xf numFmtId="22" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
239
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
240
      <alignment wrapText="1"/>
241
    </xf>
242
  </cellXfs>
243
  <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
244
  <dxfs count="0"/>
245
  <tableStyles count="0"/>
246
</styleSheet>
247
EOL;
248
    $parts['xl/workbook.xml'] = <<<EOL
249
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
250
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
251
  <workbookPr />
252
  <sheets>
253
    <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
254
  </sheets>
255
  <calcPr calcId="0"/>
256
  <fileRecoveryPr repairLoad="1"/>
257
</workbook>
258
EOL;
259

    
260
    $parts['[Content_Types].xml'] = <<<EOL
261
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
262
  <Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
263
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
264
  <Default Extension="xml" ContentType="application/xml"/>
265
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
266
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
267
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
268
</Types>
269
EOL;
270

    
271
    return $parts;
272
  }
273

    
274
}