search

ivan-berezhnov / php-excel-reader

Automatically exported from code.google.com/p/php-excel-reader
0 stars 0 forks source link

Wrong date values when formatting thru OpenOffice spreadsheet. #54

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
What steps will reproduce the problem?
1. Open a microsoft excel document (.xls);
2. Update its date format;
3. Save as .xls using OpenOffice;

What is the expected output? What do you see instead?
When I tried to echo a value on a specic formatted column/row,
php-excel-reader seems to get lost and bring different values. The expected
output would be (for example): 29/12/2009 on a DD/MM/YYYY (OpenOffice cell
format) but it shows: TueTue/DecDec/2009200920092009. It seems OpenOffice
document formats its cell with a different format than Microsoft Excel,
wich works perfectly when I saved the document on another computer with
Windows.

Please provide any additional information below.
I believe this is an interesting bug because plenty people uses OpenOffice
and documents created by it. And congratulations on this awesome php class!
10/10

Original issue reported on code.google.com by gui...@gmail.com on 28 Nov 2009 at 12:49

GoogleCodeExporter commented 9 years ago 
I am also seeing similar problems with OO-saved files, however, the problem 
isn't just with date fields. I am trying to get details of the precise OO 
version and build used, but various updates have been tried and all files saved 
with these various OO versions/revisions, seem to suffer the same problem. 
Clearly OO isn't writing the file exactly as Excel would. Interestingly Excel 
opens the OO files without problem and reports no errors or warnings, and the 
problem in reading the data via phpExcelReader can be instantly cured by 
opening the affected files in Excel, clicking save, and the re-reading in 
phpExcelReader. The problem magically goes away.

I've also noticed differences in the file sizes. File1 Excel filesaize = 
7.50Kb, OO filesize = 6.50Kb, File2, Excel filesize = 102Kb, OO filesize=134Kb, 
File2.

Some examples:

Read from Excel-saved file:

    [2] => Array
        (
            [1] => SO32-2002
            [2] => 19-Jun-2010
            [3] => 2
            [4] => 01-Jun-2010
            [5] => 18-Jun-2010
        )

And the same data when read from the OO-saved file:

    [2] => Array
        (
            [1] => SO32-2002
            [2] => DD-MMM-YYYYà¤õÿ À
            [3] => GENERAL¥
            [4] => DD-MMM-YYYYà¤õÿ À
            [5] => DD-MMM-YYYYà¤õÿ À
        )

Or a non-date example:

From Excel-saved file:

    [SO32-2002] => Array
        (
            [YID] => SO32-2002
            [Yacht description] => Jeanneau Sun Odyssey 
            [Size/model] => 32(2002)
            [24-Apr-2010] => 0
            [01-May-2010] => 0
            [08-May-2010] => 0
            [15-May-2010] => 0
            [22-May-2010] => 0
            [29-May-2010] => 0
            [05-Jun-2010] => 0
            [12-Jun-2010] => 1
            [19-Jun-2010] => 1
            [26-Jun-2010] => 1
            [03-Jul-2010] => 2
            [10-Jul-2010] => 1
            [17-Jul-2010] => 1
            [24-Jul-2010] => 2
            [31-Jul-2010] => 0
            [07-Aug-2010] => 0
            [14-Aug-2010] => 0
            [21-Aug-2010] => 1
            [28-Aug-2010] => 1
            [04-Sep-2010] => 2
            [11-Sep-2010] => 2
            [18-Sep-2010] => 2
            [25-Sep-2010] => 1
            [02-Oct-2010] => 1
            [09-Oct-2010] => 2
            [16-Oct-2010] => 2
            [23-Oct-2010] => 1
        )

And the same from the OO-saved file:

    [SO32-2002] => Array
        (
            [YID] => SO32-2002
            [Yacht description] => Jeanneau Sun Odyssey 
            [Size/model] => 32(2002)
            [DD-MMM-YYYY§4029200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4029900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4030600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4031300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4032000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4032700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4033400.00%] => GENERAL
¥
            [DD-MMM-YYYY§4034100.00%] => GENERAL
¥
            [DD-MMM-YYYY§4034800.00%] => GENERAL
¥
            [DD-MMM-YYYY§4035500.00%] => GENERAL
¥
            [DD-MMM-YYYY§4036200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4036900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4037600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4038300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4039000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4039700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4040400.00%] => GENERAL
¥
            [DD-MMM-YYYY§4041100.00%] => GENERAL
¥
            [DD-MMM-YYYY§4041800.00%] => GENERAL
¥
            [DD-MMM-YYYY§4042500.00%] => GENERAL
¥
            [DD-MMM-YYYY§4043200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4043900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4044600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4045300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4046000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4046700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4047400.00%] => GENERAL
¥
        )

Note this isn't date information, simply coulds varying from 0 to 2 but there 
seems a confusion with a date format.

An a final example of some 4-digit numbers:

