XmlImportXLSParse.php — WP All Import – Drag & Drop Import for CSV, XML, Excel & Google Sheets 4.1.1

wp-all-import / libraries / XmlImportXLSParse.php

wp-all-import / libraries Last commit date

XmlImportXLSParse.php

438 lines

1	<?php
2	// phpcs:disable WordPress.WP.AlternativeFunctions, WordPress.PHP.DevelopmentFunctions, WordPress.NamingConventions.PrefixAllGlobals
3
4	use PhpOffice\PhpSpreadsheet\IOFactory;
5	use PhpOffice\PhpSpreadsheet\Reader\IReader;
6
7	class PMXI_XLSParser{
8
9	public $csv_path;
10
11	public $_filename;
12
13	public $targetDir;
14
15	public $xml;
16
17	public function __construct($path, $targetDir = false){
18
19	$this->_filename = $path;
20
21	$wp_uploads = wp_upload_dir();
22
23	$this->targetDir = ( ! $targetDir ) ? wp_all_import_secure_file($wp_uploads['basedir'] . DIRECTORY_SEPARATOR . PMXI_Plugin::UPLOADS_DIRECTORY ) : $targetDir;
24	}
25
26	public function parse(){
27
28	$tmpname = wp_unique_filename($this->targetDir, preg_replace('%\W(xls\|xlsx)$%i', ".csv", basename($this->_filename)));
29
30	$this->csv_path = $this->targetDir . '/' . wp_all_import_url_title($tmpname);
31
32	return $this->toXML();
33	}
34
35	protected function toXML(){
36
37	// Check if alternative Excel processing is enabled for this import
38	// Use the same import ID detection logic as PMXI_Upload class for cron/CLI compatibility
39	$import_id = wp_all_import_get_import_id();
40	$use_alternative = false;
41
42	if ($import_id && $import_id !== 'new' && is_numeric($import_id)) {
43	$import = new PMXI_Import_Record();
44	$import->getById(intval($import_id));
45	if (!$import->isEmpty()) {
46	$use_alternative = !empty($import->options['use_alternative_excel_processing']);
47	}
48	}
49
50	// For new imports, check session for the setting
51	if (!$use_alternative && !empty(PMXI_Plugin::$session)) {
52	$use_alternative = PMXI_Plugin::$session->get('use_alternative_excel_processing');
53	}
54
55	// Check for global flag (used during retry attempts)
56	global $wp_all_import_force_alternative_excel;
57	if (!empty($wp_all_import_force_alternative_excel)) {
58	$use_alternative = true;
59	}
60
61	// Allow filter to override
62	$use_alternative = apply_filters('wp_all_import_use_alternative_excel_processing', $use_alternative, $this->_filename);
63
64	try {
65	if ($use_alternative) {
66	$objSpreadsheet = $this->load_excel_alternative_method($this->_filename);
67	} else {
68	// Use standard PhpSpreadsheet loading
69	$objSpreadsheet = IOFactory::load($this->_filename);
70	}
71	} catch (Exception $e) {
72	// If standard method failed and we haven't tried alternative yet, try it automatically
73	if (!$use_alternative) {
74	try {
75	$objSpreadsheet = $this->load_excel_alternative_method($this->_filename);
76
77	// If alternative method succeeded, enable it for this import
78	if ($import_id) {
79	$import = new PMXI_Import_Record();
80	$import->getById($import_id);
81	if (!$import->isEmpty()) {
82	$options = $import->options;
83	$options['use_alternative_excel_processing'] = 1;
84	$import->set(array('options' => $options))->save();
85	}
86	}
87	} catch (Exception $alternative_error) {
88	// Both methods failed, throw the original error
89	throw $e;
90	}
91	} else {
92	// Alternative method was already being used and failed
93	throw $e;
94	}
95	}
96
97	// Check if alternative method already created CSV (objSpreadsheet will be null)
98	if ($objSpreadsheet === null) {
99	// CSV already created by alternative method
100	} else {
101	// Allow filters to modify the Spreadsheet object
102	$objSpreadsheet = apply_filters('wp_all_import_phpexcel_object', $objSpreadsheet, $this->_filename);
103
104	// Set the CSV delimiter; allow filters to modify it
105	$spreadsheetDelimiter = ",";
106	$spreadsheetDelimiter = apply_filters('wp_all_import_phpexcel_delimiter', $spreadsheetDelimiter, $this->_filename);
107
108	// Create a CSV writer and set the settings
109	$objWriter = IOFactory::createWriter($objSpreadsheet, 'Csv');
110	$objWriter->setDelimiter($spreadsheetDelimiter)
111	->setEnclosure('"')
112	->setLineEnding("\r\n")
113	->setSheetIndex(0)
114	->save($this->csv_path);
115	}
116
117	include_once(PMXI_Plugin::ROOT_DIR . '/libraries/XmlImportCsvParse.php');
118
119	$this->xml = new PMXI_CsvParser( array( 'filename' => $this->csv_path, 'targetDir' => $this->targetDir ) );
120
121	@unlink($this->csv_path);
122
123	return $this->xml->xml_path;
124
125	}
126
127
128
129
130
131	/**
132	* Alternative method to load problematic Excel files
133	* Uses a more basic approach that avoids PhpSpreadsheet's memory issues
134	*
135	* @param string $filename Path to Excel file
136	* @return \PhpOffice\PhpSpreadsheet\Spreadsheet
137	*/
138	protected function load_excel_alternative_method($filename) {
139
140	// Check if required extensions are available for manual extraction
141	if (!class_exists('ZipArchive') \|\| !extension_loaded('simplexml')) {
142	// Fall back to basic PhpSpreadsheet with optimization
143	return $this->load_excel_fallback_without_zip($filename);
144	}
145
146	// Try to extract and process the Excel file manually
147	$temp_dir = sys_get_temp_dir() . '/wp_all_import_excel_' . uniqid();
148
149	try {
150	// Create temporary directory
151	if (!mkdir($temp_dir, 0755, true)) {
152	throw new Exception(esc_html("Could not create temporary directory"));
153	}
154
155	// Extract Excel file (it's a ZIP archive)
156	$zip = new ZipArchive();
157	if ($zip->open($filename) !== TRUE) {
158	throw new Exception(esc_html("Could not open Excel file as ZIP"));
159	}
160
161	$zip->extractTo($temp_dir);
162	$zip->close();
163
164	// Read the worksheet data directly from XML
165	$worksheet_file = $temp_dir . '/xl/worksheets/sheet1.xml';
166	$shared_strings_file = $temp_dir . '/xl/sharedStrings.xml';
167
168	if (!file_exists($worksheet_file)) {
169	throw new Exception(esc_html("Could not find worksheet data"));
170	}
171
172	// Parse shared strings
173	$shared_strings = array();
174	if (file_exists($shared_strings_file)) {
175	$shared_strings = $this->parse_shared_strings($shared_strings_file);
176	}
177
178	// Parse worksheet and create a simple CSV
179	$csv_data = $this->parse_worksheet_to_csv($worksheet_file, $shared_strings);
180
181	// Write CSV data to temporary file
182	$temp_csv = $temp_dir . '/converted.csv';
183	file_put_contents($temp_csv, $csv_data);
184
185	// Copy to our target CSV path
186	copy($temp_csv, $this->csv_path);
187
188	// Clean up temporary directory
189	$this->recursive_rmdir($temp_dir);
190
191	// Return a dummy spreadsheet object since we've already created the CSV
192	// We'll bypass the normal CSV conversion process
193	return null; // Signal that CSV is already created
194
195	} catch (Exception $e) {
196	// Clean up on error
197	if (is_dir($temp_dir)) {
198	$this->recursive_rmdir($temp_dir);
199	}
200	throw $e;
201	}
202	}
203
204	/**
205	* Fallback method when ZipArchive is not available
206	* Uses PhpSpreadsheet with basic optimization
207	*
208	* @param string $filename Path to Excel file
209	* @return \PhpOffice\PhpSpreadsheet\Spreadsheet
210	*/
211	protected function load_excel_fallback_without_zip($filename) {
212
213	try {
214	// Use PhpSpreadsheet with basic memory optimization
215	$reader = IOFactory::createReaderForFile($filename);
216
217	// Apply basic optimizations if methods exist
218	if (method_exists($reader, 'setReadDataOnly')) {
219	$reader->setReadDataOnly(true);
220	}
221
222	if (method_exists($reader, 'setReadEmptyCells')) {
223	$reader->setReadEmptyCells(false);
224	}
225
226	if (method_exists($reader, 'setIncludeCharts')) {
227	$reader->setIncludeCharts(false);
228	}
229
230	// Try to load only the first sheet
231	if (method_exists($reader, 'setLoadSheetsOnly')) {
232	$reader->setLoadSheetsOnly(['Sheet1', 'Sheet 1', 'Worksheet', 'Data', 'Sheet']);
233	}
234
235	return $reader->load($filename);
236
237	} catch (Exception $e) {
238	// If all else fails, use basic IOFactory load
239	error_log("WP All Import Excel: Fallback method failed, using basic load - " . $e->getMessage());
240	return IOFactory::load($filename);
241	}
242	}
243
244	/**
245	* Parse shared strings XML file
246	*
247	* @param string $file Path to sharedStrings.xml
248	* @return array
249	*/
250	protected function parse_shared_strings($file) {
251
252	$shared_strings = array();
253
254	try {
255	$xml = simplexml_load_file($file);
256	if ($xml !== false) {
257	$index = 0;
258	foreach ($xml->si as $si) {
259	$shared_strings[$index] = (string)$si->t;
260	$index++;
261	}
262	}
263	} catch (Exception $e) {
264	error_log("WP All Import Excel: Error parsing shared strings - " . $e->getMessage());
265	}
266
267	return $shared_strings;
268	}
269
270	/**
271	* Parse worksheet XML and convert to CSV
272	*
273	* @param string $file Path to worksheet XML
274	* @param array $shared_strings Shared strings array
275	* @return string CSV data
276	*/
277	protected function parse_worksheet_to_csv($file, $shared_strings) {
278
279	$csv_data = '';
280	$batch_size = 1000; // Process 1000 rows at a time
281	$row_count = 0;
282	$csv_rows = array();
283
284	try {
285	$xml = simplexml_load_file($file);
286	if ($xml !== false) {
287
288	// Process each row
289	foreach ($xml->sheetData->row as $row) {
290	$row_data = array();
291	$row_num = (int)$row['r'];
292
293	// Process each cell in the row
294	foreach ($row->c as $cell) {
295	$cell_ref = (string)$cell['r'];
296	$cell_type = (string)$cell['t'];
297
298	// Get cell value
299	$value = '';
300	if (isset($cell->v)) {
301	$cell_value = (string)$cell->v;
302
303	// If it's a shared string, look it up
304	if ($cell_type === 's' && isset($shared_strings[$cell_value])) {
305	$value = $shared_strings[$cell_value];
306	} else {
307	$value = $cell_value;
308	}
309	}
310
311	// Extract column from cell reference (e.g., 'A1' -> 'A')
312	preg_match('/([A-Z]+)/', $cell_ref, $matches);
313	$col = $matches[1];
314	$col_index = $this->column_letter_to_index($col);
315
316	// Ensure row_data array is large enough
317	while (count($row_data) <= $col_index) {
318	$row_data[] = '';
319	}
320
321	$row_data[$col_index] = $value;
322	}
323
324	// Add row to current batch
325	$csv_rows[$row_num] = $row_data;
326	$row_count++;
327
328	// Process batch if we've reached the limit
329	if ($row_count % $batch_size === 0) {
330	// Convert current batch to CSV and append to total
331	$batch_csv = $this->convert_rows_to_csv($csv_rows);
332	$csv_data .= $batch_csv;
333
334	// Clear the batch array to free memory
335	$csv_rows = array();
336
337	// Force garbage collection to keep memory usage low
338	if (function_exists('gc_collect_cycles')) {
339	gc_collect_cycles();
340	}
341	}
342	}
343
344	// Process any remaining rows in the final batch
345	if (!empty($csv_rows)) {
346	$final_batch_csv = $this->convert_rows_to_csv($csv_rows);
347	$csv_data .= $final_batch_csv;
348	}
349	}
350	} catch (Exception $e) {
351	error_log("WP All Import Excel: Error parsing worksheet - " . $e->getMessage());
352	}
353
354	return $csv_data;
355	}
356
357	/**
358	* Convert rows array to CSV format
359	*
360	* @param array $csv_rows Array of rows to convert
361	* @return string CSV data
362	*/
363	protected function convert_rows_to_csv($csv_rows) {
364
365	$csv_data = '';
366	ksort($csv_rows); // Sort by row number
367
368	foreach ($csv_rows as $row_data) {
369	$escaped_row = array_map(array($this, 'escape_csv_value'), $row_data);
370	$csv_data .= implode(',', $escaped_row) . "\r\n";
371	}
372
373	return $csv_data;
374	}
375
376	/**
377	* Escape CSV value properly
378	*
379	* @param string $value Value to escape
380	* @return string Escaped value
381	*/
382	protected function escape_csv_value($value) {
383
384	// Convert to string and handle null values
385	$value = (string)$value;
386
387	// If value contains comma, quote, or newline, wrap in quotes and escape quotes
388	if (strpos($value, ',') !== false \|\| strpos($value, '"') !== false \|\|
389	strpos($value, "\n") !== false \|\| strpos($value, "\r") !== false) {
390	return '"' . str_replace('"', '""', $value) . '"';
391	}
392
393	return $value;
394	}
395
396	/**
397	* Convert column letter to index (A=0, B=1, etc.)
398	*
399	* @param string $column Column letter(s)
400	* @return int
401	*/
402	protected function column_letter_to_index($column) {
403
404	$index = 0;
405	$length = strlen($column);
406
407	for ($i = 0; $i < $length; $i++) {
408	$index = $index * 26 + (ord($column[$i]) - ord('A') + 1);
409	}
410
411	return $index - 1; // Convert to 0-based index
412	}
413
414	/**
415	* Recursively remove directory
416	*
417	* @param string $dir Directory path
418	*/
419	protected function recursive_rmdir($dir) {
420
421	if (is_dir($dir)) {
422	$objects = scandir($dir);
423	foreach ($objects as $object) {
424	if ($object != "." && $object != "..") {
425	if (is_dir($dir . "/" . $object)) {
426	$this->recursive_rmdir($dir . "/" . $object);
427	} else {
428	unlink($dir . "/" . $object);
429	}
430	}
431	}
432	rmdir($dir);
433	}
434	}
435
436
437	}
438