Cannot read excel 2 file
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
bpranoto · 2023-03-21 15:49

I need to read excel files created by attendance machine.

When I open the file in MsOffice, the file's property says it is an
Excel2 format.

When I try to open the file with autodetection
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
wb.ReadFromFile(Self.OpenDialog1.FileName{,sfExcel2});
The autodetection mechanism shows a XMLReadError which  is eaten than,
and then it fails to read the file.

If I open the file with specific version of Excel2
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
wb.ReadFromFile(Self.OpenDialog1.FileName,sfExcel2);it throws a
EFpSpreadsheetReader exception with message BOF record not found.

Is fpSpreadsheet not able to read old excel 2 files?

Here is the full code
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 procedure
TForm1.Button1Click(Sender: TObject);var  wb:TsWorkbook; 
ws:TsWorksheet;  ptrCell:PCell;  str:String;begin 
wb:=TsWorkbook.Create;  try    Self.OpenDialog1.Filter:='Excel
file|*.xls;*.xlsx';    if Self.OpenDialog1.Execute then begin     
wb.ReadFromFile(Self.OpenDialog1.FileName,sfExcel2);     
ws:=wb.GetFirstWorksheet;      ptrCell:=ws.GetCell(0,0);     
str:=ptrCell^.UTF8StringValue;      ShowMessage( 'Cell A1 contains "'
+ str + '"');    end;  finally    wb.Free;  end; end;       
Environment:
O/S: Linux 64bit
FPC:3.2.2
Lazarus:2.2.5
fpSpreadsheet:1.14.0.0 (installed with OPM)

Thank you for the help.

Best regards,
Bambang

Attached is the simple demo project.

The link to download the excel file from my google drive is in the
unit1.pas

----------------------------------------------------------------------
wp · 2023-03-21 17:40

How was this file created? I mean: By which application?

The file has a BIFF8 header, but a typical BIFF2 record layout. I am
not sure whether reading of such a malformed file should be supported.
Because what are headers good for then, if the reading application
still has to guess whether maybe a wrong header had been written?

But anyway, it is easy to patch the file in memory. A BIFF2 file
(Excel2.1) begins with the bytes $09 $00, but your file begins with
$09 $08. So, my solution is to read the file into a memory stream,
rewind the stream to the 2nd byte and replace the $08 by a $00. This
cheats the fpspreadsheet format detection to detect the file as a
valid BIFF2 file (well, not exactly, because the header is too long by
2 bytes, but the basic structure (record header - record size - record
data) is intact), and fpspreadsheet can read the file correctly.

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 procedure
TForm1.Button1Click(Sender: TObject);var  stream: TMemoryStream;  wb:
TsWorkbook;  ws: TsWorksheet;  str: String;  cell: PCell;begin  wb :=
TsWorkbook.Create;  try    stream := TMemoryStream.Create;    try    
// Read the file into a memory stream      stream.LoadFromFile('ABSEN
WAJAH FEBRUARI 2023.xls');      // Patch the faulty byte at stream
positon 1      stream.Position := 1;      stream.WriteByte(0);      //
Rewind the stream to the beginning...      stream.Position := 0;     
// ... and read the workbook.      wb.ReadFromStream(stream,
sfExcel2);    finally      stream.Free;    end;    ws :=
wb.GetFirstWorksheet;    str := ws.ReadAsText(0, 0);    {    // This
has the problem that when A1 is empty a cell is allocated for it.   
// Just the query cells, it is better to use the FindCell method
which    // returns nil when the cell does not exist. Or: use the
Read* methods of    // the worksheet as done in the previouse line.  
cell := ws.GetCell(0, 0);    str := cell^.UTF8StringValue;    }   
ShowMessage('Cell A1 contains "' + str + '"');  finally    wb.Free; 
end;end; 
Please read also the comment that I put into the code about your usage
of the GetCell method.

----------------------------------------------------------------------
bpranoto · 2023-03-22 04:03

Wp, Thank you very much for your help.

Quote from: wp on March 21, 2023, 06:40:34 pmHow was this file
created? I mean: By which application?
This is attendance machine exported data in excel format.

QuoteThe file has a BIFF8 header, but a typical BIFF2 record layout. I
am not sure whether reading of such a malformed file should be
supported. Because what are headers good for then, if the reading
application still has to guess whether maybe a wrong header had been
written?
Agree.. What made me puzzled is Microsoft excel and Libreoffice can
read the file fine..

QuoteBut anyway, it is easy to patch the file in memory. A BIFF2 file
(Excel2.1) begins with the bytes $09 $00, but your file begins with
$09 $08. So, my solution is to read the file into a memory stream,
rewind the stream to the 2nd byte and replace the $08 by a $00. This
cheats the fpspreadsheet format detection to detect the file as a
valid BIFF2 file (well, not exactly, because the header is too long by
2 bytes, but the basic structure (record header - record size - record
data) is intact), and fpspreadsheet can read the file correctly.
Thanks for the trick.