From the Excel-saved file:

    [SO32-2002] => Array
        (
            [YID] => SO32-2002
            [Yacht description] => Jeanneau Sun Odyssey 
            [Size/model] => 32(2002)
            [24-Apr-2010] => 1100
            [01-May-2010] => 1100
            [08-May-2010] => 1100
            [15-May-2010] => 1100
            [22-May-2010] => 1100
            [29-May-2010] => 1250
            [05-Jun-2010] => 1250
            [12-Jun-2010] => 1250
            [19-Jun-2010] => 1250
            [26-Jun-2010] => 1400
            [03-Jul-2010] => 1400
            [10-Jul-2010] => 1400
            [17-Jul-2010] => 1400
            [24-Jul-2010] => 1500
            [31-Jul-2010] => 1500
            [07-Aug-2010] => 1500
            [14-Aug-2010] => 1500
            [21-Aug-2010] => 1400
            [28-Aug-2010] => 1400
            [04-Sep-2010] => 1400
            [11-Sep-2010] => 1400
            [18-Sep-2010] => 1250
            [25-Sep-2010] => 1250
            [02-Oct-2010] => 1100
            [09-Oct-2010] => 1100
            [16-Oct-2010] => 1100
            [23-Oct-2010] => 1100
        )

And the same again from the OO-saved file:

    [SO32-2002] => Array
        (
            [YID] => SO32-2002
            [Yacht description] => Jeanneau Sun Odyssey 
            [Size/model] => 32(2002)
            [DD-MMM-YYYY§4029200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4029900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4030600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4031300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4032000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4032700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4033400.00%] => GENERAL
¥
            [DD-MMM-YYYY§4034100.00%] => GENERAL
¥
            [DD-MMM-YYYY§4034800.00%] => GENERAL
¥
            [DD-MMM-YYYY§4035500.00%] => GENERAL
¥
            [DD-MMM-YYYY§4036200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4036900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4037600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4038300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4039000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4039700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4040400.00%] => GENERAL
¥
            [DD-MMM-YYYY§4041100.00%] => GENERAL
¥
            [DD-MMM-YYYY§4041800.00%] => GENERAL
¥
            [DD-MMM-YYYY§4042500.00%] => GENERAL
¥
            [DD-MMM-YYYY§4043200.00%] => GENERAL
¥
            [DD-MMM-YYYY§4043900.00%] => GENERAL
¥
            [DD-MMM-YYYY§4044600.00%] => GENERAL
¥
            [DD-MMM-YYYY§4045300.00%] => GENERAL
¥
            [DD-MMM-YYYY§4046000.00%] => GENERAL
¥
            [DD-MMM-YYYY§4046700.00%] => GENERAL
¥
            [DD-MMM-YYYY§4047400.00%] => GENERAL
¥
        )

Note again the 'confusion' over date format with 'DD-MMM-YYYY' being quoted. 
Note to this date format has been used for other data to overcome problems I 
found with confusion between US and 'rest of the world' date formats. So 
30/5/2010 without an explicit date format was interpreted by phpExcelREader as 
30-May, but 5/6/2010, the following week, was read as 6-May and not 5-Jun. 
Obviously 30/5 can't be the 5th of the 30th month and so is interpreted as 
30-May, but 5/6 can be interpreted either way around and although Excel would 
treat this as 5-Jun, phpExcelReader would flip it around (in comparison to the 
previous date) and display it as 6-May. I suspect reading from [rawdata] would 
give me the correct day number...in fact I think I confirmed that!

But excepting these OO-saved file problems, this is a stunningly useful class. 
Thank you :-)

.../Iain

Original comment by iain....@googlemail.com on 10 Jun 2010 at 11:03

Attachments:

GoogleCodeExporter commented 9 years ago 
And apologies for missing this out.

I'm using phpExcelReader v2.21 which I believe is the most current version?

.../Iain

Original comment by iain....@googlemail.com on 10 Jun 2010 at 11:08

GoogleCodeExporter commented 9 years ago 
OO version is 3.2.0 running on Vista (32-bit) plus Win7 (64-bit).

Hope this is of help.../Iain

Original comment by iain....@googlemail.com on 10 Jun 2010 at 2:29

GoogleCodeExporter commented 9 years ago 
I am using OpenOffice 3.4 version, windows vista.
In .xls sheet format is like this DD-MMM-YYYY(ex:05-Mar-2013), but when I am 
reading value from excel sheet it is giving default number format (ex: 41338). 
How to fix this issue?

Original comment by venky...@gmail.com on 9 Apr 2013 at 6:51

GoogleCodeExporter commented 9 years ago 
On line 835 in the function "_format_value" in the first line I returned:

return array ('string' => $ a, 'formatColor' => null);

and it worked in my case it worked.

In case:

// ADDED by Matt Kruse for better formatting
function _format_value ($ format, $ num, $ f) {

return array ('string' => $ a, 'formatColor' => null);

}

Surely the problem with other Excel files, but in my case the execel is simple 
then it did not yield problem.

Original comment by thalisso...@gmail.com on 7 Jul 2015 at 8:36