Defective xlsx file exception
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
VTwin · 2018-12-15 22:50

First, many thanks for FPSpreadsheet, I use it to open and save
different spreadsheet formats, which my users greatly appreciate.

When trying to open a simple LibreOffice ods spreadsheet document, I
have run into an exception:

"Project raised exception class 'EFpSpreadsheetReader' with message:
Defective internal structure of xlsx file."

which occurs at:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i if not
UnzipToStream(AStream, OOXML_PATH_XL_WORKBOOK, XMLStream) then  raise
EFPSpreadsheetReader.CreateFmt(rsDefectiveInternalFileStructure,
['xlsx']);   
If I ignore the exception the file opens and is fine. Why is it
sending an exception for 'xlsx'? Is this a bug?

Cheers,
VTwin

----------------------------------------------------------------------
wp · 2018-12-16 00:11

You want to open an odt file in fpspreadsheet, but it picks the xlsx
reader? Did you specify the correct file format?
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  book:
TsWorkbook;begin  book := TsWorkbook.Create; 
book.ReadFromFile('test.ods', sfOpenDocument);  // or, for automatic
format detection:   // book.ReadFromFile('test.ods');  ...If you did
this correctly, the next question is: Are you sure that the file
really is ods? How was it written? ods and xlsx files internally are
zip files; you can unzip your file after renaming the extension to
zip. An unzipped ods file has a file "contents.xml" in the top level
of the file hierarchy. An unzipped xlsx file has folders "_rels" and
"xl" in the top level of the file hierarchy.

----------------------------------------------------------------------
VTwin · 2018-12-16 00:44

Thanks wp. Yes, I have this:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TVList.OpenFPSFile(const apath: string): boolean;var  wb:
TsWorkbook;begin  result := false;  try    wb := TsWorkbook.Create;  
wb.ReadFromFile(apath);    result := OpenFPSWorksheet(wb, 0); 
finally    wb.Free;  end;end; 
"apath" has '.ods' extension, and is a spreadsheet file saved from
LibreOffice. So I don't see an error that I am making. If I press
"Continue" to ignore the exception I can use wb to access the file.

----------------------------------------------------------------------
VTwin · 2018-12-16 00:47

I confirmed that I can unzip the file, and that it has "content.xml"
at the top.

----------------------------------------------------------------------
wp · 2018-12-16 09:47

The exception happens during the format detection because you do not
specify a file format for the reader. fpspreadsheet looks at the
header of the file and finds the signature of a zip file; therefore,
it tries xlsx and ods as possible candidates, beginning with xlsx. If
an exception occurs it continues with ods.

The exception is caught and ignored, but the IDE while the program
runs in the debugger is interrupted and displays an exception window -
this will not happen when the program runs outside the IDE or without
the debugger. You can avoid the exception popping up in the IDE by
adding "EFpSpreadsheetReader" to the ignored-exception-list in "Tools"
> "Options" > "Debugger" > "Language exceptions".

Or, if you know for sure that the file to be opened is ods, you should
add the format specifier sfOpenDocument to the wb.ReadFromFile call.

Maybe the automatic file detection part should be rewritten to
consider the extension first.

----------------------------------------------------------------------
VTwin · 2018-12-16 13:52

Thanks wp, I appreciate your quick and clear reply. I follow the
logic, and the work around.

It seems to me that for automatic detection the file extension 'ods'
should be considered in addition to the fact that it is a 'zip' file,
since it fails otherwise.

I will either ignore the exception or code it to use the format
specifier sfOpenDocument for 'ods' files, probably the latter.

Again, I appreciate the continued work on FPSpreadsheet it is
impressive, and I have not even delved into the visual components yet.

----------------------------------------------------------------------
SeregaKR · 2018-12-17 05:18

Extension sometimes maybe wrong. I had to deal with the situation when
our partners have sent us an xlsx file with ods extension. They
manually renamed file! So we have to consider this. I thought I was
going mad trying to find out what was wrong.

----------------------------------------------------------------------
wp · 2018-12-17 08:22

In fact, this was the reason why the initial evaluation of the
extension was replaced by the current one. What I was thinking of was
to check the extension first, try to load the file and - if it fails -
repeat with the current evaluation.

----------------------------------------------------------------------
wp · 2018-12-18 00:11

The new revision checks the extension first, and only when the file
cannot be loaded successfully this way, it proceeds with the current
way of checking the file header. This way the exception for regular
ods files is avoided because xlsx had always been tested first (xlsx
and ods cannot be distinguished in the header test because both have a
zip signature).

----------------------------------------------------------------------
VTwin · 2018-12-18 02:00

Fair enough, I appreciate the difficulties in foreseeing what files
might be tried to open, including modified extensions. It sounds like
this has been addressed in revision. Very cool, thanks!

----------------------------------------------------------------------
VTwin · 2018-12-18 02:11

This is not particularly relevant, but some years ago I spent some
time figuring out magic numbers for bitmap images:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  { Magic
numbers }  mnBmp  : array[0..1] of byte = ($42, $4D); // 'BM'  mnGif
 : array[0..3] of byte = ($47, $49, $46, $38); // GIF8'  mnJpeg :
array[0..2] of byte = ($FF, $D8, $FF);  mnPcx  : array[0..3] of byte =
($0A, $05, $01, $08);  mnPng  : array[0..7] of byte = ($89, $50, $4E,
$47, $0D, $0A, $1A, $0A);                                  // . 'PNG'
CR LF SUB LF  mnPnm  : array[0..0] of byte = ($50); // 'P'  mnPsd  :
array[0..11] of byte = ($38, $42, $50, $53, $00, $01, $00, $00, $00, 
                                $00, $00, $00); // '8BPS' 01 0 0 0 0 0
0  mnTiff1 : array[0..3] of byte = ($49, $49, $2A, $00); // Intel, II
42  mnTiff2 : array[0..3] of byte = ($4D, $4D, $00, $2A); // Motorola,
MM 42
I forget why I needed it, but it was interesting that the files had a
signature that could be checked whatever the extension. I suppose some
Lazarus code uses these for raster files.

----------------------------------------------------------------------
wp · 2018-12-18 09:48

Quote from: VTwin on December 18, 2018, 03:11:26 amI suppose some
Lazarus code uses these for raster files.
Yes. Every bitmap reader has a virtual function InternalCheck which
reads the first few bytes of a file and checks against the signature
expected for the file type supported.