QuoteCode: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var  stream: TMemoryStream;  wb:
TsWorkbook;  ws: TsWorksheet;  str: String;  cell: PCell;begin  wb :=
TsWorkbook.Create;  try    stream := TMemoryStream.Create;    try    
// Read the file into a memory stream      stream.LoadFromFile('ABSEN
WAJAH FEBRUARI 2023.xls');      // Patch the faulty byte at stream
positon 1      stream.Position := 1;      stream.WriteByte(0);      //
Rewind the stream to the beginning...      stream.Position := 0;     
// ... and read the workbook.      wb.ReadFromStream(stream,
sfExcel2);    finally      stream.Free;    end;    ws :=
wb.GetFirstWorksheet;    str := ws.ReadAsText(0, 0);    {    // This
has the problem that when A1 is empty a cell is allocated for it.   
// Just the query cells, it is better to use the FindCell method
which    // returns nil when the cell does not exist. Or: use the
Read* methods of    // the worksheet as done in the previouse line.  
cell := ws.GetCell(0, 0);    str := cell^.UTF8StringValue;    }   
ShowMessage('Cell A1 contains "' + str + '"');  finally    wb.Free; 
end;end; 
Please read also the comment that I put into the code about your usage
of the GetCell method.

Thanks for the pointer of using GetCell method...

----------------------------------------------------------------------
wp · 2023-03-22 08:34

Quote from: wp on March 21, 2023, 06:40:34 pmBecause what are headers
good for then, if the reading application still has to guess whether
maybe a wrong header had been written?
Since Excel can read these files and since it is a relatively simple
modification, I committed a version of the BIFF2 reader to svn
repository which accepts also a BOF record of Excel 8. With the new
version in CCR you now can read your files directly, the special patch
is not required any more.

----------------------------------------------------------------------
bpranoto · 2023-03-22 08:50

Much appreciated wp.

BTW, will the new version automatically reflected in OPM?

----------------------------------------------------------------------
wp · 2023-03-22 09:04

No, I only prepare a new OPM release after signficant bug fixes or
after some relevant amount of new features have accumulated. But if
you don't use svn you can download the snapshot from the ccr site:
https://sourceforge.net/p/lazarus-
ccr/svn/HEAD/tree/components/fpspreadsheet/

----------------------------------------------------------------------
bpranoto · 2023-03-22 09:23

Thanks.

I have downloaded the xlsbiff2.pas.

Thank you very much again.

----------------------------------------------------------------------
bpranoto · 2023-03-22 11:50

Quote from: wp on March 21, 2023, 06:40:34 pmBut anyway, it is easy to
patch the file in memory. A BIFF2 file (Excel2.1) begins with the
bytes $09 $00, but your file begins with $09 $08.

I have another .xls file created by CLIPWKS (a dos clipper library).
fpSpreadsheet also can't read this file properly while openoffice and
ms excel can open it well. It begins with the bytes $09 $04.

I rename the file to .doc extension in order to be able to attach to
this post.

----------------------------------------------------------------------
wp · 2023-03-22 23:30

$09 $04 is the BOF record marker of the BIFF4 file format (Excel 4.0)
which is not supported by fpspreadsheet (among the xls format it
supports only those of Excel 2.1, 5.0 and 8.0)

But since the main building blocks are already there I combined them
to a reader unit for this format and committed it to the svn
repository. The unit is named similar to the others: xlsbiff4. I did a
quick test and found no issues, except for the cell background which
is not correctly rendered in the worksheetgrid at the moment.

Note, that xlsbiff4 is not used internally by the fpspreadsheet
package, there is no predefined format identifier for it (i.e.
"sfExcel4"). But by adding the unit to the uses clause the reader is
registered as a user-defined format and gets a id "sfidExcel4" which
can be used in the ReadFromFile command:

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 uses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, 
fpSpreadsheet, fpsTypes, xlsBIFF4; procedure
TForm1.Button1Click(Sender: TObject);const  fn = 'hapusaja.xls';var 
wb: TsWorkbook;  ws: TsWorksheet;  str: String;begin  wb :=
TsWorkbook.Create;  try    wb.ReadFromFile(fn, sfidExcel4);    ws :=
wb.GetFirstWorksheet;    str := ws.ReadAsText(0, 0);   
ShowMessage('Cell A1 contains "' + str + '"');  finally    wb.Free; 
end;end;
And just for clarification: This is only a reader unit - it is not
possible to write a workbook in the BIFF4 format ATM (and I don't have
plans to support this in the near future).

----------------------------------------------------------------------
bpranoto · 2023-03-23 02:32

Thank you very much WP.

----------------------------------------------------------------------
wp · 2023-03-24 00:02

Since BIFF4 is very similar to BIFF3, the format of Excel 3.0, I
extended the unit xlsbiff4 to support both formats and renamed the
unit to xlsbiff34. Like before, simply adding the unit to the uses
clause activates the readers for both formats. The formats can be
selected directly by the sfidExcel3 and sfidExcel4 format IDs, but
format auto-detections works, too.