File Source for Worksheet.php

PHPExcel_Writer_Excel2007

# Source for file Worksheet.php

Documentation is available at Worksheet.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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_Writer_Excel2007
23. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
29. /**
30. * PHPExcel_Writer_Excel2007_Worksheet
31. *
32. * @category PHPExcel
33. * @package PHPExcel_Writer_Excel2007
34. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
35. */
36. class PHPExcel_Writer_Excel2007_Worksheet extends PHPExcel_Writer_Excel2007_WriterPart
37. {
38. /**
39. * Write worksheet to XML format
40. *
41. * @param PHPExcel_Worksheet $pSheet
42. * @param string[] $pStringTable
43. * @return string XML Output
44. * @throws Exception
45. */
46. public function writeWorksheet($pSheet = null, $pStringTable = null)
47. {
48. if (! is_null($pSheet)) {
49. // Create XML writer
50. $objWriter = null;
51. if ($this-> getParentWriter()->getUseDiskCaching()) {
52. $objWriter = new PHPExcel_Shared_XMLWriter( PHPExcel_Shared_XMLWriter:: STORAGE_DISK, $this-> getParentWriter()->getDiskCachingDirectory());
53. } else {
54. $objWriter = new PHPExcel_Shared_XMLWriter( PHPExcel_Shared_XMLWriter:: STORAGE_MEMORY);
55. }
57. // XML header
58. $objWriter->startDocument('1.0','UTF-8','yes');
60. // Worksheet
61. $objWriter->startElement('worksheet');
62. $objWriter->writeAttribute('xml:space', 'preserve');
63. $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
64. $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
66. // sheetPr
67. $this->_writeSheetPr($objWriter, $pSheet);
69. // Dimension
70. $this->_writeDimension($objWriter, $pSheet);
72. // sheetViews
73. $this->_writeSheetViews($objWriter, $pSheet);
75. // sheetFormatPr
76. $this->_writeSheetFormatPr($objWriter, $pSheet);
78. // cols
79. $this->_writeCols($objWriter, $pSheet);
81. // sheetData
82. $this->_writeSheetData($objWriter, $pSheet, $pStringTable);
84. // sheetProtection
85. $this->_writeSheetProtection($objWriter, $pSheet);
87. // protectedRanges
88. $this->_writeProtectedRanges($objWriter, $pSheet);
90. // autoFilter
91. $this->_writeAutoFilter($objWriter, $pSheet);
93. // mergeCells
94. $this->_writeMergeCells($objWriter, $pSheet);
96. // conditionalFormatting
97. $this->_writeConditionalFormatting($objWriter, $pSheet);
99. // dataValidations
100. $this->_writeDataValidations($objWriter, $pSheet);
102. // hyperlinks
103. $this->_writeHyperlinks($objWriter, $pSheet);
105. // Print options
106. $this->_writePrintOptions($objWriter, $pSheet);
108. // Page margins
109. $this->_writePageMargins($objWriter, $pSheet);
111. // Page setup
112. $this->_writePageSetup($objWriter, $pSheet);
114. // Header / footer
115. $this->_writeHeaderFooter($objWriter, $pSheet);
117. // Breaks
118. $this->_writeBreaks($objWriter, $pSheet);
120. // Drawings
121. $this->_writeDrawings($objWriter, $pSheet);
123. // LegacyDrawing
124. $this->_writeLegacyDrawing($objWriter, $pSheet);
126. // LegacyDrawingHF
127. $this->_writeLegacyDrawingHF($objWriter, $pSheet);
129. $objWriter->endElement();
131. // Return
132. return $objWriter->getData();
133. } else {
134. throw new Exception("Invalid PHPExcel_Worksheet object passed.");
135. }
136. }
138. /**
139. * Write SheetPr
140. *
141. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
142. * @param PHPExcel_Worksheet $pSheet Worksheet
143. * @throws Exception
144. */
145. private function _writeSheetPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
146. {
147. // sheetPr
148. $objWriter->startElement('sheetPr');
149. //$objWriter->writeAttribute('codeName', $pSheet->getTitle());
151. // tabColor
152. if ($pSheet->isTabColorSet()) {
153. $objWriter->startElement('tabColor');
154. $objWriter->writeAttribute('rgb', $pSheet->getTabColor()->getARGB());
155. $objWriter->endElement();
156. }
158. // outlinePr
159. $objWriter->startElement('outlinePr');
160. $objWriter->writeAttribute('summaryBelow', ($pSheet->getShowSummaryBelow() ? '1' : '0'));
161. $objWriter->writeAttribute('summaryRight', ($pSheet->getShowSummaryRight() ? '1' : '0'));
162. $objWriter->endElement();
164. // pageSetUpPr
165. if ($pSheet->getPageSetup()->getFitToPage()) {
166. $objWriter->startElement('pageSetUpPr');
167. $objWriter->writeAttribute('fitToPage', '1');
168. $objWriter->endElement();
169. }
171. $objWriter->endElement();
172. }
174. /**
175. * Write Dimension
176. *
177. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
178. * @param PHPExcel_Worksheet $pSheet Worksheet
179. * @throws Exception
180. */
181. private function _writeDimension(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
182. {
183. // dimension
184. $objWriter->startElement('dimension');
185. $objWriter->writeAttribute('ref', $pSheet->calculateWorksheetDimension());
186. $objWriter->endElement();
187. }
189. /**
190. * Write SheetViews
191. *
192. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
193. * @param PHPExcel_Worksheet $pSheet Worksheet
194. * @throws Exception
195. */
196. private function _writeSheetViews(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
197. {
198. // sheetViews
199. $objWriter->startElement('sheetViews');
201. // Sheet selected?
202. $sheetSelected = false;
203. if ($this-> getParentWriter()->getPHPExcel()->getIndex($pSheet) == $this-> getParentWriter()->getPHPExcel()->getActiveSheetIndex())
204. $sheetSelected = true;
207. // sheetView
208. $objWriter->startElement('sheetView');
209. $objWriter->writeAttribute('tabSelected', $sheetSelected ? '1' : '0');
210. $objWriter->writeAttribute('workbookViewId', '0');
212. // Zoom scales
213. if ($pSheet->getSheetView()->getZoomScale() != 100) {
214. $objWriter->writeAttribute('zoomScale', $pSheet->getSheetView()->getZoomScale());
215. }
216. if ($pSheet->getSheetView()->getZoomScaleNormal() != 100) {
217. $objWriter->writeAttribute('zoomScaleNormal', $pSheet->getSheetView()->getZoomScaleNormal());
218. }
220. // Gridlines
221. if ($pSheet->getShowGridlines()) {
222. $objWriter->writeAttribute('showGridLines', 'true');
223. } else {
224. $objWriter->writeAttribute('showGridLines', 'false');
225. }
227. // Row and column headers
228. if ($pSheet->getShowRowColHeaders()) {
229. $objWriter->writeAttribute('showRowColHeaders', '1');
230. } else {
231. $objWriter->writeAttribute('showRowColHeaders', '0');
232. }
234. // Right-to-left
235. if ($pSheet->getRightToLeft()) {
236. $objWriter->writeAttribute('rightToLeft', 'true');
237. }
239. $activeCell = $pSheet->getActiveCell();
241. // Pane
242. $pane = '';
243. $topLeftCell = $pSheet->getFreezePane();
244. if (($topLeftCell != '') && ($topLeftCell != 'A1')) {
245. $activeCell = $topLeftCell;
246. // Calculate freeze coordinates
247. $xSplit = $ySplit = 0;
249. list($xSplit, $ySplit) = PHPExcel_Cell:: coordinateFromString($topLeftCell);
250. $xSplit = PHPExcel_Cell:: columnIndexFromString($xSplit);
252. // pane
253. $pane = 'topRight';
254. $objWriter->startElement('pane');
255. if ($xSplit > 1)
256. $objWriter->writeAttribute('xSplit', $xSplit - 1);
257. if ($ySplit > 1) {
258. $objWriter->writeAttribute('ySplit', $ySplit - 1);
259. $pane = ($xSplit > 1) ? 'bottomRight' : 'bottomLeft';
260. }
261. $objWriter->writeAttribute('topLeftCell', $topLeftCell);
262. $objWriter->writeAttribute('activePane', $pane);
263. $objWriter->writeAttribute('state', 'frozen');
264. $objWriter->endElement();
266. if (($xSplit > 1) && ($ySplit > 1)) {
267. // Write additional selections if more than two panes (ie both an X and a Y split)
268. $objWriter->startElement('selection'); $objWriter->writeAttribute('pane', 'topRight'); $objWriter->endElement();
269. $objWriter->startElement('selection'); $objWriter->writeAttribute('pane', 'bottomLeft'); $objWriter->endElement();
270. }
271. }
273. // Selection
274. if ($pane != '') {
275. // Only need to write selection element if we have a split pane
276. // We cheat a little by over-riding the active cell selection, setting it to the split cell
277. $objWriter->startElement('selection');
278. $objWriter->writeAttribute('pane', $pane);
279. $objWriter->writeAttribute('activeCell', $activeCell);
280. $objWriter->writeAttribute('sqref', $activeCell);
281. $objWriter->endElement();
282. }
284. $objWriter->endElement();
286. $objWriter->endElement();
287. }
289. /**
290. * Write SheetFormatPr
291. *
292. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
293. * @param PHPExcel_Worksheet $pSheet Worksheet
294. * @throws Exception
295. */
296. private function _writeSheetFormatPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
297. {
298. // sheetFormatPr
299. $objWriter->startElement('sheetFormatPr');
301. // Default row height
302. if ($pSheet->getDefaultRowDimension()->getRowHeight() >= 0) {
303. $objWriter->writeAttribute('customHeight', 'true');
304. $objWriter->writeAttribute('defaultRowHeight', PHPExcel_Shared_String:: FormatNumber($pSheet->getDefaultRowDimension()->getRowHeight()));
305. } else {
306. $objWriter->writeAttribute('defaultRowHeight', '12.75');
307. }
309. // Default column width
310. if ($pSheet->getDefaultColumnDimension()->getWidth() >= 0) {
311. $objWriter->writeAttribute('defaultColWidth', PHPExcel_Shared_String:: FormatNumber($pSheet->getDefaultColumnDimension()->getWidth()));
312. }
314. // Outline level - row
315. $outlineLevelRow = 0;
316. foreach ($pSheet->getRowDimensions() as $dimension) {
317. if ($dimension->getOutlineLevel() > $outlineLevelRow) {
318. $outlineLevelRow = $dimension->getOutlineLevel();
319. }
320. }
321. $objWriter->writeAttribute('outlineLevelRow', (int)$outlineLevelRow);
323. // Outline level - column
324. $outlineLevelCol = 0;
325. foreach ($pSheet->getColumnDimensions() as $dimension) {
326. if ($dimension->getOutlineLevel() > $outlineLevelCol) {
327. $outlineLevelCol = $dimension->getOutlineLevel();
328. }
329. }
330. $objWriter->writeAttribute('outlineLevelCol', (int)$outlineLevelCol);
332. $objWriter->endElement();
333. }
335. /**
336. * Write Cols
337. *
338. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
339. * @param PHPExcel_Worksheet $pSheet Worksheet
340. * @throws Exception
341. */
342. private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
343. {
344. // cols
345. if ( count($pSheet->getColumnDimensions()) > 0) {
346. $objWriter->startElement('cols');
348. $pSheet->calculateColumnWidths();
350. // Loop through column dimensions
351. foreach ($pSheet->getColumnDimensions() as $colDimension) {
352. // col
353. $objWriter->startElement('col');
354. $objWriter->writeAttribute('min', PHPExcel_Cell:: columnIndexFromString($colDimension->getColumnIndex()));
355. $objWriter->writeAttribute('max', PHPExcel_Cell:: columnIndexFromString($colDimension->getColumnIndex()));
357. if ($colDimension->getWidth() < 0) {
358. // No width set, apply default of 10
359. $objWriter->writeAttribute('width', '9.10');
360. } else {
361. // Width set
362. $objWriter->writeAttribute('width', PHPExcel_Shared_String:: FormatNumber($colDimension->getWidth()));
363. }
365. // Column visibility
366. if ($colDimension->getVisible() == false) {
367. $objWriter->writeAttribute('hidden', 'true');
368. }
370. // Auto size?
371. if ($colDimension->getAutoSize()) {
372. $objWriter->writeAttribute('bestFit', 'true');
373. }
375. // Custom width?
376. if ($colDimension->getWidth() != $pSheet->getDefaultColumnDimension()->getWidth()) {
377. $objWriter->writeAttribute('customWidth', 'true');
378. }
380. // Collapsed
381. if ($colDimension->getCollapsed() == true) {
382. $objWriter->writeAttribute('collapsed', 'true');
383. }
385. // Outline level
386. if ($colDimension->getOutlineLevel() > 0) {
387. $objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
388. }
390. // Style
391. $objWriter->writeAttribute('style', $colDimension->getXfIndex());
393. $objWriter->endElement();
394. }
396. $objWriter->endElement();
397. }
398. }
400. /**
401. * Write SheetProtection
402. *
403. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
404. * @param PHPExcel_Worksheet $pSheet Worksheet
405. * @throws Exception
406. */
407. private function _writeSheetProtection(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
408. {
409. // sheetProtection
410. $objWriter->startElement('sheetProtection');
412. if ($pSheet->getProtection()->getPassword() != '') {
413. $objWriter->writeAttribute('password', $pSheet->getProtection()->getPassword());
414. }
416. $objWriter->writeAttribute('sheet', ($pSheet->getProtection()->getSheet() ? 'true' : 'false'));
417. $objWriter->writeAttribute('objects', ($pSheet->getProtection()->getObjects() ? 'true' : 'false'));
418. $objWriter->writeAttribute('scenarios', ($pSheet->getProtection()->getScenarios() ? 'true' : 'false'));
419. $objWriter->writeAttribute('formatCells', ($pSheet->getProtection()->getFormatCells() ? 'true' : 'false'));
420. $objWriter->writeAttribute('formatColumns', ($pSheet->getProtection()->getFormatColumns() ? 'true' : 'false'));
421. $objWriter->writeAttribute('formatRows', ($pSheet->getProtection()->getFormatRows() ? 'true' : 'false'));
422. $objWriter->writeAttribute('insertColumns', ($pSheet->getProtection()->getInsertColumns() ? 'true' : 'false'));
423. $objWriter->writeAttribute('insertRows', ($pSheet->getProtection()->getInsertRows() ? 'true' : 'false'));
424. $objWriter->writeAttribute('insertHyperlinks', ($pSheet->getProtection()->getInsertHyperlinks() ? 'true' : 'false'));
425. $objWriter->writeAttribute('deleteColumns', ($pSheet->getProtection()->getDeleteColumns() ? 'true' : 'false'));
426. $objWriter->writeAttribute('deleteRows', ($pSheet->getProtection()->getDeleteRows() ? 'true' : 'false'));
427. $objWriter->writeAttribute('selectLockedCells', ($pSheet->getProtection()->getSelectLockedCells() ? 'true' : 'false'));
428. $objWriter->writeAttribute('sort', ($pSheet->getProtection()->getSort() ? 'true' : 'false'));
429. $objWriter->writeAttribute('autoFilter', ($pSheet->getProtection()->getAutoFilter() ? 'true' : 'false'));
430. $objWriter->writeAttribute('pivotTables', ($pSheet->getProtection()->getPivotTables() ? 'true' : 'false'));
431. $objWriter->writeAttribute('selectUnlockedCells', ($pSheet->getProtection()->getSelectUnlockedCells() ? 'true' : 'false'));
432. $objWriter->endElement();
433. }
435. /**
436. * Write ConditionalFormatting
437. *
438. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
439. * @param PHPExcel_Worksheet $pSheet Worksheet
440. * @throws Exception
441. */
442. private function _writeConditionalFormatting(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
443. {
444. // Conditional id
445. $id = 1;
447. // Loop through styles in the current worksheet
448. foreach ($pSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
449. foreach ($conditionalStyles as $conditional) {
450. // WHY was this again?
451. // if ($this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode( $conditional->getHashCode() ) == '') {
452. // continue;
453. // }
455. if ($conditional->getConditionType() != PHPExcel_Style_Conditional:: CONDITION_NONE) {
456. // conditionalFormatting
457. $objWriter->startElement('conditionalFormatting');
458. $objWriter->writeAttribute('sqref', $cellCoordinate);
460. // cfRule
461. $objWriter->startElement('cfRule');
462. $objWriter->writeAttribute('type', $conditional->getConditionType());
463. $objWriter->writeAttribute('dxfId', $this-> getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode( $conditional->getHashCode() ));
464. $objWriter->writeAttribute('priority', $id++);
466. if (($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CELLIS
467. ||
468. $conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT)
469. && $conditional->getOperatorType() != PHPExcel_Style_Conditional:: OPERATOR_NONE) {
470. $objWriter->writeAttribute('operator', $conditional->getOperatorType());
471. }
473. if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
474. && ! is_null($conditional->getText())) {
475. $objWriter->writeAttribute('text', $conditional->getText());
476. }
478. if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
479. && $conditional->getOperatorType() == PHPExcel_Style_Conditional:: OPERATOR_CONTAINSTEXT
480. && ! is_null($conditional->getText())) {
481. $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . ')))');
482. } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
483. && $conditional->getOperatorType() == PHPExcel_Style_Conditional:: OPERATOR_BEGINSWITH
484. && ! is_null($conditional->getText())) {
485. $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
486. } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
487. && $conditional->getOperatorType() == PHPExcel_Style_Conditional:: OPERATOR_ENDSWITH
488. && ! is_null($conditional->getText())) {
489. $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
490. } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
491. && $conditional->getOperatorType() == PHPExcel_Style_Conditional:: OPERATOR_NOTCONTAINS
492. && ! is_null($conditional->getText())) {
493. $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . '))');
494. } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CELLIS
495. || $conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_CONTAINSTEXT
496. || $conditional->getConditionType() == PHPExcel_Style_Conditional:: CONDITION_EXPRESSION) {
497. foreach ($conditional->getConditions() as $formula) {
498. // Formula
499. $objWriter->writeElement('formula', $formula);
500. }
501. }
503. $objWriter->endElement();
505. $objWriter->endElement();
506. }
507. }
508. }
509. }
511. /**
512. * Write DataValidations
513. *
514. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
515. * @param PHPExcel_Worksheet $pSheet Worksheet
516. * @throws Exception
517. */
518. private function _writeDataValidations(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
519. {
520. // Datavalidation collection
521. $dataValidationCollection = $pSheet->getDataValidationCollection();
523. // Write data validations?
524. if ( count($dataValidationCollection) > 0) {
525. $objWriter->startElement('dataValidations');
526. $objWriter->writeAttribute('count', count($dataValidationCollection));
528. foreach ($dataValidationCollection as $coordinate => $dv) {
529. $objWriter->startElement('dataValidation');
531. if ($dv->getType() != '') {
532. $objWriter->writeAttribute('type', $dv->getType());
533. }
535. if ($dv->getErrorStyle() != '') {
536. $objWriter->writeAttribute('errorStyle', $dv->getErrorStyle());
537. }
539. if ($dv->getOperator() != '') {
540. $objWriter->writeAttribute('operator', $dv->getOperator());
541. }
543. $objWriter->writeAttribute('allowBlank', ($dv->getAllowBlank() ? '1' : '0'));
544. $objWriter->writeAttribute('showDropDown', (!$dv->getShowDropDown() ? '1' : '0'));
545. $objWriter->writeAttribute('showInputMessage', ($dv->getShowInputMessage() ? '1' : '0'));
546. $objWriter->writeAttribute('showErrorMessage', ($dv->getShowErrorMessage() ? '1' : '0'));
548. if ($dv->getErrorTitle() !== '') {
549. $objWriter->writeAttribute('errorTitle', $dv->getErrorTitle());
550. }
551. if ($dv->getError() !== '') {
552. $objWriter->writeAttribute('error', $dv->getError());
553. }
554. if ($dv->getPromptTitle() !== '') {
555. $objWriter->writeAttribute('promptTitle', $dv->getPromptTitle());
556. }
557. if ($dv->getPrompt() !== '') {
558. $objWriter->writeAttribute('prompt', $dv->getPrompt());
559. }
561. $objWriter->writeAttribute('sqref', $coordinate);
563. if ($dv->getFormula1() !== '') {
564. $objWriter->writeElement('formula1', $dv->getFormula1());
565. }
566. if ($dv->getFormula2() !== '') {
567. $objWriter->writeElement('formula2', $dv->getFormula2());
568. }
570. $objWriter->endElement();
571. }
573. $objWriter->endElement();
574. }
575. }
577. /**
578. * Write Hyperlinks
579. *
580. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
581. * @param PHPExcel_Worksheet $pSheet Worksheet
582. * @throws Exception
583. */
584. private function _writeHyperlinks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
585. {
586. // Hyperlink collection
587. $hyperlinkCollection = $pSheet->getHyperlinkCollection();
589. // Relation ID
590. $relationId = 1;
592. // Write hyperlinks?
593. if ( count($hyperlinkCollection) > 0) {
594. $objWriter->startElement('hyperlinks');
596. foreach ($hyperlinkCollection as $coordinate => $hyperlink) {
597. $objWriter->startElement('hyperlink');
599. $objWriter->writeAttribute('ref', $coordinate);
600. if (!$hyperlink->isInternal()) {
601. $objWriter->writeAttribute('r:id', 'rId_hyperlink_' . $relationId);
602. ++$relationId;
603. } else {
604. $objWriter->writeAttribute('location', str_replace('sheet://', '', $hyperlink->getUrl()));
605. }
607. if ($hyperlink->getTooltip() != '') {
608. $objWriter->writeAttribute('tooltip', $hyperlink->getTooltip());
609. }
611. $objWriter->endElement();
612. }
614. $objWriter->endElement();
615. }
616. }
618. /**
619. * Write ProtectedRanges
620. *
621. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
622. * @param PHPExcel_Worksheet $pSheet Worksheet
623. * @throws Exception
624. */
625. private function _writeProtectedRanges(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
626. {
627. if ( count($pSheet->getProtectedCells()) > 0) {
628. // protectedRanges
629. $objWriter->startElement('protectedRanges');
631. // Loop protectedRanges
632. foreach ($pSheet->getProtectedCells() as $protectedCell => $passwordHash) {
633. // protectedRange
634. $objWriter->startElement('protectedRange');
635. $objWriter->writeAttribute('name', 'p' . md5($protectedCell));
636. $objWriter->writeAttribute('sqref', $protectedCell);
637. $objWriter->writeAttribute('password', $passwordHash);
638. $objWriter->endElement();
639. }
641. $objWriter->endElement();
642. }
643. }
645. /**
646. * Write MergeCells
647. *
648. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
649. * @param PHPExcel_Worksheet $pSheet Worksheet
650. * @throws Exception
651. */
652. private function _writeMergeCells(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
653. {
654. if ( count($pSheet->getMergeCells()) > 0) {
655. // mergeCells
656. $objWriter->startElement('mergeCells');
658. // Loop mergeCells
659. foreach ($pSheet->getMergeCells() as $mergeCell) {
660. // mergeCell
661. $objWriter->startElement('mergeCell');
662. $objWriter->writeAttribute('ref', $mergeCell);
663. $objWriter->endElement();
664. }
666. $objWriter->endElement();
667. }
668. }
670. /**
671. * Write PrintOptions
672. *
673. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
674. * @param PHPExcel_Worksheet $pSheet Worksheet
675. * @throws Exception
676. */
677. private function _writePrintOptions(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
678. {
679. // printOptions
680. $objWriter->startElement('printOptions');
682. $objWriter->writeAttribute('gridLines', ($pSheet->getPrintGridlines() ? 'true': 'false'));
683. $objWriter->writeAttribute('gridLinesSet', 'true');
685. if ($pSheet->getPageSetup()->getHorizontalCentered()) {
686. $objWriter->writeAttribute('horizontalCentered', 'true');
687. }
689. if ($pSheet->getPageSetup()->getVerticalCentered()) {
690. $objWriter->writeAttribute('verticalCentered', 'true');
691. }
693. $objWriter->endElement();
694. }
696. /**
697. * Write PageMargins
698. *
699. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
700. * @param PHPExcel_Worksheet $pSheet Worksheet
701. * @throws Exception
702. */
703. private function _writePageMargins(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
704. {
705. // pageMargins
706. $objWriter->startElement('pageMargins');
707. $objWriter->writeAttribute('left', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getLeft()));
708. $objWriter->writeAttribute('right', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getRight()));
709. $objWriter->writeAttribute('top', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getTop()));
710. $objWriter->writeAttribute('bottom', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getBottom()));
711. $objWriter->writeAttribute('header', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getHeader()));
712. $objWriter->writeAttribute('footer', PHPExcel_Shared_String:: FormatNumber($pSheet->getPageMargins()->getFooter()));
713. $objWriter->endElement();
714. }
716. /**
717. * Write AutoFilter
718. *
719. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
720. * @param PHPExcel_Worksheet $pSheet Worksheet
721. * @throws Exception
722. */
723. private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
724. {
725. if ($pSheet->getAutoFilter() != '') {
726. // autoFilter
727. $objWriter->startElement('autoFilter');
728. $objWriter->writeAttribute('ref', $pSheet->getAutoFilter());
729. $objWriter->endElement();
730. }
731. }
733. /**
734. * Write PageSetup
735. *
736. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
737. * @param PHPExcel_Worksheet $pSheet Worksheet
738. * @throws Exception
739. */
740. private function _writePageSetup(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
741. {
742. // pageSetup
743. $objWriter->startElement('pageSetup');
744. $objWriter->writeAttribute('paperSize', $pSheet->getPageSetup()->getPaperSize());
745. $objWriter->writeAttribute('orientation', $pSheet->getPageSetup()->getOrientation());
747. if (! is_null($pSheet->getPageSetup()->getScale())) {
748. $objWriter->writeAttribute('scale', $pSheet->getPageSetup()->getScale());
749. }
750. if (! is_null($pSheet->getPageSetup()->getFitToHeight())) {
751. $objWriter->writeAttribute('fitToHeight', $pSheet->getPageSetup()->getFitToHeight());
752. } else {
753. $objWriter->writeAttribute('fitToHeight', '0');
754. }
755. if (! is_null($pSheet->getPageSetup()->getFitToWidth())) {
756. $objWriter->writeAttribute('fitToWidth', $pSheet->getPageSetup()->getFitToWidth());
757. } else {
758. $objWriter->writeAttribute('fitToWidth', '0');
759. }
760. if (! is_null($pSheet->getPageSetup()->getFirstPageNumber())) {
761. $objWriter->writeAttribute('firstPageNumber', $pSheet->getPageSetup()->getFirstPageNumber());
762. $objWriter->writeAttribute('useFirstPageNumber', '1');
763. }
765. $objWriter->endElement();
766. }
768. /**
769. * Write Header / Footer
770. *
771. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
772. * @param PHPExcel_Worksheet $pSheet Worksheet
773. * @throws Exception
774. */
775. private function _writeHeaderFooter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
776. {
777. // headerFooter
778. $objWriter->startElement('headerFooter');
779. $objWriter->writeAttribute('differentOddEven', ($pSheet->getHeaderFooter()->getDifferentOddEven() ? 'true' : 'false'));
780. $objWriter->writeAttribute('differentFirst', ($pSheet->getHeaderFooter()->getDifferentFirst() ? 'true' : 'false'));
781. $objWriter->writeAttribute('scaleWithDoc', ($pSheet->getHeaderFooter()->getScaleWithDocument() ? 'true' : 'false'));
782. $objWriter->writeAttribute('alignWithMargins', ($pSheet->getHeaderFooter()->getAlignWithMargins() ? 'true' : 'false'));
784. $objWriter->writeElement('oddHeader', $pSheet->getHeaderFooter()->getOddHeader());
785. $objWriter->writeElement('oddFooter', $pSheet->getHeaderFooter()->getOddFooter());
786. $objWriter->writeElement('evenHeader', $pSheet->getHeaderFooter()->getEvenHeader());
787. $objWriter->writeElement('evenFooter', $pSheet->getHeaderFooter()->getEvenFooter());
788. $objWriter->writeElement('firstHeader', $pSheet->getHeaderFooter()->getFirstHeader());
789. $objWriter->writeElement('firstFooter', $pSheet->getHeaderFooter()->getFirstFooter());
790. $objWriter->endElement();
791. }
793. /**
794. * Write Breaks
795. *
796. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
797. * @param PHPExcel_Worksheet $pSheet Worksheet
798. * @throws Exception
799. */
800. private function _writeBreaks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
801. {
802. // Get row and column breaks
803. $aRowBreaks = array();
804. $aColumnBreaks = array();
805. foreach ($pSheet->getBreaks() as $cell => $breakType) {
806. if ($breakType == PHPExcel_Worksheet:: BREAK_ROW) {
807. $aRowBreaks[] = $cell;
808. } else if ($breakType == PHPExcel_Worksheet:: BREAK_COLUMN) {
809. $aColumnBreaks[] = $cell;
810. }
811. }
813. // rowBreaks
814. if ( count($aRowBreaks) > 0) {
815. $objWriter->startElement('rowBreaks');
816. $objWriter->writeAttribute('count', count($aRowBreaks));
817. $objWriter->writeAttribute('manualBreakCount', count($aRowBreaks));
819. foreach ($aRowBreaks as $cell) {
820. $coords = PHPExcel_Cell:: coordinateFromString($cell);
822. $objWriter->startElement('brk');
823. $objWriter->writeAttribute('id', $coords[1]);
824. $objWriter->writeAttribute('man', '1');
825. $objWriter->endElement();
826. }
828. $objWriter->endElement();
829. }
831. // Second, write column breaks
832. if ( count($aColumnBreaks) > 0) {
833. $objWriter->startElement('colBreaks');
834. $objWriter->writeAttribute('count', count($aColumnBreaks));
835. $objWriter->writeAttribute('manualBreakCount', count($aColumnBreaks));
837. foreach ($aColumnBreaks as $cell) {
838. $coords = PHPExcel_Cell:: coordinateFromString($cell);
840. $objWriter->startElement('brk');
841. $objWriter->writeAttribute('id', PHPExcel_Cell:: columnIndexFromString($coords[0]) - 1);
842. $objWriter->writeAttribute('man', '1');
843. $objWriter->endElement();
844. }
846. $objWriter->endElement();
847. }
848. }
850. /**
851. * Write SheetData
852. *
853. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
854. * @param PHPExcel_Worksheet $pSheet Worksheet
855. * @param string[] $pStringTable String table
856. * @throws Exception
857. */
858. private function _writeSheetData(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pStringTable = null)
859. {
860. if ( is_array($pStringTable)) {
861. // Flipped stringtable, for faster index searching
862. $aFlippedStringTable = $this-> getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
864. // sheetData
865. $objWriter->startElement('sheetData');
867. // Get column count
868. $colCount = PHPExcel_Cell:: columnIndexFromString($pSheet->getHighestColumn());
870. // Highest row number
871. $highestRow = $pSheet->getHighestRow();
873. // Loop through cells
874. $cellsByRow = array();
875. foreach ($pSheet->getCellCollection() as $cellID) {
876. $cellAddress = PHPExcel_Cell:: coordinateFromString($cellID);
877. $cellsByRow[$cellAddress[1]][] = $cellID;
878. }
880. $currentRow = 0;
881. while($currentRow++ < $highestRow) {
882. // Get row dimension
883. $rowDimension = $pSheet->getRowDimension($currentRow);
885. // Write current row?
886. $writeCurrentRow = isset($cellsByRow[$currentRow]) ||
887. $rowDimension->getRowHeight() >= 0 ||
888. $rowDimension->getVisible() == false ||
889. $rowDimension->getCollapsed() == true ||
890. $rowDimension->getOutlineLevel() > 0 ||
891. $rowDimension->getXfIndex() !== null;
893. if ($writeCurrentRow) {
894. // Start a new row
895. $objWriter->startElement('row');
896. $objWriter->writeAttribute('r', $currentRow);
897. $objWriter->writeAttribute('spans', '1:' . $colCount);
899. // Row dimensions
900. if ($rowDimension->getRowHeight() >= 0) {
901. $objWriter->writeAttribute('customHeight', '1');
902. $objWriter->writeAttribute('ht', PHPExcel_Shared_String:: FormatNumber($rowDimension->getRowHeight()));
903. }
905. // Row visibility
906. if ($rowDimension->getVisible() == false) {
907. $objWriter->writeAttribute('hidden', 'true');
908. }
910. // Collapsed
911. if ($rowDimension->getCollapsed() == true) {
912. $objWriter->writeAttribute('collapsed', 'true');
913. }
915. // Outline level
916. if ($rowDimension->getOutlineLevel() > 0) {
917. $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
918. }
920. // Style
921. if ($rowDimension->getXfIndex() !== null) {
922. $objWriter->writeAttribute('s', $rowDimension->getXfIndex());
923. $objWriter->writeAttribute('customFormat', '1');
924. }
926. // Write cells
927. if (isset($cellsByRow[$currentRow])) {
928. foreach($cellsByRow[$currentRow] as $cellAddress) {
929. // Write cell
930. $this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
931. }
932. }
934. // End row
935. $objWriter->endElement();
936. }
937. }
939. $objWriter->endElement();
940. } else {
941. throw new Exception("Invalid parameters passed.");
942. }
943. }
945. /**
946. * Write Cell
947. *
948. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
949. * @param PHPExcel_Worksheet $pSheet Worksheet
950. * @param PHPExcel_Cell $pCell Cell
951. * @param string[] $pStringTable String table
952. * @param string[] $pFlippedStringTable String table (flipped), for faster index searching
953. * @throws Exception
954. */
955. private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
956. {
957. if ( is_array($pStringTable) && is_array($pFlippedStringTable)) {
958. // Cell
959. $pCell = $pSheet->getCell($pCellAddress);
960. $objWriter->startElement('c');
961. $objWriter->writeAttribute('r', $pCellAddress);
963. // Sheet styles
964. if ($pCell->getXfIndex() != '') {
965. $objWriter->writeAttribute('s', $pCell->getXfIndex());
966. }
968. // If cell value is supplied, write cell value
969. $cellValue = $pCell->getValue();
970. if ( is_object($cellValue) || $cellValue !== '') {
971. // Map type
972. $mappedType = $pCell->getDataType();
974. // Write data type depending on its type
975. switch ( strtolower($mappedType)) {
976. case 'inlinestr': // Inline string
977. case 's': // String
978. case 'b': // Boolean
979. $objWriter->writeAttribute('t', $mappedType);
980. break;
981. case 'f': // Formula
982. $calculatedValue = null;
983. if ($this-> getParentWriter()->getPreCalculateFormulas()) {
984. $calculatedValue = $pCell->getCalculatedValue();
985. } else {
986. $calculatedValue = $cellValue;
987. }
988. if ( is_string($calculatedValue)) {
989. $objWriter->writeAttribute('t', 'str');
990. }
991. break;
992. case 'e': // Error
993. $objWriter->writeAttribute('t', $mappedType);
994. }
996. // Write data depending on its type
997. switch ( strtolower($mappedType)) {
998. case 'inlinestr': // Inline string
999. if (! $cellValue instanceof PHPExcel_RichText) {
1000. $objWriter->writeElement('t', PHPExcel_Shared_String:: ControlCharacterPHP2OOXML( htmlspecialchars($cellValue) ) );
1001. } else if ($cellValue instanceof PHPExcel_RichText) {
1002. $objWriter->startElement('is');
1003. $this-> getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $cellValue);
1004. $objWriter->endElement();
1005. }
1007. break;
1008. case 's': // String
1009. if (! $cellValue instanceof PHPExcel_RichText) {
1010. if (isset($pFlippedStringTable[$cellValue])) {
1011. $objWriter->writeElement('v', $pFlippedStringTable[$cellValue]);
1012. }
1013. } else if ($cellValue instanceof PHPExcel_RichText) {
1014. $objWriter->writeElement('v', $pFlippedStringTable[$cellValue->getHashCode()]);
1015. }
1017. break;
1018. case 'f': // Formula
1019. $attributes = $pCell->getFormulaAttributes();
1020. if($attributes['t'] == 'array') {
1021. $objWriter->startElement('f');
1022. $objWriter->writeAttribute('t', 'array');
1023. $objWriter->writeAttribute('ref', $pCellAddress);
1024. $objWriter->writeAttribute('aca', '1');
1025. $objWriter->writeAttribute('ca', '1');
1026. $objWriter->text( substr($cellValue, 1));
1027. $objWriter->endElement();
1028. } else {
1029. $objWriter->writeElement('f', substr($cellValue, 1));
1030. }
1031. if ($this-> getParentWriter()->getOffice2003Compatibility() === false) {
1032. if ($this-> getParentWriter()->getPreCalculateFormulas()) {
1033. $calculatedValue = $pCell->getCalculatedValue();
1034. if (! is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
1035. $objWriter->writeElement('v', PHPExcel_Shared_String:: FormatNumber($calculatedValue));
1036. } else {
1037. $objWriter->writeElement('v', '0');
1038. }
1039. } else {
1040. $objWriter->writeElement('v', '0');
1041. }
1042. }
1043. break;
1044. case 'n': // Numeric
1045. // force point as decimal separator in case current locale uses comma
1046. $objWriter->writeElement('v', str_replace(',', '.', $cellValue));
1047. break;
1048. case 'b': // Boolean
1049. $objWriter->writeElement('v', ($cellValue ? '1' : '0'));
1050. break;
1051. case 'e': // Error
1052. if ( substr($cellValue, 0, 1) == '=') {
1053. $objWriter->writeElement('f', substr($cellValue, 1));
1054. $objWriter->writeElement('v', substr($cellValue, 1));
1055. } else {
1056. $objWriter->writeElement('v', $cellValue);
1057. }
1059. break;
1060. }
1061. }
1063. $objWriter->endElement();
1064. } else {
1065. throw new Exception("Invalid parameters passed.");
1066. }
1067. }
1069. /**
1070. * Write Drawings
1071. *
1072. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
1073. * @param PHPExcel_Worksheet $pSheet Worksheet
1074. * @throws Exception
1075. */
1076. private function _writeDrawings(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
1077. {
1078. // If sheet contains drawings, add the relationships
1079. if ($pSheet->getDrawingCollection()->count() > 0) {
1080. $objWriter->startElement('drawing');
1081. $objWriter->writeAttribute('r:id', 'rId1');
1082. $objWriter->endElement();
1083. }
1084. }
1086. /**
1087. * Write LegacyDrawing
1088. *
1089. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
1090. * @param PHPExcel_Worksheet $pSheet Worksheet
1091. * @throws Exception
1092. */
1093. private function _writeLegacyDrawing(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
1094. {
1095. // If sheet contains comments, add the relationships
1096. if ( count($pSheet->getComments()) > 0) {
1097. $objWriter->startElement('legacyDrawing');
1098. $objWriter->writeAttribute('r:id', 'rId_comments_vml1');
1099. $objWriter->endElement();
1100. }
1101. }
1103. /**
1104. * Write LegacyDrawingHF
1105. *
1106. * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
1107. * @param PHPExcel_Worksheet $pSheet Worksheet
1108. * @throws Exception
1109. */
1110. private function _writeLegacyDrawingHF(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
1111. {
1112. // If sheet contains comments, add the relationships
1113. if ( count($pSheet->getHeaderFooter()->getImages()) > 0) {
1114. $objWriter->startElement('legacyDrawingHF');
1115. $objWriter->writeAttribute('r:id', 'rId_headerfooter_vml1');
1116. $objWriter->endElement();
1117. }
1118. }
1119. }

Documentation generated on Sun, 27 Feb 2011 16:37:26 -0800 by phpDocumentor 1.4.3