Default style alignment does not work for Xlsx Writer

[homersimpsons]

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?

Default alignment style should be applied with Xlsx writer.

What is the current behavior?

Default alignment style is not applied with Xlsx writer.

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$spreadsheetStyle = $spreadsheet->getDefaultStyle();
$spreadsheetStyle->getAlignment()
    ->setWrapText(true)
    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER)
    ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);

$worksheet = $spreadsheet->getActiveSheet();
$cell = $worksheet->getCell('A1');
$cell->setValue('aaaaaaaaaaaaaa');

// $xlsx = new \PhpOffice\PhpSpreadsheet\Writer\Xls($spreadsheet); // Working
$xlsx = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet); // Not Working

$xlsx->save('alignment.xlsx'); // Optionally update with `.xls`

What features do you think are causing the issue

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

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

It affects Xlsx writer but not Xls writer. I did not test for other formats

Which versions of PhpSpreadsheet and PHP are affected?

At least v2.0.0 (the latest at this time): https://github.com/PHPOffice/PhpSpreadsheet/releases/tag/2.0.0

References

• Potentially the same issue: https://github.com/PHPOffice/PhpSpreadsheet/issues/3456
• The fix used in above issue: https://github.com/PHPOffice/PhpSpreadsheet/pull/3459 (it does not look like this can be straightforward ported to v2)
• Working file: alignment.xls
• Not Working file: alignment.xlsx

/cc @oleibman as you fixed the other similar issue

Thank you to all maintainers for this awesome library !

[oleibman]

I don't see a difference in the 2 files you supplied. xls (presumed working) xlsx (presumed not working) Are you seeing something different in one of the files?

[homersimpsons]

Oh, so that should be a LibreOffice issue ? Working (xls): Not Working (xlsx)

[oleibman]

Yes, this definitely appears to be an issue with LibreOffice. As I described it in PR #3459, Excels's implementation of Alignment is "mysterious". To match Excel's behavior, LibreOffice needs to unravel the mystery when reading (and probably writing) am Xlsx spreadsheet, as PhpSpreadsheet has done.

[oleibman]

BTW, I believe LibreOffice has a problem only when Alignment is specified in the default font. As a workaround, you could explicitly specify Alignment on the specific cells which you want aligned.

[homersimpsons]

As a workaround, you could explicitly specify Alignment on the specific cells which you want aligned.

Yes, but that means doing so as a second step. Thank you for your help.

[oleibman]

Interestingly, this problem happens when the s (style number) attribute is omitted from the c (cell) tag.

<c r="A1" t="s">

However, when the s attribute is explicitly specified as 0 (default), the alignment seems okay.

<c r="A1" t="s" s="0">

I might be willing to consider adding an optional parameter to the Xlsx Writer to produce this behavior. But the problem should still be reported to LibreOffice.

[homersimpsons]

I should admit that I do not know what all of those mean. But I think I understand the different parameters:

• <c>: a cell
• r="A1": reference of the cell
• t="s": type is string
• s="0": style is "0", "0" is a reference to the default style

I might be willing to consider adding an optional parameter to the Xlsx Writer to produce this behavior.

Your call, I think that would be great for LibreOffice users. I let you re-open this issue if you want too.

I think if it is expected to be omitted to mean "default" then we can keep the current behaviour (without s="0").

To get a bit more context, how does that work in the Xls writer ?

But the problem should still be reported to LibreOffice.

I will report it in the up-coming hours, it does not look like there is an open bug reported for this on https://bugs.documentfoundation.org/buglist.cgi?quicksearch=alignment%20xlsx.

[oleibman]

Your explanation of the xml is correct. Xls format does not use Xml; it uses a proprietary binary format that I'm not particularly familiar with, but one can certainly conjecture that style # for a cell is always present.

When you do open your issue with LibreOffice, you might want to, say, set bold in the default style. This will demonstrate that that property is handled correctly even when 's' is omitted from the xml, even though alignment is not.

[homersimpsons]

I just reported it: https://bugs.documentfoundation.org/show_bug.cgi?id=159916 let's hope they can fix it easily.