Problem with .xlsx files not opening with AOO

Discuss the spreadsheet application

Problem with .xlsx files not opening with AOO

Postby John_Ha » Thu Mar 26, 2020 7:33 pm

It seems that .xlsx files created by Excel 2010 and later do not open with AOO. The user gets a Filter Selection pop-up screen where they have to select the file type. Selecting any of the Excel 2007 formats then gives a general input/output error.

Clipboard01.png

The problem appears to be because Excel 2010 and later have a maximum of 1,048,576 rows and 16,384 columns. AOO and LO have the same maximum of 1,048,576 rows but a smaller maximum of only 1,024 columns.

When such a .xlsx file is opened with LO the user gets given the following error message: "Warning loading document xxxxxxx.xlsx:
The data could not be loaded completely because the maximum number of columns per sheet was exceeded.
" LO then opens the file but only has 1,024 columns.

It seems to be a bug in AOO that the file does not open. Ideally AOO would do what LO does, namely open the file but give an error message stating that the number of columns had been exceeded.

See Problem. Can't open a .xlsx file

 Edit: Corrected to give correct row maximum for AOO and LO. 
Last edited by John_Ha on Fri Mar 27, 2020 12:53 am, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Problem with .xlsx files not opening with AOO

Postby FJCC » Thu Mar 26, 2020 8:35 pm

At the moment I have access to an Office 365 suite so I made an xlsx file of 1153 x 30 cells and then moved it to my computer with OpenOffice 4.2.0 (obviously a dev build) and it opened with no warning or filter selection. In Office 365 the file shows 16384 columns. There may be some file format setting in Office 365 that could be changed but I have not delved into that.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Problem with .xlsx files not opening with AOO

Postby robleyd » Fri Mar 27, 2020 12:27 am

AOO and LO have a smaller maximum of 65,536 rows and 1,024 columns.


From version 3.3 AOO has supported 1,048,576 rows - see the Wiki. The situation for LO is similar - https://documentation.libreoffice.org/a ... 1365249792
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3393
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Problem with .xlsx files not opening with AOO

Postby John_Ha » Fri Mar 27, 2020 12:47 am

FJCC wrote:At the moment I have access to an Office 365 suite so I made an xlsx file of 1153 x 30 cells and then moved it to my computer with OpenOffice 4.2.0 (obviously a dev build) and it opened with no warning or filter selection. In Office 365 the file shows 16384 columns. There may be some file format setting in Office 365 that could be changed but I have not delved into that.

Very interesting. What happens when you try open https://www.tlv.se/webdav/files/Periode ... 00401.xlsx with AOO. It did not open for RoryOF.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Problem with .xlsx files not opening with AOO

Postby John_Ha » Fri Mar 27, 2020 12:48 am

robleyd wrote:
AOO and LO have a smaller maximum of 65,536 rows and 1,024 columns.


From version 3.3 AOO has supported 1,048,576 rows - see the Wiki. The situation for LO is similar - https://documentation.libreoffice.org/a ... 1365249792

Thanks. The documentation Mrs Google found for me was obviously obsolete. I will correct my posts.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Problem with .xlsx files not opening with AOO

Postby FJCC » Fri Mar 27, 2020 12:53 am

John_Ha wrote:Very interesting. What happens when you try open https://www.tlv.se/webdav/files/Periode ... 00401.xlsx with AOO. It did not open for RoryOF.

I get an import filter when I try to open that file.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7680
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Problem with .xlsx files not opening with AOO

Postby John_Ha » Fri Mar 27, 2020 1:32 am

FJCC

See the discussion in Problem. Can't open a .xlsx file where Mr Programmer found that the XML has an item referring to column XFD, well beyond AOO's maximum of AMJ. Presumably this is what AOO does not handle.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7774
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests