Low-level Differences vs Native Format

Topics: Developer Forum, User Forum
May 15, 2014 at 5:39 PM
Hi all,

I'm using PHPExcel to generate several Excel forms at work, and it's been a real time-saver. So thank you for that! But we do have one nagging problem that I haven't been able to resolve: low-level differences between the PHPExcel-generated file and the "real" Excel file.

For example, if I generate a form using PHPExcel and try to load it directly into my company's IT system, it will fail every time. But if I take that exact same file, open it in Excel, and then re-save it (no other changes), it will work perfectly?

It appears that there are some hidden bytes within the Excel5 (XLS)format that don't quite mesh up exactly with the "real" format.... but what those bytes are, I can't say for sure.

Has anybody else encountered this before, and dug into determine what changes when a file is opened/saved like this? It's a real pain, since we're using this code to generate hundreds of files at a time, and having to open/save each one is quite counter-productive.

And before you ask, I cannot change to Excel7 (XLSX) format, as our IT system requires XLS only.

I am using PHP 5.3.0 with PHPExcel v. 1.7.8. I am not opposed to switching versions of PHPExcel, but the PHP itself cannot be updated. Examples of the original file and the opened/saved version can be download from http://162.200.200.89/public/output.zip
May 15, 2014 at 9:02 PM
I've found an ugly workaround to at least get us by for now.

Basically, I wrote a VBS script that will loop through the created Excel files, open them, and then re-save them to the same location. Definitely inelegant, and I'd much rather have a way of creating the file with whatever "native" bits are missing in the first place.

The workaround requires the COM function of PHP, and knowledge of the specific directory where these files are being created.

PHP Code:
($db.$i is a randomly-generated file name, incremented $loop times earlier in the code)
for ($i=1;$i<$loop;$i++){
    $runfile="wscript.exe D:\\htdocs\\Loadsheet\\output\\saveXLS.vbs \"D:\\htdocs\\Loadsheet\\output\\".$db.$i.".xls\"";
    $wait = true;  // similar to start /w in DOS, set to false for no wait
    $obj = new COM ( 'WScript.Shell' ); 

    if ( is_object ( $obj ) ) 
    { 
        $obj->Run ( 'cmd /C ' . $runfile, 0, $wait ); 
    } 
    else 
    { 
        echo 'can not create wshell object'; 
    } 

    $obj = null;
    }
saveXLS.vbs:
Set args = Wscript.Arguments
wit= args(0)
Dim SpreadSheet
SpreadSheet = wit
 
Set objExcel = CreateObject("Excel.Application") 
Set objWorkbook = objExcel.Workbooks.Open(Spreadsheet)  'open via passed parameter
 
Set objRange = objworkbook.worksheets(1).columns(1)  ' this does nothing, but sets focus so re-save works
 
objworkbook.save 
objexcel.quit 
It's definitely not the method I want, but it does the trick for now. Considering that this can generate 100+ XLS files per run, it's a huge timesaver from having to open & re-save each one!

If anyone has any better suggestions, please let me know.... thanks!