may be error
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
vk_barnaul · 2015-08-20 08:45

fpspreadsheet.pas
in  procedure TsWorkbook.ReadFromFile(AFileName: string); overload;
my excel file SheetType not detect by GetFormatFromFileHeader.
loop checking for the various biff formats.
by first  step SheetType = sfExcel8 raise exception, but FWorksheets
consist one sheet and prgoram try read by another SheetType and add
other sheet to FWorksheets.
my dirty solutions - add RemoveAllWorksheets in exception block.

----------------------------------------------------------------------
rvk · 2015-08-20 09:18

GetFormatFromFileHeader probably detected that your file was a
sfExcel8 format.

But you need to include the xlsbiff8 in your uses clause if you want
to use that format. When including xlsbiff8 it will register the
sfExcel8 format so it can work with that format.

If you want to include all possible format you can include
fpsallformats in your uses clause. It will automatically include all
known formats.

----------------------------------------------------------------------
vk_barnaul · 2015-08-20 10:34

sorry but my english is very bad.
my issue no in detect xlsbiff8 - other files detect & read perfectly).
read file partially with SheetType=sfExcel8 and raise.
program try read SheetType=sfExcel5, but data that have been read
before this step not clear.
excel file for reproduce the error https://drive.google.com/file/d/0B0
YrxFV-r1slc0JuSHNKMzM0YTA/view?usp=sharing

----------------------------------------------------------------------
rvk · 2015-08-20 10:53

There might be something corrupt in your sample.xls.
(or fpspreadsheet encounters something unexpected)

If I save your .xls in LibreOffice as .xls it reads correct in
fpspreadsheet.

I'll leave it to wp to trace though this ().

Code to reproduce (save the sample.xls to c:\temp\sample.xls):
Code: [Select]uses fpspreadsheet, fpsallformats;

procedure TForm1.Button1Click(Sender: TObject);
var
  WorkBook: TsWorkbook;
begin
  Workbook := TsWorkbook.Create;
  try
    Workbook.ReadFromFile('c:\temp\sample.xls');
  finally
    Workbook.Free;
  end;
end;

Result: see attached.

----------------------------------------------------------------------
vk_barnaul · 2015-08-20 11:02

I also did it, and I know.
But other excel readers (Microsft excel, LibreOffice, OpenOffice) read
and view this file.
I localize error:
xlsbiff2.pas
...
procedure TsSpreadBIFF2Reader.ReadFONTCOLOR(AStream: TStream);
var
  lColor: Word;
begin
  lColor := WordLEToN(AStream.ReadWord);   // Palette index
>> I not know how, but at this point FFont = Nil
  FFont.Color := IfThen(lColor = SYS_DEFAULT_WINDOW_TEXT_COLOR,
    scBlack, FPalette[lColor]);
end;

it turns out, if we meet in the file INT_EXCEL_ID_FONTCOLOR before
INT_EXCEL_ID_FONT.

----------------------------------------------------------------------
wp · 2015-08-20 11:44

If there is an error in xlsbiff2.pas then the file must be an Excel 2
file. Could this be true?

Anyway, the binary excel file format contains an oddity regarding font
numbers. Initially, MS starting counting at 0, but later they decided
to start at 1. The break happens at font #4. Simply, font #4 does not
exist. To keep the numbers intact, fpspreadsheet inserts a nil pointer
into the font list at index 4 when reading/writing a biff file.

This had been working fine some time ago, but then I decided to keep
these format-specific limitations out of TsWorksheet/TsWorkbook, and
to correct the font number immediately before writing/after reading.
Obviously, there seem to be some places left where the nil pointer
still slips through.

You could help me if you could post this file (or mail it to me if you
don't want to publish it - send me a PM to get my e-mail address).

----------------------------------------------------------------------
rvk · 2015-08-20 11:48

The file was already posted:
https://drive.google.com/file/d/0B0YrxFV-
r1slc0JuSHNKMzM0YTA/view?usp=sharing

Quote from: vk_barnaul on August 20, 2015, 12:34:47 pmexcel file for
reproduce the error https://drive.google.com/file/d/0B0YrxFV-
r1slc0JuSHNKMzM0YTA/view?usp=sharing

I couldn't do a try/except around the ReadFromFile. If I do it keeps
on throwing exceptions.

Is there a possibility to catch the exception even if we're dealing
with corrupt files?

Like this?
Code: [Select]uses fpspreadsheet, fpsallformats;

procedure TForm1.Button1Click(Sender: TObject);
var
  WorkBook: TsWorkbook;
begin
  Workbook := TsWorkbook.Create;
  try
    try
      Workbook.ReadFromFile('c:\temp\sample.xls');
    except
      on E: Exception do
        Showmessage(E.Message);
    end;
  finally
    Workbook.Free;
  end;
end;

----------------------------------------------------------------------
vk_barnaul · 2015-08-20 11:58

after read and dont exception - i read Workbook.ErrorMsg for check
error. I do not know right or wrong it is, but somehow we can get the
incorrect Workbook.
wp, please help us.

----------------------------------------------------------------------
wp · 2015-08-20 13:09

Oh, I did not see the link above...

This file is hard stuff! Lots of features that are not supported. I
traced it by using the BIFFExplorer and found the issue in several OBJ
records - these records contain a series of biff-like sub-records. In
three of them, the size of the last subrecord is not correct and
causes the reader to go beyond the end of the stream. This situation
is detected now, and an internal error message is logged, and reading
resumes at the correct location without further issues. I can open the
file by means of the fpsctrls demo. (But don't expect to see
everything what Excel and LO/OO see).

The improved version is available in svn, rev 4276.

I'll put on my to-do list to investigate why exceptions are blocked.

----------------------------------------------------------------------
rvk · 2015-08-20 13:33

Quote from: wp on August 20, 2015, 03:09:19 pm(But don't expect to see
everything what Excel and LO/OO see).

The improved version is available in svn, rev 4276.
Confirmed that sample.xls is readable by fpspreadsheet svn rev 4276.

The fact that not everything is supported doesn't necessarily need to
be a problem. Sometimes you need to extract data from an Excel file
and for that, it works perfectly.

----------------------------------------------------------------------
wp · 2015-08-20 16:16

Uploaded a new version which avoids the workbook error message.

From https://mail-archives.apache.org/mod_mbox/poi-dev/200409.mbox/%3C
C1ECA5ECAA06A64D88D955E9E152680D32A5C5@SNOWBALL2.asc.com.au%3E:
"The problem is the ftLbsData subrecord (type 0x13), which is
interpreted by POI to have a very long length. The ftLbsData record is
present whenever you have a dropdown in the worksheet, such as through
user forms, or with data validation turned on. The record length on
the ftLbsData record is always a value like 0x1fee, which I cannot
interpret. 0x1fee may be some kind of magic number, or it might be
some kind of garbage if ftLbsData always has a fixed size. Every sheet
I have looked at seems to have a 16 byte ftLbsData sub-record."

Getting the correct record length would require parsing the entire
ftLbsData structure which not needed anywhere else in fpspreadsheet.
For simplicity, I am using a 16-byte record length as suggested by
this quote. For files where this assumption fails the reader will not
crash, though, it will report only an error. Let's see...

----------------------------------------------------------------------
vk_barnaul · 2015-08-21 01:09

many thanks for the quick fix