lost formulas when loading excel 5 saving to excel 7

Topics: Developer Forum
Dec 3, 2010 at 2:57 PM

Hallo,

I have a script which is generating a large excel 5 file using the pear Spreadsheet/Excel/Writer.php

What i am trying to do is open the file in PHPExcel and then save it as 2007

it works badly

$objPHPExcel = PHPExcel_IOFactory::load("report_accnr_".date("dmY").".xls");
echo date('H:i:s') . " Write to Excel2007 format\n";
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("DailyReport_".date("dmY").".xlsx");

it works from the point of view it is saved as a 2007 file BUT its loosing a lot of formulas, these are not complicated formulas but for example

=B30+C30-G30-H30

instead in the 2007 file i have 0

 

Is there anyway of avoiding this ? because at the moment it means its unusable.

 

Another question - when trying in the above scenario to set the column widths to auto -the following did not seem to work

any ideas why not ?

echo "Excel Sheet count = ",$objPHPExcel->getSheetCount(),"\n";
foreach($objPHPExcel->getSheetNames() as $idx => $sheetName) {
        $objPHPExcel->setActiveSheetIndex($idx);
        $maxCol =$objPHPExcel->getActiveSheet()->getHighestColumn();
        for($i = 0; $i <= $maxcol; $i++) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true);
        }
}
$objPHPExcel->setActiveSheetIndex(0);

thanks

 

 

Coordinator
Dec 4, 2010 at 10:49 AM
soupdragon wrote:

I have a script which is generating a large excel 5 file using the pear Spreadsheet/Excel/Writer.php

What i am trying to do is open the file in PHPExcel and then save it as 2007

it works badly

it works from the point of view it is saved as a 2007 file BUT its loosing a lot of formulas

 

Is there anyway of avoiding this ? because at the moment it means its unusable.

 This should be "bread and butter" work for PHPExcel, it's what PHPExcel was designed to do: it should simply work. It's obvious that there's something unusual going on here, but without more information, it's difficult to identify what is wrong, or how it can be fixed. Can PHPExcel read the formulae in an equivalent workbook written by by Excel itself, rather than the one generated using Spreadsheet/Excel/Writer.php? Is it possible to send me a (small) sample of the Spreadsheet/Excel/Writer.php generated workbook to run through in debug?

 

soupdragon wrote:

Another question - when trying in the above scenario to set the column widths to auto -the following did not seem to work

any ideas why not ?

echo "Excel Sheet count = ",$objPHPExcel->getSheetCount(),"\n";
foreach($objPHPExcel->getSheetNames() as $idx => $sheetName) {
        $objPHPExcel->setActiveSheetIndex($idx);
        $maxCol =$objPHPExcel->getActiveSheet()->getHighestColumn();
        for($i = 0; $i <= $maxcol; $i++) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($i)->setAutoSize(true);
        }
}
$objPHPExcel->setActiveSheetIndex(0);

 getHighestColumn() returns the column letter; but your for loop is working with numbers ($i = 0)

Try

