Problem. Can't open a .xlsx file

Discuss the spreadsheet application

Problem. Can't open a .xlsx file

Postby assimilated » Wed Mar 25, 2020 11:53 pm

As mentioned in the Topic. I cant open a .xlsx file on OpenOffice Calc. When i try and open it in the program it just gives me a "filter" list and alot of options. Tried some that i assume would work but to no avail. I downloaded the file so I cant tell you which program was used to create it. It is an official file so there should be nothing wrong with the file itself. Any suggestions?
OpenOffice 4.17 on Win 7
assimilated
 
Posts: 3
Joined: Wed Mar 25, 2020 11:49 pm

Re: Problem. Can't open a .xlsx file

Postby FJCC » Thu Mar 26, 2020 12:33 am

That filter list is usually a sign that the file is corrupted. Can you post the file here?
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. Can't open a .xlsx file

Postby John_Ha » Thu Mar 26, 2020 1:23 pm

assimilated wrote:It is an official file so there should be nothing wrong with the file itself.

Ah! You are obviously an optimist, something needed these days :super:

Unfortunately the file is completely corrupted.
 Edit: This is the first instance I have seen where a valid .xlsx file (it is a valid ZIP file with folders etc) causes AOO to present the Filter options.

Clipboard01.gif

See later: it seems that the file has more than 1,024 columns which is the LO maximum. 
Last edited by John_Ha on Thu Mar 26, 2020 6:36 pm, 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. Can't open a .xlsx file

Postby assimilated » Thu Mar 26, 2020 5:42 pm

I ended up using some website to view it online, but seems a waste to have a program suite and not use it.

Yes im an optimist. Goverment sites could easily be run on incompetence though.

link to the file: https://www.tlv.se/webdav/files/Periode ... 00401.xlsx
OpenOffice 4.17 on Win 7
assimilated
 
Posts: 3
Joined: Wed Mar 25, 2020 11:49 pm

Re: Problem. Can't open a .xlsx file

Postby RoryOF » Thu Mar 26, 2020 5:53 pm

.xlsx is a proprietary format and should be opened with the commercial program for which it was designed. You should lodge a complaint with the Information Commissioner (however called) for the State or government who control the site, complaining about use of secret formats requiring commercial programs for the distribution of their information, when there are OpenDocument formats, controlled by international standards.

You want it, you do it!
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31232
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Problem. Can't open a .xlsx file

Postby John_Ha » Thu Mar 26, 2020 6:15 pm

The file is not corrupted.

It opens fine with LO apart from this error message which says the maximum number of columns in LO has been exceeded and therefore that data is missing. Presumably AOO couldn't handle the file at all.

Clipboard02.gif

In LO the file goes to Column AMJ which is 1,024 columns which is presumably the LO limit. Only columns A to Q contain data so it is rather annoying that the other blank columns are not just ignored. It has 6,655 rows.

Clipboard03.gif

See [Tutorial] Differences between Writer and MS Word files for a description of the problem: it seems Excel can work with more columns than LO or AOO.
Last edited by John_Ha on Thu Mar 26, 2020 6:26 pm, 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. Can't open a .xlsx file

Postby assimilated » Thu Mar 26, 2020 6:20 pm

I totally agree and find it reasonable that information to the public should be easily accessible. However the goverment is more likely to call me a querulant.

What is LO?
I tried with OpenCalc.
OpenOffice 4.17 on Win 7
assimilated
 
Posts: 3
Joined: Wed Mar 25, 2020 11:49 pm

Re: Problem. Can't open a .xlsx file

Postby John_Ha » Thu Mar 26, 2020 6:26 pm

LibreOffice
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. Can't open a .xlsx file

Postby John_Ha » Thu Mar 26, 2020 6:43 pm

Research shows that Excel 2010 and later .xlsx files have a maximum of 1,048,576 rows and 16,384 columns.

AOO and LO .ods files have the same maximum of 1,048,576 rows but the smaller maximum of 1,024 columns.

It seems a bug in AOO that the .xlsx file does not open. Ideally it would open but state that the number of columns had been exceeded as does LO.

 Edit: Row maximum for AOO and LO corrected 
Last edited by John_Ha on Fri Mar 27, 2020 12:57 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. Can't open a .xlsx file

Postby jrkrideau » Thu Mar 26, 2020 6:44 pm

I also successfully loading it in L/O & Gnumeric.

L/O gives th warning Warning loading document periodens-vara-april-20200401.xlsx:
The data could not be loaded completely because the maximum number of columns per sheet was exceeded.
and loaded

Gnumeric gave a warning At A1: ''160301-160331'!$A$1:$Q$1' Could not find matching closing quote and loaded

Just for the heck of it I also read in into R. Loaded with no warnings.

For all three programmes I get 6655 rows and 17 columns.
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Problem. Can't open a .xlsx file

Postby MrProgrammer » Fri Mar 27, 2020 12:04 am

assimilated wrote:I cant open a .xlsx file on OpenOffice Calc.
I must agree with Villeroy: use Excel and only Excel to open this crap.

jrkrideau wrote:For all three programmes I get 6655 rows and 17 columns.
Numbers (a MacOS application) also sees 6655 rows and 17 columns. I can view the data, but the application warns that the formatting will be wrong. Numbers is a nice spreadsheet program but it is not capable of formatting the sheet as Excel does. Presumably there are Numbers features which cannot be reproduced in Excel. I do not believe that Excel can open a file in the native Numbers format.

John_Ha wrote:Ideally it would open but state that the number of columns/rows had been exceeded as does LO.
This XML in sheet111.xml seems to be source of the "columns exceeded" problem. Someone selected 16384 cells in row 1 before applying conditional formatting. The entire rule is bogus because A1 contains the text "Status" so both of the formulas are false.
<x:conditionalFormatting sqref="A1:XFD1">
<x:cfRule type="expression" dxfId="1" priority="2">
<x:formula>$A1="PV"</x:formula>
</x:cfRule>
<x:cfRule type="expression" dxfId="0" priority="3">
<x:formula>$A1="R1"</x:formula>
</x:cfRule>
</x:conditionalFormatting>
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3982
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Problem. Can't open a .xlsx file

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

MrProgrammer wrote:This XML in sheet111.xml seems to be source of the "columns exceeded" problem. Someone selected 16384 cells in row 1 before applying conditional formatting. The entire rule is bogus because A1 contains the text "Status" so both of the formulas are false.
<x:conditionalFormatting sqref="A1:XFD1">
<x:cfRule type="expression" dxfId="1" priority="2">
<x:formula>$A1="PV"</x:formula>
</x:cfRule>
<x:cfRule type="expression" dxfId="0" priority="3">
<x:formula>$A1="R1"</x:formula>
</x:cfRule>
</x:conditionalFormatting>

Presumably the author selected the entire row by clicking the row number. The first row (column headings) was frozen in .xlsx file I could not follow the XML - could the freeze have been the cause of the problem?

Presumably it's a bug - AOO should have set the A1:XFD1 to A1:AMJ1 to reflect that the right-most column is AMJ.

So, it appears that

1. If the entire row had not been conditionally formatted, the file would have opened OK in both AOO and LO even though it has 16,384 columns, and presumably with no error message as no data/formatting etc appeared in columns 16,385 and above.

2. The entire row 1 was formatted. LO handled it gracefully, gave an error message, and showed the first 16,384 columns. AOO failed to open it.
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 7 guests