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

Unmerge cells removes content #3065

Closed
peterrehm opened this issue Sep 14, 2022 · 6 comments
Closed

Unmerge cells removes content #3065

peterrehm opened this issue Sep 14, 2022 · 6 comments

Comments

@peterrehm
Copy link

peterrehm commented Sep 14, 2022

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?

When unmerging cells in Excel, the content of the cells is preserved. Attached is the original file where I have merged
cells and the content is not in the main cell. Unmerging it shows the colored cells and the content when using excel.

What is the current behavior?

The background color is preserved, not the content.

What are the steps to reproduce?

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';

        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($fileName);

        $sheetNames = $spreadsheet->getSheetNames();

        foreach ($sheetNames as $sheetName) {
            foreach ($worksheet->getMergeCells() as $cells) {
                $worksheet->unmergeCells($cells);
            }
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save(__DIR__ . '/../../file_output.xlsx');

If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

What features do you think are causing the issue

- [X] Reader
- [ ] Writer
- [ ] Styles
- [ ] Data Validations
- [ ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

file_original.xlsx
file_output_unmerged_phpspreadsheet.xlsx
file_unmerged_excel.xlsx
Unmerg

Which versions of PhpSpreadsheet and PHP are affected?

PHP 8.1 and phpspreadsheet 1.24.1

@MarkBaker
Copy link
Member

When cells are merged using MS Excel, only the top left cell in the merge range retains any value

image

So how did you create an xlsx spreadsheet file that retains the values in other cells within that merge range?

@peterrehm
Copy link
Author

Thanks for the very fast response. I got that through an Excel Export of an application.

@MarkBaker
Copy link
Member

MarkBaker commented Sep 14, 2022

It's certainly non-standard for an Xlsx file; I'm currently trying to recreate it using the standard Spreadsheet applications that can be used to generate Xlsx files

I know that OpenOffice allows cell values (other than the top-left cell) to be retained or merged into the merge range when merging cells; but OO doesn't support saving to Xlsx.

image

LibreOffice 7.4 also seems to support a similar feature, and will save as Xlsx to create a file similar to your own where the cells can be unmerged to show their original content.

image

It's probably only a matter of time until Excel does support that feature, so we should probably prepare for it

@peterrehm
Copy link
Author

Thanks for the great analysis - would be awesome if you could find any solution.
Let me know if I can help.

@MarkBaker
Copy link
Member

Thanks for the great analysis - would be awesome if you could find any solution.
Let me know if I can help.

Probably just testing against the latest master branch

@peterrehm
Copy link
Author

Thanks, it works like a charm. You have provided provided an amazing support 🥰

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

No branches or pull requests

2 participants