Skip to content

[Feature Request]: Ability to set built-in styles for a cell (XLSX) #1632

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

Closed
serverdevil opened this issue Aug 29, 2020 · 1 comment · Fixed by #4478
Closed

[Feature Request]: Ability to set built-in styles for a cell (XLSX) #1632

serverdevil opened this issue Aug 29, 2020 · 1 comment · Fixed by #4478

Comments

@serverdevil
Copy link

This is:

- [ ] a bug report
- [x] a feature request
- [x] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

A feature request to support setting built-in styles on a cell when writing to XLSX. We are setting 0 a.k.a Normal for all. Would be good to have ability to set from the list of built-in types e.g. Hyperlink.
setHyperlink is useful to set the cell's hyperlink but the styling does not reflect it.

Code reference (\PhpSpreadsheet\Reader\Xlsx\Styles.php).

//    Cell Styles
       if ($this->styleXml->cellStyles) {
           foreach ($this->styleXml->cellStyles->cellStyle as $cellStyle) {
               if ((int) ($cellStyle['builtinId']) == 0) {
                   if (isset($this->cellStyles[(int) ($cellStyle['xfId'])])) {
                       // Set default style
                       $style = new Style();
                       $this->readStyle($style, $this->cellStyles[(int) ($cellStyle['xfId'])]);
                        // normal style, currently not using it for anything
                   }
               }
           }
       }

Essentially, one of these.
image

Thoughts?

@stale
Copy link

stale bot commented Dec 25, 2020

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

@stale stale bot added the stale label Dec 25, 2020
@oleibman oleibman removed the stale label Aug 21, 2024
oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue May 21, 2025
Fix PHPOffice#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.
@oleibman oleibman mentioned this issue May 21, 2025
11 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

Successfully merging a pull request may close this issue.

2 participants