Handling dates

[stephengmatthews]

I create a new xlsx file using libreoffice on ubuntu, enter a date (e.g., 27/04/2020) in one cell, and produce the same xlsx but using excel on android with the same date. I run the following code, and the results are different. I was expecting to see "2020-04-27 00:00:00" in both arrays. Am I doing something wrong.

I'm using simplexlsx 0.8.14, LibreOffice 6.0.7.3 00m0(Build:3), and ubuntu 18.04.

date-excel-android.xlsx
date-libreoffice-ubuntu.xlsx

<?php
ini_set('error_reporting', E_ALL);
ini_set('display_errors', true);

require_once __DIR__.'/../src/SimpleXLSX.php';

if ( $xlsx = SimpleXLSX::parse('date-excel-android.xlsx') ) {
	print_r( $xlsx->rows() );
} else {
	echo SimpleXLSX::parseError();
}

if ( $xlsx = SimpleXLSX::parse('date-libreoffice-ubuntu.xlsx') ) {
	print_r( $xlsx->rows() );
} else {
	echo SimpleXLSX::parseError();
}

Array
(
    [0] => Array
        (
            [0] => 2020-04-27 00:00:00
        )

)
Array
(
    [0] => Array
        (
            [0] => 43948
        )

)

[stephengmatthews]

I used rowsEx() instead of rows() in test.php from my previous post. It produced:

Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [type] => 
                    [name] => A1
                    [value] => 2020-04-27 00:00:00
                    [href] => 
                    [f] => 
                    [format] => mm-dd-yy
                    [r] => 1
                )

        )

)
Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [type] => n
                    [name] => A1
                    [value] => 43948
                    [href] => 
                    [f] => 
                    [format] => DD/MM/YYYY
                    [r] => 1
                )

        )

)

It appears that the date is assigned the type n. I looked inside the XLSX files at xl/worksheets/sheet1.xml to find that Excel does not set the cell type attribute (t) but libreoffice does. That is, Excel has
<c r="A1" s="1"><v>43948</v></c>
and libreoffice has
<c r="A1" s="1" t="n"><v>43948</v></c>

I checked the OOXML standard to find there is a value for number but not date. I used the web format available at https://c-rex.net/projects/samples/ooxml/e1/Part4/OOXML_P4_DOCX_ST_CellType_topic_ID0E6NEFB.html

I made two changes to value() in SimpleXLSX.php. They are:

1. L833 checks the type and assumes it's a number if the type is not set. Specifically checking for a number (n) handles the case where libreoffice sets this value for the cell type. I changed
   if ( !$dataType ) { // number
   to
   if ( !$dataType || $dataType === 'n' ) { // number

2. L837 checks the format. The logic seems to be: assume it's a date if the format specifies a month (i.e., m). The Excel file used lowercase and the libreoffice file used uppercase. I changed
   if ( strpos( $format, 'm') !== false ) {
   to
   if ( preg_match('/[mM]/', $format) ) {

This resolved the issue for me. I've only tested it on the two files I provided.

[shuchkin]

Thx Stephen, prefect bug description and solution )
Added your code in 0.8.15