Images, formulas and checkboxes disappear

[kboumedal]

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 current behavior?

Images (sheet "Accueil"), formulas (sheet "Data" cell C15) and checkboxes (sheet "Renovez votre maison) disappear after IOFactory::createWriter and save

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

// Create new Spreadsheet object
        $spreadsheet = IOFactory::load(
[Simulateur_B_to_C_8.1_SANS_MACRO_2025 - Copie.xlsx](https://github.com/user-attachments/files/18151163/Simulateur_B_to_C_8.1_SANS_MACRO_2025.-.Copie.xlsx)
);

$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

$temporaryPathfile = rtrim(sys_get_temp_dir(), \DIRECTORY_SEPARATOR).\DIRECTORY_SEPARATOR.md5(uniqid().mt_rand());

$writer->save($temporaryPathfile);

### What features do you think are causing the issue

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

### Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 3.6, PHP8.3

[oleibman]

I have downloaded your spreadsheet, but don't understand what problems you are having.

• Sheet Acceuil has at least some images on the copy. Which do you think are missing?
• Agree that Data!C15 seems to contain a value rather than a formula.
• Where are the checkboxes supposed to be on sheet "Renovez ..."?

Even given the answers to the questions above, this spreadsheet may be too complicated for me to debug. Might you be able to simplify it at all?

[oleibman]

The "value rather than formula" problem indicates we may not be handling "shared formulae" correctly. Here's what the xml has to say:

<c r="C14" t="b">
<f>IF(B14="oui",TRUE,FALSE)</f>
<v>0</v>
</c>
...
<c r="C15" t="b">
<f t="shared" si="0"/>
<v>0</v>
</c>

C15 is apparently using a "shared formula" derived from C14, but I don't see anything in C14 to suggest that it is a shared formula. I will need to research.

[oleibman]

The following xml lines may be relevant, in particular the line with ref:

<c r="C10" t="b">
<f>IF(B10="oui",TRUE,FALSE)</f>
<v>0</v>
</c>
...
<c r="C11" t="b">
<f t="shared" ref="C11:C16" si="0">IF(B11="oui",TRUE,FALSE)</f>
<v>0</v>
</c>

[oleibman]

Shared formulae work correctly on a much simpler spreadsheet. Whatever the problem is here, it seems specific to the more complicated spreadsheet.

[oleibman]

The problem seems to be the handling of shared formulae with a boolean result. Expect a fix for that problem in a day or two. I still need more information about the other problems.

[oleibman]

I believe the missing pictures (e.g. B8 on sheet Acceuil) are placed in the cells rather than over them. This is a very recent addition to Excel and we do not yet support it, which is why they are not found in the copy.

Still need more information about missing checkboxes.

[kboumedal]

Thanks for your quick response

Here is an image showing a checkbox disappearing in the "Renovez.." sheet

[oleibman]

Can you download your spreadsheet with sheet protection off, at least on the worksheet you want me to look at? Or, if you prefer, let me know the password to unprotect it.

[kboumedal]

Sorry for the delay, here is the document without the password Simulateur_B_to_C_8.1_SANS_MACRO_2025 - copieV2.xlsx

[oleibman]

Thank you for the sample. I see what the problem is. The documentation about it is pretty sparse, so I don't know what, if anything, I can do about it. For now, I'm just going to have to document what I've found.

Your spreadsheet contains a file xl/featurePropertyBag/featurePropertyBag.xml. This describes the checkbox, and may or may not be the same for all spreadsheets which use checkboxes in this manner, i.e. by selecting "Checkbox" on the "Insert tab". It appears to be available only for Excel 365. PhpSpreadsheet knows nothing about this file, ignoring it on read and not writing it on output.

The style for a cell which uses a checkbox in this manner appears like:

<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0">
<extLst>
<ext uri="{C7286773-470A-42A8-94C5-96B5CB345126}" xmlns:xfpb="http://schemas.microsoft.com/office/spreadsheetml/2022/featurepropertybag">
<xfpb:xfComplement i="0"/>
</ext>
</extLst>
</xf>

The first line there is "normal" styling for the cell. The extlst portion is what appears to implement the checkbox. PhpSpreadsheet knows nothing about this, ignoring extlst on read and not generating it on write.

So, a solution, if any, for this particular problem would be to:

• possibly add a "checkbox" property to Style
• detect the checkbox extlst on read
• check if there are any such checkboxes before write
  • if yes, write out featurePropertyBag
  • if yes, write exlst for style when appropriate

This seems like it could be a lot of work. It won't happen any time soon. I will leave this ticket open in the meantime.

[kboumedal]

Ok thank you for the feedback, let me know when the problem is solved

[kboumedal]

Hi, any feedback on this issue ?

[oleibman]

It's on my to-do list, but there is much ahead of it. Sorry.

[kboumedal]

Hello! I'm coming back on this issue as our client is requesting a solution for this.

Any feedback ?

[oleibman]

Someone else is actively working on PR #4664. I have not had a chance to assess how close it is to being merged.

[raziel057]

Hello, I currently implemented the read of images in cells. I just tested and if I read and write the image that was in cell is taken and rewritten as floating picture. It's clearly not perfect but better than in current situation.

We should add a property inCell to know how to write the drawing and recreate the richData folder structure if there is some inCell drawing to store.

[oleibman]

@kboumedal I just tried to load and save your spreadsheet using the newest PR from @raziel057 PR #4677. The good news is that the missing images were copied faithfully. However, there were some new problems with the output spreadsheet. So, progress is being made, but we aren't all the way there yet.

[oleibman]

@kboumedal To investigate some problems, can you supply a version of your spreadsheet where sheet "Annexe 3" is not protected, and sheets "Paramètres" and "Paramètres Lesosai" are not hidden.

[kboumedal]

Hi, thank you for your feedback, here is the file updated :

Simulateur_B_to_C_8.1_SANS_MACRO_2025.-.copieV3.xlsx

[oleibman]

Thank you. I'm afraid that there was nothing obvious that I could see that was wrong with the "corrupted" worksheets. Your workbook may be just too complicated for me to analyze. I will keep trying, but am not all that hopeful.