/**
* Update cell comments when inserting/deleting rows/columns
*
* @param Worksheet $pSheet The worksheet that we're editing
* @param string $pBefore Insert/Delete before this cell address (e.g. 'A1')
* @param integer $beforeColumnIndex Index number of the column we're inserting/deleting before
* @param integer $pNumCols Number of columns to insert/delete (negative values indicate deletion)
* @param integer $beforeRow Number of the row we're inserting/deleting before
* @param integer $pNumRows Number of rows to insert/delete (negative values indicate deletion)
*/
protected function adjustComments($pSheet, $pBefore, $beforeColumnIndex, $pNumCols, $beforeRow, $pNumRows)
{
$aComments = $pSheet->getComments();
$aNewComments = array();
// the new array of all comments
foreach ($aComments as $key => &$value) {
// Any comments inside a deleted range will be ignored
if (!self::cellAddressInDeleteRange($key, $beforeRow, $pNumRows, $beforeColumnIndex, $pNumCols)) {
// Otherwise build a new array of comments indexed by the adjusted cell reference
$newReference = $this->updateCellReference($key, $pBefore, $pNumCols, $pNumRows);
$aNewComments[$newReference] = $value;
}
}
// Replace the comments array with the new set of comments
$pSheet->setComments($aNewComments);
}
/**
* Write worksheet relationships to XML format
*
* Numbering is as follows:
* rId1 - Drawings
* rId_hyperlink_x - Hyperlinks
*
* @param Worksheet $pWorksheet
* @param int $pWorksheetId
* @return string XML Output
* @throws Exception
*/
public function writeWorksheetRelationships(Worksheet $pWorksheet = null, $pWorksheetId = 1)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new Shared_XMLWriter(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');
// Write drawing relationships?
if ($pWorksheet->getDrawingCollection()->count() > 0) {
$this->_writeRelationship($objWriter, 1, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing', '../drawings/drawing' . $pWorksheetId . '.xml');
}
// Write hyperlink relationships?
$i = 1;
foreach ($pWorksheet->getHyperlinkCollection() as $hyperlink) {
if (!$hyperlink->isInternal()) {
$this->_writeRelationship($objWriter, '_hyperlink_' . $i, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink', $hyperlink->getUrl(), 'External');
++$i;
}
}
// Write comments relationship?
$i = 1;
if (count($pWorksheet->getComments()) > 0) {
$this->_writeRelationship($objWriter, '_comments_vml' . $i, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing', '../drawings/vmlDrawing' . $pWorksheetId . '.vml');
$this->_writeRelationship($objWriter, '_comments' . $i, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments', '../comments' . $pWorksheetId . '.xml');
}
// Write header/footer relationship?
$i = 1;
if (count($pWorksheet->getHeaderFooter()->getImages()) > 0) {
$this->_writeRelationship($objWriter, '_headerfooter_vml' . $i, 'http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing', '../drawings/vmlDrawingHF' . $pWorksheetId . '.vml');
}
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Write VML comments to XML format
*
* @param Worksheet $pWorksheet
* @return string XML Output
* @throws Exception
*/
public function writeVMLComments(Worksheet $pWorksheet = null)
{
// Create XML writer
$objWriter = null;
if ($this->getParentWriter()->getUseDiskCaching()) {
$objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_DISK, $this->getParentWriter()->getDiskCachingDirectory());
} else {
$objWriter = new Shared_XMLWriter(Shared_XMLWriter::STORAGE_MEMORY);
}
// XML header
$objWriter->startDocument('1.0', 'UTF-8', 'yes');
// Comments cache
$comments = $pWorksheet->getComments();
// 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_t202');
$objWriter->writeAttribute('coordsize', '21600,21600');
$objWriter->writeAttribute('o:spt', '202');
$objWriter->writeAttribute('path', 'm,l,21600r21600,l21600,xe');
// v:stroke
$objWriter->startElement('v:stroke');
$objWriter->writeAttribute('joinstyle', 'miter');
$objWriter->endElement();
// v:path
$objWriter->startElement('v:path');
$objWriter->writeAttribute('gradientshapeok', 't');
$objWriter->writeAttribute('o:connecttype', 'rect');
$objWriter->endElement();
$objWriter->endElement();
// Loop through comments
foreach ($comments as $key => $value) {
$this->_writeVMLComment($objWriter, $key, $value);
}
$objWriter->endElement();
// Return
return $objWriter->getData();
}
/**
* Write LegacyDrawing
*
* @param Shared_XMLWriter $objWriter XML Writer
* @param Worksheet $pSheet Worksheet
* @throws Exception
*/
private function _writeLegacyDrawing(Shared_XMLWriter $objWriter = null, Worksheet $pSheet = null)
{
// If sheet contains comments, add the relationships
if (count($pSheet->getComments()) > 0) {
$objWriter->startElement('legacyDrawing');
$objWriter->writeAttribute('r:id', 'rId_comments_vml1');
$objWriter->endElement();
}
}
