How was this file created? I mean: By which application?This is attendance machine exported data in excel format.
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?Agree.. What made me puzzled is Microsoft excel and Libreoffice can read the file fine..
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.Thanks for the trick.
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.
Because 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.
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.