Problem with fpspreadsheet
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
MSC · 2014-08-27 17:59

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 · 2014-08-27 20:56

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.

----------------------------------------------------------------------
MSC · 2014-08-28 08:01

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 · 2014-08-28 10:18

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.

----------------------------------------------------------------------
MSC · 2014-08-28 11:36

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 · 2014-08-28 11:51

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.

----------------------------------------------------------------------
MSC · 2014-08-28 12:30

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 · 2014-08-28 13:10

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.

----------------------------------------------------------------------
Mike.Cornflake · 2014-08-28 13:29

Out of curiosity, can Excel open the output from
OpenOffice/LibreOffice?

----------------------------------------------------------------------
wp · 2014-08-28 13:35

@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.

----------------------------------------------------------------------
MSC · 2014-08-28 14:09

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 · 2014-08-28 15:05

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;

----------------------------------------------------------------------
MSC · 2014-08-28 18:38

Now .xlsx file is displayed corretly.
Thx

----------------------------------------------------------------------
MSC · 2014-09-09 18:56

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 · 2014-09-09 21:00

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.

----------------------------------------------------------------------
MSC · 2014-09-10 18:54

I have now added "fpsallformats" to my code, but result is the same:
- .xls is read normal
- .ods still gives "List index (-1) out of bounds" error
- - xlsx still says "Tried to read a spreadsheet using an unsupported
format"
For each filetype, I`ve restarted the sample programm new (do not
press any button while a spreadsheet  (only .xls works here) is
already shown).
This time I´ve only tested lazarus 1.2.4 32 bit and fpc 2.6.4 (on a
win7 64bit machine).

Of course did I see and used the combobox for file extension on
Spready, but anyway it did`nt find the .xlsx file, which is definitely
in the folder!

Here is the code again:
Code: [Select]unit Main;

{$mode objfpc}{$H+}

interface

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

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
  sWorkSheetGrid1.LoadFromSpreadsheetFile(Mydir + 'ELPP0.xls');
end;

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

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

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

end.

Attached are also the spreadsheets, I wanted to read. ELPP0.xlsx is
directly loaded from internet (I`ve only renamed it to a shorter
name), while .ods and .xls are created from Libre Office Version
4.2.5.2 using the .xlsx  and the "save as" function.
Hope this will help to find the problem.
Regards MSC

----------------------------------------------------------------------
wp · 2014-09-10 22:00

OK, after using fpsgrid to show your files I can confirm your issues:

The xlsx file not showing up is due to a typo in the Filter property
of the OpenDialog: of course, the filter for xlsx must be '*.xlsx',
not '*.xslx'. The older versions of spready (I think the one in the
1.2(.1) release) have that same issue.

The ods bug was more difficult to find because of the many rows. The
crash is caused by a "table:named-expression" node appearing after all
the row nodes. I did not expect that, and therefore the reader did not
find the style expected. You can fix that easily:
Open "fpsopendocument.pas".Find the method
"TsSpreadOpenDocReader.ReadRowsAndCells()". At the very top,
immediately after the "while Assigned(rowNode) do" you find "if
rowNode.NodeName = '#text'". Replace this by "if rowNode.NodeName <>
'table:table-row', and you'll be done.To get rid of the underlined
text you can do this:
"fpsopendocument.pas" againFind
"TsSpreadOpenDocReader.ReadFont()"About in the center of the
procedure, there is a section beginning with "fntStyles := [];"Replace
the code from here to the next empty line byCode: [Select]  fntStyles
:= [];
  if GetAttrValue(ANode, 'fo:font-style') = 'italic' then
    Include(fntStyles, fssItalic);
  if GetAttrValue(ANode, 'fo:font-weight') = 'bold' then
    Include(fntStyles, fssBold);
  s := GetAttrValue(ANode, 'style:text-underline-style');
  if not ((s = '') or (s = 'none')) then
    Include(fntStyles, fssUnderline);
  s := GetAttrValue(ANode, 'style:text-strike-through-style');
  if not ((s = '') or (s = 'none')) then
    Include(fntStyles, fssStrikeout);