This project has moved. For the latest updates, please go here.
3
Vote

Excel 2007 - Macros - CustomUI

description

You will find in the attachment the modified and added files to PHPExcel v1.7.8 to enable reading and writing macros, elements of customizing the Ribbon in Excel 2007.
Signed macros are handled, "user" images of the Ribbon also .

If this is of any interest, some improvements can be made, for example the use of caching system to preserve the memory.

file attachments

comments

mendo wrote Mar 27, 2013 at 7:42 PM

Hi, nice work =D

I have a macro ( " testing

mendo wrote Mar 27, 2013 at 7:54 PM

Sorry for the last comment, I don't know how delete it.

when I load a .xlsm and save it, phpexcel, build a workbook for each sheet, how can avoid this? Thanks.

wrote Mar 27, 2013 at 7:58 PM

LWol wrote Mar 28, 2013 at 7:54 AM

Strange. I used your file as an attachment to test: load it and save it under a different name, load it and add some data, an additional sheet then save it.
I always have a single file result, with the number of sheets expected.
Can you post the piece of code that leads to the result that you have found?

wrote Mar 28, 2013 at 1:45 PM

mendo wrote Mar 28, 2013 at 1:45 PM

Hi this is the code and attached the image from the macro.

/* Error reporting /
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

/* PHPExcel_IOFactory /
require_once '../Classes/PHPExcel/IOFactory.php';

echo date('H:i:s') , " Load from Excel2007 template" , EOL;
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("templates/testing_macro.xlsm");

echo ('Saving...');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('testing.xlsm');
header('Cache-Control: max-age=0');

LWol wrote Mar 29, 2013 at 11:56 AM

The image highlights what I hadn't seen, thank you.
I work in the correction.

mendo wrote Mar 30, 2013 at 9:41 PM

thanks, i will hope the update, good job.

wrote Apr 2, 2013 at 6:13 AM

LWol wrote Apr 2, 2013 at 6:13 AM

You will find attached a version correcting the problem to some extent.
The problem exists when deleting a sheet known by the VBA project.
Should analyze the VBA code to rewrite it without the references to the deleted worksheet. Beyond the difficulty - in general, a VBA project is encoded as in previous versions of Excel files, but more in detail, the macro part is not documented - if the project is signed, it is impossible to change.

Added: A file listing the added public methods. Consider as a draft, subject to change.
This can avoid loading each time the template file for the sole purpose to recover the macros, the Ribbon. These methods also help define the appropriate codeName.

The change of name (by setTitle) does not change the codeName, operation similar to Excel, so the attached code continues to operate: you have a sheet named 'Synthesis January 2013" with event procedures, today you want to name this sheet "Synthesis April 2013", you call setTitle() to do, event procedures are not disrupted.
Conversely, if it is difficult to clean the previous data in a sheet, you take his codeName, you delete the sheet, and then you create a new worksheet in which you give the appropriate title and the saved codeName.

nmaxeve wrote Apr 16, 2013 at 7:14 AM

Hi All!

I have an Excel file with macros (button click event to say "Hi"), but after success script below i have result.xlsm with no macros:

require_once 'Classes/PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("/srv/www/dev/maksud/macro.xlsm");

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('/srv/www/dev/maksud/result.xlsm');


What is problem with my script?

Thanks!

LWol wrote Apr 18, 2013 at 3:47 PM

At first glance, no problem in your script.
Can you test by adding something like this between the load and save:
if(!defined('EOL')) define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
if($objPHPExcel->hasMacros()){
    echo 'macros in file '.EOL;
    if($objPHPExcel->hasMacrosCertificate())
        echo ' signed '.EOL;
    else
        echo ' not signed'.EOL;
}else
    echo 'no macros in file'.EOL;
if($objPHPExcel->hasRibbon()){
    echo 'Ribbon in file'.EOL;
    if($objPHPExcel->hasRibbonBinObjects())
        echo 'additional objects for the Ribbon'.EOL;
    else
        echo 'NO additional object for the Ribbon'.EOL;
}else
    echo 'No ribbon'.EOL;
You have errors, an unexpected return?

Can you attach your source file and your result file (BEFORE you open it with Excel)?

Livinstone wrote May 20, 2013 at 5:53 AM

I have same problem :(
  • macros in file
  • not signed
  • No ribbon
    What it mean? after success script, my button disappeared :(((

LWol wrote May 20, 2013 at 2:05 PM

This means that the script found macros, they are not signed, and that there are no custom Ribbon. If these information are consistent with the contents of the original file, it is likely that the macros have been copied.
The disappearance of a button is a problem that is unrelated to this patch but the fact that PHPExcel does not support the form elements.
I examine this aspect.

wrote Jul 3, 2013 at 1:45 PM

AnujAroshA wrote Jul 3, 2013 at 1:45 PM

I have a Excel template file that has Macro enabled. I tried to use that template and put some data to the Excel sheet using PHP and again try to save/download as a .xlsm file in a separate place. I could able to save the file but cannot open that file using MS Excel software. Following is the error I'm getting.
Excel cannot open the file 'filename.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
I have attached my source here with. If I save the file with .xlsx extension, it saved and open without any error. But without Marco that the template file has.

I need to have that Marco with the final download. Where can I edit my source to do that? Is that the Content-Type or something else?

Livinstone wrote Jul 3, 2013 at 3:10 PM

AnujAroshA, PHPEXCEL does not support macros and xlsm files.
I found one solution to this problem.
I use LibXL library and php_excel extention.
you'll be able to open excel files with macros, update data and save.
details here link
p.s. sorry for my english;)

AnujAroshA wrote Jul 4, 2013 at 6:10 AM

Thanks Livinstone for your comment. I'll look at it.

LWol wrote Jul 4, 2013 at 7:03 AM

If you open xlsm file with Notepad, you see an error message?

Note : If a xlsx file contains macros, Excel removes them immediately.

DennisBraga wrote Jul 23, 2013 at 5:04 PM

Here's my problem: I downloaded the sheet up there that only says "Hi!" when opened for testing. I tried the code and it didn't keep the macro. When I openned the excel file, it didn't said "hi!". Here's my final code
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE); 


include_once "PHPExcel.php";
include_once "PHPExcel/IOFactory.php";
$filePath = "/var/www/macros/macro.xlsm";
$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load($filePath);


if(!defined('EOL')) define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
if($objPHPExcel->hasMacros()){
    echo 'macros in file '.EOL;
    if($objPHPExcel->hasMacrosCertificate())
        echo ' signed ' . EOL;
    else
        echo ' not signed' . EOL;
}else
    echo 'no macros in file' . EOL;

if($objPHPExcel->hasRibbon()){
    echo 'Ribbon in file'.EOL;
    if($objPHPExcel->hasRibbonBinObjects())
        echo 'additional objects for the Ribbon' . EOL;
    else
        echo 'NO additional object for the Ribbon' . EOL;
}else
    echo 'No ribbon' . EOL;

// Write Excel 2007 document       
$filename = "/var/www/macros/result/output.xlsm";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save($filename);

exit;
Th output is without the macro. Even when I browser 'till there, I ain't able to see the macro. =/

Here the output of the code.
macros in file 
 not signed
No ribbon
... what am I doing wrong? =/ =/

DennisBraga wrote Jul 24, 2013 at 1:57 PM

Nevermind... I found it! The writer was "Excel5", and it suposed to be "Excel2007". My bad!

DennisBraga wrote Jul 24, 2013 at 2:02 PM

Hey, why don't you post it on packagist? Or allow someone else to do it? It would be great!! Thanks for the great work... helped me A LOT!!!! I can't thank you enough!

P.S.: If you're runing out of time for it, count on me! ;)

LWol wrote Jul 25, 2013 at 8:29 AM

Glad it works for you.
Should I see to adapt it to version 1.7.9 as add handling of form components (those that are available directly on the worksheet, the boxes dialog defined in the project are managed).

All must be of sufficient quality to ensure that Mark agrees to take into account its integration in the library.

MarkBaker wrote Jul 25, 2013 at 9:40 AM

If you do submit, then please remember that we're on github now for the source code: The develop branch is the latest patches post 1.7.9 release; and there's also a develop_2.0.0 branch which is the latest code for the 2.0 release (not yet working, and with a lot of work still to do) which is assuming a minimum PHP 5.3.0

LWol wrote Jul 29, 2013 at 1:27 PM

OK. Create an account on GitHub, fork of PHPExcel, editing files, push... Apart from all that I have not thought of, it must remain the pull request.

bulldogs wrote Feb 2, 2014 at 9:17 AM

Apologies - am a bit new to phpexcel. The files downloaded from "PHPExcel 1.7.8 modified files for macros v0.02.zip" should be copied to replace the existing files downloaded from the standard PHPExcel files?

Is the above compatible with PHPExcel 1.7.9? Would appreciate any help - many thanks

afall wrote Apr 20, 2015 at 9:09 AM

Hello.

Thank your for your work that helps me a lot to fix an issue.

But i ve one more point to solve. My macro is launched by a button created in a sheet. When I use a normal version of Php_Excel I can't avoid to lose the button with the macro. Your version helped me to keep the macro, but I still lose the button.

Do you have any suggestion ?

Thank you in advance.

wrote Oct 10, 2016 at 10:57 AM

Ahsun wrote Jan 6 at 11:22 AM

What should we do if we have the latest PHPExcel version i.e 1.8
The Macros are not properly read and write in the 1.8 version. Can you please guide or upload the code compatible for this version

Thanks