[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