Ignore cell errors in Excel (e.g. "Number Stored as Text")

[sgarner]

This is:

- [ ] a bug report
- [x] 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?

When a cell contains a value that looks like a number but is intentionally formatted as text (sometimes this is desirable because the values might not always be purely numeric and you don't want Excel to reformat them), and written to an XLSX file, Excel displays a warning beside the cell when viewing the worksheet.

Excel has an option to "Ignore" the error, which appears to be a flag that is then written to the cell. It would be great if this functionality could be exposed as part of the PhpSpreadsheet API.

For reference, there is a JavaScript project for writing XLSX files called SheetJS which has this feature, here is how they do it.

What is the current behavior?

The warning is visible to users next to Text formatted cells that Excel thinks are numbers, and there's nothing I can do about it. Users might choose to "Convert to Number" and create formatting problems (including data corruption if exported to CSV).

What are the steps to reproduce?

N/A

[MarkBaker]

Setting a style of quotePrefix for those cells should resolve the issue

[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.

[LarryBarker]

@MarkBaker I'm having similar issues but a different warning, that "The formula in this cell differs from the formula in this area of the spreadsheet." Are you aware of any way to disable these warnings entirely? Or a way to resolve them?

I have a fairly complex and customized export that is seeing these errors more and more and my client has required me to fi the "little green triangles." Here is a sample:

What's frustrating/confusing is that these errors don't seem to be consistent. For example, if you look at the first section, the Loan Amount and Initial Funding columns have no issues. However, the next section is displaying errors, despite the cell values following the exact same pattern, with the exception the first row has $0, and the next has a different value.

Then if you look at the second section, the Spread column, has a warning because 4.25% is not the same as 4.50% so there is a warning.

I have tried multiple combinations of styling, number formatting, etc. and cannot seem to eliminate these warnings. Here is a code sample for the screen shot:

 // Apply special formatting for spread percentage
$sheet->setCellValue('J' . $i, $row->spreadPercent ? '=TEXT(' . $row->spreadPercent . ', "0.00%")' : '')
->getStyle('J' . $i)
->getAlignment()
->setHorizontal(Alignment::HORIZONTAL_RIGHT);
$sheet->getStyle('J' . $i)->setQuotePrefix(true)
->getNumberFormat()
->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);

 // Apply special formatting for loan origination fee percentage
$sheet->setCellValue('K' . $i, $row->originationFee ? '=TEXT('.$row->originationFee . ', "0.00%")' : '')
->getStyle('K' . $i)->getAlignment()
->setHorizontal(Alignment::HORIZONTAL_CENTER);
$sheet->getStyle('K' . $i)->setQuotePrefix(true)
->getNumberFormat()
->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);

Any feedback or input is greatly appreciated!

[oleibman]

Reopening; I will submit a PR later today to address this. The suggestion above to use quotePrefix to eliminate the problem is not effective.