1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29: 30: 31: 32: 33: 34: 35:
36: class PHPExcel_Writer_Excel2007_Worksheet extends PHPExcel_Writer_Excel2007_WriterPart
37: {
38: 39: 40: 41: 42: 43: 44: 45: 46:
47: public function writeWorksheet($pSheet = null, $pStringTable = null, $includeCharts = FALSE)
48: {
49: if (!is_null($pSheet)) {
50:
51: $objWriter = null;
52: if ($this->getParentWriter()->getUseDiskCaching()) {
53: $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
54: } else {
55: $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY);
56: }
57:
58:
59: $objWriter->startDocument('1.0','UTF-8','yes');
60:
61:
62: $objWriter->startElement('worksheet');
63: $objWriter->writeAttribute('xml:space', 'preserve');
64: $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
65: $objWriter->writeAttribute('xmlns:r', 'http://schemas.openxmlformats.org/officeDocument/2006/relationships');
66:
67:
68: $this->_writeSheetPr($objWriter, $pSheet);
69:
70:
71: $this->_writeDimension($objWriter, $pSheet);
72:
73:
74: $this->_writeSheetViews($objWriter, $pSheet);
75:
76:
77: $this->_writeSheetFormatPr($objWriter, $pSheet);
78:
79:
80: $this->_writeCols($objWriter, $pSheet);
81:
82:
83: $this->_writeSheetData($objWriter, $pSheet, $pStringTable);
84:
85:
86: $this->_writeSheetProtection($objWriter, $pSheet);
87:
88:
89: $this->_writeProtectedRanges($objWriter, $pSheet);
90:
91:
92: $this->_writeAutoFilter($objWriter, $pSheet);
93:
94:
95: $this->_writeMergeCells($objWriter, $pSheet);
96:
97:
98: $this->_writeConditionalFormatting($objWriter, $pSheet);
99:
100:
101: $this->_writeDataValidations($objWriter, $pSheet);
102:
103:
104: $this->_writeHyperlinks($objWriter, $pSheet);
105:
106:
107: $this->_writePrintOptions($objWriter, $pSheet);
108:
109:
110: $this->_writePageMargins($objWriter, $pSheet);
111:
112:
113: $this->_writePageSetup($objWriter, $pSheet);
114:
115:
116: $this->_writeHeaderFooter($objWriter, $pSheet);
117:
118:
119: $this->_writeBreaks($objWriter, $pSheet);
120:
121:
122: $this->_writeDrawings($objWriter, $pSheet, $includeCharts);
123:
124:
125: $this->_writeLegacyDrawing($objWriter, $pSheet);
126:
127:
128: $this->_writeLegacyDrawingHF($objWriter, $pSheet);
129:
130: $objWriter->endElement();
131:
132:
133: return $objWriter->getData();
134: } else {
135: throw new PHPExcel_Writer_Exception("Invalid PHPExcel_Worksheet object passed.");
136: }
137: }
138:
139: 140: 141: 142: 143: 144: 145:
146: private function _writeSheetPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
147: {
148:
149: $objWriter->startElement('sheetPr');
150:
151: if($pSheet->getParent()->hasMacros()){
152: if($pSheet->hasCodeName()==false){
153: $pSheet->setCodeName($pSheet->getTitle());
154: }
155: $objWriter->writeAttribute('codeName', $pSheet->getCodeName());
156: }
157: $autoFilterRange = $pSheet->getAutoFilter()->getRange();
158: if (!empty($autoFilterRange)) {
159: $objWriter->writeAttribute('filterMode', 1);
160: $pSheet->getAutoFilter()->showHideRows();
161: }
162:
163:
164: if ($pSheet->isTabColorSet()) {
165: $objWriter->startElement('tabColor');
166: $objWriter->writeAttribute('rgb', $pSheet->getTabColor()->getARGB());
167: $objWriter->endElement();
168: }
169:
170:
171: $objWriter->startElement('outlinePr');
172: $objWriter->writeAttribute('summaryBelow', ($pSheet->getShowSummaryBelow() ? '1' : '0'));
173: $objWriter->writeAttribute('summaryRight', ($pSheet->getShowSummaryRight() ? '1' : '0'));
174: $objWriter->endElement();
175:
176:
177: if ($pSheet->getPageSetup()->getFitToPage()) {
178: $objWriter->startElement('pageSetUpPr');
179: $objWriter->writeAttribute('fitToPage', '1');
180: $objWriter->endElement();
181: }
182:
183: $objWriter->endElement();
184: }
185:
186: 187: 188: 189: 190: 191: 192:
193: private function _writeDimension(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
194: {
195:
196: $objWriter->startElement('dimension');
197: $objWriter->writeAttribute('ref', $pSheet->calculateWorksheetDimension());
198: $objWriter->endElement();
199: }
200:
201: 202: 203: 204: 205: 206: 207:
208: private function _writeSheetViews(PHPExcel_Shared_XMLWriter $objWriter = NULL, PHPExcel_Worksheet $pSheet = NULL)
209: {
210:
211: $objWriter->startElement('sheetViews');
212:
213:
214: $sheetSelected = false;
215: if ($this->getParentWriter()->getPHPExcel()->getIndex($pSheet) == $this->getParentWriter()->getPHPExcel()->getActiveSheetIndex())
216: $sheetSelected = true;
217:
218:
219:
220: $objWriter->startElement('sheetView');
221: $objWriter->writeAttribute('tabSelected', $sheetSelected ? '1' : '0');
222: $objWriter->writeAttribute('workbookViewId', '0');
223:
224:
225: if ($pSheet->getSheetView()->getZoomScale() != 100) {
226: $objWriter->writeAttribute('zoomScale', $pSheet->getSheetView()->getZoomScale());
227: }
228: if ($pSheet->getSheetView()->getZoomScaleNormal() != 100) {
229: $objWriter->writeAttribute('zoomScaleNormal', $pSheet->getSheetView()->getZoomScaleNormal());
230: }
231:
232:
233: if ($pSheet->getSheetView()->getView() !== PHPExcel_Worksheet_SheetView::SHEETVIEW_NORMAL) {
234: $objWriter->writeAttribute('view', $pSheet->getSheetView()->getView());
235: }
236:
237:
238: if ($pSheet->getShowGridlines()) {
239: $objWriter->writeAttribute('showGridLines', 'true');
240: } else {
241: $objWriter->writeAttribute('showGridLines', 'false');
242: }
243:
244:
245: if ($pSheet->getShowRowColHeaders()) {
246: $objWriter->writeAttribute('showRowColHeaders', '1');
247: } else {
248: $objWriter->writeAttribute('showRowColHeaders', '0');
249: }
250:
251:
252: if ($pSheet->getRightToLeft()) {
253: $objWriter->writeAttribute('rightToLeft', 'true');
254: }
255:
256: $activeCell = $pSheet->getActiveCell();
257:
258:
259: $pane = '';
260: $topLeftCell = $pSheet->getFreezePane();
261: if (($topLeftCell != '') && ($topLeftCell != 'A1')) {
262: $activeCell = $topLeftCell;
263:
264: $xSplit = $ySplit = 0;
265:
266: list($xSplit, $ySplit) = PHPExcel_Cell::coordinateFromString($topLeftCell);
267: $xSplit = PHPExcel_Cell::columnIndexFromString($xSplit);
268:
269:
270: $pane = 'topRight';
271: $objWriter->startElement('pane');
272: if ($xSplit > 1)
273: $objWriter->writeAttribute('xSplit', $xSplit - 1);
274: if ($ySplit > 1) {
275: $objWriter->writeAttribute('ySplit', $ySplit - 1);
276: $pane = ($xSplit > 1) ? 'bottomRight' : 'bottomLeft';
277: }
278: $objWriter->writeAttribute('topLeftCell', $topLeftCell);
279: $objWriter->writeAttribute('activePane', $pane);
280: $objWriter->writeAttribute('state', 'frozen');
281: $objWriter->endElement();
282:
283: if (($xSplit > 1) && ($ySplit > 1)) {
284:
285: $objWriter->startElement('selection'); $objWriter->writeAttribute('pane', 'topRight'); $objWriter->endElement();
286: $objWriter->startElement('selection'); $objWriter->writeAttribute('pane', 'bottomLeft'); $objWriter->endElement();
287: }
288: }
289:
290:
291:
292:
293:
294: $objWriter->startElement('selection');
295: if ($pane != '') {
296: $objWriter->writeAttribute('pane', $pane);
297: }
298: $objWriter->writeAttribute('activeCell', $activeCell);
299: $objWriter->writeAttribute('sqref', $activeCell);
300: $objWriter->endElement();
301:
302:
303: $objWriter->endElement();
304:
305: $objWriter->endElement();
306: }
307:
308: 309: 310: 311: 312: 313: 314:
315: private function _writeSheetFormatPr(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
316: {
317:
318: $objWriter->startElement('sheetFormatPr');
319:
320:
321: if ($pSheet->getDefaultRowDimension()->getRowHeight() >= 0) {
322: $objWriter->writeAttribute('customHeight', 'true');
323: $objWriter->writeAttribute('defaultRowHeight', PHPExcel_Shared_String::FormatNumber($pSheet->getDefaultRowDimension()->getRowHeight()));
324: } else {
325: $objWriter->writeAttribute('defaultRowHeight', '14.4');
326: }
327:
328:
329: if ((string)$pSheet->getDefaultRowDimension()->getzeroHeight() == '1' ||
330: strtolower((string)$pSheet->getDefaultRowDimension()->getzeroHeight()) == 'true' ) {
331: $objWriter->writeAttribute('zeroHeight', '1');
332: }
333:
334:
335: if ($pSheet->getDefaultColumnDimension()->getWidth() >= 0) {
336: $objWriter->writeAttribute('defaultColWidth', PHPExcel_Shared_String::FormatNumber($pSheet->getDefaultColumnDimension()->getWidth()));
337: }
338:
339:
340: $outlineLevelRow = 0;
341: foreach ($pSheet->getRowDimensions() as $dimension) {
342: if ($dimension->getOutlineLevel() > $outlineLevelRow) {
343: $outlineLevelRow = $dimension->getOutlineLevel();
344: }
345: }
346: $objWriter->writeAttribute('outlineLevelRow', (int)$outlineLevelRow);
347:
348:
349: $outlineLevelCol = 0;
350: foreach ($pSheet->getColumnDimensions() as $dimension) {
351: if ($dimension->getOutlineLevel() > $outlineLevelCol) {
352: $outlineLevelCol = $dimension->getOutlineLevel();
353: }
354: }
355: $objWriter->writeAttribute('outlineLevelCol', (int)$outlineLevelCol);
356:
357: $objWriter->endElement();
358: }
359:
360: 361: 362: 363: 364: 365: 366:
367: private function _writeCols(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
368: {
369:
370: if (count($pSheet->getColumnDimensions()) > 0) {
371: $objWriter->startElement('cols');
372:
373: $pSheet->calculateColumnWidths();
374:
375:
376: foreach ($pSheet->getColumnDimensions() as $colDimension) {
377:
378: $objWriter->startElement('col');
379: $objWriter->writeAttribute('min', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
380: $objWriter->writeAttribute('max', PHPExcel_Cell::columnIndexFromString($colDimension->getColumnIndex()));
381:
382: if ($colDimension->getWidth() < 0) {
383:
384: $objWriter->writeAttribute('width', '9.10');
385: } else {
386:
387: $objWriter->writeAttribute('width', PHPExcel_Shared_String::FormatNumber($colDimension->getWidth()));
388: }
389:
390:
391: if ($colDimension->getVisible() == false) {
392: $objWriter->writeAttribute('hidden', 'true');
393: }
394:
395:
396: if ($colDimension->getAutoSize()) {
397: $objWriter->writeAttribute('bestFit', 'true');
398: }
399:
400:
401: if ($colDimension->getWidth() != $pSheet->getDefaultColumnDimension()->getWidth()) {
402: $objWriter->writeAttribute('customWidth', 'true');
403: }
404:
405:
406: if ($colDimension->getCollapsed() == true) {
407: $objWriter->writeAttribute('collapsed', 'true');
408: }
409:
410:
411: if ($colDimension->getOutlineLevel() > 0) {
412: $objWriter->writeAttribute('outlineLevel', $colDimension->getOutlineLevel());
413: }
414:
415:
416: $objWriter->writeAttribute('style', $colDimension->getXfIndex());
417:
418: $objWriter->endElement();
419: }
420:
421: $objWriter->endElement();
422: }
423: }
424:
425: 426: 427: 428: 429: 430: 431:
432: private function _writeSheetProtection(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
433: {
434:
435: $objWriter->startElement('sheetProtection');
436:
437: if ($pSheet->getProtection()->getPassword() != '') {
438: $objWriter->writeAttribute('password', $pSheet->getProtection()->getPassword());
439: }
440:
441: $objWriter->writeAttribute('sheet', ($pSheet->getProtection()->getSheet() ? 'true' : 'false'));
442: $objWriter->writeAttribute('objects', ($pSheet->getProtection()->getObjects() ? 'true' : 'false'));
443: $objWriter->writeAttribute('scenarios', ($pSheet->getProtection()->getScenarios() ? 'true' : 'false'));
444: $objWriter->writeAttribute('formatCells', ($pSheet->getProtection()->getFormatCells() ? 'true' : 'false'));
445: $objWriter->writeAttribute('formatColumns', ($pSheet->getProtection()->getFormatColumns() ? 'true' : 'false'));
446: $objWriter->writeAttribute('formatRows', ($pSheet->getProtection()->getFormatRows() ? 'true' : 'false'));
447: $objWriter->writeAttribute('insertColumns', ($pSheet->getProtection()->getInsertColumns() ? 'true' : 'false'));
448: $objWriter->writeAttribute('insertRows', ($pSheet->getProtection()->getInsertRows() ? 'true' : 'false'));
449: $objWriter->writeAttribute('insertHyperlinks', ($pSheet->getProtection()->getInsertHyperlinks() ? 'true' : 'false'));
450: $objWriter->writeAttribute('deleteColumns', ($pSheet->getProtection()->getDeleteColumns() ? 'true' : 'false'));
451: $objWriter->writeAttribute('deleteRows', ($pSheet->getProtection()->getDeleteRows() ? 'true' : 'false'));
452: $objWriter->writeAttribute('selectLockedCells', ($pSheet->getProtection()->getSelectLockedCells() ? 'true' : 'false'));
453: $objWriter->writeAttribute('sort', ($pSheet->getProtection()->getSort() ? 'true' : 'false'));
454: $objWriter->writeAttribute('autoFilter', ($pSheet->getProtection()->getAutoFilter() ? 'true' : 'false'));
455: $objWriter->writeAttribute('pivotTables', ($pSheet->getProtection()->getPivotTables() ? 'true' : 'false'));
456: $objWriter->writeAttribute('selectUnlockedCells', ($pSheet->getProtection()->getSelectUnlockedCells() ? 'true' : 'false'));
457: $objWriter->endElement();
458: }
459:
460: 461: 462: 463: 464: 465: 466:
467: private function _writeConditionalFormatting(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
468: {
469:
470: $id = 1;
471:
472:
473: foreach ($pSheet->getConditionalStylesCollection() as $cellCoordinate => $conditionalStyles) {
474: foreach ($conditionalStyles as $conditional) {
475:
476:
477:
478:
479: if ($conditional->getConditionType() != PHPExcel_Style_Conditional::CONDITION_NONE) {
480:
481: $objWriter->startElement('conditionalFormatting');
482: $objWriter->writeAttribute('sqref', $cellCoordinate);
483:
484:
485: $objWriter->startElement('cfRule');
486: $objWriter->writeAttribute('type', $conditional->getConditionType());
487: $objWriter->writeAttribute('dxfId', $this->getParentWriter()->getStylesConditionalHashTable()->getIndexForHashCode( $conditional->getHashCode() ));
488: $objWriter->writeAttribute('priority', $id++);
489:
490: if (($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS
491: ||
492: $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT)
493: && $conditional->getOperatorType() != PHPExcel_Style_Conditional::OPERATOR_NONE) {
494: $objWriter->writeAttribute('operator', $conditional->getOperatorType());
495: }
496:
497: if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
498: && !is_null($conditional->getText())) {
499: $objWriter->writeAttribute('text', $conditional->getText());
500: }
501:
502: if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
503: && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT
504: && !is_null($conditional->getText())) {
505: $objWriter->writeElement('formula', 'NOT(ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . ')))');
506: } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
507: && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_BEGINSWITH
508: && !is_null($conditional->getText())) {
509: $objWriter->writeElement('formula', 'LEFT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
510: } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
511: && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_ENDSWITH
512: && !is_null($conditional->getText())) {
513: $objWriter->writeElement('formula', 'RIGHT(' . $cellCoordinate . ',' . strlen($conditional->getText()) . ')="' . $conditional->getText() . '"');
514: } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
515: && $conditional->getOperatorType() == PHPExcel_Style_Conditional::OPERATOR_NOTCONTAINS
516: && !is_null($conditional->getText())) {
517: $objWriter->writeElement('formula', 'ISERROR(SEARCH("' . $conditional->getText() . '",' . $cellCoordinate . '))');
518: } else if ($conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CELLIS
519: || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT
520: || $conditional->getConditionType() == PHPExcel_Style_Conditional::CONDITION_EXPRESSION) {
521: foreach ($conditional->getConditions() as $formula) {
522:
523: $objWriter->writeElement('formula', $formula);
524: }
525: }
526:
527: $objWriter->endElement();
528:
529: $objWriter->endElement();
530: }
531: }
532: }
533: }
534:
535: 536: 537: 538: 539: 540: 541:
542: private function _writeDataValidations(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
543: {
544:
545: $dataValidationCollection = $pSheet->getDataValidationCollection();
546:
547:
548: if (!empty($dataValidationCollection)) {
549: $objWriter->startElement('dataValidations');
550: $objWriter->writeAttribute('count', count($dataValidationCollection));
551:
552: foreach ($dataValidationCollection as $coordinate => $dv) {
553: $objWriter->startElement('dataValidation');
554:
555: if ($dv->getType() != '') {
556: $objWriter->writeAttribute('type', $dv->getType());
557: }
558:
559: if ($dv->getErrorStyle() != '') {
560: $objWriter->writeAttribute('errorStyle', $dv->getErrorStyle());
561: }
562:
563: if ($dv->getOperator() != '') {
564: $objWriter->writeAttribute('operator', $dv->getOperator());
565: }
566:
567: $objWriter->writeAttribute('allowBlank', ($dv->getAllowBlank() ? '1' : '0'));
568: $objWriter->writeAttribute('showDropDown', (!$dv->getShowDropDown() ? '1' : '0'));
569: $objWriter->writeAttribute('showInputMessage', ($dv->getShowInputMessage() ? '1' : '0'));
570: $objWriter->writeAttribute('showErrorMessage', ($dv->getShowErrorMessage() ? '1' : '0'));
571:
572: if ($dv->getErrorTitle() !== '') {
573: $objWriter->writeAttribute('errorTitle', $dv->getErrorTitle());
574: }
575: if ($dv->getError() !== '') {
576: $objWriter->writeAttribute('error', $dv->getError());
577: }
578: if ($dv->getPromptTitle() !== '') {
579: $objWriter->writeAttribute('promptTitle', $dv->getPromptTitle());
580: }
581: if ($dv->getPrompt() !== '') {
582: $objWriter->writeAttribute('prompt', $dv->getPrompt());
583: }
584:
585: $objWriter->writeAttribute('sqref', $coordinate);
586:
587: if ($dv->getFormula1() !== '') {
588: $objWriter->writeElement('formula1', $dv->getFormula1());
589: }
590: if ($dv->getFormula2() !== '') {
591: $objWriter->writeElement('formula2', $dv->getFormula2());
592: }
593:
594: $objWriter->endElement();
595: }
596:
597: $objWriter->endElement();
598: }
599: }
600:
601: 602: 603: 604: 605: 606: 607:
608: private function _writeHyperlinks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
609: {
610:
611: $hyperlinkCollection = $pSheet->getHyperlinkCollection();
612:
613:
614: $relationId = 1;
615:
616:
617: if (!empty($hyperlinkCollection)) {
618: $objWriter->startElement('hyperlinks');
619:
620: foreach ($hyperlinkCollection as $coordinate => $hyperlink) {
621: $objWriter->startElement('hyperlink');
622:
623: $objWriter->writeAttribute('ref', $coordinate);
624: if (!$hyperlink->isInternal()) {
625: $objWriter->writeAttribute('r:id', 'rId_hyperlink_' . $relationId);
626: ++$relationId;
627: } else {
628: $objWriter->writeAttribute('location', str_replace('sheet://', '', $hyperlink->getUrl()));
629: }
630:
631: if ($hyperlink->getTooltip() != '') {
632: $objWriter->writeAttribute('tooltip', $hyperlink->getTooltip());
633: }
634:
635: $objWriter->endElement();
636: }
637:
638: $objWriter->endElement();
639: }
640: }
641:
642: 643: 644: 645: 646: 647: 648:
649: private function _writeProtectedRanges(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
650: {
651: if (count($pSheet->getProtectedCells()) > 0) {
652:
653: $objWriter->startElement('protectedRanges');
654:
655:
656: foreach ($pSheet->getProtectedCells() as $protectedCell => $passwordHash) {
657:
658: $objWriter->startElement('protectedRange');
659: $objWriter->writeAttribute('name', 'p' . md5($protectedCell));
660: $objWriter->writeAttribute('sqref', $protectedCell);
661: if (!empty($passwordHash)) {
662: $objWriter->writeAttribute('password', $passwordHash);
663: }
664: $objWriter->endElement();
665: }
666:
667: $objWriter->endElement();
668: }
669: }
670:
671: 672: 673: 674: 675: 676: 677:
678: private function _writeMergeCells(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
679: {
680: if (count($pSheet->getMergeCells()) > 0) {
681:
682: $objWriter->startElement('mergeCells');
683:
684:
685: foreach ($pSheet->getMergeCells() as $mergeCell) {
686:
687: $objWriter->startElement('mergeCell');
688: $objWriter->writeAttribute('ref', $mergeCell);
689: $objWriter->endElement();
690: }
691:
692: $objWriter->endElement();
693: }
694: }
695:
696: 697: 698: 699: 700: 701: 702:
703: private function _writePrintOptions(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
704: {
705:
706: $objWriter->startElement('printOptions');
707:
708: $objWriter->writeAttribute('gridLines', ($pSheet->getPrintGridlines() ? 'true': 'false'));
709: $objWriter->writeAttribute('gridLinesSet', 'true');
710:
711: if ($pSheet->getPageSetup()->getHorizontalCentered()) {
712: $objWriter->writeAttribute('horizontalCentered', 'true');
713: }
714:
715: if ($pSheet->getPageSetup()->getVerticalCentered()) {
716: $objWriter->writeAttribute('verticalCentered', 'true');
717: }
718:
719: $objWriter->endElement();
720: }
721:
722: 723: 724: 725: 726: 727: 728:
729: private function _writePageMargins(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
730: {
731:
732: $objWriter->startElement('pageMargins');
733: $objWriter->writeAttribute('left', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getLeft()));
734: $objWriter->writeAttribute('right', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getRight()));
735: $objWriter->writeAttribute('top', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getTop()));
736: $objWriter->writeAttribute('bottom', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getBottom()));
737: $objWriter->writeAttribute('header', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getHeader()));
738: $objWriter->writeAttribute('footer', PHPExcel_Shared_String::FormatNumber($pSheet->getPageMargins()->getFooter()));
739: $objWriter->endElement();
740: }
741:
742: 743: 744: 745: 746: 747: 748:
749: private function _writeAutoFilter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
750: {
751: $autoFilterRange = $pSheet->getAutoFilter()->getRange();
752: if (!empty($autoFilterRange)) {
753:
754: $objWriter->startElement('autoFilter');
755:
756:
757: $range = PHPExcel_Cell::splitRange($autoFilterRange);
758: $range = $range[0];
759:
760: if (strpos($range[0],'!') !== false) {
761: list($ws,$range[0]) = explode('!',$range[0]);
762: }
763: $range = implode(':', $range);
764:
765: $objWriter->writeAttribute('ref', str_replace('$','',$range));
766:
767: $columns = $pSheet->getAutoFilter()->getColumns();
768: if (count($columns > 0)) {
769: foreach($columns as $columnID => $column) {
770: $rules = $column->getRules();
771: if (count($rules > 0)) {
772: $objWriter->startElement('filterColumn');
773: $objWriter->writeAttribute('colId', $pSheet->getAutoFilter()->getColumnOffset($columnID));
774:
775: $objWriter->startElement( $column->getFilterType());
776: if ($column->getJoin() == PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_COLUMN_JOIN_AND) {
777: $objWriter->writeAttribute('and', 1);
778: }
779:
780: foreach ($rules as $rule) {
781: if (($column->getFilterType() === PHPExcel_Worksheet_AutoFilter_Column::AUTOFILTER_FILTERTYPE_FILTER) &&
782: ($rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL) &&
783: ($rule->getValue() === '')) {
784:
785: $objWriter->writeAttribute('blank', 1);
786: } elseif($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DYNAMICFILTER) {
787:
788: $objWriter->writeAttribute('type', $rule->getGrouping());
789: $val = $column->getAttribute('val');
790: if ($val !== NULL) {
791: $objWriter->writeAttribute('val', $val);
792: }
793: $maxVal = $column->getAttribute('maxVal');
794: if ($maxVal !== NULL) {
795: $objWriter->writeAttribute('maxVal', $maxVal);
796: }
797: } elseif($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_TOPTENFILTER) {
798:
799: $objWriter->writeAttribute('val', $rule->getValue());
800: $objWriter->writeAttribute('percent', (($rule->getOperator() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_PERCENT) ? '1' : '0'));
801: $objWriter->writeAttribute('top', (($rule->getGrouping() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_TOPTEN_TOP) ? '1': '0'));
802: } else {
803:
804: $objWriter->startElement($rule->getRuleType());
805:
806: if ($rule->getOperator() !== PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_COLUMN_RULE_EQUAL) {
807: $objWriter->writeAttribute('operator', $rule->getOperator());
808: }
809: if ($rule->getRuleType() === PHPExcel_Worksheet_AutoFilter_Column_Rule::AUTOFILTER_RULETYPE_DATEGROUP) {
810:
811: foreach($rule->getValue() as $key => $value) {
812: if ($value > '') $objWriter->writeAttribute($key, $value);
813: }
814: $objWriter->writeAttribute('dateTimeGrouping', $rule->getGrouping());
815: } else {
816: $objWriter->writeAttribute('val', $rule->getValue());
817: }
818:
819: $objWriter->endElement();
820: }
821: }
822:
823: $objWriter->endElement();
824:
825: $objWriter->endElement();
826: }
827: }
828: }
829:
830: $objWriter->endElement();
831: }
832: }
833:
834: 835: 836: 837: 838: 839: 840:
841: private function _writePageSetup(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
842: {
843:
844: $objWriter->startElement('pageSetup');
845: $objWriter->writeAttribute('paperSize', $pSheet->getPageSetup()->getPaperSize());
846: $objWriter->writeAttribute('orientation', $pSheet->getPageSetup()->getOrientation());
847:
848: if (!is_null($pSheet->getPageSetup()->getScale())) {
849: $objWriter->writeAttribute('scale', $pSheet->getPageSetup()->getScale());
850: }
851: if (!is_null($pSheet->getPageSetup()->getFitToHeight())) {
852: $objWriter->writeAttribute('fitToHeight', $pSheet->getPageSetup()->getFitToHeight());
853: } else {
854: $objWriter->writeAttribute('fitToHeight', '0');
855: }
856: if (!is_null($pSheet->getPageSetup()->getFitToWidth())) {
857: $objWriter->writeAttribute('fitToWidth', $pSheet->getPageSetup()->getFitToWidth());
858: } else {
859: $objWriter->writeAttribute('fitToWidth', '0');
860: }
861: if (!is_null($pSheet->getPageSetup()->getFirstPageNumber())) {
862: $objWriter->writeAttribute('firstPageNumber', $pSheet->getPageSetup()->getFirstPageNumber());
863: $objWriter->writeAttribute('useFirstPageNumber', '1');
864: }
865:
866: $objWriter->endElement();
867: }
868:
869: 870: 871: 872: 873: 874: 875:
876: private function _writeHeaderFooter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
877: {
878:
879: $objWriter->startElement('headerFooter');
880: $objWriter->writeAttribute('differentOddEven', ($pSheet->getHeaderFooter()->getDifferentOddEven() ? 'true' : 'false'));
881: $objWriter->writeAttribute('differentFirst', ($pSheet->getHeaderFooter()->getDifferentFirst() ? 'true' : 'false'));
882: $objWriter->writeAttribute('scaleWithDoc', ($pSheet->getHeaderFooter()->getScaleWithDocument() ? 'true' : 'false'));
883: $objWriter->writeAttribute('alignWithMargins', ($pSheet->getHeaderFooter()->getAlignWithMargins() ? 'true' : 'false'));
884:
885: $objWriter->writeElement('oddHeader', $pSheet->getHeaderFooter()->getOddHeader());
886: $objWriter->writeElement('oddFooter', $pSheet->getHeaderFooter()->getOddFooter());
887: $objWriter->writeElement('evenHeader', $pSheet->getHeaderFooter()->getEvenHeader());
888: $objWriter->writeElement('evenFooter', $pSheet->getHeaderFooter()->getEvenFooter());
889: $objWriter->writeElement('firstHeader', $pSheet->getHeaderFooter()->getFirstHeader());
890: $objWriter->writeElement('firstFooter', $pSheet->getHeaderFooter()->getFirstFooter());
891: $objWriter->endElement();
892: }
893:
894: 895: 896: 897: 898: 899: 900:
901: private function _writeBreaks(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
902: {
903:
904: $aRowBreaks = array();
905: $aColumnBreaks = array();
906: foreach ($pSheet->getBreaks() as $cell => $breakType) {
907: if ($breakType == PHPExcel_Worksheet::BREAK_ROW) {
908: $aRowBreaks[] = $cell;
909: } else if ($breakType == PHPExcel_Worksheet::BREAK_COLUMN) {
910: $aColumnBreaks[] = $cell;
911: }
912: }
913:
914:
915: if (!empty($aRowBreaks)) {
916: $objWriter->startElement('rowBreaks');
917: $objWriter->writeAttribute('count', count($aRowBreaks));
918: $objWriter->writeAttribute('manualBreakCount', count($aRowBreaks));
919:
920: foreach ($aRowBreaks as $cell) {
921: $coords = PHPExcel_Cell::coordinateFromString($cell);
922:
923: $objWriter->startElement('brk');
924: $objWriter->writeAttribute('id', $coords[1]);
925: $objWriter->writeAttribute('man', '1');
926: $objWriter->endElement();
927: }
928:
929: $objWriter->endElement();
930: }
931:
932:
933: if (!empty($aColumnBreaks)) {
934: $objWriter->startElement('colBreaks');
935: $objWriter->writeAttribute('count', count($aColumnBreaks));
936: $objWriter->writeAttribute('manualBreakCount', count($aColumnBreaks));
937:
938: foreach ($aColumnBreaks as $cell) {
939: $coords = PHPExcel_Cell::coordinateFromString($cell);
940:
941: $objWriter->startElement('brk');
942: $objWriter->writeAttribute('id', PHPExcel_Cell::columnIndexFromString($coords[0]) - 1);
943: $objWriter->writeAttribute('man', '1');
944: $objWriter->endElement();
945: }
946:
947: $objWriter->endElement();
948: }
949: }
950:
951: 952: 953: 954: 955: 956: 957: 958:
959: private function _writeSheetData(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pStringTable = null)
960: {
961: if (is_array($pStringTable)) {
962:
963: $aFlippedStringTable = $this->getParentWriter()->getWriterPart('stringtable')->flipStringTable($pStringTable);
964:
965:
966: $objWriter->startElement('sheetData');
967:
968:
969: $colCount = PHPExcel_Cell::columnIndexFromString($pSheet->getHighestColumn());
970:
971:
972: $highestRow = $pSheet->getHighestRow();
973:
974:
975: $cellsByRow = array();
976: foreach ($pSheet->getCellCollection() as $cellID) {
977: $cellAddress = PHPExcel_Cell::coordinateFromString($cellID);
978: $cellsByRow[$cellAddress[1]][] = $cellID;
979: }
980:
981: $currentRow = 0;
982: while($currentRow++ < $highestRow) {
983:
984: $rowDimension = $pSheet->getRowDimension($currentRow);
985:
986:
987: $writeCurrentRow = isset($cellsByRow[$currentRow]) ||
988: $rowDimension->getRowHeight() >= 0 ||
989: $rowDimension->getVisible() == false ||
990: $rowDimension->getCollapsed() == true ||
991: $rowDimension->getOutlineLevel() > 0 ||
992: $rowDimension->getXfIndex() !== null;
993:
994: if ($writeCurrentRow) {
995:
996: $objWriter->startElement('row');
997: $objWriter->writeAttribute('r', $currentRow);
998: $objWriter->writeAttribute('spans', '1:' . $colCount);
999:
1000:
1001: if ($rowDimension->getRowHeight() >= 0) {
1002: $objWriter->writeAttribute('customHeight', '1');
1003: $objWriter->writeAttribute('ht', PHPExcel_Shared_String::FormatNumber($rowDimension->getRowHeight()));
1004: }
1005:
1006:
1007: if ($rowDimension->getVisible() == false) {
1008: $objWriter->writeAttribute('hidden', 'true');
1009: }
1010:
1011:
1012: if ($rowDimension->getCollapsed() == true) {
1013: $objWriter->writeAttribute('collapsed', 'true');
1014: }
1015:
1016:
1017: if ($rowDimension->getOutlineLevel() > 0) {
1018: $objWriter->writeAttribute('outlineLevel', $rowDimension->getOutlineLevel());
1019: }
1020:
1021:
1022: if ($rowDimension->getXfIndex() !== null) {
1023: $objWriter->writeAttribute('s', $rowDimension->getXfIndex());
1024: $objWriter->writeAttribute('customFormat', '1');
1025: }
1026:
1027:
1028: if (isset($cellsByRow[$currentRow])) {
1029: foreach($cellsByRow[$currentRow] as $cellAddress) {
1030:
1031: $this->_writeCell($objWriter, $pSheet, $cellAddress, $pStringTable, $aFlippedStringTable);
1032: }
1033: }
1034:
1035:
1036: $objWriter->endElement();
1037: }
1038: }
1039:
1040: $objWriter->endElement();
1041: } else {
1042: throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
1043: }
1044: }
1045:
1046: 1047: 1048: 1049: 1050: 1051: 1052: 1053: 1054: 1055:
1056: private function _writeCell(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $pCellAddress = null, $pStringTable = null, $pFlippedStringTable = null)
1057: {
1058: if (is_array($pStringTable) && is_array($pFlippedStringTable)) {
1059:
1060: $pCell = $pSheet->getCell($pCellAddress);
1061: $objWriter->startElement('c');
1062: $objWriter->writeAttribute('r', $pCellAddress);
1063:
1064:
1065: if ($pCell->getXfIndex() != '') {
1066: $objWriter->writeAttribute('s', $pCell->getXfIndex());
1067: }
1068:
1069:
1070: $cellValue = $pCell->getValue();
1071: if (is_object($cellValue) || $cellValue !== '') {
1072:
1073: $mappedType = $pCell->getDataType();
1074:
1075:
1076: switch (strtolower($mappedType)) {
1077: case 'inlinestr':
1078: case 's':
1079: case 'b':
1080: $objWriter->writeAttribute('t', $mappedType);
1081: break;
1082: case 'f':
1083: $calculatedValue = ($this->getParentWriter()->getPreCalculateFormulas()) ?
1084: $pCell->getCalculatedValue() :
1085: $cellValue;
1086: if (is_string($calculatedValue)) {
1087: $objWriter->writeAttribute('t', 'str');
1088: }
1089: break;
1090: case 'e':
1091: $objWriter->writeAttribute('t', $mappedType);
1092: }
1093:
1094:
1095: switch (strtolower($mappedType)) {
1096: case 'inlinestr':
1097: if (! $cellValue instanceof PHPExcel_RichText) {
1098: $objWriter->writeElement('t', PHPExcel_Shared_String::ControlCharacterPHP2OOXML( htmlspecialchars($cellValue) ) );
1099: } else if ($cellValue instanceof PHPExcel_RichText) {
1100: $objWriter->startElement('is');
1101: $this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $cellValue);
1102: $objWriter->endElement();
1103: }
1104:
1105: break;
1106: case 's':
1107: if (! $cellValue instanceof PHPExcel_RichText) {
1108: if (isset($pFlippedStringTable[$cellValue])) {
1109: $objWriter->writeElement('v', $pFlippedStringTable[$cellValue]);
1110: }
1111: } else if ($cellValue instanceof PHPExcel_RichText) {
1112: $objWriter->writeElement('v', $pFlippedStringTable[$cellValue->getHashCode()]);
1113: }
1114:
1115: break;
1116: case 'f':
1117: $attributes = $pCell->getFormulaAttributes();
1118: if($attributes['t'] == 'array') {
1119: $objWriter->startElement('f');
1120: $objWriter->writeAttribute('t', 'array');
1121: $objWriter->writeAttribute('ref', $pCellAddress);
1122: $objWriter->writeAttribute('aca', '1');
1123: $objWriter->writeAttribute('ca', '1');
1124: $objWriter->text(substr($cellValue, 1));
1125: $objWriter->endElement();
1126: } else {
1127: $objWriter->writeElement('f', substr($cellValue, 1));
1128: }
1129: if ($this->getParentWriter()->getOffice2003Compatibility() === false) {
1130: if ($this->getParentWriter()->getPreCalculateFormulas()) {
1131:
1132: if (!is_array($calculatedValue) && substr($calculatedValue, 0, 1) != '#') {
1133: $objWriter->writeElement('v', PHPExcel_Shared_String::FormatNumber($calculatedValue));
1134: } else {
1135: $objWriter->writeElement('v', '0');
1136: }
1137: } else {
1138: $objWriter->writeElement('v', '0');
1139: }
1140: }
1141: break;
1142: case 'n':
1143:
1144: $objWriter->writeElement('v', str_replace(',', '.', $cellValue));
1145: break;
1146: case 'b':
1147: $objWriter->writeElement('v', ($cellValue ? '1' : '0'));
1148: break;
1149: case 'e':
1150: if (substr($cellValue, 0, 1) == '=') {
1151: $objWriter->writeElement('f', substr($cellValue, 1));
1152: $objWriter->writeElement('v', substr($cellValue, 1));
1153: } else {
1154: $objWriter->writeElement('v', $cellValue);
1155: }
1156:
1157: break;
1158: }
1159: }
1160:
1161: $objWriter->endElement();
1162: } else {
1163: throw new PHPExcel_Writer_Exception("Invalid parameters passed.");
1164: }
1165: }
1166:
1167: 1168: 1169: 1170: 1171: 1172: 1173: 1174:
1175: private function _writeDrawings(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null, $includeCharts = FALSE)
1176: {
1177: $chartCount = ($includeCharts) ? $pSheet->getChartCollection()->count() : 0;
1178:
1179: if (($pSheet->getDrawingCollection()->count() > 0) ||
1180: ($chartCount > 0)) {
1181: $objWriter->startElement('drawing');
1182: $objWriter->writeAttribute('r:id', 'rId1');
1183: $objWriter->endElement();
1184: }
1185: }
1186:
1187: 1188: 1189: 1190: 1191: 1192: 1193:
1194: private function _writeLegacyDrawing(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
1195: {
1196:
1197: if (count($pSheet->getComments()) > 0) {
1198: $objWriter->startElement('legacyDrawing');
1199: $objWriter->writeAttribute('r:id', 'rId_comments_vml1');
1200: $objWriter->endElement();
1201: }
1202: }
1203:
1204: 1205: 1206: 1207: 1208: 1209: 1210:
1211: private function _writeLegacyDrawingHF(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null)
1212: {
1213:
1214: if (count($pSheet->getHeaderFooter()->getImages()) > 0) {
1215: $objWriter->startElement('legacyDrawingHF');
1216: $objWriter->writeAttribute('r:id', 'rId_headerfooter_vml1');
1217: $objWriter->endElement();
1218: }
1219: }
1220: }
1221: