| 1 |
<?php |
| 2 |
/** |
| 3 |
* TablePress Table Import PHPSpreadsheet Class |
| 4 |
* |
| 5 |
* @package TablePress |
| 6 |
* @subpackage Export/Import |
| 7 |
* @author Tobias Bäthge |
| 8 |
* @since 2.0.0 |
| 9 |
*/ |
| 10 |
|
| 11 |
// Prohibit direct script loading. |
| 12 |
defined( 'ABSPATH' ) || die( 'No direct script access allowed!' ); |
| 13 |
|
| 14 |
/** |
| 15 |
* TablePress Table Import PHPSpreadsheet Class |
| 16 |
* |
| 17 |
* @package TablePress |
| 18 |
* @subpackage Export/Import |
| 19 |
* @author Tobias Bäthge |
| 20 |
* @since 2.0.0 |
| 21 |
*/ |
| 22 |
class TablePress_Import_PHPSpreadsheet extends TablePress_Import_Base { |
| 23 |
|
| 24 |
/** |
| 25 |
* Initializes the Import class. |
| 26 |
* |
| 27 |
* @since 2.0.0 |
| 28 |
*/ |
| 29 |
public function __construct() { |
| 30 |
// Load PHPSpreadsheet via the Composer autoloading mechanism. |
| 31 |
TablePress::load_file( 'autoload.php', 'libraries' ); |
| 32 |
} |
| 33 |
|
| 34 |
/** |
| 35 |
* Imports a table from a file. |
| 36 |
* |
| 37 |
* @since 2.0.0 |
| 38 |
* |
| 39 |
* @param array $file File to import. |
| 40 |
* @return array|WP_Error Table array on success, WP_Error on error. |
| 41 |
*/ |
| 42 |
public function import_table( array $file ) { |
| 43 |
$data = file_get_contents( $file['location'] ); |
| 44 |
if ( false === $data ) { |
| 45 |
return new WP_Error( 'table_import_phpspreadsheet_data_read', '', $file['location'] ); |
| 46 |
} |
| 47 |
|
| 48 |
// Remove a possible UTF-8 Byte-Order Mark (BOM). |
| 49 |
$bom = pack( 'CCC', 0xef, 0xbb, 0xbf ); |
| 50 |
if ( 0 === strncmp( $data, $bom, 3 ) ) { |
| 51 |
$data = substr( $data, 3 ); |
| 52 |
} |
| 53 |
|
| 54 |
if ( '' === $data ) { |
| 55 |
return new WP_Error( 'table_import_phpspreadsheet_data_empty', '', $file['location'] ); |
| 56 |
} |
| 57 |
|
| 58 |
$table = $this->_maybe_import_json( $data ); |
| 59 |
if ( false !== $table ) { |
| 60 |
return $table; |
| 61 |
} |
| 62 |
|
| 63 |
$table = $this->_maybe_import_html( $data ); |
| 64 |
if ( false !== $table ) { |
| 65 |
return $table; |
| 66 |
} |
| 67 |
|
| 68 |
return $this->_import_phpspreadsheet( $file ); |
| 69 |
} |
| 70 |
|
| 71 |
/** |
| 72 |
* Tries to import a table with the JSON format. |
| 73 |
* |
| 74 |
* @since 2.0.0 |
| 75 |
* |
| 76 |
* @param string $data Data to import. |
| 77 |
* @return array|WP_Error|false Table array on success, WP_Error on error, false if the file is not a JSON file. |
| 78 |
*/ |
| 79 |
protected function _maybe_import_json( $data ) { |
| 80 |
// If the first non-whitespace character is not a { or [, the file is not a supported JSON file. |
| 81 |
$data = ltrim( $data ); |
| 82 |
$first_character = $data[0]; |
| 83 |
if ( '{' !== $first_character && '[' !== $first_character ) { |
| 84 |
return false; |
| 85 |
} |
| 86 |
|
| 87 |
$json_table = json_decode( $data, true ); |
| 88 |
|
| 89 |
// Check if JSON could be decoded. If not, this is probably not a JSON file. |
| 90 |
if ( is_null( $json_table ) ) { |
| 91 |
return false; |
| 92 |
} |
| 93 |
|
| 94 |
// Specifically cast to an array again. |
| 95 |
$json_table = (array) $json_table; |
| 96 |
|
| 97 |
if ( isset( $json_table['data'] ) ) { |
| 98 |
// JSON data contained a full export. |
| 99 |
$table = $json_table; |
| 100 |
} else { |
| 101 |
// JSON data contained only the data of a table, but no options. |
| 102 |
$table = array( 'data' => array() ); |
| 103 |
foreach ( $json_table as $row ) { |
| 104 |
// Turn row into indexed arrays with numeric keys. |
| 105 |
$row = array_values( (array) $row ); |
| 106 |
|
| 107 |
// Remove entries of multi-dimensional arrays. |
| 108 |
foreach ( $row as &$cell ) { |
| 109 |
if ( is_array( $cell ) ) { |
| 110 |
$cell = ''; |
| 111 |
} |
| 112 |
} |
| 113 |
unset( $cell ); // Unset use-by-reference parameter of foreach loop. |
| 114 |
|
| 115 |
$table['data'][] = $row; |
| 116 |
} |
| 117 |
} |
| 118 |
|
| 119 |
$this->pad_array_to_max_cols( $table['data'] ); |
| 120 |
return $table; |
| 121 |
} |
| 122 |
|
| 123 |
/** |
| 124 |
* Tries to import a table with the HTML format. |
| 125 |
* |
| 126 |
* @since 2.0.0 |
| 127 |
* |
| 128 |
* @param string $data Data to import. |
| 129 |
* @return array|WP_Error|false Table array on success, WP_Error on error, false if the file is not a JSON file. |
| 130 |
*/ |
| 131 |
protected function _maybe_import_html( $data ) { |
| 132 |
TablePress::load_file( 'html-parser.class.php', 'libraries' ); |
| 133 |
$table = HTML_Parser::parse( $data ); |
| 134 |
|
| 135 |
// Check if the HTML code could be parsed. If not, this is probably not an HTML file. |
| 136 |
if ( is_wp_error( $table ) ) { |
| 137 |
return false; |
| 138 |
} |
| 139 |
|
| 140 |
$this->pad_array_to_max_cols( $table['data'] ); |
| 141 |
return $table; |
| 142 |
} |
| 143 |
|
| 144 |
/** |
| 145 |
* Tries to import a table via PHPSpreadsheet. |
| 146 |
* |
| 147 |
* @since 2.0.0 |
| 148 |
* |
| 149 |
* @param array $file File to import. |
| 150 |
* @return array|WP_Error Table array on success, WP_Error on error. |
| 151 |
*/ |
| 152 |
protected function _import_phpspreadsheet( array $file ) { |
| 153 |
// Rename the temporary file, as PHPSpreadsheet tries to infer the format from the file's extension. |
| 154 |
if ( '' !== $file['extension'] ) { |
| 155 |
$temp_file = pathinfo( $file['location'] ); |
| 156 |
if ( ! isset( $temp_file['extension'] ) || $file['extension'] !== $temp_file['extension'] ) { |
| 157 |
$new_location = "{$temp_file['dirname']}/{$temp_file['filename']}.{$file['extension']}"; |
| 158 |
if ( rename( $file['location'], $new_location ) ) { |
| 159 |
$file['location'] = $new_location; |
| 160 |
} |
| 161 |
} |
| 162 |
} |
| 163 |
|
| 164 |
try { |
| 165 |
// Treat all cell values as strings, except for formulas (due to recognition of quoted/escaped formulas like `'=A2`). |
| 166 |
\TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \TablePress\PhpOffice\PhpSpreadsheet\Cell\StringValueBinder() ); |
| 167 |
\TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell::getValueBinder()->setFormulaConversion( false ); |
| 168 |
|
| 169 |
/* |
| 170 |
* Try to detect a reader from the file extension and MIME type. |
| 171 |
* Fall back to CSV if no reader could be determined. |
| 172 |
*/ |
| 173 |
try { |
| 174 |
$reader = \TablePress\PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile( $file['location'] ); |
| 175 |
} catch ( \TablePress\PhpOffice\PhpSpreadsheet\Reader\Exception $exception ) { |
| 176 |
$reader = \TablePress\PhpOffice\PhpSpreadsheet\IOFactory::createReader( 'Csv' ); |
| 177 |
// Append .csv to the file name, so that \TablePress\PhpOffice\PhpSpreadsheet\Reader\Csv::canRead() returns true. |
| 178 |
$new_location = $file['location'] . '.csv'; |
| 179 |
if ( rename( $file['location'], $new_location ) ) { |
| 180 |
$file['location'] = $new_location; |
| 181 |
} |
| 182 |
} |
| 183 |
|
| 184 |
$class_name = get_class( $reader ); |
| 185 |
$class_type = explode( '\\', $class_name ); |
| 186 |
$detected_format = strtolower( array_pop( $class_type ) ); |
| 187 |
|
| 188 |
if ( 'csv' === $detected_format ) { |
| 189 |
$reader->setInputEncoding( \TablePress\PhpOffice\PhpSpreadsheet\Reader\Csv::GUESS_ENCODING ); |
| 190 |
$reader->setEscapeCharacter( ( PHP_VERSION_ID < 70400 ) ? "\x0" : '' ); // Disable the proprietary escape mechanism of PHP's fgetcsv() in PHP >= 7.4. |
| 191 |
} |
| 192 |
|
| 193 |
$reader->setIncludeCharts( false ); |
| 194 |
$reader->setReadEmptyCells( true ); |
| 195 |
|
| 196 |
// For non-Excel files, import only the data, but ignore formatting. |
| 197 |
if ( ! in_array( $detected_format, array( 'xlsx', 'xls' ), true ) ) { |
| 198 |
$reader->setReadDataOnly( true ); |
| 199 |
} |
| 200 |
|
| 201 |
// For formats where it's supported, import only the first sheet. |
| 202 |
if ( in_array( $detected_format, array( 'csv', 'html', 'slk' ), true ) ) { |
| 203 |
$reader->setSheetIndex( 0 ); |
| 204 |
} |
| 205 |
|
| 206 |
$spreadsheet = $reader->load( $file['location'] ); |
| 207 |
$worksheet = $spreadsheet->getActiveSheet(); |
| 208 |
$cell_collection = $worksheet->getCellCollection(); |
| 209 |
$comments = $worksheet->getComments(); |
| 210 |
|
| 211 |
$table = array( |
| 212 |
'data' => array(), |
| 213 |
); |
| 214 |
|
| 215 |
$min_col = 'A'; |
| 216 |
$min_row = 1; |
| 217 |
$max_col = $worksheet->getHighestColumn(); |
| 218 |
$max_row = $worksheet->getHighestRow(); |
| 219 |
|
| 220 |
// Adapted from \TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::rangeToArray(). |
| 221 |
++$max_col; // Due to for-loop with characters for columns. |
| 222 |
for ( $row = $min_row; $row <= $max_row; $row++ ) { |
| 223 |
$row_data = array(); |
| 224 |
for ( $col = $min_col; $col !== $max_col; $col++ ) { |
| 225 |
$cell_reference = $col . $row; |
| 226 |
if ( ! $cell_collection->has( $cell_reference ) ) { |
| 227 |
$row_data[] = ''; |
| 228 |
continue; |
| 229 |
} |
| 230 |
|
| 231 |
$cell = $cell_collection->get( $cell_reference ); |
| 232 |
$value = $cell->getValue(); |
| 233 |
if ( is_null( $value ) ) { |
| 234 |
$row_data[] = ''; |
| 235 |
continue; |
| 236 |
} |
| 237 |
|
| 238 |
if ( $value instanceof \TablePress\PhpOffice\PhpSpreadsheet\RichText\RichText ) { |
| 239 |
$cell_data = $this->parse_rich_text( $value ); |
| 240 |
} else { |
| 241 |
$cell_data = (string) $value; |
| 242 |
} |
| 243 |
|
| 244 |
// Apply data type formatting. |
| 245 |
$style = $spreadsheet->getCellXfByIndex( $cell->getXfIndex() ); |
| 246 |
$cell_data = \TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat::toFormattedString( |
| 247 |
$cell_data, |
| 248 |
$style->getNumberFormat() ? $style->getNumberFormat()->getFormatCode() : \TablePress\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_GENERAL, |
| 249 |
array( $this, 'format_color' ) |
| 250 |
); |
| 251 |
|
| 252 |
if ( strlen( $cell_data ) > 1 && '=' === $cell_data[0] ) { |
| 253 |
if ( $style->getQuotePrefix() ) { |
| 254 |
// Prepend a ' to quoted/escaped formulas (so that they are shown as text). This is currently not supported (at least) for the XLS format. |
| 255 |
$cell_data = "'{$cell_data}"; |
| 256 |
} else { |
| 257 |
// Bail early, to not add inline HTML styling around formulas, as they won't work anymore then. |
| 258 |
$row_data[] = $cell_data; |
| 259 |
continue; |
| 260 |
} |
| 261 |
} |
| 262 |
|
| 263 |
$cell_has_hyperlink = $worksheet->hyperlinkExists( $cell_reference ) && ! $worksheet->getHyperlink( $cell_reference )->isInternal(); |
| 264 |
|
| 265 |
$font = $style->getFont(); |
| 266 |
|
| 267 |
if ( $font->getSuperscript() ) { |
| 268 |
$cell_data = "<sup>{$cell_data}</sup>"; |
| 269 |
} |
| 270 |
if ( $font->getSubscript() ) { |
| 271 |
$cell_data = "<sub>{$cell_data}</sub>"; |
| 272 |
} |
| 273 |
if ( $font->getStrikethrough() ) { |
| 274 |
$cell_data = "<del>{$cell_data}</del>"; |
| 275 |
} |
| 276 |
if ( $font->getUnderline() !== \TablePress\PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_NONE && ! $cell_has_hyperlink ) { |
| 277 |
$cell_data = "<u>{$cell_data}</u>"; |
| 278 |
} |
| 279 |
if ( $font->getBold() ) { |
| 280 |
$cell_data = "<strong>{$cell_data}</strong>"; |
| 281 |
} |
| 282 |
if ( $font->getItalic() ) { |
| 283 |
$cell_data = "<em>{$cell_data}</em>"; |
| 284 |
} |
| 285 |
$color = $font->getColor()->getRGB(); |
| 286 |
if ( '' !== $color && '000000' !== $color && ! $cell_has_hyperlink ) { |
| 287 |
// Don't add the span if the color is black, as that's the default, or if it's in a hyperlink. |
| 288 |
$color_css = esc_attr( "color:#{$color};" ); |
| 289 |
$cell_data = "<span style=\"{$color_css}\">{$cell_data}</span>"; |
| 290 |
} |
| 291 |
|
| 292 |
// Convert Hyperlinks to HTML code. |
| 293 |
if ( $cell_has_hyperlink ) { |
| 294 |
$url = $worksheet->getHyperlink( $cell_reference )->getUrl(); |
| 295 |
if ( '' !== $url ) { |
| 296 |
$title = $worksheet->getHyperlink( $cell_reference )->getTooltip(); |
| 297 |
if ( '' !== $title ) { |
| 298 |
$title = ' title="' . esc_attr( $title ) . '"'; |
| 299 |
} |
| 300 |
$url = esc_url( $url ); |
| 301 |
$cell_data = "<a href=\"{$url}\"{$title}>{$cell_data}</a>"; |
| 302 |
} |
| 303 |
} |
| 304 |
|
| 305 |
// Add comments. |
| 306 |
if ( isset( $comments[ $cell_reference ] ) ) { |
| 307 |
$sanitized_comment = esc_html( $worksheet->getComment( $cell_reference )->getText()->getPlainText() ); |
| 308 |
if ( '' !== $sanitized_comment ) { |
| 309 |
$cell_data .= '<div class="comment">' . $sanitized_comment . '</div>'; |
| 310 |
} |
| 311 |
} |
| 312 |
|
| 313 |
$row_data[] = $cell_data; |
| 314 |
} |
| 315 |
$table['data'][] = $row_data; |
| 316 |
} |
| 317 |
|
| 318 |
// Convert merged cells to trigger words. |
| 319 |
$merged_cells = $worksheet->getMergeCells(); |
| 320 |
foreach ( $merged_cells as $merged_cells_range ) { |
| 321 |
$cells = explode( ':', $merged_cells_range ); |
| 322 |
$first_cell = \TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate::indexesFromString( $cells[0] ); |
| 323 |
$last_cell = \TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate::indexesFromString( $cells[1] ); |
| 324 |
for ( $row_idx = $first_cell[1]; $row_idx <= $last_cell[1]; $row_idx++ ) { |
| 325 |
for ( $column_idx = $first_cell[0]; $column_idx <= $last_cell[0]; $column_idx++ ) { |
| 326 |
if ( $row_idx === $first_cell[1] && $column_idx === $first_cell[0] ) { |
| 327 |
continue; // Keep value of first cell. |
| 328 |
} elseif ( $row_idx === $first_cell[1] && $column_idx > $first_cell[0] ) { |
| 329 |
$table['data'][ $row_idx - 1 ][ $column_idx - 1 ] = '#colspan#'; |
| 330 |
} elseif ( $row_idx > $first_cell[1] && $column_idx === $first_cell[0] ) { |
| 331 |
$table['data'][ $row_idx - 1 ][ $column_idx - 1 ] = '#rowspan#'; |
| 332 |
} else { |
| 333 |
$table['data'][ $row_idx - 1 ][ $column_idx - 1 ] = '#span#'; |
| 334 |
} |
| 335 |
} |
| 336 |
} |
| 337 |
} |
| 338 |
|
| 339 |
// Save PHP memory. |
| 340 |
$spreadsheet->disconnectWorksheets(); |
| 341 |
unset( $comments, $cell_collection, $worksheet, $spreadsheet ); |
| 342 |
|
| 343 |
return $table; |
| 344 |
} catch ( \TablePress\PhpOffice\PhpSpreadsheet\Reader\Exception $exception ) { |
| 345 |
return new WP_Error( 'table_import_phpspreadsheet_failed', '', 'Exception: ' . $exception->getMessage() ); |
| 346 |
} |
| 347 |
} |
| 348 |
|
| 349 |
/** |
| 350 |
* Parses PHPSpreadsheet RichText elements and converts formatting to HTML tags. |
| 351 |
* |
| 352 |
* @param RichText $value RichText element. |
| 353 |
* @return string Cell value with HTML formatting. |
| 354 |
*/ |
| 355 |
protected function parse_rich_text( $value ) { |
| 356 |
$cell_data = ''; |
| 357 |
$elements = $value->getRichTextElements(); |
| 358 |
foreach ( $elements as $element ) { |
| 359 |
$element_data = $element->getText(); |
| 360 |
|
| 361 |
// Rich text start? |
| 362 |
if ( $element instanceof \TablePress\PhpOffice\PhpSpreadsheet\RichText\Run ) { |
| 363 |
$font = $element->getFont(); |
| 364 |
|
| 365 |
if ( $font->getSuperscript() ) { |
| 366 |
$element_data = "<sup>{$element_data}</sup>"; |
| 367 |
} |
| 368 |
if ( $font->getSubscript() ) { |
| 369 |
$element_data = "<sub>{$element_data}</sub>"; |
| 370 |
} |
| 371 |
if ( $font->getStrikethrough() ) { |
| 372 |
$element_data = "<del>{$element_data}</del>"; |
| 373 |
} |
| 374 |
if ( $font->getUnderline() !== \TablePress\PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_NONE ) { |
| 375 |
$element_data = "<u>{$element_data}</u>"; |
| 376 |
} |
| 377 |
if ( $font->getBold() ) { |
| 378 |
$element_data = "<strong>{$element_data}</strong>"; |
| 379 |
} |
| 380 |
if ( $font->getItalic() ) { |
| 381 |
$element_data = "<em>{$element_data}</em>"; |
| 382 |
} |
| 383 |
$color = $font->getColor()->getRGB(); |
| 384 |
if ( '' !== $color ) { |
| 385 |
$color_css = esc_attr( "color:#{$color};" ); |
| 386 |
$element_data = "<span style=\"{$color_css}\">{$element_data}</span>"; |
| 387 |
} |
| 388 |
} |
| 389 |
|
| 390 |
$cell_data .= $element_data; |
| 391 |
} |
| 392 |
return $cell_data; |
| 393 |
} |
| 394 |
|
| 395 |
/** |
| 396 |
* Adds color to formatted string as inline style, e.g. from conditional formatting. |
| 397 |
* |
| 398 |
* @param string $value Plain formatted value without color. |
| 399 |
* @param string $format_code Format code. |
| 400 |
* @return string Value with color format applied. |
| 401 |
*/ |
| 402 |
public function format_color( $value, $format_code ) { |
| 403 |
// Color information, e.g. [Red] is always at the beginning of the format code. |
| 404 |
$color = ''; |
| 405 |
if ( 1 === preg_match( '/^\\[[a-zA-Z]+\\]/', $format_code, $matches ) ) { |
| 406 |
$color = str_replace( array( '[', ']' ), '', $matches[0] ); |
| 407 |
$color = strtolower( $color ); |
| 408 |
} |
| 409 |
|
| 410 |
if ( '' !== $color ) { |
| 411 |
$color = esc_attr( "color:{$color};" ); |
| 412 |
$value = "<span style=\"{$color}\">{$value}</span>"; |
| 413 |
} |
| 414 |
|
| 415 |
return $value; |
| 416 |
} |
| 417 |
|
| 418 |
} // class TablePress_Import_PHPSpreadsheet |
| 419 |
|
