Read/Write macro in .xlsm

Topics: Developer Forum, User Forum
Apr 6, 2010 at 2:07 AM

Hi,

I saw some earlier discussions regarding macro support with PHPExcel.  At the time, macros were not being supported.

In my case, I do not want to access or modify the macros at all.  I would simply like to maintain them as-is when my .xlsm file is written.

I would like to read an .xlsm file, modify some of the cells, and write it back out, leaving the macro intact.

I added the following to public static function createReaderForFile($pFilename) in IOFactory.php in order to read in an .xlsm file:

case 'xlsm':
                    $reader = self::createReader('Excel2007');
                    break;

 

However, when I try to write out the updated .xlsm file, I get something like the following:

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

$objWriter->save($_SESSION['sheetWriteDir']."/".$_SESSION['sheetname']);

 

Fatal error: Maximum execution time of 30 seconds exceeded in C:\PHPExcel\Classes\PHPExcel\Calculation.php on line 1935 or

Fatal error: Maximum execution time of 30 seconds exceeded in C:\PHPExcel\Classes\PHPExcel\Calculation.php on line 2706

(I tried bumping up the time out time, but I don't really think that's the issue, especially if PHPExcel just doesn't like that there are macros in the .xlsm.)

 

Is there any way to accomplish what I'm trying to do? 

 

Thanks very much,

-ira


Developer
Apr 20, 2010 at 5:38 AM

Sorry, we have not made any progress on this. I think it is not as simple as just carrying over the macro sheet.

Jan 3, 2011 at 11:23 PM

I just ran into this issue and this is what I did.

First xlsx and xlsm files are nothing more than a fancy zip file.

knowing that you should be able to open the xlsm file with PHP's zip library.  Look under the xl folder for files named *.bin.
My example used vbaProject.bin

You can store the contents of that file to the temporary directory. Generate your xlsx file and with a little bit of magic generate your xlsm file again.

  1. Reinsert your vbaProject.bin files in the xl folder
  2. Update [Content_Types].xml file
  3. Update xl/_rels/workbook.xml.rels file

/**************************************************************************************************/

THIS IS TO GET YOU INTO THE RIGHT DIRECTION NOT GUARANTEEING THAT THIS SOLUTION IS GOING TO WORK FOR EACH UNIQUE CASE
THIS EXAMPLE INCLUDE ONE MACRO IN A SIMPLE EXCEL DOCUMENT

/*************************************************************************************************/

Here is my example

      // Write Excel 2007 document      
       $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
       
      // Generate a temp file and save Excel document to temp file
        $tmpfname = tempnam("/tmp", "FOO");
        $objWriter->save($tmpfname);
   
        require_once('ZipArchive.php'); // Referance post http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=60358 helper class for PHP builds missing ZIp wrappers.
        $zip = new ZipArchive; // Start Zip archive
        $zip->open($tmpfname); // Open our stored Excel document

        // Add our VBA script to the Excel Zip Document
        $zip->addFile('[PATH_TO_YOUR_STORED_VBA_BIN_FILE]/vbaProject.bin', 'xl/vbaProject.bin');
       
        // Get the contents of our Content Types xml document from our Excel Zip Document
        $ContentTypes = $zip->getFromName('[Content_Types].xml');

       // Generate an XML object with PHP's DOM functions http://us.php.net/manual/en/book.dom.php
        $ContentTypesXML = new DomDocument();
        $success = (int) @$ContentTypesXML->loadXML( $ContentTypes );
        $Types = $ContentTypesXML->getElementsByTagName('Types')->item(0);
       
        // Add Override node to our Content Types with the file location of our VBA script
        $Override = $ContentTypesXML->createElement("Override");
        $Override = $Types->appendChild($Override);
        $Override->setAttribute('PartName', '/xl/vbaProject.bin');
        $Override->setAttribute('ContentType', 'application/vnd.ms-office.vbaProject');
       
        // Find out workbook and update the content type to be xlsm instead of xlsx
        foreach($Types->getElementsByTagName('Override') as $Override){
            if($Override->hasAttribute('PartName') && $Override->getAttribute('PartName')=="/xl/workbook.xml" ){
                $Override->setAttribute('ContentType', 'application/vnd.ms-excel.sheet.macroEnabled.main+xml');
            }
        }
        // Save content type back to our Excel Zip Document
        $zip->addFromString('[Content_Types].xml', $ContentTypesXML->saveXML() );
       
        // Get our workbook relationship xml document
        $Workbook = $zip->getFromName('xl/_rels/workbook.xml.rels');

       // Generate an XML object with PHP's DOM functions http://us.php.net/manual/en/book.dom.php
        $WorkbookXML = new DomDocument();
        $success = (int) @$WorkbookXML->loadXML( $Workbook );
        $Rltns = $WorkbookXML->getElementsByTagName('Relationships')->item(0);
       
        // Add Relationship that points to our VBA script
        $Rltn = $WorkbookXML->createElement("Relationship");
        $Rltn = $Rltns->appendChild($Rltn);
        $Rltn->setAttribute('Id', 'rId99'); // Arbitraty Relationship ID NOTE may need a higher number based on the number of worksheets and other elements in your Excel document, update would be to calculate the number of children inside the Relationships XML Node
        $Rltn->setAttribute('Type', 'http://schemas.microsoft.com/office/2006/relationships/vbaProject');
        $Rltn->setAttribute('Target', 'vbaProject.bin'); // Our VBA script
               
        // Save our updated XML to our Workbook relationship xml
        $zip->addFromString('xl/_rels/workbook.xml.rels', $WorkbookXML->saveXML() );   
       
        $zip->close(); // Close the zip file.

        // Output xlsm headers
        header('Content-Type: application/vnd.ms-excel.sheet.macroEnabled.main+xml'); // xlsm
        //header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); // xlsx
        header('Content-Disposition: attachment;filename="Report.xlsm"');
        header('Cache-Control: max-age=0');  
      
        $handle = fopen($tmpfname, "r");
        $BUFF = fread($handle, filesize($tmpfname));
        fclose($handle);
        unset($handle);

        echo $BUFF;
       
        unlink($tmpfname);

 

Good Luck

May 6, 2011 at 4:54 PM

is there any advance on this, is there a way to clone a .xlsm file and keep the macros in the new file ?

Coordinator
May 6, 2011 at 10:09 PM

There has been no development on support for xlsm files, nor for macros. This is very low on the priority list.

Aug 24, 2011 at 9:49 AM
Edited Aug 24, 2011 at 9:58 AM

I add the feature base on PHPExcel 1.7.6 , and fix some bug , but I can't submit source code into SVN . 

Someone want to use it ,please contact me!

In my new revision, PhpExcel can copy a xlsm file with micro as a template and save it as another xlsm file , the micro or VBA project will be remained in new file;

Sep 19, 2011 at 2:40 PM

Hi,

We would like to have a look first to your feature before implementing the "zip rebuild" method. Could you please sent it to me ?

THX

Bruno

Sep 28, 2011 at 4:55 PM
wuyuqun wrote:

I add the feature base on PHPExcel 1.7.6 , and fix some bug , but I can't submit source code into SVN . 

Someone want to use it ,please contact me!

In my new revision, PhpExcel can copy a xlsm file with micro as a template and save it as another xlsm file , the micro or VBA project will be remained in new file;

hi wuyuqun,

I want to use the macro function, could you send me a copy?

Oct 31, 2011 at 4:18 PM

I want the feature,my  email is dongkui122811@hotmail.com

Nov 21, 2011 at 10:30 PM

Hello, I am very interested in the feature to read a xlsm template and save it as xlsm. Could you please send it to me ? My address is benjaminabbou@hotmail.com

Thanks in advance

Dec 14, 2011 at 1:52 PM
wuyuqun wrote:

I add the feature base on PHPExcel 1.7.6 , and fix some bug , but I can't submit source code into SVN . 

Someone want to use it ,please contact me!

In my new revision, PhpExcel can copy a xlsm file with micro as a template and save it as another xlsm file , the micro or VBA project will be remained in new file;

I need to edit and modify the xlsm file with micro as a template and save it as another xlsm file. so could share the script with me. my mailid: suriit38@gmail.com

Dec 19, 2011 at 5:54 AM
suryaindus wrote:
wuyuqun wrote:

I add the feature base on PHPExcel 1.7.6 , and fix some bug , but I can't submit source code into SVN . 

Someone want to use it ,please contact me!

In my new revision, PhpExcel can copy a xlsm file with micro as a template and save it as another xlsm file , the micro or VBA project will be remained in new file;

I need to edit and modify the xlsm file with micro as a template and save it as another xlsm file. so could share the script with me. my mailid: suriit38@gmail.com

Hi wuyuqun,]

