Recent

Author Topic: Problem with fpspreadsheet  (Read 12269 times)

MSC

  • Jr. Member
  • **
  • Posts: 54
Problem with fpspreadsheet
« on: August 27, 2014, 07:59:18 pm »
Hi,
I´ve just started first attempts in fpspreadsheet.
When I try to read an Open Office Calc Sheet (ELpp.ods) which has about 250 rows, I got a strange exception mesage:
   "[TsSpreadBIFF8Reader.ReadWideString] Expected CONTINUE record not found".
This happens in file xlsbiff8.pas at line 1348.
When I limit(cut) the number of rows to 114 lines, its working. If I add one more line, I got this exeption.
The same happens, if I use a ELpp.xls file. Is there any limitation in code, which I haven´t found or what did I wrong?

This is the code I´m using:
Code: [Select]
procedure TForm1.Button1Click(Sender: TObject);
begin
  MyDir := ExtractFilePath(ParamStr(0));
  InputFileName := MyDir + 'ELpp1.xls';
  try
    sWorksheetGrid1.LoadFromSpreadsheetFile((InputFileName));
  finally
    ...
  end;
end; 

I´m using Windows7 64bit, but Lazarus 1.2.4 32bit, fpspreadsheet 1.2

Regards MSC

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #1 on: August 27, 2014, 10:56:07 pm »
I am a bit confused because you speak of an ods file, but you get an error message from an unit which is responsible for xls reading, and then you are showing code for an xls file.

Anyway, if I remember correctly BIFF records in xls files have a length restriction. If the record size is larger than a given limit (which I don't know at the moment) I think the file format allows for "CONTINUE" records. These records are implemented in the biff8 reader code, but I am not sure if it covers all cases.

How long is the text in your cells?

I'd propose that you post here the file which shows the problem. If you do not want to make your original file public you certainly can create a dummy file with the same issue.
« Last Edit: August 27, 2014, 11:10:57 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #2 on: August 28, 2014, 10:01:27 am »
Hi,
the code is quite simple: just a form with a TsWorksheetGrid (to show the result) and a Button to read the external file and fill the TsWorksheetGrid.
This is the code:
Code: [Select]
unit Main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  fpspreadsheet, fpsallformats, fpspreadsheetgrid;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    sWorksheetGrid1: TsWorksheetGrid;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  InputFilename: String;
  cell: PCell;
  i: Integer;
  Form1: TForm1;
  MyWorksheet: TsWorksheet;
  MyDir: string;

implementation

{$R *.lfm}

{ TForm1 }


procedure TForm1.Button1Click(Sender: TObject);
begin
  MyDir := ExtractFilePath(ParamStr(0));

  InputFileName := MyDir + 'ELpp1.xls';    // or use 'ELpp1.ods' (working)

  if not FileExists(InputFileName) then begin
    Showmessage('Input file '+ InputFileName+ ' does not exist. Please check first.');
    Halt;
  end;

  try
    sWorksheetGrid1.LoadFromSpreadsheetFile((InputFileName));
  except
    showmessage('Fault');
  end;
end; 

After deleting further worksheets in the original file, it seems now to work correct for the open source ELpp1.ods file. If I swap to ELpp1.xls, I still got the mentioned exception.
By the way, I can live with ELpp1.ods.
Enclosed the excel-file, one in odf and one in xls format. Maybe th xls format isn´t quite to standard???

Regards
MSC

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #3 on: August 28, 2014, 12:18:49 pm »
Thanks for posting the files. The crash happens when the xls reader tries to load a shared string containing the greek symbol for Ohms. Looking at these records using the BIFFExplorer I see that they seem to be written in big-endian format, but Excel requires little-Endian (lower-valued byte first). Since the string length is 16-bit it is read completely wrong which screws up the entire reading proces. How did you create the xls file?

Loading the ods file into the spready demo I see that all cells have underlined font. This seems to be a bug of fpspreadsheet which I will investigate.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #4 on: August 28, 2014, 01:36:18 pm »
Hi WD,
thanks for your quick reply.

The file was downloaded from the internet as an .xlsx file type.
I have converted it to .ods and .xls using LibreOffice 4.2.5.2 (using simple save as ...).

Regards
MSC

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #5 on: August 28, 2014, 01:51:08 pm »
Could you also post the original xlsx file? I want to understand how the conversion error occured.
And the LibreOffice, on which operating system was this runnung?

[EDIT]: BTW, the trunk version of fpspreadsheet has a reader for xlsx now. This means there is no need to convert a file from xlsx format for loading it into fpspreadsheet.
« Last Edit: September 10, 2014, 10:08:40 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #6 on: August 28, 2014, 02:30:24 pm »
Windows7  64bit
Lazarus 1.2.4 32bit
LibreOffice 4.2.5.2 on this machine/operating system.

On the original excel-file, I had to remove the second page (SMT) and reset all formatting to standard.
Otherwise I got errormessage 'List index (-1) out of bounds'.

By the way, when dropping a TsWorkSheetGrid onto the form, most times the IDE gets frozen (already created a bugreport).

Will try to download new version from trunk.

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #7 on: August 28, 2014, 03:10:59 pm »
Excel 2007 can convert the xlsx file to an xls file readable by fpspreadsheet. The xls output of both OpenOffice and LibreOffice cannot be read. Therefore, I think that the problem is not due to a bug of fpspreadsheet.
« Last Edit: August 28, 2014, 03:52:34 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

Mike.Cornflake

  • Hero Member
  • *****
  • Posts: 1248
Re: Problem with fpspreadsheet
« Reply #8 on: August 28, 2014, 03:29:07 pm »
Out of curiosity, can Excel open the output from OpenOffice/LibreOffice?
Lazarus Trunk/FPC Trunk on Windows [7, 10]
  Have you tried searching this forum or the wiki?:   http://wiki.lazarus.freepascal.org/Alternative_Main_Page
  BOOKS! (Free and otherwise): http://wiki.lazarus.freepascal.org/Pascal_and_Lazarus_Books_and_Magazines

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #9 on: August 28, 2014, 03:35:31 pm »
@MSC: The bug that you report is fixed in the trunk version, but not in v1.2.

To fix it in 1.2, open fpspreadsheetgrid.pas and go to line 1179 (procedure TsCustomWorksheetGrid.DrawFrozenPaneBorders). At the beginning of the procedure add a "if FWorksheet = nil then exit". The entire procedure should be
Code: [Select]
procedure TsCustomWorksheetGrid.DrawFrozenPaneBorders(ARect: TRect);
begin
  if FWorksheet = nil then exit;           // <--- added
  if (soHasFrozenPanes in FWorksheet.Options) then begin
    Canvas.Pen.Style := psSolid;
    Canvas.Pen.Color := clBlack;
    Canvas.Pen.Width := 1;
    if FFrozenRows > 0 then
      Canvas.Line(ARect.Left, ARect.Top, ARect.Right, ARect.Top);
    if FFrozenCols > 0 then
      Canvas.Line(ARect.Left, ARect.Top, ARect.Left, ARect.Bottom);
  end;
end;

[EDIT] The new stable version 1.2.1 has this bug fixed.

@Mike.Cornflake: I just tried some of my ods test files with Excel2007: No it does not open them, maybe the more recent versions can.
« Last Edit: September 10, 2014, 10:09:57 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #10 on: August 28, 2014, 04:09:31 pm »
Having tested with newest version 1.(2) from trunk.
.ods and .xls are working now, but only if I delete the second page in calcsheet.
.xls still got text with underscore, .ods text is ok.

Also .xlsx is partially working, even with the original downloaded file without any alterations (format settings, 2 pages). BUT:
        On column A some columns by mistake are left emty in TsWorkSheetGrid !!!
        This are colums A, row 82-167, 178-186, 204-205, 208-209.
        These fields are not empty in original .xlsx.

Regards
MSC

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #11 on: August 28, 2014, 05:05:15 pm »
Reading of the shared strings of xlsx was a bit simplified. It seems that your file contains strings composed of several parts ("rich strings"?). As a quick fix of the missing cell content replace in xlsxooxml.pas the procedure TsSpreadOOXMLReader.ReadSharedStrings by

Code: [Select]
procedure TsSpreadOOXMLReader.ReadSharedStrings(ANode: TDOMNode);
var
  valuenode: TDOMNode;
  childnode: TDOMNode;
  nodename: String;
  s: String;
begin
  while Assigned(ANode) do begin
    if ANode.NodeName = 'si' then begin
      s := '';
      valuenode := ANode.FirstChild;
      while valuenode <> nil do begin
        nodename := valuenode.NodeName;
        if nodename = 't' then
          s := GetNodeValue(valuenode)
        else
        if nodename = 'r' then begin
          childnode := valuenode.FirstChild;
          while childnode <> nil do begin
            s := s + GetNodeValue(childnode);
            childnode := childnode.NextSibling;
          end;
        end;
        valuenode := valuenode.NextSibling;
      end;
      FSharedStrings.Add(s);
    end;
    ANode := ANode.NextSibling;
  end;
end;

Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #12 on: August 28, 2014, 08:38:43 pm »
Now .xlsx file is displayed corretly.
Thx

MSC

  • Jr. Member
  • **
  • Posts: 54
Re: Problem with fpspreadsheet
« Reply #13 on: September 09, 2014, 08:56:17 pm »
Having tested fpSpreadsheet Vers. 1.2.1 with the earlier mentioned ELPP.xls, .ods and .xlsx files. Now get on all three version error "Tried to read a spreadsheet using an unsupported format". This is on Lazarus 1.2.4 in 32 and 64-bit. Here is the simple source code sample:
Code: [Select]
unit Main;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, FileUtil, fpspreadsheetgrid, Forms, Controls, Graphics,
  Dialogs, StdCtrls;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    sWorksheetGrid1: TsWorksheetGrid;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;
  MyDir : String;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  MyDir := MyDir + 'ELPP0.xls';
  sWorkSheetGrid1.LoadFromSpreadsheetFile(Mydir);
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  MyDir := MyDir + 'ELPP0.ods' ;
  sWorkSheetGrid1.LoadFromSpreadsheetFile(Mydir);
end;

procedure TForm1.Button3Click(Sender: TObject);
begin
  MyDir := MyDir + 'ELPP0.xlsx';
  sWorkSheetGrid1.LoadFromSpreadsheetFile(Mydir);
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  MyDir := ExtractFilePath(ParamStr(0));
end;

end.   

Also tried spready from the fpSpreadsheet samples. Here .xls file is shown and seems to be ok.
Trying to open .ods file gives error "List index (-1) out of bounds.
And the .xlsx file in not even shown in the file open dialog!

There might be some problems in file recognition?
What did I make wrong? Any ideas?

wp

  • Hero Member
  • *****
  • Posts: 6016
Re: Problem with fpspreadsheet
« Reply #14 on: September 09, 2014, 11:00:00 pm »
Reading and writing of spreadsheet files is done by means of specialized reader and writer classes. There is a unit for each file format. This is a very flexible concept. To save memory, however, FPSpreadsheet does not link these units automatically - if you want to read an xls file you don't need the ods reader. Therefore, you have to specify the unit with the corresponding reader in the uses clause: Add "xlsBiff8" if you want to read an Excel97-XP file, "xlsOOXML" for an xlsx file, or "fpsopendocument" for an ods file (there are some other units for less important formats). Or you can add "fpsallformats" to get access to all file formats implemented. If you try to open a file without corresponding reader/writer you get the "unsupported format" error.

I just saw that this requirement does note become very clear in the wiki article. I'll update it.

[EDIT] The wiki now contains a section on the reader/writer units to be linked in.

The spready demo should open all your files without errors (there are some display issues, though). Did you see the file type selection combo in the FileOpen dialog? It allows to select between the various file formats. It defaults to *.xls, therefore you don't see *.xlsx and *.ods.

Spready is rather complex. To start with the spreadsheet grid it is certainly better to look at the fpsgrid demo.

It is not a good idea in your code that you always add the file name to Mydir and store the result in the same variable again. So, when MyDir is "C:\temp\" initially and you open "ELPP0.xls", then MyDir becomes "C:\Temp\ELPP0.xls" which is fine. But when you open "ELPP0.xlsx" afterwards, then MyDir is "C:\Temp\ELPP0.xlsEPP0.xlsx" which is not good. Do the following instead:

Code: [Select]
procedure TForm1.Button3Click(Sender: TObject);
begin
  sWorkSheetGrid1.LoadFromSpreadsheetFile(Mydir + 'ELPP0.xlsx');
end;

What exactly are you doing to get the "List index out of bounds" error? I don't know this one. Please give details that I can reproduce it.
« Last Edit: September 10, 2014, 10:14:30 am by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10