Not formatting cell correctly with [MM] code

[garrettgjb]

This is:

- [x ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

I expect for the column containing "Duration" should come in as "mm" format as defined in excel. Meaning it should look like an integer as you see when you are in excel.

The column subtracts one date from another date.
I.e.

2018-01-22 02:00:00 PM - 2018-01-22 01:00:00 PM

We format is as [mm] so it would be 60.

I think what is happening is this library doesn't know how to do binary operations with dates. If you multiple the decimal number by 1440 you get the correct number.

What is the current behavior?

Duration column comes in as a decimal in the php code.

What are the steps to reproduce?

Import the following file with phpspreadsheet:
importtest-phpspreadsheet.xlsx

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

	require __DIR__ . '/vendor/autoload.php';

	$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

	$file="importtest-phpspreadsheet.xlsx";
	$spreadsheet = $reader->load($file);
	$worksheet = $spreadsheet->getActiveSheet();

	echo "Should be 60";
	echo "\nActual: " . ($spreadsheet->getActiveSheet()->getCell('K2')->getFormattedValue());
	echo "\nFinished";

toArray return will have incorrect values.

Which versions of PhpSpreadsheet and PHP are affected?

PHP 5.6, Latest phpspreadsheet from composer

[PowerKiKi]

The code provided is not valid PHP code and cannot be run. And even if it could it would not show anything useful regarding this issue.

Anyway the described behavior is the expected behavior. The value you got are indeed the values stored in the file. What you see in Excel is not the raw values, but formatted value. You should have a look at getFormattedValue()

[garrettgjb]

Please check again. I updated the code so it is runnable, just put the spreadsheet in the same dir.

Output:
Should be 60
Actual: 0.041666666671517

My previous example called toArray with no params which my research showed should have formatted. In my updated sample, I have called getFormattedValue as you said so you can see its not working.

[garrettgjb]

I've reposted the issue here since this one is closed:
#343

[garrettgjb]

@PowerKiKi Can this issue be reopened? I'm guessing you were able to recreate it since you changed the title.

[PowerKiKi]

Please refrain from opening new issue for the same problem, we can continue the discussion here and re-open as needed.

The MCVE should not rely on an external file and the code should have been simplified down to:

<?php

require __DIR__ . '/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

var_dump(NumberFormat::toFormattedString(0.041666666671517, '[MM]'));

[garrettgjb]

@PowerKiKi sorry, I'm new to github issue reporting process and didn't realize the problem was with toFormattedString.

[garrettgjb]

I did a little debugging and it looks like in an attempt to remove color information, we clear out the format variable so it doesn't go into the date format code.

From toFormattedString:

$color_regex = '/^\\[[a-zA-Z]+\\]/'; $format = preg_replace($color_regex, '', $format);
It goes from "[MM]" to ""

I'm not an expert and regexp's and I don't know what color formatting looks like or I would attempt a fix.

[PowerKiKi]

I suggest you add a case for colors in tests/data/Style/NumberFormat.php and case for [MM] in tests/data/Style/NumberFormatDates.php and keep trying to debug/fix it while testing tests/PhpSpreadsheetTests/Style/NumberFormatTest.php.

The following page should help you find out what syntaxes should be supported: https://support.office.com/en-us/article/create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[oleibman]

Fixed by 699da09