protected function _signupSheet($event, $includeEndingTerms, $includeNotEndingTerms) { $sheet = new PHPExcel_Worksheet($this->_excelDoc, 'Signup Sheet for Workshop ' . $event['workshopTitle']); // Set up the margins so the header doesn't bleed into the page $sheet->getPageMargins()->setTop(1.5); // Make a three column page layout $sheet->getColumnDimension('A')->setWidth(16); $sheet->getColumnDimension('B')->setWidth(16); $sheet->getColumnDimension('C')->setWidth(45); $config = new Zend_Config_Xml(APPLICATION_PATH . '/configs/config.xml', 'production'); $date = new DateTime($event['date']); $startTime = new DateTime($event['startTime']); $endTime = new DateTime($event['endTime']); // Set the header on odd pages. // The code formatting is off because the header doesn't ignore spaces. /* * Format: * Title * Room name * date('D, M d, Y') (startTime('g:i A') - endTime('g:i A')) * Instructors * */ $sheet->getHeaderFooter()->setOddHeader('&C&B&14' . $event['workshopTitle'] . '&14&B&12 ' . chr(10) . $event['location'] . chr(10) . $date->format('l, M d, Y') . '(' . $startTime->format('g:i A') . ' - ' . $endTime->format('g:i A') . ')' . chr(10) . 'Instructor: ' . implode(',', $event['instructors']) . '&12&C'); // Write Column Headers for the table $sheet->setCellValue('A1', 'First Name'); $sheet->setCellValue('B1', 'Last Name'); $sheet->setCellValue('C1', 'Signature'); // reformat it a little bit in a simpler way for us to use it in our // spreadsheet printin' loop $rows = array(); foreach ($event['attendeeList'] as $a) { $rows[] = array($a['firstName'], $a['lastName']); } $signin = new PHPExcel_Style(); $signin->applyFromArray(array('borders' => array('bottom' => array('style' => PHPExcel_Style_Border::BORDER_THIN)))); $rowCounter = 3; foreach ($rows as $row) { $row = array_values($row); // put the totals in the row $char = self::A; foreach ($row as $cell) { $sheet->setCellValue(chr($char) . $rowCounter, $cell); $char++; } $rowCounter++; } $tableHeaderStyle = new PHPExcel_Style(); $tableHeaderStyle->applyFromArray($this->_tableHeaderStyleArray); $tableBodyStyle = new PHPExcel_Style(); $tableBodyStyle->applyFromArray($this->_contentStyleArray); $sheet->setSharedStyle($tableHeaderStyle, 'A1:C1'); $sheet->setSharedStyle($tableBodyStyle, 'A3:B' . ($rowCounter - 1)); $sheet->setSharedStyle($signin, 'C3:C' . ($rowCounter - 1)); return $sheet; }
/** * Store the footer caption BIFF record. */ private function _writeFooter() { $record = 0x15; // Record identifier /* removing for now // need to fix character count (multibyte!) if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) { $str = $this->_phpSheet->getHeaderFooter()->getOddFooter(); } else { $str = ''; } */ $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter()); $length = strlen($recordData); $header = pack("vv", $record, $length); $this->_append($header . $recordData); }
// DDE links, OLE links // offset: 0; size: 2; 0x0000 // offset: 2; size: var; encoded source document name $this->_externalBooks[] = array('type' => 'DDEorOLE'); } } /** * Read EXTERNNAME record. */ private function _readExternName() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // external sheet references provided for named cells if ($this->_version == self::XLS_BIFF8) { // offset: 0; size: 2; options $options = self::_GetInt2d($recordData, 0); // offset: 2; size: 2; // offset: 4; size: 2; not used
/** * Write LegacyDrawingHF * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeLegacyDrawingHF(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // If sheet contains comments, add the relationships if (count($pSheet->getHeaderFooter()->getImages()) > 0) { $objWriter->startElement('legacyDrawingHF'); $objWriter->writeAttribute('r:id', 'rId_headerfooter_vml1'); $objWriter->endElement(); } }
/** * Write header/footer drawing relationships to XML format * * @param PHPExcel_Worksheet $pWorksheet * @return string XML Output * @throws Exception */ public function writeHeaderFooterDrawingRelationships(PHPExcel_Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Relationships $objWriter->startElement('Relationships'); $objWriter->writeAttribute('xmlns', 'http://schemas.openxmlformats.org/package/2006/relationships'); // Loop trough images and write relationships foreach ($pWorksheet->getHeaderFooter()->getImages() as $key => $value) { // Write relationship for image drawing $this->_writeRelationship($objWriter, $key, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/image', '../media/' . $value->getIndexedFilename()); } $objWriter->endElement(); // Return return $objWriter->getData(); }
/** * Write VML header/footer images to XML format * * @param PHPExcel_Worksheet $pWorksheet * @return string XML Output * @throws PHPExcel_Writer_Exception */ public function writeVMLHeaderFooterImages(PHPExcel_Worksheet $pWorksheet = null) { // Create XML writer $objWriter = null; if ($this->getParentWriter()->getUseDiskCaching()) { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory()); } else { $objWriter = new PHPExcel_Shared_XMLWriter(PHPExcel_Shared_XMLWriter::STORAGE_MEMORY); } // XML header $objWriter->startDocument('1.0', 'UTF-8', 'yes'); // Header/footer images $images = $pWorksheet->getHeaderFooter()->getImages(); // xml $objWriter->startElement('xml'); $objWriter->writeAttribute('xmlns:v', 'urn:schemas-microsoft-com:vml'); $objWriter->writeAttribute('xmlns:o', 'urn:schemas-microsoft-com:office:office'); $objWriter->writeAttribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel'); // o:shapelayout $objWriter->startElement('o:shapelayout'); $objWriter->writeAttribute('v:ext', 'edit'); // o:idmap $objWriter->startElement('o:idmap'); $objWriter->writeAttribute('v:ext', 'edit'); $objWriter->writeAttribute('data', '1'); $objWriter->endElement(); $objWriter->endElement(); // v:shapetype $objWriter->startElement('v:shapetype'); $objWriter->writeAttribute('id', '_x0000_t75'); $objWriter->writeAttribute('coordsize', '21600,21600'); $objWriter->writeAttribute('o:spt', '75'); $objWriter->writeAttribute('o:preferrelative', 't'); $objWriter->writeAttribute('path', 'm@4@5l@4@11@9@11@9@5xe'); $objWriter->writeAttribute('filled', 'f'); $objWriter->writeAttribute('stroked', 'f'); // v:stroke $objWriter->startElement('v:stroke'); $objWriter->writeAttribute('joinstyle', 'miter'); $objWriter->endElement(); // v:formulas $objWriter->startElement('v:formulas'); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'if lineDrawn pixelLineWidth 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @0 1 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum 0 0 @1'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @2 1 2'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @3 21600 pixelWidth'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @3 21600 pixelHeight'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @0 0 1'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @6 1 2'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @7 21600 pixelWidth'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @8 21600 0'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'prod @7 21600 pixelHeight'); $objWriter->endElement(); // v:f $objWriter->startElement('v:f'); $objWriter->writeAttribute('eqn', 'sum @10 21600 0'); $objWriter->endElement(); $objWriter->endElement(); // v:path $objWriter->startElement('v:path'); $objWriter->writeAttribute('o:extrusionok', 'f'); $objWriter->writeAttribute('gradientshapeok', 't'); $objWriter->writeAttribute('o:connecttype', 'rect'); $objWriter->endElement(); // o:lock $objWriter->startElement('o:lock'); $objWriter->writeAttribute('v:ext', 'edit'); $objWriter->writeAttribute('aspectratio', 't'); $objWriter->endElement(); $objWriter->endElement(); // Loop through images foreach ($images as $key => $value) { $this->_writeVMLHeaderFooterImage($objWriter, $key, $value); } $objWriter->endElement(); // Return return $objWriter->getData(); }
/** * Store the footer caption BIFF record. * * @access private */ function _storeFooter() { $record = 0x15; // Record identifier /* removing for now // need to fix character count (multibyte!) if (strlen($this->_phpSheet->getHeaderFooter()->getOddFooter()) <= 255) { $str = $this->_phpSheet->getHeaderFooter()->getOddFooter(); } else { $str = ''; } */ if ($this->_BIFF_version == 0x600) { $recordData = PHPExcel_Shared_String::UTF8toBIFF8UnicodeLong($this->_phpSheet->getHeaderFooter()->getOddFooter()); $length = strlen($recordData); } else { $cch = strlen($str); // Length of footer string $length = 1 + $cch; $data = pack("C", $cch); $recordData = $data . $str; } $header = pack("vv", $record, $length); $this->_prepend($header . $recordData); }
protected function setHeaderFooter(\PHPExcel_Worksheet $sheet) { $translator = $this->getTranslator(); $textRight = $translator->translate('Page') . ' &P / &N'; $sheet->getHeaderFooter()->setOddHeader('&L&16&G ' . $translator->translate($this->getTitle())); $sheet->getHeaderFooter()->setOddFooter('&R' . $textRight); }
/** * Read FOOTER record */ private function _readFooter() { $spos = $this->_pos; $length = $this->_GetInt2d($this->_data, $spos + 2); $recordData = substr($this->_data, $spos + 4, $length); $spos += 4; if (!$this->_readDataOnly) { // offset: 0; size: var // realized that $recordData can be empty even when record exists if ($recordData) { $string = $this->_readUnicodeStringLong($recordData); $this->_phpSheet->getHeaderFooter()->setOddFooter($string['value']); $this->_phpSheet->getHeaderFooter()->setEvenFooter($string['value']); } } $this->_pos += 4 + $length; }
/** * Set the properties of a worksheet * * @param PHPExcel_Worksheet $worksheet * @param string $WSKind kind of worksheet * @param array $DProps array with the document properties * @param string $thisDate timestamp of the execution of the script */ function setWorksheetProperties($worksheet, $wsKind, $DProps, $thisDate) { $worksheet->SetTitle($wsKind . ' ' . $thisDate); $worksheet->getTabColor()->setRGB($DProps[$wsKind]['FillColor']); // Set the printout options switch ($DProps[$wsKind]['PageSize']) { case 'A4': $page_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4; break; case 'A3': $page_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A3; break; case 'Letter': $page_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_LETTER; break; case 'Legal': $page_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_LEGAL; break; default: $page_size = PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4; break; } // Set orientation, fit to page width, paper size and page number to 1 $worksheet->getPageSetup()->setOrientation($DProps[$wsKind]['Orientation'])->setFitToPage(true)->setFitToWidth(1)->setFitToHeight(0)->setPaperSize($page_size)->setFirstPageNumber(1); // Set title of header and page number footer for printout $worksheet->getHeaderFooter()->setOddHeader($DProps[$wsKind]['Title'])->setEvenHeader($DProps[$wsKind]['Title'])->setOddFooter('&RPage &P of &N')->setEvenFooter('&RPage &P of &N'); // Set repeating header for most worksheets on the printout switch ($wsKind) { case 'Front Page': break; case 'DC Stats': case 'Rack Inventory': case 'DC Inventory': $worksheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 1); break; } }
/** * Write Header / Footer * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeHeaderFooter(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // headerFooter $objWriter->startElement('headerFooter'); $objWriter->writeAttribute('differentOddEven', $pSheet->getHeaderFooter()->getDifferentOddEven() ? 'true' : 'false'); $objWriter->writeAttribute('differentFirst', $pSheet->getHeaderFooter()->getDifferentFirst() ? 'true' : 'false'); $objWriter->writeAttribute('scaleWithDoc', $pSheet->getHeaderFooter()->getScaleWithDocument() ? 'true' : 'false'); $objWriter->writeAttribute('alignWithMargins', $pSheet->getHeaderFooter()->getAlignWithMargins() ? 'true' : 'false'); $objWriter->writeElement('oddHeader', $pSheet->getHeaderFooter()->getOddHeader()); $objWriter->writeElement('oddFooter', $pSheet->getHeaderFooter()->getOddFooter()); $objWriter->writeElement('evenHeader', $pSheet->getHeaderFooter()->getEvenHeader()); $objWriter->writeElement('evenFooter', $pSheet->getHeaderFooter()->getEvenFooter()); $objWriter->writeElement('firstHeader', $pSheet->getHeaderFooter()->getFirstHeader()); $objWriter->writeElement('firstFooter', $pSheet->getHeaderFooter()->getFirstFooter()); $objWriter->endElement(); }