Context Navigation

← Previous Revision
Latest Revision
Next Revision →
Blame
Revision Log

OOCalc.php @ 1

Last change on this file since 1 was 1, checked in by dungnv, 11 years ago

File size: 25.7 KB

Line
1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 PHPExcel
6	*
7	* This library is free software; you can redistribute it and/or
8	* modify it under the terms of the GNU Lesser General Public
9	* License as published by the Free Software Foundation; either
10	* version 2.1 of the License, or (at your option) any later version.
11	*
12	* This library is distributed in the hope that it will be useful,
13	* but WITHOUT ANY WARRANTY; without even the implied warranty of
14	* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15	* Lesser General Public License for more details.
16	*
17	* You should have received a copy of the GNU Lesser General Public
18	* License along with this library; if not, write to the Free Software
19	* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
20	*
21	* @category PHPExcel
22	* @package PHPExcel_Reader
23	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version 1.8.0, 2014-03-02
26	*/
27
28
29	/** PHPExcel root directory */
30	if (!defined('PHPEXCEL_ROOT')) {
31	/**
32	* @ignore
33	*/
34	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36	}
37
38	/**
39	* PHPExcel_Reader_OOCalc
40	*
41	* @category PHPExcel
42	* @package PHPExcel_Reader
43	* @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
44	*/
45	class PHPExcel_Reader_OOCalc extends PHPExcel_Reader_Abstract implements PHPExcel_Reader_IReader
46	{
47	/**
48	* Formats
49	*
50	* @var array
51	*/
52	private $_styles = array();
53
54
55	/**
56	* Create a new PHPExcel_Reader_OOCalc
57	*/
58	public function __construct() {
59	$this->_readFilter = new PHPExcel_Reader_DefaultReadFilter();
60	}
61
62
63	/**
64	* Can the current PHPExcel_Reader_IReader read the file?
65	*
66	* @param string $pFilename
67	* @return boolean
68	* @throws PHPExcel_Reader_Exception
69	*/
70	public function canRead($pFilename)
71	{
72	// Check if file exists
73	if (!file_exists($pFilename)) {
74	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
75	}
76
77	$zipClass = PHPExcel_Settings::getZipClass();
78
79	// Check if zip class exists
80	// if (!class_exists($zipClass, FALSE)) {
81	// throw new PHPExcel_Reader_Exception($zipClass . " library is not enabled");
82	// }
83
84	$mimeType = 'UNKNOWN';
85	// Load file
86	$zip = new $zipClass;
87	if ($zip->open($pFilename) === true) {
88	// check if it is an OOXML archive
89	$stat = $zip->statName('mimetype');
90	if ($stat && ($stat['size'] <= 255)) {
91	$mimeType = $zip->getFromName($stat['name']);
92	} elseif($stat = $zip->statName('META-INF/manifest.xml')) {
93	$xml = simplexml_load_string($zip->getFromName('META-INF/manifest.xml'), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
94	$namespacesContent = $xml->getNamespaces(true);
95	if (isset($namespacesContent['manifest'])) {
96	$manifest = $xml->children($namespacesContent['manifest']);
97	foreach($manifest as $manifestDataSet) {
98	$manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']);
99	if ($manifestAttributes->{'full-path'} == '/') {
100	$mimeType = (string) $manifestAttributes->{'media-type'};
101	break;
102	}
103	}
104	}
105	}
106
107	$zip->close();
108
109	return ($mimeType === 'application/vnd.oasis.opendocument.spreadsheet');
110	}
111
112	return FALSE;
113	}
114
115
116	/**
117	* Reads names of the worksheets from a file, without parsing the whole file to a PHPExcel object
118	*
119	* @param string $pFilename
120	* @throws PHPExcel_Reader_Exception
121	*/
122	public function listWorksheetNames($pFilename)
123	{
124	// Check if file exists
125	if (!file_exists($pFilename)) {
126	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
127	}
128
129	$zipClass = PHPExcel_Settings::getZipClass();
130
131	$zip = new $zipClass;
132	if (!$zip->open($pFilename)) {
133	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
134	}
135
136	$worksheetNames = array();
137
138	$xml = new XMLReader();
139	$res = $xml->open('zip://'.realpath($pFilename).'#content.xml', null, PHPExcel_Settings::getLibXmlLoaderOptions());
140	$xml->setParserProperty(2,true);
141
142	// Step into the first level of content of the XML
143	$xml->read();
144	while ($xml->read()) {
145	// Quickly jump through to the office:body node
146	while ($xml->name !== 'office:body') {
147	if ($xml->isEmptyElement)
148	$xml->read();
149	else
150	$xml->next();
151	}
152	// Now read each node until we find our first table:table node
153	while ($xml->read()) {
154	if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
155	// Loop through each table:table node reading the table:name attribute for each worksheet name
156	do {
157	$worksheetNames[] = $xml->getAttribute('table:name');
158	$xml->next();
159	} while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT);
160	}
161	}
162	}
163
164	return $worksheetNames;
165	}
166
167
168	/**
169	* Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns)
170	*
171	* @param string $pFilename
172	* @throws PHPExcel_Reader_Exception
173	*/
174	public function listWorksheetInfo($pFilename)
175	{
176	// Check if file exists
177	if (!file_exists($pFilename)) {
178	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
179	}
180
181	$worksheetInfo = array();
182
183	$zipClass = PHPExcel_Settings::getZipClass();
184
185	$zip = new $zipClass;
186	if (!$zip->open($pFilename)) {
187	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
188	}
189
190	$xml = new XMLReader();
191	$res = $xml->open('zip://'.realpath($pFilename).'#content.xml', null, PHPExcel_Settings::getLibXmlLoaderOptions());
192	$xml->setParserProperty(2,true);
193
194	// Step into the first level of content of the XML
195	$xml->read();
196	while ($xml->read()) {
197	// Quickly jump through to the office:body node
198	while ($xml->name !== 'office:body') {
199	if ($xml->isEmptyElement)
200	$xml->read();
201	else
202	$xml->next();
203	}
204	// Now read each node until we find our first table:table node
205	while ($xml->read()) {
206	if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) {
207	$worksheetNames[] = $xml->getAttribute('table:name');
208
209	$tmpInfo = array(
210	'worksheetName' => $xml->getAttribute('table:name'),
211	'lastColumnLetter' => 'A',
212	'lastColumnIndex' => 0,
213	'totalRows' => 0,
214	'totalColumns' => 0,
215	);
216
217	// Loop through each child node of the table:table element reading
218	$currCells = 0;
219	do {
220	$xml->read();
221	if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) {
222	$rowspan = $xml->getAttribute('table:number-rows-repeated');
223	$rowspan = empty($rowspan) ? 1 : $rowspan;
224	$tmpInfo['totalRows'] += $rowspan;
225	$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
226	$currCells = 0;
227	// Step into the row
228	$xml->read();
229	do {
230	if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
231	if (!$xml->isEmptyElement) {
232	$currCells++;
233	$xml->next();
234	} else {
235	$xml->read();
236	}
237	} elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) {
238	$mergeSize = $xml->getAttribute('table:number-columns-repeated');
239	$currCells += $mergeSize;
240	$xml->read();
241	}
242	} while ($xml->name != 'table:table-row');
243	}
244	} while ($xml->name != 'table:table');
245
246	$tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'],$currCells);
247	$tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1;
248	$tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
249	$worksheetInfo[] = $tmpInfo;
250	}
251	}
252
253	// foreach($workbookData->table as $worksheetDataSet) {
254	// $worksheetData = $worksheetDataSet->children($namespacesContent['table']);
255	// $worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
256	//
257	// $rowIndex = 0;
258	// foreach ($worksheetData as $key => $rowData) {
259	// switch ($key) {
260	// case 'table-row' :
261	// $rowDataTableAttributes = $rowData->attributes($namespacesContent['table']);
262	// $rowRepeats = (isset($rowDataTableAttributes['number-rows-repeated'])) ?
263	// $rowDataTableAttributes['number-rows-repeated'] : 1;
264	// $columnIndex = 0;
265	//
266	// foreach ($rowData as $key => $cellData) {
267	// $cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
268	// $colRepeats = (isset($cellDataTableAttributes['number-columns-repeated'])) ?
269	// $cellDataTableAttributes['number-columns-repeated'] : 1;
270	// $cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
271	// if (isset($cellDataOfficeAttributes['value-type'])) {
272	// $tmpInfo['lastColumnIndex'] = max($tmpInfo['lastColumnIndex'], $columnIndex + $colRepeats - 1);
273	// $tmpInfo['totalRows'] = max($tmpInfo['totalRows'], $rowIndex + $rowRepeats);
274	// }
275	// $columnIndex += $colRepeats;
276	// }
277	// $rowIndex += $rowRepeats;
278	// break;
279	// }
280	// }
281	//
282	// $tmpInfo['lastColumnLetter'] = PHPExcel_Cell::stringFromColumnIndex($tmpInfo['lastColumnIndex']);
283	// $tmpInfo['totalColumns'] = $tmpInfo['lastColumnIndex'] + 1;
284	//
285	// }
286	// }
287	}
288
289	return $worksheetInfo;
290	}
291
292
293	/**
294	* Loads PHPExcel from file
295	*
296	* @param string $pFilename
297	* @return PHPExcel
298	* @throws PHPExcel_Reader_Exception
299	*/
300	public function load($pFilename)
301	{
302	// Create new PHPExcel
303	$objPHPExcel = new PHPExcel();
304
305	// Load into this instance
306	return $this->loadIntoExisting($pFilename, $objPHPExcel);
307	}
308
309
310	private static function identifyFixedStyleValue($styleList,&$styleAttributeValue) {
311	$styleAttributeValue = strtolower($styleAttributeValue);
312	foreach($styleList as $style) {
313	if ($styleAttributeValue == strtolower($style)) {
314	$styleAttributeValue = $style;
315	return true;
316	}
317	}
318	return false;
319	}
320
321
322	/**
323	* Loads PHPExcel from file into PHPExcel instance
324	*
325	* @param string $pFilename
326	* @param PHPExcel $objPHPExcel
327	* @return PHPExcel
328	* @throws PHPExcel_Reader_Exception
329	*/
330	public function loadIntoExisting($pFilename, PHPExcel $objPHPExcel)
331	{
332	// Check if file exists
333	if (!file_exists($pFilename)) {
334	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! File does not exist.");
335	}
336
337	$timezoneObj = new DateTimeZone('Europe/London');
338	$GMT = new DateTimeZone('UTC');
339
340	$zipClass = PHPExcel_Settings::getZipClass();
341
342	$zip = new $zipClass;
343	if (!$zip->open($pFilename)) {
344	throw new PHPExcel_Reader_Exception("Could not open " . $pFilename . " for reading! Error opening file.");
345	}
346
347	// echo '<h1>Meta Information</h1>';
348	$xml = simplexml_load_string($zip->getFromName("meta.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
349	$namespacesMeta = $xml->getNamespaces(true);
350	// echo '<pre>';
351	// print_r($namespacesMeta);
352	// echo '</pre><hr />';
353
354	$docProps = $objPHPExcel->getProperties();
355	$officeProperty = $xml->children($namespacesMeta['office']);
356	foreach($officeProperty as $officePropertyData) {
357	$officePropertyDC = array();
358	if (isset($namespacesMeta['dc'])) {
359	$officePropertyDC = $officePropertyData->children($namespacesMeta['dc']);
360	}
361	foreach($officePropertyDC as $propertyName => $propertyValue) {
362	$propertyValue = (string) $propertyValue;
363	switch ($propertyName) {
364	case 'title' :
365	$docProps->setTitle($propertyValue);
366	break;
367	case 'subject' :
368	$docProps->setSubject($propertyValue);
369	break;
370	case 'creator' :
371	$docProps->setCreator($propertyValue);
372	$docProps->setLastModifiedBy($propertyValue);
373	break;
374	case 'date' :
375	$creationDate = strtotime($propertyValue);
376	$docProps->setCreated($creationDate);
377	$docProps->setModified($creationDate);
378	break;
379	case 'description' :
380	$docProps->setDescription($propertyValue);
381	break;
382	}
383	}
384	$officePropertyMeta = array();
385	if (isset($namespacesMeta['dc'])) {
386	$officePropertyMeta = $officePropertyData->children($namespacesMeta['meta']);
387	}
388	foreach($officePropertyMeta as $propertyName => $propertyValue) {
389	$propertyValueAttributes = $propertyValue->attributes($namespacesMeta['meta']);
390	$propertyValue = (string) $propertyValue;
391	switch ($propertyName) {
392	case 'initial-creator' :
393	$docProps->setCreator($propertyValue);
394	break;
395	case 'keyword' :
396	$docProps->setKeywords($propertyValue);
397	break;
398	case 'creation-date' :
399	$creationDate = strtotime($propertyValue);
400	$docProps->setCreated($creationDate);
401	break;
402	case 'user-defined' :
403	$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
404	foreach ($propertyValueAttributes as $key => $value) {
405	if ($key == 'name') {
406	$propertyValueName = (string) $value;
407	} elseif($key == 'value-type') {
408	switch ($value) {
409	case 'date' :
410	$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'date');
411	$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_DATE;
412	break;
413	case 'boolean' :
414	$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'bool');
415	$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_BOOLEAN;
416	break;
417	case 'float' :
418	$propertyValue = PHPExcel_DocumentProperties::convertProperty($propertyValue,'r4');
419	$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_FLOAT;
420	break;
421	default :
422	$propertyValueType = PHPExcel_DocumentProperties::PROPERTY_TYPE_STRING;
423	}
424	}
425	}
426	$docProps->setCustomProperty($propertyValueName,$propertyValue,$propertyValueType);
427	break;
428	}
429	}
430	}
431
432
433	// echo '<h1>Workbook Content</h1>';
434	$xml = simplexml_load_string($zip->getFromName("content.xml"), 'SimpleXMLElement', PHPExcel_Settings::getLibXmlLoaderOptions());
435	$namespacesContent = $xml->getNamespaces(true);
436	// echo '<pre>';
437	// print_r($namespacesContent);
438	// echo '</pre><hr />';
439
440	$workbook = $xml->children($namespacesContent['office']);
441	foreach($workbook->body->spreadsheet as $workbookData) {
442	$workbookData = $workbookData->children($namespacesContent['table']);
443	$worksheetID = 0;
444	foreach($workbookData->table as $worksheetDataSet) {
445	$worksheetData = $worksheetDataSet->children($namespacesContent['table']);
446	// print_r($worksheetData);
447	// echo '<br />';
448	$worksheetDataAttributes = $worksheetDataSet->attributes($namespacesContent['table']);
449	// print_r($worksheetDataAttributes);
450	// echo '<br />';
451	if ((isset($this->_loadSheetsOnly)) && (isset($worksheetDataAttributes['name'])) &&
452	(!in_array($worksheetDataAttributes['name'], $this->_loadSheetsOnly))) {
453	continue;
454	}
455
456	// echo '<h2>Worksheet '.$worksheetDataAttributes['name'].'</h2>';
457	// Create new Worksheet
458	$objPHPExcel->createSheet();
459	$objPHPExcel->setActiveSheetIndex($worksheetID);
460	if (isset($worksheetDataAttributes['name'])) {
461	$worksheetName = (string) $worksheetDataAttributes['name'];
462	// Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in
463	// formula cells... during the load, all formulae should be correct, and we're simply
464	// bringing the worksheet name in line with the formula, not the reverse
465	$objPHPExcel->getActiveSheet()->setTitle($worksheetName,false);
466	}
467
468	$rowID = 1;
469	foreach($worksheetData as $key => $rowData) {
470	// echo '<b>'.$key.'</b><br />';
471	switch ($key) {
472	case 'table-header-rows':
473	foreach ($rowData as $key=>$cellData) {
474	$rowData = $cellData;
475	break;
476	}
477	case 'table-row' :
478	$rowDataTableAttributes = $rowData->attributes($namespacesContent['table']);
479	$rowRepeats = (isset($rowDataTableAttributes['number-rows-repeated'])) ?
480	$rowDataTableAttributes['number-rows-repeated'] : 1;
481	$columnID = 'A';
482	foreach($rowData as $key => $cellData) {
483	if ($this->getReadFilter() !== NULL) {
484	if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) {
485	continue;
486	}
487	}
488
489	// echo '<b>'.$columnID.$rowID.'</b><br />';
490	$cellDataText = (isset($namespacesContent['text'])) ?
491	$cellData->children($namespacesContent['text']) :
492	'';
493	$cellDataOffice = $cellData->children($namespacesContent['office']);
494	$cellDataOfficeAttributes = $cellData->attributes($namespacesContent['office']);
495	$cellDataTableAttributes = $cellData->attributes($namespacesContent['table']);
496
497	// echo 'Office Attributes: ';
498	// print_r($cellDataOfficeAttributes);
499	// echo '<br />Table Attributes: ';
500	// print_r($cellDataTableAttributes);
501	// echo '<br />Cell Data Text';
502	// print_r($cellDataText);
503	// echo '<br />';
504	//
505	$type = $formatting = $hyperlink = null;
506	$hasCalculatedValue = false;
507	$cellDataFormula = '';
508	if (isset($cellDataTableAttributes['formula'])) {
509	$cellDataFormula = $cellDataTableAttributes['formula'];
510	$hasCalculatedValue = true;
511	}
512
513	if (isset($cellDataOffice->annotation)) {
514	// echo 'Cell has comment<br />';
515	$annotationText = $cellDataOffice->annotation->children($namespacesContent['text']);
516	$textArray = array();
517	foreach($annotationText as $t) {
518	foreach($t->span as $text) {
519	$textArray[] = (string)$text;
520	}
521	}
522	$text = implode("\n",$textArray);
523	// echo $text,'<br />';
524	$objPHPExcel->getActiveSheet()->getComment( $columnID.$rowID )
525	// ->setAuthor( $author )
526	->setText($this->_parseRichText($text) );
527	}
528
529	if (isset($cellDataText->p)) {
530	// Consolidate if there are multiple p records (maybe with spans as well)
531	$dataArray = array();
532	// Text can have multiple text:p and within those, multiple text:span.
533	// text:p newlines, but text:span does not.
534	// Also, here we assume there is no text data is span fields are specified, since
535	// we have no way of knowing proper positioning anyway.
536	foreach ($cellDataText->p as $pData) {
537	if (isset($pData->span)) {
538	// span sections do not newline, so we just create one large string here
539	$spanSection = "";
540	foreach ($pData->span as $spanData) {
541	$spanSection .= $spanData;
542	}
543	array_push($dataArray, $spanSection);
544	} else {
545	array_push($dataArray, $pData);
546	}
547	}
548	$allCellDataText = implode($dataArray, "\n");
549
550	// echo 'Value Type is '.$cellDataOfficeAttributes['value-type'].'<br />';
551	switch ($cellDataOfficeAttributes['value-type']) {
552	case 'string' :
553	$type = PHPExcel_Cell_DataType::TYPE_STRING;
554	$dataValue = $allCellDataText;
555	if (isset($dataValue->a)) {
556	$dataValue = $dataValue->a;
557	$cellXLinkAttributes = $dataValue->attributes($namespacesContent['xlink']);
558	$hyperlink = $cellXLinkAttributes['href'];
559	}
560	break;
561	case 'boolean' :
562	$type = PHPExcel_Cell_DataType::TYPE_BOOL;
563	$dataValue = ($allCellDataText == 'TRUE') ? True : False;
564	break;
565	case 'percentage' :
566	$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
567	$dataValue = (float) $cellDataOfficeAttributes['value'];
568	if (floor($dataValue) == $dataValue) {
569	$dataValue = (integer) $dataValue;
570	}
571	$formatting = PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00;
572	break;
573	case 'currency' :
574	$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
575	$dataValue = (float) $cellDataOfficeAttributes['value'];
576	if (floor($dataValue) == $dataValue) {
577	$dataValue = (integer) $dataValue;
578	}
579	$formatting = PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE;
580	break;
581	case 'float' :
582	$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
583	$dataValue = (float) $cellDataOfficeAttributes['value'];
584	if (floor($dataValue) == $dataValue) {
585	if ($dataValue == (integer) $dataValue)
586	$dataValue = (integer) $dataValue;
587	else
588	$dataValue = (float) $dataValue;
589	}
590	break;
591	case 'date' :
592	$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
593	$dateObj = new DateTime($cellDataOfficeAttributes['date-value'], $GMT);
594	$dateObj->setTimeZone($timezoneObj);
595	list($year,$month,$day,$hour,$minute,$second) = explode(' ',$dateObj->format('Y m d H i s'));
596	$dataValue = PHPExcel_Shared_Date::FormattedPHPToExcel($year,$month,$day,$hour,$minute,$second);
597	if ($dataValue != floor($dataValue)) {
598	$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15.' '.PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
599	} else {
600	$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSX15;
601	}
602	break;
603	case 'time' :
604	$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
605	$dataValue = PHPExcel_Shared_Date::PHPToExcel(strtotime('01-01-1970 '.implode(':',sscanf($cellDataOfficeAttributes['time-value'],'PT%dH%dM%dS'))));
606	$formatting = PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4;
607	break;
608	}
609	// echo 'Data value is '.$dataValue.'<br />';
610	// if ($hyperlink !== NULL) {
611	// echo 'Hyperlink is '.$hyperlink.'<br />';
612	// }
613	} else {
614	$type = PHPExcel_Cell_DataType::TYPE_NULL;
615	$dataValue = NULL;
616	}
617
618	if ($hasCalculatedValue) {
619	$type = PHPExcel_Cell_DataType::TYPE_FORMULA;
620	// echo 'Formula: ', $cellDataFormula, PHP_EOL;
621	$cellDataFormula = substr($cellDataFormula,strpos($cellDataFormula,':=')+1);
622	$temp = explode('"',$cellDataFormula);
623	$tKey = false;
624	foreach($temp as &$value) {
625	// Only replace in alternate array entries (i.e. non-quoted blocks)
626	if ($tKey = !$tKey) {
627	$value = preg_replace('/\[([^\.]+)\.([^\.]+):\.([^\.]+)\]/Ui','$1!$2:$3',$value); // Cell range reference in another sheet
628	$value = preg_replace('/\[([^\.]+)\.([^\.]+)\]/Ui','$1!$2',$value); // Cell reference in another sheet
629	$value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/Ui','$1:$2',$value); // Cell range reference
630	$value = preg_replace('/\[\.([^\.]+)\]/Ui','$1',$value); // Simple cell reference
631	$value = PHPExcel_Calculation::_translateSeparator(';',',',$value,$inBraces);
632	}
633	}
634	unset($value);
635	// Then rebuild the formula string
636	$cellDataFormula = implode('"',$temp);
637	// echo 'Adjusted Formula: ', $cellDataFormula, PHP_EOL;
638	}
639
640	$colRepeats = (isset($cellDataTableAttributes['number-columns-repeated'])) ?
641	$cellDataTableAttributes['number-columns-repeated'] : 1;
642	if ($type !== NULL) {
643	for ($i = 0; $i < $colRepeats; ++$i) {
644	if ($i > 0) {
645	++$columnID;
646	}
647	if ($type !== PHPExcel_Cell_DataType::TYPE_NULL) {
648	for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) {
649	$rID = $rowID + $rowAdjust;
650	$objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $dataValue),$type);
651	if ($hasCalculatedValue) {
652	// echo 'Forumla result is '.$dataValue.'<br />';
653	$objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->setCalculatedValue($dataValue);
654	}
655	if ($formatting !== NULL) {
656	$objPHPExcel->getActiveSheet()->getStyle($columnID.$rID)->getNumberFormat()->setFormatCode($formatting);
657	} else {
658	$objPHPExcel->getActiveSheet()->getStyle($columnID.$rID)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);
659	}
660	if ($hyperlink !== NULL) {
661	$objPHPExcel->getActiveSheet()->getCell($columnID.$rID)->getHyperlink()->setUrl($hyperlink);
662	}
663	}
664	}
665	}
666	}
667
668	// Merged cells
669	if ((isset($cellDataTableAttributes['number-columns-spanned'])) \|\| (isset($cellDataTableAttributes['number-rows-spanned']))) {
670	if (($type !== PHPExcel_Cell_DataType::TYPE_NULL) \|\| (!$this->_readDataOnly)) {
671	$columnTo = $columnID;
672	if (isset($cellDataTableAttributes['number-columns-spanned'])) {
673	$columnTo = PHPExcel_Cell::stringFromColumnIndex(PHPExcel_Cell::columnIndexFromString($columnID) + $cellDataTableAttributes['number-columns-spanned'] -2);
674	}
675	$rowTo = $rowID;
676	if (isset($cellDataTableAttributes['number-rows-spanned'])) {
677	$rowTo = $rowTo + $cellDataTableAttributes['number-rows-spanned'] - 1;
678	}
679	$cellRange = $columnID.$rowID.':'.$columnTo.$rowTo;
680	$objPHPExcel->getActiveSheet()->mergeCells($cellRange);
681	}
682	}
683
684	++$columnID;
685	}
686	$rowID += $rowRepeats;
687	break;
688	}
689	}
690	++$worksheetID;
691	}
692	}
693
694	// Return
695	return $objPHPExcel;
696	}
697
698
699	private function _parseRichText($is = '') {
700	$value = new PHPExcel_RichText();
701
702	$value->createText($is);
703
704	return $value;
705	}
706
707	}

Note: See TracBrowser for help on using the repository browser.

Context Navigation

source: sourcecode/application/libraries/PHPExcel/Reader/OOCalc.php @ 1

Download in other formats: