Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The 'getFormattedValue()' method return format pattern instead of number formatted according to accounting number format #1929

Closed
merapidev opened this issue Mar 16, 2021 · 3 comments · Fixed by #3053

Comments

@merapidev
Copy link

This is:

- [x] a bug report

What is the expected behavior?

The method getFormattedValue will return:

  • (79.3%) for cell D2
  • 383.7% for cell D3
  • (0.79) for cell E2

What is the current behavior?

The method getFormattedValue return:

  • ("#,##0.0%") for cell D2
  • 110.1f% for cell D3
  • ("#,##0.00") for cell E2

What are the steps to reproduce?

Use attached excel file:
spreadsheet_test.xlsx

OR

Prepare excel file as shown below:
image

Formula for D2 and E2: =(B2-C2)/C2
Formula for D3 and E3: =(B3-C3)/C3

Formatting for D2 and D3:
image

Formatting for E2 and E3:
image

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

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load('spreadsheet_test.xlsx');
$sheet = $spreadsheet->getSheet(0);

$d2 = $sheet->getCell('D2')->getFormattedValue();
$d3 = $sheet->getCell('D3')->getFormattedValue();
$e2 = $sheet->getCell('E2')->getFormattedValue();
$e3 = $sheet->getCell('E3')->getFormattedValue();

file_put_contents('D2.txt', $d2);
file_put_contents('D3.txt', $d3);
file_put_contents('E2.txt', $e2);
file_put_contents('E3.txt', $e3);

Which versions of PhpSpreadsheet and PHP are affected?

Phpspreadsheet: 1.17.1
PHP: 7.4.15

@MarkBaker
Copy link
Member

Using the current master branch, I'm seeing the expected values for the Delta ((0.79) and 3.84); but can confirm that there is an issue with the percentage format masks.

I'm already doing some work on the formatting code after an unrelated bugfix last night (related to the _ placeholder in format masks), so I'll have a closer look tonight and try to identify the problem.

@merapidev
Copy link
Author

merapidev commented Mar 30, 2021

@MarkBaker Hi, did you identify the problem?

When the next release is expected?

@merapidev
Copy link
Author

merapidev commented Jun 9, 2021

Hi @MarkBaker,

I checked the formatting with new version (1.18) and there is small issue.
Expected value is (79.3%) for cell D2 but it return ( 79.3%).

oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Sep 7, 2022
Fix PHPOffice#1929. This was already substantially fixed, but there was a lingering problem with an unexpected leading space. It turns out there was also a problem with leading zeros, also fixed. There are also problems involving commas; fixing those seems too complicated to delay these changes, but I will add it to my to-do list.
oleibman added a commit that referenced this issue Sep 12, 2022
Fix #1929. This was already substantially fixed, but there was a lingering problem with an unexpected leading space. It turns out there was also a problem with leading zeros, also fixed. There are also problems involving commas; fixing those seems too complicated to delay these changes, but I will add it to my to-do list.
MarkBaker added a commit that referenced this issue Sep 25, 2022
### Added

- Implementation of the new `TEXTBEFORE()`, `TEXTAFTER()` and `TEXTSPLIT()` Excel Functions
- Implementation of the `ARRAYTOTEXT()` and `VALUETOTEXT()` Excel Functions
- Support for [mitoteam/jpgraph](https://packagist.org/packages/mitoteam/jpgraph) implementation of
  JpGraph library to render charts added.
- Charts: Add Gradients, Transparency, Hidden Axes, Rounded Corners, Trendlines, Date Axes.

### Changed

- Allow variant behaviour when merging cells [Issue #3065](#3065)
  - Merge methods now allow an additional `$behaviour` argument. Permitted values are:
    - Worksheet::MERGE_CELL_CONTENT_EMPTY - Empty the content of the hidden cells (the default behaviour)
    - Worksheet::MERGE_CELL_CONTENT_HIDE - Keep the content of the hidden cells
    - Worksheet::MERGE_CELL_CONTENT_MERGE - Move the content of the hidden cells into the first cell

### Deprecated

- Axis getLineProperty deprecated in favor of getLineColorProperty.
- Moved majorGridlines and minorGridlines from Chart to Axis. Setting either in Chart constructor or through Chart methods, or getting either using Chart methods is deprecated.
- Chart::EXCEL_COLOR_TYPE_* copied from Properties to ChartColor; use in Properties is deprecated.
- ChartColor::EXCEL_COLOR_TYPE_ARGB deprecated in favor of EXCEL_COLOR_TYPE_RGB ("A" component was never allowed).
- Misspelled Properties::LINE_STYLE_DASH_SQUERE_DOT deprecated in favor of LINE_STYLE_DASH_SQUARE_DOT.
- Clone not permitted for Spreadsheet. Spreadsheet->copy() can be used instead.

### Removed

- Nothing

### Fixed

- Fix update to defined names when inserting/deleting rows/columns [Issue #3076](#3076) [PR #3077](#3077)
- Fix DataValidation sqRef when inserting/deleting rows/columns [Issue #3056](#3056) [PR #3074](#3074)
- Named ranges not usable as anchors in OFFSET function [Issue #3013](#3013)
- Fully flatten an array [Issue #2955](#2955) [PR #2956](#2956)
- cellExists() and getCell() methods should support UTF-8 named cells [Issue #2987](#2987) [PR #2988](#2988)
- Spreadsheet copy fixed, clone disabled. [PR #2951](#2951)
- Fix PDF problems with text rotation and paper size. [Issue #1747](#1747) [Issue #1713](#1713) [PR #2960](#2960)
- Limited support for chart titles as formulas [Issue #2965](#2965) [Issue #749](#749) [PR #2971](#2971)
- Add Gradients, Transparency, and Hidden Axes to Chart [Issue #2257](#2257) [Issue #2229](#2929) [Issue #2935](#2935) [PR #2950](#2950)
- Chart Support for Rounded Corners and Trendlines [Issue #2968](#2968) [Issue #2815](#2815) [PR #2976](#2976)
- Add setName Method for Chart [Issue #2991](#2991) [PR #3001](#3001)
- Eliminate partial dependency on php-intl in StringHelper [Issue #2982](#2982) [PR #2994](#2994)
- Minor changes for Pdf [Issue #2999](#2999) [PR #3002](#3002) [PR #3006](#3006)
- Html/Pdf Do net set background color for cells using (default) nofill [PR #3016](#3016)
- Add support for Date Axis to Chart [Issue #2967](#2967) [PR #3018](#3018)
- Reconcile Differences Between Css and Excel for Cell Alignment [PR #3048](#3048)
- R1C1 Format Internationalization and Better Support for Relative Offsets [Issue #1704](#1704) [PR #3052](#3052)
- Minor Fix for Percentage Formatting [Issue #1929](#1929) [PR #3053](#3053)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

Successfully merging a pull request may close this issue.

2 participants