Six years ago, my PR #1557 was merged. It was written mainly to improve code coverage in Xml Reader, but some changes and enhancements were made to the Reader code. I noted in that ticket: "File templates/Excel2003XMLTest.xml, used in some tests, is not
readable by a current version of Excel. I have substituted a new file
excel2003.xml to be used in its place. I have not deleted the original
in case someone in future (possibly me) wants to see what it needs to
make it usable." That time has finally arrived.
Excel could not open the file. LibreOffice could, and did a pretty decent job, but something seemed wrong with how it handled formulas. PhpSpreadsheet could not read it, because a field identified as a date/timestamp was wrong - it specified a time-of-day hour as `288`. I made a copy of the file, changed the invalid hour to something valid. It turns out that was not Excel's problem - it still could not read the file. But, for the first time that I can recall, Excel produced decent diagnostic messages! They were in a location that you can't get to through Windows Explorer, even with "hide system files" turned off. However, you could get to it through Windows Command Prompt. There were 56 problems, all involving formulas (which was probably LibreOffice's problem). The formulas were all of the form:
```
of:=[.B1]+[.C1]
```
Now, XML spreadsheets use RC format rather than A1 format for its cells. The formula above is of a style that LibreOffice uses, but Excel does not understand it. It is looking for something like:
```
ss:Formula="=RC[-6]+RC[-5]
```
Excel2003Xml doesn't understand the formula in the file, but PhpSpreadsheet does. But, before it could do so, we had to eliminate the Exception when we tried to parse the invalid date/time. So, the first order of business was to wrap the Xml Reader date conversion logic in a try/catch. This allowed PhpSpreadsheet to read the file, and then to save it as an Xlsx file. Then we could open the Xlsx file in Excel, and save it as Xml. Voilà. We have converted the unreadable Xml file to a readable one. LibreOffice can also read the new file, and the formulas are now correct. BTW, I have no idea what was intended on sheet "Report Data" cells G17:G29. They all subtract a value from an empty cell, so the result is always negative, and try to format it as a date (which Excel doesn't like for negative numbers). Whatever that problem is, there is no need to fix it at this time.
At this point, a little cleanup was still needed. The Xml file was using some strange named styles, e.g. `Medium Date`. I was able to get a list of these with the google query `excel 2003 ooxml named number formats`. However, the list came only in the AI portion of the response; I could not find a link to anything permanent. So I went with what it showed me.
The earlier PR suggested that handling UTF-16 for this format would be difficult. However, saving the now-usable UTF-8 Xml file as UTF-16 with BOM results in a file which PhpSpreadsheet (and Excel and LibreOffice) can read without any code changes.
Fix#4809. This initial work for this PR was done by @xxltomcat-ux in PR #4812. That PR needed some work, and was accidentally closed in a manner that caused all its work to be lost. As compared with that version, this version corrects a write bug when Ods output is added to 27_Images_Xlsx, adds limited support for Read, and adds some tests (more are probably needed). What follows here is the original description from the closed PR.
# Summary
Currently, the ODS writer in PhpSpreadsheet does not support exporting images. Images (Drawing or MemoryDrawing objects) are completely ignored in ODS exports, though they work correctly for XLSX. This issue proposes and provides a full implementation to add support for image/drawing export in ODS, bringing feature parity with the XLSX writer.
# Problem
- ODS exports silently drop images and worksheet graphics
- This is a required feature for interoperability with LibreOffice/OpenOffice users
# Key Implementation:
- Update: Writer/Ods.php to collect/package images into the export ZIP
- Update: Writer/Ods/Content.php to integrate images into content.xml and table cells
- New: Writer/Ods/Drawing.php to manage extraction & XML for worksheet images (Drawing & MemoryDrawing)
- Update: Writer/Ods/MetaInf.php to list all Pictures/ images in the manifest
- Update: Reader/Ods.php to read drawing-related Xml and add those which meet certain criteria to spreadsheet.
# Features
- Exports all worksheet images (Drawing, MemoryDrawing)
- Embeds images in Pictures/ directory inside ODS
- Writes <draw:frame> and <draw:image> elements linked to cell positions
- Updates META-INF/manifest.xml with all images
- Handles cell/row mapping & coordinates
- Supports PNG, JPEG, GIF, BMP
Shorten @see references to unqualified class names and drop a stray blank
line after the sample header docblock, matching the project's php-cs-fixer
and phpcs rules.
Adds runnable samples under samples/PivotTable demonstrating the pivot table
feature, discovered automatically by the sample browser:
- 01_Create_PivotTable: build a basic pivot (row/column/value fields) from a
source range and save it as Xlsx.
- 02_PivotTable_Page_Filter: add a page (report filter) field.
- 03_PivotTable_Grouping: group a numeric field into ranges and a date field
by quarter.
- 04_Read_PivotTable: write a pivot, load it back, and inspect the pivot table
object model (source, row/column/page fields, value aggregation).
Directory is named PivotTable to match the PascalCase convention used by the
other sample categories (Table, Chart, ...).
Many problems with Dependabot this month. Phpstan introduced a lot of new "errors". These are now fixed or annotated. I combined this with a change to require comments for `phpstan-ignore`. These won't always be useful, but I think requiring them makes sense.
Tcpdf is more of a non-update. Our composer.json specified `^6.5`. For some reason, Dependabot decided it was okay to change that to `^6.5||^7.0`, which seems presumptuous. (One of the triggers was probably the elimination of Php8.1, since the new product requires 8.2+.) Tcpdf is nominally deprecated, replaced by tc-lib-pdf. Tcpdf 7 passes control to the new product. However, the upgrade is not straightforward. The user needs to supply font files which were formerly distributed with the product, and a code change to define a (shudder) global constant is required. Consequently, Dependabot's upgrade failed its unit tests. While I may evaluate what might be needed at some point in the future, for now I am just updating composer.json to reject Tcpdf 7+.
Implements native sparkline support (line, column, and win/loss) in the
Xlsx reader and writer, resolving issue #4941.
- New model classes under Worksheet/Sparkline: Sparkline, SparklineGroup,
and the SparklineType enum.
- Worksheet gains a sparkline group collection with addSparkline(),
addSparklineGroup(), getSparklineGroupCollection(), and
removeSparklineGroupCollection(), plus deep-clone support.
- Reader/Xlsx/Sparklines parses x14:sparklineGroups from the sheet extLst.
- Writer/Xlsx/Worksheet emits x14:sparklineGroups; writeExtLst is
refactored so conditional-formatting data bars and sparklines share a
single extLst.
- Adds unit tests, an Xlsx round-trip test, a runnable sample, and docs.
Writer/Xlsx/Chart hard-codes a lot of output values. While nobody has reported a problem involving these, it makes sense to me to copy those values over from what Xlsx/Reader/Chart read, rather than hard-coding them. This involves adding some new properties to Chart and Layout, all, naturally, initialized to the values that we have been hard-coding.
- Chart\date1904
- Chart\lang
- Chart\pageMargins
- Chart\pageSetup
- Layout\bodyPr
With one exception, I have not investigated any of the new properties in depth. That may come in time. The one I did look at is `date1904`. Chances are that it should match the equivalent spreadsheet setting, but it won't matter if there are no dates on your chart, and it often won't matter even if you do. It may well matter if you are using a `date axis`. While looking into that, it became apparent that some Shared Date conversions need to be a bit more flexible, specifying an optional `calendar` parameter rather than relying on the Spreadsheet `calendar` (which is not accessible while processing the chart) or the Shared\Date `calendar` (which is accessible).
Fix#413, which went stale in 2018 and is now reopened. A DataTable can be used on a chart in place of a Legend (or in addition to one, but it doesn't really make sense to have both). The fix was substantially developed by @topaDev before the issue went stale. This PR provides basic support for reading and writing DataTables on charts. The new sample on this PR shows them in action. They have obscure features which are not implemented (e.g. `glow`); those will have to wait for another day.
Fix#661 (marked stale in 2018, but now reopened). That issue was already mostly resolved by many changes to Xlsx Chart Writer logic some time ago. However, a new problem popped up. PR #2950 added `brightness` logic to Xlsx Reader and Writer. That was done primarily for the benefit of scatter charts. Xlsx Writer writes two brightness properties `lumMod` and `lumOff`. These values are complete complementary (if you know one, you know the other), so I am not sure why both are needed, but my scatter chart testing indicated that they were. It turns out that Radar charts can also set brightness, but, if the writer specifies both `lumMod` and `lumOff`, the resulting chart is slightly off. There may be more to this, but that's all I can deal with for now - I suppress writing `lumOff` if we're writing a radar chart. If there are other problems in this area, I will wait for them to be reported. None of the existing radar chart samples used the brightness properties; however, the example attached to 661 did. It is added as a new Sample, and tests are added.
Continuing the work of PR #4810 and PR #4813. All "real" borders are implemented. Pseudo-borders (outline, vertical, horizontal, inside, and allBorders) are not because I'm not really sure how, or even if, Ods handles them. However, Sample20_Read_Ods demonstrates a way to emulate `outline` using just the real borders. In template OOCalcTest.ods cells B18:C19 are merged, and the merged cell has multi-colored borders, something which `outline` isn't able to do. Ods Reader handles this by assigning the border style first to cell B18 (which is what Ods wants, and it doesn't hurt Xlsx/Xls) and then to cells B18:C19 (which is what Xlsx and Xls want, and it doesn't hurt Ods).
Ods Reader changes still to come
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Ods Writer changes still to come
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Not currently on my to-do list
- Pseudo-borders
- Subscript and superscript fonts (these really only make sense for RichText, and I'm not sure how Ods handles RichText)
- Default border, alignment, and protection. I'm not convinced there's a use case. Ods handles its defaults in two different manners, and I don't want to spend the time trying to understand it without justification.
Continuing the work of PR #4810.
Ods Reader changes still to come
- Default border, alignment, protection
- Font
- subscript
- superscript
- Borders (up next)
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Ods Writer changes still to come
- Default border, alignment, protections
- Style applied to entire row
- Style applied to entire column
- Parent styles?
Redo of PR #4799 due to failed attempt to resolve merge conflict.
Fix#4798. Partially address #3961. Ods Reader supports very little related to styling. Ods Writer supports many styling details, but has not heretofore supported Number Formatting. 3961 addresses both issues; I created 4798 specifically for the Writer side.
Writing number formats to Excel is pretty simple - you just supply a string and that is used directly in the Xml. Ods is much more complicated - it requires Xml nodes that give a complete description of the styling. For that reason, it is difficult and painstaking to convert from the string that Excel (and PhpSpreadsheet) uses to what Ods requires.
This PR provides code to support almost all the styles defined as constants in Style/NumberFormat. It also allows the user to add code to handle otherwise unhandled styles. New Sample55_DefinedStyles demonstrates the use of all the constant styles, plus the addition of a couple of custom styles. I may be amenable to adding some unsupported styles to the built-in list, but the custom style option will always be around in case I am being slow or unreasonable.
This PR does not fully support Ods Reader handling of styles. However, based on the new Writer output, it will often be able to guess the true type of numeric items and assign an appropriate style for that type. So, for example, if it can identify the field as a date, it will assign a date style. It will not always match the style in the sheet being read, but it is a big advance from just formatting the data as a generic number.
Fix#3159, at least as far as we can. Requester would probably prefer that we automatically translate the PhpSpreadsheet header and footer information to forms that the Pdf vendors can handle. That seems like it would be very difficult. On the other hand, we can add some new properties to the Pdf writers to make it *relatively* easy for the user to define their own headers and footers for the writers.
Mpdf, as mentioned in the issue, can already handle this in conjunction with the `editHtmlCallback` property. This is demonstraded in Sample21c, which is now renamed to something more descriptive, `Mpdf_Custom_Headers`. That is the only change needed for Mpdf.
Tcpdf, which currently automatically prevents any footers or headers, needs some new properties to allow them. Even with the new properties, a user would need to extend both PhpSpreadsheet's Tcpdf Writer, and the vendor's own class (extending that class is actually the vendor's recommendation). A new sample `Tcpdf_Custom_Headers` demonstrates how this would be done.
Dompdf has 2 ways to add headers and footers. One is pretty good at styling, but doesn't offer much flexibility for dynamic properties other than the current page number; it can't handle different odd/even/first headers, nor the number of pages in the document. The second makes the dynamic properties possible, but seems difficult to style. Two new samples `Dompdf_Custom_Headers` (using `editHtmlCallback`) and `Dompdf_Canvas_Headers` (using a new protected method `callPageScript`) illustrate the use of each.
None of the samples demonstrates the full capabilities of what can go into a header or footer. I consider that out of scope; enabling the ability is one thing, perfecting it is quite another.
The table attributes `border`, `cellspacing`, and `cellpadding` are used in Html Writer. They are deprecated in Html5 in favor of css attributes. Further, their use is redundant or outright wrong. Their use is eliminated. A sample is added to show how equivalents might be used.
Using a default style looks better than leaving them unstyled. User has ability to ignore unstyled tables as before, but I don't know why one would want to.
Fix#4600. String incrementation through the `++` operator is deprecated in Php 8.5. Because we make use of that operator to iterate through columns, we are particularly hard hit by that change - unaddressed, it causes over 2,000 errors in our test suite! It is, fortunately, not as difficult as I feared to correct. Replacing the `++` operator with a call to new method `StringHelper::stringIncrement` in 79 statements scattered over 31 source modules (in src, samples, test, and infra) eliminates all the messages in the test suite. It is possible that others are lurking, but I don't know a systematic way of determining if there are others. We'll stick with this for now, and deal with any others as they show up.
This PR will be applied to the master, release390, and release222 branches. It will not be applied to the release210 or release1291 branches, which will now accept security changes only.
Fix#1104, which went stale over 6 years ago, and is now reopened. This PR addresses the alignment of the table, not alignment of text. Support is added for the following:
- Html. Full support.
- Mpdf. Full support, except that, when mixed LTR and RTL worksheets are output, all tables will be on the left of the page (but will be properly aligned).
- Tcpdf. Full support when all worksheets being output are RTL; no support when mixed LTR and RTL worksheets are output.
- Dompdf. No support.
A while back, Microsoft introduced changes to the default theme colors and fonts. We are adding support for this new theme so that users can use it easily on new spreadsheets if they wish. (It is already supported when using the Xlsx Reader to load an appropriate file.) The old theme was described by constants COLOR_SCHEME_2013_PLUS_NAME and COLOR_SCHEME_2013_PLUS; these are deprecated in favor of COLOR_SCHEME_2013_2022_NAME and COLOR_SCHEME_2013_2022. The new theme is described by constants COLOR_SCHEME_2023_PLUS_NAME and COLOR_SCHEME_2023_PLUS. PhpSpreadsheet's default theme remains COLOR_SCHEME_2007_2010, to avoid breaking changes.
A third optional parameter `$spreadsheet` is added to setThemeColorName. If specified, the default font names for the theme will be applied to the default style for the spreadsheet. You can thus use the new theme with its relatively new default `Aptos Narrow` font. IMHO, that isn't necessarily a good choice, but it is available. MS stores the new font in a different location than other system fonts, and that can lead to portability problems, e.g. if your spreadsheet uses Aptos and you export it to Html, browsers will not be able to find the font and a substitute font will be used.
Fix#1154, which went stale over 5 years ago, and is now reopened. RichText elements can be a Run, which sets its own style, or a TextElement, which doesn't. As the issue states, TextElement handling is inconsistent. This PR forces it to inherit the style of the cell. As implemented, this will change it to a Run when it is read in, but there should be no practical difference as far as the end-user is concerned. There is no change as far as Run is concerned. The user suggested 3 options - TextElement and Run both inherit for any unspecified style elements, TextElement inherits and Run does not, neither inherits. Option 2 makes most sense to me. Option 1 may not even be possible (we can't tell if, say, the user explicityl set Italic to false, or if that was just the default choice).
Tests are added for all of Xlsx, Xls, and Html. Html reading of RichText elements is not well-supported. The tests are fairly difficult to understand. A new sample is added to demonstrate this change.
Fix#1632. Excel automatically supplies a style for cells which contain hyperlinks (e.g. underlined, and blue text changing to purple after the link has been followed). PhpSpreadsheet cannot handle the style automatically. The user can, with some effort, specify a style for the cell which mimics Excel's choice (except for the color change after following).
Examining a sheet with a hyperlink created through Excel, it appears that Excel creates 3 different entries in styles.xml, one for cellStyleXfs, one for cellXfs, and one for cellStyles. It is difficult for me to figure out how they interrelate. This is especially so since PhpSpreadsheet outputs only 1 entry (for the default style) for each of cellStyles and cellStyleXfs. However, it appears that only the cellXfs entry is required, and, when it specifies a font whose color specifies `theme="10"` rather than an rgb value, the style works as expected.
In order to implement this, it is necessary to add a `theme` property, with setter and getter, to Style/Color. There are 12 possible values for theme, 0-11 representing 0=dk1 1=lt1 2=dk2 3=lt2 4-9=accent1-6 10=hlink 11=folHlink. This PR is mainly to allow the use of hlink, but the others are also usable if a use case arises for them. If a theme is set for Color, Xlsx Writer will use the theme rather than rgb when generating the color xml. Other writers will continue to use rgb rather than theme, so there is a use case for setting both if you want to generate both Xlsx and some other format.
The `theme` property will, for now, be ignored except for Font. There is probably a case to be made for using it for Fill, and maybe for Border and other areas that I haven't yet considered. I will wait for someone to make that case before adding those.
In order to make it as easy as possible to use this, a method `setHyperlinkTheme` is added to both Style/Color and Style/Font. The one in Color sets `theme` to the appropriate value. The one in Font calls the one in Color, and also sets `underline` on (this will be honored by other writers in addition to Xlsx).
Samples which use hyperlinks are updated to use `setHyperlinkTheme`. So is `Reader\Xlsx\HyperlinkTest`, with appropriate tests added.
Phpstan level 10 reports an enormous number of errors. So does the excluded missingType.iterableValue. I do not plan to introduce either any time soon. But I will submit piecemeal changes from time to time. Changes will be mostly limited to phpdoc type declarations.
Phpstan hasn't identified any errors in Samples in a long time. But, as it turns out, one particular error is suppressed:
```
Variable $helper might not be defined.
```
This error would be generated by almost all samples, and the errors that it suppresses will become problematic if we move to Level 10. We are not yet committed to doing that, but it is pretty easy to write a script to change the samples so that error no longer happens, and there isn't really any reason to delay doing so. The results of that script constitute this PR.
oleibman
saifulislamferoz
kemo
issa