Double Values in a merged range after clicking enable editing

[berberon]

*** SOLVED ***

Hi,

I've encountered a strange issue.

In my Excel sheet, I have a set of three merged cells with a formula in the first cell, along with some conditional formatting that works perfectly.

Initially, everything looks fine when I open the Excel file. However, as soon as I click on "Enable Editing" at the top, the formula value doubles, appearing twice.

Another issue I've noticed is when I select the merged range and click on "Merge & Center", the value appears only in one cell. But if I click "Merge & Center" a second time, it affects all similar merged cells in the Excel sheet, making everything look correct.

Also, if i don't merge, there is no problem. The value is set to a single cell.

I've attached an example file. The issue seems to be with the merged cells at the bottom of the table. What could be causing this?

PricezBI_מחיר_נפוץ_שני_תאריכים_1_2024-05-12-15-38.xlsx

[MarkBaker]

Merging cells can have some interesting effects when the merge has been done in different spreadsheet applications.
Excel itself only provides a single option, to take the value from the top-left cell in the range, and blank all the other cells.
Open/Libre Office can do the same, but also allows the options of moving all the cell values to the top-left (displayed) cell, or of keeping the content of the remaining cells but hiding those values. This last option is the default.
If the worksheet was created using Open/Libre Office and this default option was used, then the other cell values are still in the file. If it is then unmerged, those hidden cell values are revealed.

The quirk might be related to this, but I can try investigating further. Perhaps you can provide more details of how the file was created.

[berberon]

this is an excel created from scratch with php spreadsheet

i think the problem is with the conditional formatting and not the merge.

[berberon]

If I create the conditional format manually in the excel it works ok.
if I create it with PHP spreadsheet it has double values

[berberon]

Please let me know if there is anything you need from me to reproduce this.

[berberon]

I spent hours playing with this yesterday and this morning again. It came aparent that the problem is related to the conditional formating. I tried moving code around, changing the order of things but nothing changed.

I tried removing the applyFromArray() and the problem was solved. There was no change in the format but also no double values.

At this point i strted removing lines from the formatting array untill i found the problematic line :

'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],

Removing this line from

[
	'font'  => ['normal'  => true,'color' => ['rgb' => '638420'],'size'  => 11,'name'  => 'Calibri'],
	'fill' => ['fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,'color' => ['rgb' => 'd7efc7']],
	'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER],
	'borders' => ['allborders' => ['style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,'color' => ['rgb' => 'bbb9b9']]]
]

Solved the problem.

[oleibman]

Thank you for all the information. I need to do more testing, but I believe the Xlsx Writer is writing out Alignment values in Conditional Styles, and should not be doing so - Alignment is not a property you can set in a conditional style in Excel. This is consistent with your explanation that removing the alignment line solved your problem. I need to develop some test cases, but should have a PR ready within a few days.

[berberon]

I'm kind of new to working with GitHub / Composer.
When you guys make an Update / Fix a bug, how can i update my server?
Also, I'm in the middle of migrating from Centos 7 / php 7.4 to Ubuntu / php 8 and this is why I'm going over all of my PHPEXCEL and transfering to phpspreadsheet.

the PHP 7 server is running 1.29, the PHP 8 is running ^2.0

is there a way to update the fix on both?

[oleibman]

In this case, you don't really need the fix; you can just update your application to not specify Alignment in your conditional style.

We do not retrofit our fixes to prior releases. If you really need a fix after it has been merged but before a release containing the PR is available:

composer require phpoffice/phpspreadsheet:dev-master

There are also ways to get code even before it's been merged.

Once a new release is available, you can just use:

composer update

[berberon]

Thanks,

I apprecuate the explanation.

[oleibman]

Closing. Solved by PR #4027, and user has indicated "Solved" at the top of this discussion.