$spreadsheet->getCell()->setValue($val) unexpected result sometimes (no idea why) #3711
Comments
|
All your cell values are nominally strings because of the arrays that you're using. Let's take a look at the cell whose value is setValue($row[$columnName])With the default value binder, the cell will wind up with a value of floating point 21.8. setDataType(PhpSpreadsheet\Cell\DataType::TYPE_STRING)That tells PhpSpreadsheet to treat the value as a string even though it is floating point. Now, in several places in the writer, when it sees the value should be treated as a string, it uses the value as an index to an array. But the actual value is float, so it tries to use the float value as an index, and that is deprecated (and will generate deprecation messages if you are so configured), and, in addition, will, as you have seen, wind up with an incorrect result (it will use 21 as the index, not 21.8, so if you had another similar value like, say, 21.2, both would wind up using the same index). Using I think you are probably better off omitting |
Fix PHPOffice#3711. User set a cell value to float (implicitly by default value binder), then used `setDataType` to change its type to string. This caused a problem for Xlsx Writer, which uses the string cell values as an index into a Shared String array. However, as the cell actually contained a floating point value, Php treated it as an integer index; such a treatment is both deprecated, and leads to invalid values in the spreadsheet. The use case for `setDataType` is not strong. The user always has the option to use `setValueExplicit` if the type is important. Setting a type afterwards, i.e. irrespective of the value, seems like a peculiar action. Indeed, there are no tests whatever for such use in the unit test suite. There are two possible approaches to fixing this problem. The first is to add casts to the 3 or 4 places in Writer Xlsx which might be affected by this problem (hoping that you've found them all and realizing that similar changes might be needed for other Writers). The second is to change `setDataType` to call `setValueExplicit` using the current value of the cell, thereby possibly changing the cell value. I have gone with the second option - it seems like a much more logical approach, and guarantees that the content of the cell will always be consistent with its declared type. It is, however, a breaking change; if, for example, you have a cell with a string or numeric value and specify `boolean` to `setDataType`, the cell's value will change to `true` or `false` with no way to get back to the original.
|
@oleibman thx a lot for the quick and detailed reply, but i think you did not analyzed issue completely (or i not completely understand your previous message). I know it's hard to analyze cyrillic-based array keys, let me try to explain more clear: all array values in example above is strings, moreover value '21.8' meets once in all data set. So when i set |
|
The problem is that you had one value initialized to 'Высота, см' => '21.8', // approximately line 134, winds up in cell AZ1
'Остатки на складе998' => '21', // approximately line 842, winds up in cell O14
'Остатки на складе998' => '21', // approximately line 921, winds up in cell O15Because of the bug which the PR will fix, cells AZ1, O14, and O15 wound up with the same value, namely 21.8. After the fix, they will all wind up with the proper values. Please let me know if you do not feel that is the case. |
* Inconsistency Between Actual and Declared Type - Minor Break Fix #3711. User set a cell value to float (implicitly by default value binder), then used `setDataType` to change its type to string. This caused a problem for Xlsx Writer, which uses the string cell values as an index into a Shared String array. However, as the cell actually contained a floating point value, Php treated it as an integer index; such a treatment is both deprecated, and leads to invalid values in the spreadsheet. The use case for `setDataType` is not strong. The user always has the option to use `setValueExplicit` if the type is important. Setting a type afterwards, i.e. irrespective of the value, seems like a peculiar action. Indeed, there are no tests whatever for such use in the unit test suite. There are two possible approaches to fixing this problem. The first is to add casts to the 3 or 4 places in Writer Xlsx which might be affected by this problem (hoping that you've found them all and realizing that similar changes might be needed for other Writers). The second is to change `setDataType` to call `setValueExplicit` using the current value of the cell, thereby possibly changing the cell value. I have gone with the second option - it seems like a much more logical approach, and guarantees that the content of the cell will always be consistent with its declared type. It is, however, a breaking change; if, for example, you have a cell with a string or numeric value and specify `boolean` to `setDataType`, the cell's value will change to `true` or `false` with no way to get back to the original. * Strict Types for New Tests Consistent with work being done in PR #3718. * Improve Test Better match to original issue. * Typo
This is:
What is the expected behavior?
setValue() method results should be equal to passed value.
What is the current behavior?
has some strange values instead of exprected.
What are the steps to reproduce?
The result is in file and screenshot
But if I start use
->setValueExplicit($value, PhpSpreadsheet\Cell\DataType::TYPE_STRING);instead ofsetValue()everything going to be ok, as i expects.Can someone explain me why? is it a bug, isn't it?
What features do you think are causing the issue
Which versions of PhpSpreadsheet and PHP are affected?
v. 1.25 + php 7.3
The text was updated successfully, but these errors were encountered: