Exception in XLSReadWriteII 5

Questions and answers on how to use XLSReadWriteII 5.
Post Reply
John Elrick
Posts: 3
Joined: Mon Jan 26, 2015 8:36 pm

Exception in XLSReadWriteII 5

Post by John Elrick »

Delphi XE2. XLSReadWriteII version 5. We are registered owners under Fenestra Technologies.

The issue involves a spreadsheet created by TXLSFile (http://sm-software.com/txlsfile.htm). We used TXLSFile to generate certain spreadsheets in last year's release of our software. This year, we switched to XLSReadWriteII for XLSX support. When our software attempts to load an XLS file created with TXLSFile, we receive the following error (duplicated in minimal test program):

Code: Select all

exception class    : Exception
exception message  : Error on reading record # 187, 0092 Offs: 0000130F List index out of bounds (64).

main thread ($ce4):
008135ae +11ea TestReadWrite.exe BIFF_ReadII5    817 +342 TXLSReadII.LoadFromStream
008279c4 +0070 TestReadWrite.exe BIFF5           585   +8 TBIFF5.LoadFromStream
0082791c +0038 TestReadWrite.exe BIFF5           568   +2 TBIFF5.Read
009091eb +0117 TestReadWrite.exe XLSReadWriteII5 558  +17 TXLSReadWriteII5.LoadFromFile
0090942f +0013 TestReadWrite.exe XLSReadWriteII5 625   +1 TXLSReadWriteII5.Read
00909dd6 +002a TestReadWrite.exe uTestForm        49   +6 TForm1.readSpreadsheetIntoMemory
00909da3 +000f TestReadWrite.exe uTestForm        37   +1 TForm1.Button1Click
The spreadsheet in question loads without any issues in both in Excel 2010 and LibreOffice 4.2.7.2.

If we load the spreadsheet into Excel and Save As to a new file, the new copy loads without any issues. The spreadsheet is test data and could be supplied for debugging purposes if required.
John Elrick
Posts: 3
Joined: Mon Jan 26, 2015 8:36 pm

Re: Exception in XLSReadWriteII 5

Post by John Elrick »

Addendum.

In an effort to understand the error further, I commented out the following in BFFReadII5.pas

Code: Select all

procedure TXLSReadII.LoadFromStream(Stream: TStream);
...
        if InsertRecord then
          CurrRecs.AddRec(Header, PBuf);
        Inc(Count);
      end;
    except
      raise;
//      on E: Exception do
//        raise Exception.CreateFmt('Error on reading record # %d, %.4X Offs: %.8X' + #13 + E.Message,[Count, Header.RecID, FXLSStream.Pos]);
    end;
...
The deeper error exposed is in Xc12DataStyleSheet5.pas

Code: Select all

exception class    : EListError
exception message  : List index out of bounds (64).

main thread ($a64):
00474d69 +015 TestReadWrite.exe System.Classes                TList.Get
007259ce +01e TestReadWrite.exe Xc12DataStyleSheet5 2311   +1 TXc12IndexColorObjs.GetItems
00815619 +071 TestReadWrite.exe BIFF_ReadII5        1458  +10 TXLSReadII.RREC_PALETTE
00812cd3 +90f TestReadWrite.exe BIFF_ReadII5         577 +102 TXLSReadII.LoadFromStream
008278ec +070 TestReadWrite.exe BIFF5                585   +8 TBIFF5.LoadFromStream
00827844 +038 TestReadWrite.exe BIFF5                568   +2 TBIFF5.Read
00909113 +117 TestReadWrite.exe XLSReadWriteII5      558  +17 TXLSReadWriteII5.LoadFromFile
00909357 +013 TestReadWrite.exe XLSReadWriteII5      625   +1 TXLSReadWriteII5.Read
00909cfe +02a TestReadWrite.exe uTestForm             49   +6 TForm1.readSpreadsheetIntoMemory
00909ccb +00f TestReadWrite.exe uTestForm             37   +1 TForm1.Button1Click
Maybe this will be of assistance. Again, the offer of the actual spreadsheet is open.
John Elrick
Posts: 3
Joined: Mon Jan 26, 2015 8:36 pm

Re: Exception in XLSReadWriteII 5

Post by John Elrick »

Further experimentation demonstrates that the following changes prevent the exception from occurring:

Code: Select all

     TXc12IndexColorObjs = class(TXLSStyleObjectList)
     // jee -- start workaround for bug
     private
       itsDefaultIndex: integer;
       function findDefault: TXc12IndexColorObj;
     // jee -- end workaround for bug
protected
...
{ TXc12IndexColorObjs }

...
procedure TXc12IndexColorObjs.Clear;
begin
  inherited;
  itsDefaultIndex := -1;
  SetIsDefault;
end;

constructor TXc12IndexColorObjs.Create;
begin
  inherited Create;
  itsDefaultIndex := -1;
end;

function TXc12IndexColorObjs.findDefault: TXc12IndexColorObj;
var
  i: integer;
begin
  if itsDefaultIndex > -1 then
    result := TXc12IndexColorObj(inherited Items[itsDefaultIndex])
  else if count > 0 then
    begin
      i := pred(count);
      while i > 0 do
        begin
          if items[i].RGB = 0 then
            break;
          dec(i);
        end;
      itsDefaultIndex := i;
      result := findDefault;
    end
  else
    result := nil;
end;

function TXc12IndexColorObjs.GetItems(Index: integer): TXc12IndexColorObj;
begin
  // jee temporary work around
  if (index < count) then
    Result := TXc12IndexColorObj(inherited Items[Index])
  else
    result := findDefault;

  if not assigned(result) then
    raise Exception.CreateFmt('Error reading color object at position %d. The list of color objects is empty.', [index]);
end;

larsa
Site Admin
Posts: 926
Joined: Mon Jun 27, 2005 9:30 pm

Re: Exception in XLSReadWriteII 5

Post by larsa »

Hello

Your fix seems to work, but please send me a file with this error and I shall take a look at it.
Lars Arvidsson, Axolot Data
Post Reply