tablepress / classes / class-evaluate-phpspreadsheet.php

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

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