/**
* Write VML comment to XML format
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param string $pCellReference Cell reference
* @param PHPExcel_Comment $pComment Comment
* @throws Exception
*/
public function _writeVMLComment(PHPExcel_Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', PHPExcel_Comment $pComment = null)
{
// Metadata
list($column, $row) = PHPExcel_Cell::coordinateFromString($pCellReference);
$column = PHPExcel_Cell::columnIndexFromString($column);
$id = 1024 + $column + $row;
$id = substr($id, 0, 4);
// v:shape
$objWriter->startElement('v:shape');
$objWriter->writeAttribute('id', '_x0000_s' . $id);
$objWriter->writeAttribute('type', '#_x0000_t202');
$objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $pComment->getWidth() . ';height:' . $pComment->getHeight() . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden'));
$objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB());
$objWriter->writeAttribute('o:insetmode', 'auto');
// v:fill
$objWriter->startElement('v:fill');
$objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB());
$objWriter->endElement();
// v:shadow
$objWriter->startElement('v:shadow');
$objWriter->writeAttribute('on', 't');
$objWriter->writeAttribute('color', 'black');
$objWriter->writeAttribute('obscured', 't');
$objWriter->endElement();
// v:path
$objWriter->startElement('v:path');
$objWriter->writeAttribute('o:connecttype', 'none');
$objWriter->endElement();
// v:textbox
$objWriter->startElement('v:textbox');
$objWriter->writeAttribute('style', 'mso-direction-alt:auto');
// div
$objWriter->startElement('div');
$objWriter->writeAttribute('style', 'text-align:left');
$objWriter->endElement();
$objWriter->endElement();
// x:ClientData
$objWriter->startElement('x:ClientData');
$objWriter->writeAttribute('ObjectType', 'Note');
// x:MoveWithCells
$objWriter->writeElement('x:MoveWithCells', '');
// x:SizeWithCells
$objWriter->writeElement('x:SizeWithCells', '');
// x:Anchor
//$objWriter->writeElement('x:Anchor', $column . ', 15, ' . ($row - 2) . ', 10, ' . ($column + 4) . ', 15, ' . ($row + 5) . ', 18');
// x:AutoFill
$objWriter->writeElement('x:AutoFill', 'False');
// x:Row
$objWriter->writeElement('x:Row', $row - 1);
// x:Column
$objWriter->writeElement('x:Column', $column - 1);
$objWriter->endElement();
$objWriter->endElement();
}
/**
* Write VML comment to XML format
*
* @param PHPExcel_Worksheet $pWorksheet
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param string $pCellReference Cell reference
* @param PHPExcel_Comment $pComment Comment
* @throws PHPExcel_Writer_Exception
*/
public function _writeVMLComment(PHPExcel_Worksheet $pWorksheet = null, PHPExcel_Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', PHPExcel_Comment $pComment = null)
{
// Metadata
list($column, $row) = PHPExcel_Cell::coordinateFromString($pCellReference);
$column = PHPExcel_Cell::columnIndexFromString($column);
$id = 1024 + $column + $row;
$id = substr($id, 0, 4);
$cssWidth = $pComment->getWidth();
$cssHeight = $pComment->getHeight();
// v:shape
$objWriter->startElement('v:shape');
$objWriter->writeAttribute('id', '_x0000_s' . $id);
$objWriter->writeAttribute('type', '#_x0000_t202');
$objWriter->writeAttribute('style', 'position:absolute;margin-left:' . $pComment->getMarginLeft() . ';margin-top:' . $pComment->getMarginTop() . ';width:' . $cssWidth . ';height:' . $cssHeight . ';z-index:1;visibility:' . ($pComment->getVisible() ? 'visible' : 'hidden'));
$objWriter->writeAttribute('fillcolor', '#' . $pComment->getFillColor()->getRGB());
$objWriter->writeAttribute('o:insetmode', 'auto');
// v:fill
$objWriter->startElement('v:fill');
$objWriter->writeAttribute('color2', '#' . $pComment->getFillColor()->getRGB());
$objWriter->endElement();
// v:shadow
$objWriter->startElement('v:shadow');
$objWriter->writeAttribute('on', 't');
$objWriter->writeAttribute('color', 'black');
$objWriter->writeAttribute('obscured', 't');
$objWriter->endElement();
// v:path
$objWriter->startElement('v:path');
$objWriter->writeAttribute('o:connecttype', 'none');
$objWriter->endElement();
// v:textbox
$objWriter->startElement('v:textbox');
$objWriter->writeAttribute('style', 'mso-direction-alt:auto');
// div
$objWriter->startElement('div');
$objWriter->writeAttribute('style', 'text-align:left');
$objWriter->endElement();
$objWriter->endElement();
// x:ClientData
$objWriter->startElement('x:ClientData');
$objWriter->writeAttribute('ObjectType', 'Note');
// x:MoveWithCells
$objWriter->writeElement('x:MoveWithCells', '');
// x:SizeWithCells
$objWriter->writeElement('x:SizeWithCells', '');
// x:Anchor
// anchor is a nice way to locate the comment sensibly with respect to the sheet's rows/columns, but
// in order to do so, need to be able to convert roughly to point dimensions from the comments
// width/height, which are optimized for css
if (preg_match('/\\s*pt\\s*$/', $cssWidth) && preg_match('/\\s*pt\\s*$/', $cssHeight)) {
// compute CSS height to an integer # pts
$width = intval(preg_replace('/\\s*pt\\s*$/', '', $cssWidth));
$height = intval(preg_replace('/\\s*pt\\s*$/', '', $cssHeight));
// starting from the row/column this is being placed in try
// to figure out the row column that should anchor the lower-right
// corner of the comment
$clearedWidth = 0;
$clearedHeight = 0;
$maxColumns = 2;
$maxRows = 10;
// loop incremenets, so decrement both to start
$curColumn = $column - 1;
$curRow = $row - 1;
while ($clearedWidth < $width && $curColumn - $column < $maxColumns) {
++$curColumn;
$dim = $pWorksheet->getColumnDimensionByColumn($curColumn, false);
$clearedWidth += $dim && $dim->getWidth() > 0 ? $dim->getWidth() : 96;
}
while ($clearedHeight < $height && $curRow - $row < $maxRows) {
++$curRow;
$dim = $pWorksheet->getRowDimension($curRow, false);
$clearedHeight += $dim && $dim->getRowHeight() > 0 ? $dim->getRowHeight() : 14;
}
$colBump = 15;
$rowBump = 10;
$anchor = $column . ', ' . $colBump . ', ' . ($row - 1) . ', ' . $rowBump . ', ' . $curColumn . ', ' . max($clearedWidth - $width, 0) . ', ' . $curRow . ', ' . max($clearedHeight - $height, 0);
// lower-right row offset
$objWriter->writeElement('x:Anchor', $anchor);
}
// x:AutoFill
$objWriter->writeElement('x:AutoFill', 'False');
// x:Row
$objWriter->writeElement('x:Row', $row - 1);
// x:Column
$objWriter->writeElement('x:Column', $column - 1);
$objWriter->endElement();
$objWriter->endElement();
}
/**
* Write comment to XML format
*
* @param PHPExcel_Shared_XMLWriter $objWriter XML Writer
* @param string $pCellReference Cell reference
* @param PHPExcel_Comment $pComment Comment
* @param array $pAuthors Array of authors
* @throws Exception
*/
public function _writeComment(PHPExcel_Shared_XMLWriter $objWriter = null, $pCellReference = 'A1', PHPExcel_Comment $pComment = null, $pAuthors = null)
{
// comment
$objWriter->startElement('comment');
$objWriter->writeAttribute('ref', $pCellReference);
$objWriter->writeAttribute('authorId', $pAuthors[$pComment->getAuthor()]);
// text
$objWriter->startElement('text');
$this->getParentWriter()->getWriterPart('stringtable')->writeRichText($objWriter, $pComment->getText());
$objWriter->endElement();
$objWriter->endElement();
}
private function applyCellCommentOptions(\PHPExcel_Comment $comment, array $options)
{
$setHeight = array_key_exists('height', $options) && is_numeric($options['height']) && $options['height'] > 0;
if ($setHeight) {
$height = $options['height'] . 'pt';
$comment->setHeight($height);
}
$setWidth = array_key_exists('width', $options) && is_numeric($options['width']) && $options['width'] > 0;
if ($setWidth) {
$width = $options['width'] . 'pt';
$comment->setWidth($width);
}
}