$maxCol =$objPHPExcel->getActiveSheet()->getHighestColumn();
$maxCol++;
        for($i = 'A'; $i != $maxcol; $i++) {
Dec 6, 2010 at 9:38 AM

Using your

$maxCol =$objPHPExcel->getActiveSheet()->getHighestColumn();
$maxCol++;
for($i = 'A'; $i != $maxcol; $i++) {

I get the following


PHP Fatal error:  Uncaught exception 'Exception' with message 'Column string index can not be longer than 3 characters.' in /var/www/html/dailyreport/Classes/PHPExcel/Cell.php:645
Stack trace:
#0 /var/www/html/dailyreport/Classes/PHPExcel/Worksheet.php(1093): PHPExcel_Cell::columnIndexFromString('AAAA')

 

Which is definately wrong as the highest should be about "BD"

How exactly is it checking highest column ?

Coordinator
Dec 6, 2010 at 12:16 PM
The getHighestColumn() and getHighestRow() methods return the
_cachedHighestColumn and _cachedHighestRow properties held against the
worksheet.
Whenever a new cell is added to the worksheet (whether by loading an
existing workbook or manually adding a cell to a worksheet), the calls
to getCell() and getCellByColumnAndRow() include blocks of code that
compare the current values of _cachedHighestColumn and _cachedHighestRow
with the row and column of the new cell. If the new cell address is a
higher row or column value than the current _cachedHighestColumn or
_cachedHighestRow, then these property values are updated to match those
of the new cell. The setCellValue() and related methods always call
getCell(), so it shouldn't be possible for any cell creation to bypass
this update.
When deleting cells, the _cachedHighestColumn and _cachedHighestRow
properties aren't reduced in any way, so if deleting cells they will
still reflect the highest row and column values that existed prior to
any deletion.
When saving a workbook, the garbage collection process that is executed
prior to the actual save updates these properties to reflect the actual
highest values based on the full set of existing cells in each worksheet.
Dec 6, 2010 at 4:23 PM
Hallo,

Can i send you the sample excel to this address ?

thanks


From: [email removed]
To: [email removed]
Date: Mon, 6 Dec 2010 04:17:07 -0800
Subject: Re: lost formulas when loading excel 5 saving to excel 7 [PHPExcel:237035]

From: MarkBaker
The getHighestColumn() and getHighestRow() methods return the
_cachedHighestColumn and _cachedHighestRow properties held against the
worksheet.
Whenever a new cell is added to the worksheet (whether by loading an
existing workbook or manually adding a cell to a worksheet), the calls
to getCell() and getCellByColumnAndRow() include blocks of code that
compare the current values of _cachedHighestColumn and _cachedHighestRow
with the row and column of the new cell. If the new cell address is a
higher row or column value than the current _cachedHighestColumn or
_cachedHighestRow, then these property values are updated to match those
of the new cell. The setCellValue() and related methods always call
getCell(), so it shouldn't be possible for any cell creation to bypass
this update.
When deleting cells, the _cachedHighestColumn and _cachedHighestRow
properties aren't reduced in any way, so if deleting cells they will
still reflect the highest row and column values that existed prior to
any deletion.
When saving a workbook, the garbage collection process that is executed
prior to the actual save updates these properties to reflect the actual
highest values based on the full set of existing cells in each worksheet.
Read the full discussion online.
To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com@discussions.codeplex.com)
To start a new discussion for this project, email PHPExcel@discussions.codeplex.com@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Dec 7, 2010 at 1:07 PM
soupdragon wrote:

Can i send you the sample excel to this address ?

 

 

Yes, you can

Dec 7, 2010 at 2:27 PM

Ok this is a part of the table - main problems turn up on the third sheet

for example D 24 has the formula =B24+C24-G24-H24
which after opening and saving just gets lost

thanks for the help

From: [email removed]
To: [email removed]
Date: Tue, 7 Dec 2010 05:08:07 -0800
Subject: Re: lost formulas when loading excel 5 saving to excel 7 [PHPExcel:237035]

From: MarkBaker
soupdragon wrote:

Can i send you the sample excel to this address ?


Yes, you can
Read the full discussion online.
To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com@discussions.codeplex.com)
To start a new discussion for this project, email PHPExcel@discussions.codeplex.com@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Dec 8, 2010 at 2:43 PM

I really need to see the actual file.

If I create a file containing the formula =B24+C24-G24-H24 it works perfectly correctly. There must be something about the file which is being generated by the pear Spreadsheet/Excel/Writer.php that doesn't read correctly; but without a sample to demonstrate the problem, I can't even begin to guess.

Dec 9, 2010 at 10:14 AM
Ok strange i had attached the file (well part of it)

take 2 !


From: [email removed]
To: [email removed]
Date: Wed, 8 Dec 2010 06:43:47 -0800
Subject: Re: lost formulas when loading excel 5 saving to excel 7 [PHPExcel:237035]

From: MarkBaker
I really need to see the actual file.
If I create a file containing the formula =B24+C24-G24-H24 it works perfectly correctly. There must be something about the file which is being generated by the pear Spreadsheet/Excel/Writer.php that doesn't read correctly; but without a sample to demonstrate the problem, I can't even begin to guess.
Read the full discussion online.
To add a post to this discussion, reply to this email (PHPExcel@discussions.codeplex.com@discussions.codeplex.com)
To start a new discussion for this project, email PHPExcel@discussions.codeplex.com@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com
Coordinator
Dec 9, 2010 at 9:27 PM

Still no joy. Try sending to strrev(uk dot co dot demon dot lange at mark).

Dec 20, 2010 at 2:12 PM

have you now received my file ?

 

thanks