/** * @return void */ public function testRegisterStyleShouldUpdateId() { $style1 = (new StyleBuilder())->setFontBold()->build(); $style2 = (new StyleBuilder())->setFontUnderline()->build(); $this->assertEquals(0, $this->defaultStyle->getId(), 'Default style ID should be 0'); $this->assertNull($style1->getId()); $this->assertNull($style2->getId()); $styleHelper = new StyleHelper($this->defaultStyle); $registeredStyle1 = $styleHelper->registerStyle($style1); $registeredStyle2 = $styleHelper->registerStyle($style2); $this->assertEquals(1, $registeredStyle1->getId()); $this->assertEquals(2, $registeredStyle2->getId()); }
/** * Register a border definition * * @param \Box\Spout\Writer\Style\Style $style */ protected function registerBorder($style) { $styleId = $style->getId(); if ($style->shouldApplyBorder()) { $border = $style->getBorder(); $serializedBorder = serialize($border); $isBorderAlreadyRegistered = isset($this->registeredBorders[$serializedBorder]); if ($isBorderAlreadyRegistered) { $registeredStyleId = $this->registeredBorders[$serializedBorder]; $registeredBorderId = $this->styleIdToBorderMappingTable[$registeredStyleId]; $this->styleIdToBorderMappingTable[$styleId] = $registeredBorderId; } else { $this->registeredBorders[$serializedBorder] = $styleId; $this->styleIdToBorderMappingTable[$styleId] = count($this->registeredBorders); } } else { // If no border should be applied - the mapping is the default border: 0 $this->styleIdToBorderMappingTable[$styleId] = 0; } }
/** * Adds data to the worksheet. * * @param array $dataRow Array containing data to be written. Cannot be empty. * Example $dataRow = ['data1', 1234, null, '', 'data5']; * @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. * @return void * @throws \Box\Spout\Common\Exception\IOException If the data cannot be written * @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported */ public function addRow($dataRow, $style) { $cellNumber = 0; $rowIndex = $this->lastWrittenRowIndex + 1; $numCells = count($dataRow); $rowXML = '<row r="' . $rowIndex . '" spans="1:' . $numCells . '">'; foreach ($dataRow as $cellValue) { $columnIndex = CellHelper::getCellIndexFromColumnIndex($cellNumber); $cellXML = '<c r="' . $columnIndex . $rowIndex . '"'; $cellXML .= ' s="' . $style->getId() . '"'; if (CellHelper::isNonEmptyString($cellValue)) { // CFDB EDIT BEGIN: Special case added to handle HYPERLINK functions // this IF wrapping exiting code in ELSE $matches = array(); if (preg_match('/=HYPERLINK\\("(.*)","(.*)"\\)/', $cellValue, $matches)) { // Create a Formula $url = $this->stringsEscaper->escape($matches[1]); $text = $this->stringsEscaper->escape($matches[2]); $formula = sprintf('HYPERLINK("%s","%s")', $url, $text); $cellXML = sprintf('<c r="%s%s" t="str"><f>%s</f><v>%s</v></c>', $columnIndex, $rowIndex, $formula, $text); } else { // CFDB EDIT END if ($this->shouldUseInlineStrings) { $cellXML .= ' t="inlineStr"><is><t>' . $this->stringsEscaper->escape($cellValue) . '</t></is></c>'; } else { $sharedStringId = $this->sharedStringsHelper->writeString($cellValue); $cellXML .= ' t="s"><v>' . $sharedStringId . '</v></c>'; } } // CFDB EDIT this line } else { if (CellHelper::isBoolean($cellValue)) { $cellXML .= ' t="b"><v>' . $cellValue . '</v></c>'; } else { if (CellHelper::isNumeric($cellValue)) { $cellXML .= '><v>' . $cellValue . '</v></c>'; } else { if (empty($cellValue)) { // don't write empty cells (not appending to $cellXML is the right behavior!) $cellXML = ''; } else { throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . gettype($cellValue)); } } } } $rowXML .= $cellXML; $cellNumber++; } $rowXML .= '</row>'; $wasWriteSuccessful = fwrite($this->sheetFilePointer, $rowXML); if ($wasWriteSuccessful === false) { throw new IOException("Unable to write data in {$this->worksheetFilePath}"); } // only update the count if the write worked $this->lastWrittenRowIndex++; }
/** * Adds data to the worksheet. * * @param array $dataRow Array containing data to be written. * Example $dataRow = ['data1', 1234, null, '', 'data5']; * @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. * @return void * @throws \Box\Spout\Common\Exception\IOException If the data cannot be written * @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported */ public function addRow($dataRow, $style) { $cellNumber = 0; $rowIndex = $this->lastWrittenRowIndex + 1; $numCells = count($dataRow); $data = ' <row r="' . $rowIndex . '" spans="1:' . $numCells . '">' . PHP_EOL; foreach ($dataRow as $cellValue) { $columnIndex = CellHelper::getCellIndexFromColumnIndex($cellNumber); $data .= ' <c r="' . $columnIndex . $rowIndex . '"'; $data .= ' s="' . $style->getId() . '"'; if (CellHelper::isNonEmptyString($cellValue)) { if ($this->shouldUseInlineStrings) { $data .= ' t="inlineStr"><is><t>' . $this->stringsEscaper->escape($cellValue) . '</t></is></c>' . PHP_EOL; } else { $sharedStringId = $this->sharedStringsHelper->writeString($cellValue); $data .= ' t="s"><v>' . $sharedStringId . '</v></c>' . PHP_EOL; } } else { if (CellHelper::isNumeric($cellValue) || CellHelper::isBoolean($cellValue)) { $data .= '><v>' . $cellValue . '</v></c>' . PHP_EOL; } else { if (empty($cellValue)) { $data .= '/>' . PHP_EOL; } else { throw new InvalidArgumentException('Trying to add a value with an unsupported type: ' . gettype($cellValue)); } } } $cellNumber++; } $data .= ' </row>' . PHP_EOL; $wasWriteSuccessful = fwrite($this->sheetFilePointer, $data); if ($wasWriteSuccessful === false) { throw new IOException("Unable to write data in {$this->worksheetFilePath}"); } // only update the count if the write worked $this->lastWrittenRowIndex++; }
/** * Adds data to the worksheet. * * @param array $dataRow Array containing data to be written. Cannot be empty. * Example $dataRow = ['data1', 1234, null, '', 'data5']; * @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. * @return void * @throws \Box\Spout\Common\Exception\IOException If the data cannot be written * @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported */ public function addRow($dataRow, $style) { // $dataRow can be an associative array. We need to transform // it into a regular array, as we'll use the numeric indexes. $dataRowWithNumericIndexes = array_values($dataRow); $styleIndex = $style->getId() + 1; // 1-based $cellsCount = count($dataRow); $this->maxNumColumns = max($this->maxNumColumns, $cellsCount); $data = '<table:table-row table:style-name="ro1">'; $currentCellIndex = 0; $nextCellIndex = 1; for ($i = 0; $i < $cellsCount; $i++) { $currentCellValue = $dataRowWithNumericIndexes[$currentCellIndex]; // Using isset here because it is way faster than array_key_exists... if (!isset($dataRowWithNumericIndexes[$nextCellIndex]) || $currentCellValue !== $dataRowWithNumericIndexes[$nextCellIndex]) { $numTimesValueRepeated = $nextCellIndex - $currentCellIndex; $data .= $this->getCellContent($currentCellValue, $styleIndex, $numTimesValueRepeated); $currentCellIndex = $nextCellIndex; } $nextCellIndex++; } $data .= '</table:table-row>'; $wasWriteSuccessful = fwrite($this->sheetFilePointer, $data); if ($wasWriteSuccessful === false) { throw new IOException("Unable to write data in {$this->worksheetFilePath}"); } // only update the count if the write worked $this->lastWrittenRowIndex++; }
/** * Returns the contents of the "<style:style>" section, inside "<office:automatic-styles>" section * * @param \Box\Spout\Writer\Style\Style $style * @return string */ protected function getStyleSectionContent($style) { $defaultStyle = $this->getDefaultStyle(); $styleIndex = $style->getId() + 1; // 1-based $content = '<style:style style:data-style-name="N0" style:family="table-cell" style:name="ce' . $styleIndex . '" style:parent-style-name="Default">'; if ($style->shouldApplyFont()) { $content .= '<style:text-properties'; $fontColor = $style->getFontColor(); if ($fontColor !== $defaultStyle->getFontColor()) { $content .= ' fo:color="#' . $fontColor . '"'; } $fontName = $style->getFontName(); if ($fontName !== $defaultStyle->getFontName()) { $content .= ' style:font-name="' . $fontName . '" style:font-name-asian="' . $fontName . '" style:font-name-complex="' . $fontName . '"'; } $fontSize = $style->getFontSize(); if ($fontSize !== $defaultStyle->getFontSize()) { $content .= ' fo:font-size="' . $fontSize . 'pt" style:font-size-asian="' . $fontSize . 'pt" style:font-size-complex="' . $fontSize . 'pt"'; } if ($style->isFontBold()) { $content .= ' fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"'; } if ($style->isFontItalic()) { $content .= ' fo:font-style="italic" style:font-style-asian="italic" style:font-style-complex="italic"'; } if ($style->isFontUnderline()) { $content .= ' style:text-underline-style="solid" style:text-underline-type="single"'; } if ($style->isFontStrikethrough()) { $content .= ' style:text-line-through-style="solid"'; } $content .= '/>'; } if ($style->shouldWrapText()) { $content .= '<style:table-cell-properties fo:wrap-option="wrap" style:vertical-align="automatic"/>'; } if ($style->shouldApplyBorder()) { $borderProperty = '<style:table-cell-properties %s />'; $borders = array_map(function (BorderPart $borderPart) { return BorderHelper::serializeBorderPart($borderPart); }, $style->getBorder()->getParts()); $content .= sprintf($borderProperty, implode(' ', $borders)); } if ($style->shouldApplyBackgroundColor()) { $content .= sprintf(' <style:table-cell-properties fo:background-color="#%s"/>', $style->getBackgroundColor()); } $content .= '</style:style>'; return $content; }
/** * Returns the contents of the background color definition for the "<style:table-cell-properties>" section * * @param \Box\Spout\Writer\Style\Style $style * @return string */ private function getBackgroundColorXMLContent($style) { return sprintf('<style:table-cell-properties fo:background-color="#%s"/>', $style->getBackgroundColor()); }
/** * Set the "wrap text" option if a cell of the given row contains a new line. * * @NOTE: There is a bug on the Mac version of Excel (2011 and below) where new lines * are ignored even when the "wrap text" option is set. This only occurs with * inline strings (shared strings do work fine). * A workaround would be to encode "\n" as "_x000D_" but it does not work * on the Windows version of Excel... * * @param \Box\Spout\Writer\Style\Style $style The original style * @param array $dataRow The row the style will be applied to * @return \Box\Spout\Writer\Style\Style The eventually updated style */ protected function applyWrapTextIfCellContainsNewLine($style, $dataRow) { // if the "wrap text" option is already set, no-op if ($style->hasSetWrapText()) { return $style; } foreach ($dataRow as $cell) { if (is_string($cell) && strpos($cell, "\n") !== false) { $style->setShouldWrapText(); break; } } return $style; }
/** * Sets the style to be applied to the next written rows * until it is changed or reset. * * @param Style\Style $style * @return void */ private function setRowStyle($style) { // Merge given style with the default one to inherit custom properties $this->rowStyle = $style->mergeWith($this->defaultRowStyle); }
/** * Returns the contents of the "<style:style>" section, inside "<office:automatic-styles>" section * * @param \Box\Spout\Writer\Style\Style $style * @return string */ protected function getStyleSectionContent($style) { $defaultStyle = $this->getDefaultStyle(); $styleIndex = $style->getId() + 1; // 1-based $content = '<style:style style:data-style-name="N0" style:family="table-cell" style:name="ce' . $styleIndex . '" style:parent-style-name="Default">'; if ($style->shouldApplyFont()) { $content .= '<style:text-properties'; $fontColor = $style->getFontColor(); if ($fontColor !== $defaultStyle->getFontColor()) { $content .= ' fo:color="#' . $fontColor . '"'; } $fontName = $style->getFontName(); if ($fontName !== $defaultStyle->getFontName()) { $content .= ' style:font-name="' . $fontName . '" style:font-name-asian="' . $fontName . '" style:font-name-complex="' . $fontName . '"'; } $fontSize = $style->getFontSize(); if ($fontSize !== $defaultStyle->getFontSize()) { $content .= ' fo:font-size="' . $fontSize . 'pt" style:font-size-asian="' . $fontSize . 'pt" style:font-size-complex="' . $fontSize . 'pt"'; } if ($style->isFontBold()) { $content .= ' fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"'; } if ($style->isFontItalic()) { $content .= ' fo:font-style="italic" style:font-style-asian="italic" style:font-style-complex="italic"'; } if ($style->isFontUnderline()) { $content .= ' style:text-underline-style="solid" style:text-underline-type="single"'; } if ($style->isFontStrikethrough()) { $content .= ' style:text-line-through-style="solid"'; } $content .= '/>'; } if ($style->shouldWrapText()) { $content .= '<style:table-cell-properties fo:wrap-option="wrap" style:vertical-align="automatic"/>'; } $content .= '</style:style>'; return $content; }
/** * Adds data to the worksheet. * * @param array $dataRow Array containing data to be written. Cannot be empty. * Example $dataRow = ['data1', 1234, null, '', 'data5']; * @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. * @return void * @throws \Box\Spout\Common\Exception\IOException If the data cannot be written * @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported */ public function addRow($dataRow, $style) { $styleIndex = $style->getId() + 1; // 1-based $cellsCount = count($dataRow); $this->maxNumColumns = max($this->maxNumColumns, $cellsCount); $data = '<table:table-row table:style-name="ro1">'; $currentCellIndex = 0; $nextCellIndex = 1; for ($i = 0; $i < $cellsCount; $i++) { $currentCellValue = $dataRow[$currentCellIndex]; if (!array_key_exists($nextCellIndex, $dataRow) || $currentCellValue !== $dataRow[$nextCellIndex]) { $numTimesValueRepeated = $nextCellIndex - $currentCellIndex; $data .= $this->getCellContent($currentCellValue, $styleIndex, $numTimesValueRepeated); $currentCellIndex = $nextCellIndex; } $nextCellIndex++; } $data .= '</table:table-row>'; $wasWriteSuccessful = fwrite($this->sheetFilePointer, $data); if ($wasWriteSuccessful === false) { throw new IOException("Unable to write data in {$this->worksheetFilePath}"); } // only update the count if the write worked $this->lastWrittenRowIndex++; }
/** * Adds data to the worksheet. * * @param array $dataRow Array containing data to be written. Cannot be empty. * Example $dataRow = ['data1', 1234, null, '', 'data5']; * @param \Box\Spout\Writer\Style\Style $style Style to be applied to the row. NULL means use default style. * @return void * @throws \Box\Spout\Common\Exception\IOException If the data cannot be written * @throws \Box\Spout\Common\Exception\InvalidArgumentException If a cell value's type is not supported */ public function addRow($dataRow, $style) { $cellNumber = 0; $rowIndex = $this->lastWrittenRowIndex + 1; $numCells = count($dataRow); $rowXML = '<row r="' . $rowIndex . '" spans="1:' . $numCells . '">'; foreach ($dataRow as $cellValue) { $rowXML .= $this->getCellXML($rowIndex, $cellNumber, $cellValue, $style->getId()); $cellNumber++; } $rowXML .= '</row>'; $wasWriteSuccessful = fwrite($this->sheetFilePointer, $rowXML); if ($wasWriteSuccessful === false) { throw new IOException("Unable to write data in {$this->worksheetFilePath}"); } // only update the count if the write worked $this->lastWrittenRowIndex++; }
/** * @param $formName string * @param $options array of option_name => option_value * @return void */ public function export($formName, $options = null) { $this->setOptions($options); $this->setCommonOptions(); // Security Check if (!$this->isAuthorized()) { $this->assertSecurityErrorMessage(); return; } if (version_compare(phpversion(), '5.4') < 0) { $this->echoHeaders(array('Content-Type: text/html')); printf('<html><head><title>%s</title></head>', __('PHP Upgrade Needed', 'contact-form-7-to-database-extension')); _e('CFDB Excel file export requires PHP 5.4 or later on your server.', 'contact-form-7-to-database-extension'); echo '<br/>'; _e('Your server\'s PHP version: ', 'contact-form-7-to-database-extension'); echo phpversion(); echo '<br/>'; printf('<a href="https://wordpress.org/about/requirements/">%s</a>', __('See WordPress Recommended PHP Version', 'contact-form-7-to-database-extension')); printf('</body></html'); return; } require_once 'Spout-2.4.2/Autoloader/autoload.php'; // required PHP 5.4 // Query DB for the data for that form $submitTimeKeyName = 'Submit_Time_Key'; $this->setDataIterator($formName, $submitTimeKeyName); $this->clearOutputBuffer(); $type = Type::XLSX; $suffix = 'xlsx'; if (isset($options['format'])) { switch ($options['format']) { case 'ods': $type = Type::ODS; $suffix = 'ods'; break; // case 'csv' : // $type = Type::CSV; // $suffix = 'csv'; // break; // case 'csv' : // $type = Type::CSV; // $suffix = 'csv'; // break; default: break; } } $writer = WriterFactory::create($type); $writer->openToBrowser("{$formName}.{$suffix}"); // stream data directly to the browser // Column Headers if (isset($this->options['header']) && $this->options['header'] != 'true') { // do not output column headers } else { $headerRow = array(); foreach ($this->dataIterator->getDisplayColumns() as $aCol) { $colDisplayValue = $aCol; if ($this->headers && isset($this->headers[$aCol])) { $colDisplayValue = $this->headers[$aCol]; } $headerRow[] = $colDisplayValue; } $headerStyle = new Style(); $headerStyle->setFontBold(); $writer->addRowWithStyle($headerRow, $headerStyle); // add a row at a time } // Rows // $showFileUrlsInExport = $this->plugin->getOption('ShowFileUrlsInExport') == 'true'; while ($this->dataIterator->nextRow()) { $dataRow = array(); $fields_with_file = null; if (isset($this->dataIterator->row['fields_with_file']) && $this->dataIterator->row['fields_with_file'] != null) { $fields_with_file = explode(',', $this->dataIterator->row['fields_with_file']); } foreach ($this->dataIterator->getDisplayColumns() as $aCol) { $cell = isset($this->dataIterator->row[$aCol]) ? $this->dataIterator->row[$aCol] : ''; if ($aCol == 'Submitted' && isset($this->dataIterator->row[$submitTimeKeyName])) { // Put date in a format that Excel et. al. understand $timestamp = $this->dataIterator->row[$submitTimeKeyName]; $cell = date('Y-m-d H:i:s', $timestamp); } if ($fields_with_file && $cell && in_array($aCol, $fields_with_file)) { // In the case of file links, we want to create a HYPERLINK formula as a link to download the file. // But the Spout library doesn't support creating formulas. // http://cfdbplugin.com/?p=1430 $url = $this->plugin->getFileUrl($this->dataIterator->row[$submitTimeKeyName], $formName, $aCol); if ($type == Type::ODS) { $cell = "=HYPERLINK(\"{$url}\"; \"{$cell}\")"; } else { $cell = "=HYPERLINK(\"{$url}\", \"{$cell}\")"; } } $dataRow[] = $cell; } $writer->addRow($dataRow); // add a row at a time } $writer->close(); }