Conditional formatting when exporting the file as Excel2007 (.xlsx) and reading it with the software Excel2003

Topics: User Forum
Sep 4, 2008 at 1:38 PM
Hello,
I create an Excel 2007 file as template with some conditional formatting in. Then I wrote some more data in it with a PHP script and export this file again.
I read the file with Excel 2003 because it is the only software we have as standard here.
And there is an error concerning the conditional formatting:
"
This file was created in a newer version of Microsoft Excel. The file has been converted to a format you can work with but the following issues were encountered. The file has been opened in read-only mode to protect the original file.

- Some cells contain types of conditional formatting that are not supported in this version of Excel.
"
Then the conditional formatting is not working in Excel2003. The funny thing is that the conditional formatting in the template is working when I open it with Excel2003. Do you have any ideas how to solve this issue?

Thank you!
Ludo
Developer
Sep 4, 2008 at 1:58 PM
Not sure about this one as I have not worked much with conditional formatting. Sounds like Excel2007 writer is writing some extra data which is not needed, but which makes MS Office Excel 2003 choke.

Can you possibly try to cut the problem down to simplest possible spreadsheet still showing the error and upload the file somewhere? E.g. just a single cell with conditional formatting. Then maybe some of us can do some tests. Also, do you see the error after a plain read/write using Excel2007 reader/writer without adding any data?
Sep 4, 2008 at 3:43 PM
Edited Sep 4, 2008 at 3:45 PM
Hello Koyama,

I uploaded my file here -> http://www.sendspace.com/file/whtopy. There is conditional formatting in this simple template. You can test it when writting a bigger number in 'Served market' than in 'Total market' for example.

Here is the code I used for plain read/write test:

<?
session_start();
include('mysql_connection.php');

/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');

/** Include different files from the library to read and write **/
include 'PHPExcel/IOFactory.php';
include 'PHPExcel/Reader/Excel2007.php';
include 'PHPExcel/Writer/Excel5.php';
if (!file_exists("../files/testingphpexcel.xlsx")) {
    exit("Can not find the template file!");
}

echo date('H:i:s') . 'start loading'.'<br/>';
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n".'<br/>';

$objReader = PHPExcel_IOFactory::createReader("Excel2007");
$objPHPExcel = $objReader->load("../files/testingphpexcel.xlsx");

echo date('H:i:s') . 'file loaded'.'<br/>';
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n".'<br/>';


echo date('H:i:s') .'no write (test)'.'<br/>';
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n".'<br/>';

//Save the template file as Excel 2003 and not 2007
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setOffice2003Compatibility(true);
$objWriter->save("../files/success2007.xlsx");

echo date('H:i:s') .'saved'.'<br/>';
echo date('H:i:s') . " Peak memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB\r\n".'<br/>';
?>


Then the conditional formatting is not working any more! :-(

Here is the result I got:

17:38:15start loading
17:38:15 Peak memory usage: 6 MB
17:38:29file loaded
17:38:29 Peak memory usage: 64.75 MB
17:38:29no write (test)
17:38:29 Peak memory usage: 64.75 MB
17:39:28saved
17:39:28 Peak memory usage: 69 MB
Thank you very much for providing so much help!
Ludo
Developer
Sep 4, 2008 at 6:07 PM
Hmm... this looks like a bug. Let's see what the Excel2007 developers say?

http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7516
Sep 5, 2008 at 12:05 AM
Ok, thanks Koyama for your help!