Hi,
I'm having troubles reading a spreadsheet with the demo's of fpspreadsheet. The demo's crash. Could I send a spreadsheet so someone can look why the demo's crash ? If that gets fixed I can go further and make my own application.
The fpspreadsheet is the version included in Lazarus 0.9.28.2.

Thanks,

Koenraad Lelong.

Koenraad Lelon wrote

I'm having troubles reading a spreadsheet with the demo's of fpspreadsheet. The demo's crash.

Which demo exactly and which format?

The fpspreadsheet is the version included in Lazarus 0.9.28.2.

I didn't know it is included in Lazarus .... afaik it's available only in subversion lazarus-ccr

Hi Felipe,

It seems I forgot that I downloaded it via svn. The files are from 28-06-2010, about 9:00.
I tried excel5demo and excel8demo.
This is what I get :

I:\test-lk>excel5read.exe
Opening input file I:\test-lk\test.xls
An unhandled exception occurred at $00426E68 :
Exception : FPSpreadsheet: Reading the OLE document failed $00426E68 TSSPREADBIFF5READER__READFROMFILE, line 1256 of xlsbiff5.pas $0042502D TSWORKBOOK__READFROMFILE, line 696 of fpspreadsheet.pas $00401756 main, line 31 of excel5read.lpr

I:\test-lk>excel8read.exe
Opening input file I:\test-lk\test.xls
An unhandled exception occurred at $00426FF8 :
Exception : FPSpreadsheet: Reading the OLE document failed $00426FF8 TSSPREADBIFF8READER__READFROMFILE, line 1240 of xlsbiff8.pas $0042502D TSWORKBOOK__READFROMFILE, line 696 of fpspreadsheet.pas $00401756 main, line 34 of excel8read.lpr

I enclosed the excel-file.
With the original test.xls the demo's worked fine.

Thanks,

Koenraad Lelong.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.xls.zip
Type: application/zip
Size: 6009 bytes
Desc: not available
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20100729/1bc460d9/attachment.zip>

Ah, excel 5+ binary format is terrible ... I would be more inclined to fix something readable like OpenDocument or OOXML. You can use OpenOffice which is free to convert a XLS to OpenDocument.

thanks,

Felipe Monteiro de Carvalho wrote

Ah, excel 5+ binary format is terrible ... I would be more inclined to fix something readable like OpenDocument or OOXML. You can use OpenOffice which is free to convert a XLS to OpenDocument.

Strange advise.
OpenOffice is not always available to the end user where the program must run ?

Michael.

Hi,

I tried with excel2read, and that application does not crash, it gives no data back though.

My application would be a transcoder. I get excel-files from a customer, and I need a csv-like output. This is read by a proprietary application. What I do now is read the excel-file in OOo or MS-Excel, save it as csv and, with an editor, I fix the file (with a bunch of replaces) so that the prop. application can read the file.
I would like to automate this : just read the excel-file and have the output-file ready to read.
I could start from OOo format, it would be one step more than I would like, but a lot less work than now.

I'll have a look at the OOo demo's.

Thanks,

Koenraad Lelong.

Michael Van Canneyt wrote

Strange advise. OpenOffice is not always available to the end user where the program must run ?

Open Office is free, Excel is not. So I think OO is more "available".

-Michael

If you need a non-Lazarus solution and are competent in Java, then it may be worth looking at POI (Poor Obfuscation Implementation) and HSSF (Horrible Spreadsheet Format).

http://www.javabeat.net/articles/41-apache-poi-reading-excel-sheet-using-java-1.html

I wasn't aware of the existence of fpspreadsheet and will have a look at it, but POI is under a lot of development and seems very active. I am on their mailing list.

Well, not in a corporate environment where M$-Office is pre-installed, and the user has no administrative rights.

I would say that well over 90% of our customers are in this situation.

Michael.

2010/7/29 Koenraad Lelon :

It seems I forgot that I downloaded it via svn. The files are from 28-06-2010, about 9:00. I tried excel5demo and excel8demo. This is what I get :

Just try again with the latest subversion and excel8demo. I just fixed the issue. It now works.

Can I use your file as the standard reading example?

