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();
}
