tablepress / classes / class-evaluate-phpspreadsheet.php

class-evaluate-phpspreadsheet.php in TablePress – Tables in WordPress made easy 3.4, at classes/class-evaluate-phpspreadsheet.php

141 lines 6.0 KB

Raw Download Zip

1	<?php
2	/**
3	* TablePress Formula Evaluation PHPSpreadsheet Class.
4	*
5	* @package TablePress
6	* @subpackage Formulas
7	* @author Tobias Bäthge
8	* @since 2.0.0
9	*/
10
11	declare(strict_types=1);
12
13	// Prohibit direct script loading.
14	defined( 'ABSPATH' ) \|\| die( 'No direct script access allowed!' );
15
16	/**
17	* TablePress Formula Evaluation PHPSpreadsheet Class
18	*
19	* @package TablePress
20	* @subpackage Formulas
21	* @author Tobias Bäthge
22	* @since 2.0.0
23	*/
24	class TablePress_Evaluate_PHPSpreadsheet {
25
26	/**
27	* Initializes the Formula Evaluation PHPSpreadsheet class.
28	*
29	* @since 2.0.0
30	*/
31	public function __construct() {
32	// Load PHPSpreadsheet via its autoloading mechanism.
33	TablePress::load_file( 'autoload.php', 'libraries/vendor' );
34	}
35
36	/**
37	* Evaluates formulas in the passed table.
38	*
39	* @since 1.0.0
40	*
41	* @param array<int, array<int, string>> $table_data Table data in which formulas shall be evaluated.
42	* @param string $table_id ID of the passed table.
43	* @return array<int, array<int, string>> Table data with evaluated formulas.
44	*/
45	public function evaluate_table_data( array $table_data, string $table_id ): array {
46	$table_has_formulas = false;
47
48	// Loop through all cells to check for formulas and convert notations.
49	foreach ( $table_data as &$row ) {
50	foreach ( $row as &$cell_content ) {
51	if ( '' === $cell_content \|\| '=' === $cell_content \|\| '=' !== $cell_content[0] ) {
52	continue;
53	}
54
55	$table_has_formulas = true;
56
57	// Convert legacy "formulas in text" notation (`=Text {A3+B3} Text`) to standard Excel notation (`="Text "&A3+B3&" Text"`).
58	if ( 1 === preg_match( '#{(.+?)}#', $cell_content ) ) {
59	$cell_content = str_replace( '"', '""', $cell_content ); // Preserve existing quotation marks in text around formulas.
60	$cell_content = '="' . substr( $cell_content, 1 ) . '"'; // Wrap the whole cell content in quotation marks, as there will be text around formulas.
61	$cell_content = (string) preg_replace( '#{(.+?)}#', '"&$1&"', $cell_content, -1, $count ); // Convert all wrapped formulas to standard Excel notation.
62	}
63	}
64	}
65	unset( $row, $cell_content ); // Unset use-by-reference parameters of foreach loops.
66
67	// No need to use the PHPSpreadsheet Calculation engine if the table does not contain formulas.
68	if ( ! $table_has_formulas ) {
69	return $table_data;
70	}
71
72	try {
73	$spreadsheet = new \TablePress\PhpOffice\PhpSpreadsheet\Spreadsheet();
74	$worksheet = $spreadsheet->setActiveSheetIndex( 0 );
75	$worksheet->fromArray( /* $source / $table_data, / $nullValue */ '' );
76
77	// Don't allow cyclic references.
78	\TablePress\PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance( $spreadsheet )->cyclicFormulaCount = 0;
79
80	/*
81	* Register variables as Named Formulas.
82	* The variables `ROW`, `COLUMN`, `CELL`, `PI`, and `E` should be considered deprecated and only their formulas should be used.
83	*/
84	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'TABLE_ID', $worksheet, $table_id ) );
85	$num_rows = (string) count( $table_data );
86	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'NUM_ROWS', $worksheet, $num_rows ) );
87	$num_columns = (string) count( $table_data[0] );
88	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'NUM_COLUMNS', $worksheet, $num_columns ) );
89	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'ROW', $worksheet, '=ROW()' ) );
90	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'COLUMN', $worksheet, '=COLUMN()' ) );
91	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'CELL', $worksheet, '=ADDRESS(ROW(),COLUMN(),4)' ) );
92	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'PI', $worksheet, '=PI()' ) );
93	$spreadsheet->addNamedFormula( new \TablePress\PhpOffice\PhpSpreadsheet\NamedFormula( 'E', $worksheet, '=EXP(1)' ) );
94
95	// Loop through all table cells and replace formulas with evaluated values.
96	$cell_collection = $worksheet->getCellCollection();
97	foreach ( $table_data as $row_idx => &$row ) {
98	foreach ( $row as $column_idx => &$cell_content ) {
99	if ( strlen( $cell_content ) > 1 && '=' === $cell_content[0] ) {
100	// Adapted from \TablePress\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::rangeToArray().
101	$cell_reference = \TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex( $column_idx + 1 ) . ( $row_idx + 1 );
102	if ( $cell_collection->has( $cell_reference ) ) {
103	$cell = $cell_collection->get( $cell_reference );
104	try {
105	$cell_content = (string) $cell->getCalculatedValue();
106
107	// Convert hyperlinks, e.g. generated via `=HYPERLINK()` to HTML code.
108	$cell_has_hyperlink = $worksheet->hyperlinkExists( $cell_reference ) && ! $worksheet->getHyperlink( $cell_reference )->isInternal();
109	if ( $cell_has_hyperlink ) {
110	$url = $worksheet->getHyperlink( $cell_reference )->getUrl();
111	if ( '' !== $url ) {
112	$url = esc_url( $url );
113	$cell_content = "<a href=\"{$url}\">{$cell_content}</a>";
114	}
115	}
116
117	// Sanitize the output of the evaluated formula.
118	$cell_content = wp_kses_post( $cell_content ); // Equals wp_filter_post_kses(), but without the unnecessary slashes handling.
119	} catch ( \Throwable $exception ) {
120	$message = str_replace( 'Worksheet!', '', $exception->getMessage() );
121	$cell_content = "!ERROR! {$message}";
122	}
123	}
124	}
125	}
126	}
127	unset( $row, $cell_content ); // Unset use-by-reference parameters of foreach loops.
128
129	// Save PHP memory.
130	$spreadsheet->disconnectWorksheets();
131	unset( $cell_collection, $worksheet, $spreadsheet );
132	} catch ( \Throwable $exception ) {
133	$message = str_replace( 'Worksheet!', '', $exception->getMessage() );
134	$table_data = array( array( "!ERROR! {$message}" ) );
135	}
136
137	return $table_data;
138	}
139
140	} // class TablePress_Evaluate_PHPSpreadsheet
141