Importing or opening xlsx file results in blank spreadsheet

Post here for help on using FreeCAD's graphical user interface (GUI).
Forum rules
and Helpful information
IMPORTANT: Please click here and read this first, before asking for help

Also, be nice to others! Read the FreeCAD code of conduct!
Venefyxatu
Posts: 4
Joined: Wed Sep 25, 2019 11:32 am

Importing or opening xlsx file results in blank spreadsheet

Postby Venefyxatu » Thu Sep 26, 2019 9:01 pm

As the title says.

I've tried a few combinations:
  • Opening an Excel-generated xlsx file results in a spreadsheet with the correct name, but no content
  • Opening a LibreOffice-generated xlsx file results in a spreadsheet with the correct name, but no content
  • Importing an Excel-generated xlsx file results in a spreadsheet called Spreadsheet with some garbage characters
  • Importing a LibreOffice-generated xlsx file results in a spreadsheet called Spreadsheet with no content
By way of experiment, I installed the latest FreeCAD on a Windows 10 virtual machine, but everything I did there (File -> New, File -> Open) resulted in the window going completely black. I guess it's not happy about running under VirtualBox :-)

I've attached basic_specs.xlsx (LibreOffice-generated) and basic_specs_excel.xlsx (Excel-generated) in case anyone wants to experiment.

My search also led me to https://www.freecadweb.org/tracker/view.php?id=2737 , but that issue was closed as fixed sometime in 2016.
I've also encountered a forum thread somewhere advising someone to get rid of system.cfg and user.cfg, so I did that as well without noticeable results.

There are no non-unicode characters in any of the paths involved (/home/erik and /home/erik/Downloads).

Linux FreeCAD information:

OS: Ubuntu 18.04.1 LTS
Word size of OS: 64-bit
Word size of FreeCAD: 64-bit
Version: 0.18.3.
Build type: Release
Python version: 3.6.8
Qt version: 5.9.5
Coin version: 4.0.0a
OCC version: 7.3.0
Locale: English/UnitedStates (en_US)

Where do I go from here? File a bug report? Is there something else I can try? Something I can do to help figure out what the problem is? My first guess would be I'm missing a library somewhere, but I have no idea which one.
Attachments
basic_specs.xlsx
(4.65 KiB) Downloaded 75 times
basic_specs_excel.xlsx
(9.4 KiB) Downloaded 75 times
chrisb
Posts: 28855
Joined: Tue Mar 17, 2015 9:14 am

Re: Importing or opening xlsx file results in blank spreadsheet

Postby chrisb » Thu Sep 26, 2019 11:29 pm

Import of xlsx files is not expected to work Documentation says that only CSV files can be imported: https://www.freecadweb.org/wiki/Spreads ... _exporting
A Sketcher Lecture with in-depth information is available in English, auf deutsch, en français, en español.
kisolre
Posts: 2343
Joined: Wed Nov 21, 2018 1:13 pm

Re: Importing or opening xlsx file results in blank spreadsheet

Postby kisolre » Fri Sep 27, 2019 7:48 am

chrisb wrote:
Thu Sep 26, 2019 11:29 pm
Import of xlsx files is not expected to work Documentation says that only CSV files can be imported: https://www.freecadweb.org/wiki/Spreads ... _exporting
Actully... second paragraph :-)
chrisb
Posts: 28855
Joined: Tue Mar 17, 2015 9:14 am

Re: Importing or opening xlsx file results in blank spreadsheet

Postby chrisb » Fri Sep 27, 2019 7:52 am

Ooooops :oops: :oops: Thanks for the clarification.
A Sketcher Lecture with in-depth information is available in English, auf deutsch, en français, en español.
User avatar
Roy_043
Posts: 2030
Joined: Thu Dec 27, 2018 12:28 pm

Re: Importing or opening xlsx file results in blank spreadsheet

Postby Roy_043 » Fri Sep 27, 2019 8:35 am

There is an error in the Report view:
Traceback (most recent call last):
File "<string>", line 1, in <module>
File "C:\Program Files\FreeCAD 0.18\Mod\Spreadsheet\importXLSX.py", line 480, in insert
handleWorkSheet(myDom, theSheet, stringList)
File "C:\Program Files\FreeCAD 0.18\Mod\Spreadsheet\importXLSX.py", line 329, in handleWorkSheet
handleCells(row.getElementsByTagName("c"), actSheet, strList)
File "C:\Program Files\FreeCAD 0.18\Mod\Spreadsheet\importXLSX.py", line 374, in handleCells
actCellSheet.set(ref, (sList[int(theValue)]).encode('utf8'))
<class 'TypeError'>: set() argument 2 must be str, not bytes


So this is definitely a bug.
Syres
Posts: 816
Joined: Thu Aug 09, 2018 11:14 am

Re: Importing or opening xlsx file results in blank spreadsheet

Postby Syres » Fri Sep 27, 2019 9:19 am

Having done a few tests with some Excel files of my own, it would appear src\Mod\Spreadsheet\importXLSX.py needs some (more) love but this bug can be fixed by changing at line 57:

Code: Select all

import zipfile
import xml.dom.minidom
import FreeCAD as App

try: import FreeCADGui
to

Code: Select all

import zipfile
import xml.dom.minidom
import FreeCAD as App
import sys

try: import FreeCADGui
and at line 374 from:

Code: Select all

          if cellType == 's':
            actCellSheet.set(ref, (sList[int(theValue)]).encode('utf8'))


def handleWorkBook(theBook, sheetDict, Doc):
to

Code: Select all

          if cellType == 's':
            if sys.version_info.major >= 3:
                actCellSheet.set(ref, (sList[int(theValue)]))
            else:
                actCellSheet.set(ref, (sList[int(theValue)]).encode('utf8'))


def handleWorkBook(theBook, sheetDict, Doc):
I'll submit a PR for this to be ready for 0.18.4.
User avatar
papyblaise
Posts: 2617
Joined: Thu Jun 13, 2019 4:28 pm
Location: France

Re: Importing or opening xlsx file results in blank spreadsheet

Postby papyblaise » Fri Sep 27, 2019 9:45 am

Hello
I open your file whith OpenOfice
I open a spreadsheet in Fc
Copy / Past , that all
Attachments
import xls.PNG
import xls.PNG (27.67 KiB) Viewed 377 times
Venefyxatu
Posts: 4
Joined: Wed Sep 25, 2019 11:32 am

Re: Importing or opening xlsx file results in blank spreadsheet

Postby Venefyxatu » Fri Sep 27, 2019 10:54 am

Wow, you people are fast :shock:

Syres: your patch works for me, thanks!

papyblaise: that's indeed what I've been doing as a workaround, although as soon as you start using formulas it shows its limitations, as only the values are copied. Still, good enough when getting started :-)
Syres
Posts: 816
Joined: Thu Aug 09, 2018 11:14 am

Re: Importing or opening xlsx file results in blank spreadsheet

Postby Syres » Fri Sep 27, 2019 12:09 pm

PR Submitted https://github.com/FreeCAD/FreeCAD/pull/2558 and requested backport to 0.18
User avatar
papyblaise
Posts: 2617
Joined: Thu Jun 13, 2019 4:28 pm
Location: France

Re: Importing or opening xlsx file results in blank spreadsheet

Postby papyblaise » Fri Sep 27, 2019 1:05 pm

as only the values are copied
So it's your spreadsheet that should be put in the right format, tell him to display the formulas and you will import the formulas and not the result
Attachments
import et formules.PNG
import et formules.PNG (26.27 KiB) Viewed 344 times