phpword create multiple object link to different sheets in same excel file

Question

I create multiple object which link to different sheets from same excel file using phpword template processor. I have create customized function in template processor.php to create object link to sheet.

 public function setObject($search,$fileInfo,$id){
    $xmlWriter = new XMLWriter();
    /*
        fileInfo array
        name->''
        sheet->''
        range->':'
    */
    /*
    id array
    objectId->
    rIdObject->
    rIdImage->
    */
    $file= $fileInfo['name'];
    $fileNameParts = explode('.', $file);
    $ext = end($fileNameParts); 
    if($ext=='xls'){
        $progId='Excel.Sheet.8';
    }
    elseif($ext=='xlsx'){
        $progId='Excel.Sheet.12';
    }
    else{
        $progId='Package';
    }
    $sheet=preg_replace("/\\\\/","/",$file).'!'.$fileInfo['sheet'].'!'.$fileInfo['range'];
    //relationship excel file
    $xmlExcObjectRelation = "<Relationship Id=\"{$id["rIdObject"]}\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject\" Target=\"$sheet\" TargetMode=\"External\"/>";
    $this->tempDocumentRelations[$this->getMainPartName()] = str_replace('</Relationships>', $xmlExcObjectRelation, $this->tempDocumentRelations[$this->getMainPartName()]) . '</Relationships>';
    //relationship thumbnail excel file
    $this->zipClass->pclzipAddFile('vendor/phpoffice/phpword/src/PhpWord/resources/xls.png', 'word/media/' . 'xls.png');
    $xmliconRelation="<Relationship Id=\"{$id["rIdImage"]}\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/image\" Target=\"media/xls.png\"/>";            
    $this->tempDocumentRelations[$this->getMainPartName()] = str_replace('</Relationships>', $xmliconRelation, $this->tempDocumentRelations[$this->getMainPartName()]) . '</Relationships>';
    
    //register content type
    if(strpos($this->tempDocumentContentTypes, "xls") === false){
        $excelXlsContentType="<Default Extension=\"xls\" ContentType=\"application/vnd.ms-excel\"/>";
        $this->tempDocumentContentTypes = str_replace('</Types>', $excelXlsContentType , $this->tempDocumentContentTypes).'</Types>';        
    }
    if(strpos($this->tempDocumentContentTypes, "xlsx") === false){
        $excelXlsxContentType="<Default Extension=\"xlsx\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\"/>";
        $this->tempDocumentContentTypes = str_replace('</Types>',$excelXlsxContentType , $this->tempDocumentContentTypes).'</Types>';
    }
    if(strpos($this->tempDocumentContentTypes, "png") === false){
        $excelIconContentType="<Default Extension=\"png\" ContentType=\"image/png\"/>";
        $this->tempDocumentContentTypes = str_replace('</Types>', $excelIconContentType , $this->tempDocumentContentTypes).'</Types>';
    }
    
    
    $xmlWriter->startElement('w:r');
    $xmlWriter->startElement('w:object');
    $xmlWriter->writeAttribute('w:dxaOrig', '7247');
    $xmlWriter->writeAttribute('w:dyaOrig', '2920');

    //custom - add (v:shapetype)
    $xmlWriter->startElement('v:shapetype');
    $xmlWriter->writeAttribute('stroked','f');
    $xmlWriter->writeAttribute('filled','f');
    $xmlWriter->writeAttribute('path','m@4@5l@4@11@9@11@9@5xe');
    $xmlWriter->writeAttribute('o:preferrelative','t');
    $xmlWriter->writeAttribute('o:spt',75);
    $xmlWriter->writeAttribute('coordsize','21600,21600');
    $xmlWriter->writeAttribute('id','_x0000_t75');
    
    
        $xmlWriter->startElement('v:stroke');
        $xmlWriter->writeAttribute('joinstyle','miter');
        $xmlWriter->endElement();// v:stroke
    
        $xmlWriter->startElement('v:formulas');
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','if lineDrawn pixelLineWidth 0');
            $xmlWriter->endElement();// v:f 1
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','sum @0 1 0');
            $xmlWriter->endElement();// v:f 2   
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','sum 0 0 @1');
            $xmlWriter->endElement();// v:f 3   
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @2 1 2');
            $xmlWriter->endElement();// v:f4
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @3 21600 pixelWidth');
            $xmlWriter->endElement();// v:f5    
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @3 21600 pixelHeight');
            $xmlWriter->endElement();// v:f6
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','sum @0 0 1');
            $xmlWriter->endElement();// v:f7
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @6 1 2');
            $xmlWriter->endElement();// v:f8
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @7 21600 pixelWidth');
            $xmlWriter->endElement();// v:f9
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','sum @8 21600 0');
            $xmlWriter->endElement();// v:f10
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','prod @7 21600 pixelHeight');
            $xmlWriter->endElement();// v:f11
            $xmlWriter->startElement('v:f');
            $xmlWriter->writeAttribute('eqn','sum @10 21600 0');
            $xmlWriter->endElement();// v:f12
        $xmlWriter->endElement();// v:formulas  
        $xmlWriter->startElement('v:path');
        $xmlWriter->writeAttribute('o:connecttype','rect');
        $xmlWriter->writeAttribute('gradientshapeok','t');
        $xmlWriter->writeAttribute('o:extrusionok','f');
        $xmlWriter->endElement();// v:path
        $xmlWriter->startElement('o:lock');
        $xmlWriter->writeAttribute('aspectratio','t');
        $xmlWriter->writeAttribute('v:ext','edit');
        $xmlWriter->endElement();// o:lock
    $xmlWriter->endElement();// v:shapetype
    // Icon
    $xmlWriter->startElement('v:shape');
    $xmlWriter->writeAttribute('id', '_x0000_i1025');
    $xmlWriter->writeAttribute('type', '#_x0000_t75');
    $xmlWriter->writeAttribute('style', 'width:362.25pt;height:146.25pt');
    $xmlWriter->writeAttribute('o:ole', '');

    $xmlWriter->startElement('v:imagedata');
    $xmlWriter->writeAttribute('r:id', $id['rIdImage']);
    $xmlWriter->writeAttribute('o:title', '');
    $xmlWriter->endElement(); // v:imagedata
    $xmlWriter->endElement(); // v:shape

    // Object
    $xmlWriter->startElement('o:OLEObject');
    $xmlWriter->writeAttribute('Type', 'Link');
    
    $xmlWriter->writeAttribute('ProgID', $progId);//package to Excel.Sheet.12 if object is excel xlsx, Excel.Sheet.12 of object is excel xls
    $xmlWriter->writeAttribute('ShapeID', '_x0000_i1025');
    $xmlWriter->writeAttribute('DrawAspect', 'Content');
    $xmlWriter->writeAttribute('ObjectID',$id['objectId']);
    $xmlWriter->writeAttribute('r:id',  $id['rIdObject']);
    $xmlWriter->endElement(); // o:OLEObject

    $xmlWriter->endElement(); // w:object
    $xmlWriter->endElement(); // w:r
    
    $this->replaceXmlBlock($search, '<w:p>' . $xmlWriter->getData() . '</w:p>', 'w:p');
}