Dave Coventry wrote I wasn't aware of the existence of fpspreadsheet and will have a look at it, but POI is under a lot of development and seems very active. I am on their mailing list.

It looks like we aren't that bad either.

How difficult would be to add text orientation in cell ? Or any other cell formatting (though it's a minor issue for me) ? Many report cases require for crosstab reports to have text headers being vertical.

Boguslaw

Hello Lazarus-List,

you wrote

> It looks like we aren't that bad either.

BB> How difficult would be to add text orientation in cell ? BB> Or any other cell formatting (though it's a minor issue for me) ? BB> Many report cases require for crosstab reports to have text headers BB> being vertical.

Almost all attributes in Excel and I think in the other formats requires in my opinion a large rewrite in the write procedure specially, in fact in the "how data is streamed to file". I do not know what Felipe thinks about this, but is quite possible that basic formatting could be added in a near future if Felipe thinks that the rewrite of this pieces of code is OK.

In the other hand, text orientation is not a simple attribute, but maybe it could be done :-?

2010/7/30 Bogus?aw Brandys :

How difficult would be to add text orientation in cell ?

Now the following works:

  MyWorksheet.WriteUTF8Text(0, 0, Str_First);
  MyWorksheet.WriteTextRotation(0, 0, rt90DegreeClockwiseRotation);

Only in biff 8 format and only for texts at the moment.

You can choose counterclockwise or clockwise.

Jos? Mejuto wrote

Almost all attributes in Excel and I think in the other formats requires in my opinion a large rewrite in the write procedure specially, in fact in the "how data is streamed to file".

Was pretty easy, see here:

http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr?view=revision&revision=1264 http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr?view=revision&revision=1265

But watch out that I accidentely also commited an unfinished encoding support. It shouldn't do any harm, however.

2010/7/30 Bogus?aw Brandys :

Or any other cell formatting (though it's a minor issue for me) ?

I tryed to add font support in biff 8 writer some time ago, I didn't make it. I don't know why it refused to use the custom font. If your formatting is not font-related, then it should be easy to add, like it was easy to add text rotating. Background color and cell border are not font-related, for example.

Felipe,

This is marvelous ! Many thanks!

Boguslaw

I have error about missing fpolestorage unit.

Boguslaw

D:\projekty\lazarus-ccr\lazarus-ccr\components\fpspreadsheet\laz_fpspreadsheet.pas(1,1) Fatal: Can't find unit fpolestorage used by laz_fpspreadsheet

Which is strange because I see that unit in component directory

Boguslaw

Ok,I see where is the problem. Previously I had component installed from different directory.

Hello Lazarus-List,

you wrote

> requires in my opinion a large rewrite in the write procedure > specially, in fact in the "how data is streamed to file".

FMdC> Was pretty easy, see here:
FMdC> http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr?view=revision&revision=1264 FMdC> http://lazarus-ccr.svn.sourceforge.net/viewvc/lazarus-ccr?view=revision&revision=1265 FMdC> But watch out that I accidentely also commited an unfinished encoding FMdC> support. It shouldn't do any harm, however.

I was thinking in the formula sections and others like shared strings where the stream engine first must scan all labels and/or formulas to produce a tag at the beginning of the excel file. This seems to be incompatible on how data is being streamed now. Anyway I must check the sources again, maybe you had changed something that makes this assertion invalid now ;)

I now have in test (finally) a formula handling (missing functions support by now). I'll try to integrate with some basic excel formulas like "=A1+B1". I never thought that the RPN -> Infix coversion would be so tricky :)

On 29/07/2010 16:53, Koenraad Lelon wrote:
...

...
If you're interested in an alternative: ExcelInterfaces provides an interface to Excel using the COM interface. This is very stable over time. One asks for the data directly through Excel installed. It's possible to read/write data, and also to format the spreadsheets.

Take a look here, if your other solutions don't work. I'm also always interested in feedback.

I hope this helps
Thierry

The link is here:
http://www.tcoq.org/composants

Thierry.

Hi,

Thanks, I can read the spreadsheet now.
And go ahead and use the file, since I "published" it, there is no reason not to.

Koenraad Lelong.