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