Then , I replace variable in template2.docx with excel object and generate docx file

<?php
 require '../vendor/autoload.php';
 $templateProcessor = new \PhpOffice\PhpWord\TemplateProcessor('template2.docx');
 $fileInfo  =[
                        'name' => dirname(__FILE__).'\file2.xlsx',
                        'sheet'=> 'sheet1',
                        'range'=> 'R1C1:'.'R5C5'];
 $id        =[
                        'objectId'  => 'O_F1_1', //Object_File2_sheet1
                        'rIdObject' => 'RO_F1_1',//rIdObject_File2_sheet1
                        'rIdImage'  => 'RI_F1_1' //rIdImage_File2_sheet1
                        ];
$templateProcessor->setObject('sheet1',$fileInfo,$id);
$fileInfo2  =[

                         'name' => dirname(__FILE__).'\file2.xlsx',
                         'sheet'=> 'sheet2',
                         'range'=> 'R1C1:'.'R5C5'];
$id2            =[
                        'objectId'  => 'O_F1_2', //Object_File2_sheet2
                        'rIdObject' => 'RO_F1_2',//rIdObject_File2_sheet2
                        'rIdImage'  => 'RI_F1_2' //rIdImage_File2_sheet2 
                        ];
$templateProcessor->setObject('sheet2',$fileInfo2,$id2);
$fileInfo3  =[

                         'name' => dirname(__FILE__).'\file1.xlsx',
                         'sheet'=> 'sheet3',
                         'range'=> 'R1C1:'.'R5C5'];
$id3            =[
                        'objectId'  => 'O_F1_3', //Object_File1_sheet3
                        'rIdObject' => 'RO_F1_3',//rIdObject_File1_sheet3
                        'rIdImage'  => 'RI_F1_3' //rIdImage_File1_sheet3
                        ];
$templateProcessor->setObject('sheet3',$fileInfo3,$id3);
$templateProcessor->saveAs('output.docx');

Output file prompt to update link when open the file. When clicked ok, only first object link updated. I have try add sheet 3 from different file also giving same result.

I have checked linked worksheet object. Only the first object link exist in the list.

Premlatha · Accepted Answer · 2023-05-26 08:26:28Z

0

Shape id which is _x0000_i1025 must be unique for each object link. _x0000_i1025 used in element v:shape [attribute:id] and o:OLEObject [attribute:ShapeID] I have changed the value to _x0000_i1026 for second object link. Then , both first and second link work as expected.

answered May 26, 2023 at 8:26

Premlatha

2,0042 gold badges26 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

phpword create multiple object link to different sheets in same excel file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related