PHPExcel Deletes Embedded VBA

Hi Experts.

I'm developing a PHP Application that needs to write data to an Excel report.  To do this, I'm using a pre-created Excel file (.xls, not .xlt) that has buttons and VBA, copy it, then use the PHPExcel library to update the copied file, using the following code:

	private static function excel_create_file($title, $with_compare, $sess_id, $date_range_1, $date_range_2,&$db){
        
        //copy template file
       
            //Temporarily Change directory to root to copy files...
            $currentDIR = getcwd();
            chdir("../");
            $rootDIR = getcwd();
            //Change DIR back
            chdir($currentDIR);
            
        $slash = DIRECTORY_SEPARATOR;

        //echo 'dir = ' . $currentDIR . '<br>';
        
        $file_name = "SearchReport_" . $sess_id . ".xls";
        
        $file_from = $rootDIR . $slash . "search_bookings" . $slash . "report_template.xls";
        $file_physical = $rootDIR . $slash . "downloads" . $slash . $file_name;
        
        global $env;
        $file_virtual = $env['url'] . '/downloads/' . $file_name;
        
        $copy_success = copy($file_from, $file_physical);
        
    /** Include PHPExcel */
        error_reporting(E_ALL);
        set_time_limit(0);
                
        $fileType = 'Excel5';
        
        require_once '../excel_lib/PHPExcel/IOFactory.php';
        
        // Read the file
        $objReader = PHPExcel_IOFactory::createReader($fileType);
        $objPHPExcel = $objReader->load($file_physical);

        // Admin/cover sheet
        self::excel_sheet_admin($objPHPExcel, $title, $date_range_1, $date_range_2);
/*        
        //Create 1 product sheets
        self::excel_sheet_1_product($objPHPExcel, 'Loc_CSSTAlpha', TRUE, $with_compare, $sess_id, $db); //Report 4.4
*/
        // Write the file
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $fileType);
        $objWriter->save($file_physical);
        
        return $file_virtual;
        
    }
    
    private static function excel_sheet_admin(&$excel_obj, $title, $date_range_1, $date_range_2){
        
        $excel_obj->setActiveSheetIndexByName('Admin')
                  ->setCellValue('B3', $title)
                  ->setCellValue('B4', $date_range_1)
                  ->setCellValue('B5', $date_range_2);
    }
    

Open in new window


if I comment out Lines 27-47 (the PHPExcel) the buttons and their VBA are maintained, but as soon as I release these lines, and run the above code, I lose my buttons and VBA. Even hitting Alt+F11 my VBA code is deleted.

How can I populate an existing Excel file while maintaining the buttons and VBA?

I know that VBA is not PHP, but Excel is VBA.

Any feedback will be greatly appreciated.
APD TorontoSoftware DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Is your server windows or linux?  I wonder if you would need to edit it using .NET in order to preserve any vba.  If that is the case, create a .NET page to edit the excel file. Have the .NET page accept a get or post like an internal web service and to hit it from PHP using cURL would be the only thing I can think of.
0
APD TorontoSoftware DeveloperAuthor Commented:
It's Linux.  Can it be the file type (line 30)?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You can read about the file types in the documentation https://github.com/PHPOffice/PHPExcel/blob/1.8/Documentation/PHPExcel%20developer%20documentation.doc but that is not the issue.

Using this PHPExcel to access an excel file and edit it does not do the same thing as if you manually updated the file.  PHPExcel will read what it can leaving out VBA, images etc. then follow your instructions to update data and recreate it.  I would bet if you use a chart function, it does not look the same because it uses a 3rd party chart library to create the chart. (http://jpgraph.net/)

The answer is you you can't do what you want with what you are using.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

APD TorontoSoftware DeveloperAuthor Commented:
Any other php tool, or suggestion?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Anything you are going to use to read and write an excel file is going to omit VBA as I explained with the exception of using .NET.  That leaves an option of using a .NET container http://blogs.msdn.com/b/webdev/archive/2015/01/14/running-asp-net-5-applications-in-linux-containers-with-docker.aspx. It seams like a lot of work though.  Otherwise, try getting a shared .NET server or perhaps use azure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
APD TorontoSoftware DeveloperAuthor Commented:
I am out right now, but I found this

http://phpexcel.codeplex.com/workitem/19017

As well, what do you think about JS?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
That looks promising.
0
APD TorontoSoftware DeveloperAuthor Commented:
Do you have any idea on how to install and use it?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I'm just starting with PHPExcel myself.  I can't come back to this until later next week though.  Looking at the files in the link you provided it looks like you may have to have access to the macros separate?
0
APD TorontoSoftware DeveloperAuthor Commented:
What do you mean separate?

I am also trying the code they posted, which I am including below, but I am missing IOFactory.php, which was part of the original package. I am assuming that I need to copy/paste files.
0
APD TorontoSoftware DeveloperAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.