[Libreoffice-bugs] [Bug 118227] New: XML 2003 Excel Bugs
bugzilla-daemon at bugs.documentfoundation.org
bugzilla-daemon at bugs.documentfoundation.org
Mon Jun 18 14:53:56 UTC 2018
https://bugs.documentfoundation.org/show_bug.cgi?id=118227
Bug ID: 118227
Summary: XML 2003 Excel Bugs
Product: LibreOffice
Version: 6.0.4.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: libreoffice-bugs at lists.freedesktop.org
Reporter: miscellaneous at paintdrawer.co.uk
There are a number of Excel XML 2003 errors in both reading and writing.
This format is very useful to have functioning as it is extremely easy for a
script to generate, takes up little space, and is openable by both Excel and
LibreOffice. In referring here to XML files, XML 2003 is meant.
The code below illustrates a number of problems:-
* COLORS - Basic named colours are not recognised (here 'red') but hex (here
#00ff00) is. Excel recognises them both.
* DATES - 1999-04-01 is not recognised as a date, but 1999-04-02T00:00:00.000
is. Excel recognises them both. This shorter form of date is very helpful in
terms of file size.
* COLUMN ASSIGNMENT - If you remove ss:ExpandedColumnCount="3" and also <Column
ss:StyleID="s2"/> then LibreOffice will not correctly apply the third column
even though it has index="3". Excel does so correctly.
* AUTOFIT - The reader is supposed to autofit the AutoFitWidth columns (numbers
and dates) but LibreOffice fails to do so. Excel does.
* DATE FORMATS - With <NumberFormat ss:Format="yyyy/mm\-dd;@"/> LibreOffice
loads the two separator symbols as / and - respectively which I think is
technically sensible, however my Excel loads them differently (the / comes out
as -) and this may need to be borne in mind or noted for compatibility reasons.
* LOCKS - Open an XML file in LibreOffice and then close the file (but not
LibreOffice) - LibreOffice retains a lock on it and you have to close the
entire application to close the lock. It ought to release the lock on closure,
or perhaps earlier on finishing reading the file. If the file is read-only, I
would suggest releasing the lock when the reading is complete. This should be
very easy to fix and would be useful as testing XML output involves closing and
opening numerous times and closing the application each time is a pain!
Some further issues -
* CELL BLOAT - Create a one-cell spreadsheet in LibreOffice, save it and read
it back. Reading in takes a long time because the export has written a vast
number of empty cells/rows, creating a large file. Removing those empty cells,
the result is small and LibreOffice reads it in swiftly. Excel does not produce
these empty cells when the same task is carried out.
* SAVING - Create a new Calc spreadsheet, put a date in cell A1, format it
YYYY-MM-DD from the date format list, then save as M.S. Excel XML 2003, close
and reopen that file. The resulting file reads in the date, but not the format.
Cheers,
David
SAMPLE FILE (utf8 file no utf8 BOF marker) -
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:x =
"urn:schemas-microsoft-com:office:excel" xmlns:ss =
"urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="s1"><Font ss:Bold="1" ss:Color="#008000"/><NumberFormat
ss:Format="0.00_ ;[Red]\-0.00\ "/></Style>
<Style ss:ID="s2"><Font ss:Color="red"/></Style>
<Style ss:ID="s3"><Font ss:Color="blue"/><NumberFormat
ss:Format="yyyy/mm\-dd;@"/></Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:TopCell="2" ss:LeftCell="2" ss:DefaultColumnWidth="20"
ss:ExpandedColumnCount="3">
<Column ss:StyleID="s1" ss:AutoFitWidth="1"/>
<Column ss:StyleID="s2"/>
<Column ss:StyleID="s3" ss:Index="3" ss:AutoFitWidth="1"/>
<Row ss:StyleID="s1">
<Cell ss:Index="2"><Data ss:Type="String">Title</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">111</Data></Cell>
<Cell><Data ss:Type="String">aaaa</Data></Cell>
<Cell><Data ss:Type="DateTime">1999-04-01</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">2222</Data></Cell>
<Cell><Data ss:Type="String">bbbb</Data></Cell>
<Cell><Data ss:Type="DateTime">1999-04-02T00:00:00.000</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
--
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20180618/f1ba56a3/attachment-0001.html>
More information about the Libreoffice-bugs
mailing list