search

PHPOffice / PhpSpreadsheet

A pure PHP library for reading and writing spreadsheet files
https://phpspreadsheet.readthedocs.io
MIT License
13.24k stars 3.42k forks source link

Concatenated cell content is only displayed up to the first "&" #3010

Open rf1234 opened 2 years ago

rf1234 commented 2 years ago

Discussed in https://github.com/PHPOffice/PhpSpreadsheet/discussions/3009

Originally posted by **rf1234** August 17, 2022 I have a cell containing a string and a date like this: ="Gesamtbetrag in EUR "&BaseData!C3 The cell BaseData!C3 is filled by PHPSpreadsheet with a regular date in format DD.MM.YYYY. When I open the sheet after the BaseData sheet was filled by PHPSpreadsheet I only see "Gesamtbetrag in EUR". If I then click on the cell and click into the editing field above the spreadsheet WITHOUT DOING ANYTHING ELSE, the date suddenly is being displayed. Same here: The content is =HeaderFilters!H3&", "&HeaderFilters!I3 Initially I only see the value of the first referenced cell which is a date in format DD.MM.YYYY like above. The commas isn't shown and the time in the second reference cell isn't shown either. If I click into the editing field above the spreadsheet, the rest is shown as well. My referenced cells are properly formatted as DATE and TIME in the second case. My target cells are formatted as STANDARD. How can I change Excel's behavior here? The weird thing is that the problem occured only recently without making any changes in my code. If I open the spreadsheet with Google Sheets it is ok, so it is something about MS Excel 2019 and PHP Spreadsheet, unfortunately. Couldn't find anything on this elsewhere though. Best regards Roland
MarkBaker commented 2 years ago

What version of PhpSpreadsheet are you running?

rf1234 commented 2 years ago

I run PHPSpreadsheet version 1.2.1 for this sheet.

I have to run multiple different versions for my needs: One version doesn't do XLSM sheets, the other one doesn't do graphs properly. In total I run 1.2.1, 1.3.1 and 1.6.0

MarkBaker commented 2 years ago

1.2.1 is a very ancient version (released Mar 04, 2018); even 1.6.0 is old (released Jan 02, 2019). Can you please try running against an up-to-date version of PhpSpreadsheet: the current latest release is 1.24.1 (from July 18th this year)

rf1234 commented 2 years ago

I was afraid that was going to happen, Mark! It will be really difficult for me because I had to implement a couple of hacks in PHP Spreadsheet but I will try.

Do you have any other idea for me, please?

I remember implementing a hack for a "secondary Y-axis" in one of the versions I use. That had been a pull request for some time. Has "secondary Y-axis" been implemented at all?

Roland

MarkBaker commented 2 years ago

I couldn't say if secondary Y-axis has been implemented or not; but @oleibman may be able to answer, he has made significant improvements in chart handling in the last 6 months

rf1234 commented 2 years ago

Hi Mark,

just ran my report with 1.24.1 - and the error disappeared. Thank god this report is a simple report - so it looks ok. For some time I will be using four different PHP Spreadsheet versions in parallel then.

I just searched for "secondary" in my different versions of PHP Spreadsheet. Nothing in version 1.24.1. In my hacked versions I have 75 occurences each. I even made an example with it, but don't recall how to use the examples. It is a long time ago since I built this stuff!

Here is a link to a question that includes a link to a pull request on secondary Y-axis. I remember there had been more pull requests. I copied my solution from a different pull request though. https://github.com/PHPOffice/PhpSpreadsheet/issues/1072

Roland

oleibman commented 2 years ago

Sorry, no secondary axis yet. If you want to open a feature request, preferably with a simple example or two, I can put it on my to-do list. I will certainly look at the PR you linked above.

MarkBaker commented 2 years ago

You may find that some of the other issues that mean you need to run multiple different versions of PhpSpreadsheet have also been resolved over the last five years, and that you can run 1.24.1 instead of some of those other versions

rf1234 commented 2 years ago

Thanks, guys!

Didn't find the examples in the downloaded package of the new release. Are they in a new location now?

MarkBaker commented 2 years ago

Didn't find the examples in the downloaded package of the new release. Are they in a new location now?

They're not included in the download because too many people were moaning that it made the download too big; so they're only visbible here in the repo, of it you do a git clone to pull the repo

rf1234 commented 2 years ago

too many people were moaning that it made the download too big

I would really like to have the problems those people have ... Thanks again for your quick help!!

rf1234 commented 2 years ago

In the meantime I have managed to migrate three of my 25 reports to the new PHP Spreadsheet version. I also tried with a graphic report that doesn't use secondary Y-axis - and it crashed immediately. Nothing in the console but a broken Excel file was the result. Unfortunately that has been the case with all versions higher than 1.2.1 that I have tried with these reports.

I attached a sample file that I created with version 1.2.1 The first two sheets of the workbook are monthly and quarterly views. The remaining sheets are usually "very hidden" and serve as data sources.

Any idea why this doesn't work with subsequent versions?

I also attached the broken file created by version 1.24.1. That is the second file attached.

MarktZinsenDevisen_2022-06-30 (3).xlsx MarktZinsenDevisen_2022-06-30 (1).xlsx

oleibman commented 2 years ago

I agree that I see a problem with the newer version. But your spreadsheet has 12 charts! Is it possible, for starters, to give me a fighting chance by creating a spreadsheet with only one chart that works in the old version and fails in the new?

rf1234 commented 2 years ago

Hmmm, I am afraid I don't have any simple use cases. I wrote this stuff about four years ago.

I was able to resolve my immediate problems with the new version now. Hope I will survive with the hacked old version for a few more weeks ... Because I will be traveling the next months and probably will only be able to look into this in more detail in September or October.

So please bear with me for a while. I agree you deserve a better test case and I am willing to provide it. But I need more time. Thank you for your understanding and for your help!! I am impressed with how fast you are responding! (That was very different when I worked with PHPSpreadsheet intensively a couple of years ago.)

Best Roland

rf1234 commented 2 years ago

But your spreadsheet has 12 charts!

I only found six. But you are right! I have what I call "legend charts" because otherwise I couldn't display a legend in an acceptable manner using PHP Spreadsheet. Everything looked really ugly ... In a legend chart everything except for the legend is invisible. But that way the solution was much more flexible and looked a lot better!

My 25 reports consume over 16,000 lines of code ... I really gave you the most simple one, I swear ...

oleibman commented 2 years ago

Just as a suggestion, each chart has to be explicitly added to the spreadsheet using $sheet->addChart($chart). Can you comment these out then add them back one by one till something breaks?

rf1234 commented 2 years ago

Yep, it's on my list!