I am in great need of your script, could you please send it my mail is. my mail id: suriit38@gmail.com

Dec 19, 2011 at 8:09 PM

I want too the feature,my  email id:  akvamarion@gmail.com

Dec 19, 2011 at 8:24 PM

 I am in great need of your script too

my mail id: lookameroly@gmail.com

Dec 20, 2011 at 4:57 AM
wuyuqun wrote:

I add the feature base on PHPExcel 1.7.6 , and fix some bug , but I can't submit source code into SVN . 

Someone want to use it ,please contact me!

In my new revision, PhpExcel can copy a xlsm file with micro as a template and save it as another xlsm file , the micro or VBA project will be remained in new file;

 

I am in great need of your script, could you please send it my mail is. my mail id: suriit38@gmail.com

Jan 4, 2012 at 10:22 AM

Does anyone really get the email about the script?? 

Jan 4, 2012 at 11:38 AM

No, did not get any answer

Jan 8, 2015 at 1:07 AM
Could you send me the code please.

My email es rodolfoh@neus42.com Thanks in Advance
Apr 13, 2015 at 12:06 PM
Edited Apr 14, 2015 at 12:10 PM
Anyone has received the script?

if yes, can someone send me please.

My email is ponderarvalor.jonathan@gmail.com Thank you
Oct 19, 2015 at 5:29 PM
I'm interested in this script.

Can someone send me: feusebio@gmail.com

Thanks
Dec 17, 2015 at 6:37 AM
Hi Guys,

Please share the script with me.

My email id is ajaytripathi.10@gmail.com Thank You.
Feb 25 at 12:30 PM
Edited Feb 25 at 12:40 PM
e