[Lazarus] New Spreadsheet interface component available for Excel and OO Calc

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

[Lazarus] New Spreadsheet interface component available for Excel and OO Calc

Thierry Coq
Please find an upgrade of the ExcelInterface component, now called
SpreadsheetInterface, here:
http://www.tcoq.org/composants

It allows programs to connect to Excel using the OLE interface, and
connect to the Open Office Calc using the UNO bridge, using the same
interface. The OLE and UNO details are hidden using interfaces. The Open
Office Calc interface is very preliminary, but demonstrates basic cell
editing: values, strings and formulas. It has been tested under Windows
for the time being, and with Lazarus 0.9.29 and FPC 2.5.1.
The design and user manual is very limited, so please look at the test
suite for examples on how to use.
All comments and suggestions are welcome.
Thanks to the Pas Uno Bridge team, for their huge work!
Enjoy,
Thierry



--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

faber
I try to test it, but no success,
bin file need cppu3.dll, I found it, then I need MSVCR70.dll, I found it, then I need sal3.dll - I give up.
I try to compile from source, then I get err:

ExcelUtilities.pas(839,38) Error: class type expected, but got "IBaseSpreadsheetObject"


regards
faber

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

Thierry Coq
Hello,
thanks for trying it so fast.

Please, can you specify which compiler, which operating system, which lazarus you use, for the compiler test?

Do you have OpenOffice installed? The test executable is compiled to use open office by default: if it isn't installed, things like sal3.dll will be missing.

I'll try checking which conditions create the compiler error.
Thierry.


On 07/02/2011 00:08, faber wrote:
I try to test it, but no success,
bin file need cppu3.dll, I found it, then I need MSVCR70.dll, I found it, then I need sal3.dll - I give up.
I try to compile from source, then I get err:

ExcelUtilities.pas(839,38) Error: class type expected, but got "IBaseSpreadsheetObject"


regards
faber
-- _______________________________________________ Lazarus mailing list [hidden email] http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

faber
2011/2/7 Thierry Coq <[hidden email]>
Hello,
thanks for trying it so fast.

Please, can you specify which compiler, which operating system, which lazarus you use, for the compiler test?

Do you have OpenOffice installed? The test executable is compiled to use open office by default: if it isn't installed, things like sal3.dll will be missing.

I'll try checking which conditions create the compiler error.
Thierry.


I use fpc 2.4.3 (laz-0.9.31) . OK, I installed openoffice. Remained a compiler error.

regards
faber

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

faber
Hi,
I temporarily comment TSheets.GetItem. I try to test the connection to MS Excel 2007.
It works ok but I've question about how to get information
how many rows and cols is in the sheet ? so far I do not know when to stop reading sheet ?!
It is implemented ?

best regards
faber

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

Thierry Coq
Selon faber <[hidden email]>:
Hello,
thanks for you trying the software. I have a warning on GetItem, which is hiding
an ancester method.

N° of rows and columns not yet implemented. I could have them next week-end.
My usual practice is to loop on the cells in a given row or column until a
condition is met, for example 'end' in the cell value, or '' empty.

Best regards
Thierry

> Hi,
> I temporarily comment TSheets.GetItem. I try to test the connection to MS
> Excel
> 2007.
> It works ok but I've question about how to get information how many rows and
> cols is in the sheet ? so far I do not know when to stop reading sheet ?!
> It is implemented ?
>
> best regards
> faber
>



--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

faber
2011/2/9 <[hidden email]>
Selon faber <[hidden email]>:
Hello,
thanks for you trying the software. I have a warning on GetItem, which is hiding
an ancester method.

N° of rows and columns not yet implemented. I could have them next week-end.
My usual practice is to loop on the cells in a given row or column until a
condition is met, for example 'end' in the cell value, or '' empty.


Some remarks.
There is something wrong with TWorkBook.Close. When I have DisplayAlerts=TRUE then it always ask "File FALSE.xlsx already exists. overwrite it ?". Maybe it should have DISPID_CLOSE instead of DISPID_SAVEAS.

What should look like the correct procedure for closing Excel without save file ?
I use:

  ExcelWb.Close(false);   // Close with DISPID_CLOSE
  ExcelApp.Quit;

and Excel process still exists untl I close my app.
With DISPID_CLOSE I see that the process releases some memory (probably correctly closes sheet) but the process still exists (hangs?).

I found some workaround for RowCount in TSheet.GetLastRowFilledIn



best regards
faber

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

Thierry Coq
Hello,
a new version is available here: http://www.tcoq.org/composants, correcting glitches and adding improvements (see below).

A direct link to the download is here:
http://www.tcoq.org/SpreadsheetInterface_sources_V0.11.zip


N° of rows and columns not yet implemented. I could have them next week-end.
My usual practice is to loop on the cells in a given row or column until a
condition is met, for example 'end' in the cell value, or '' empty.
New functions are introduced on the ISheet interface: GetLastColumn and GetLastRow. That should help you. It took me a little time: I coded them in both Excel and OpenOffice.


Some remarks.
There is something wrong with TWorkBook.Close. When I have DisplayAlerts=TRUE then it always ask "File FALSE.xlsx already exists. overwrite it ?". Maybe it should have DISPID_CLOSE instead of DISPID_SAVEAS.

What should look like the correct procedure for closing Excel without save file ?
I use:

  ExcelWb.Close(false);   // Close with DISPID_CLOSE
  ExcelApp.Quit;
You're right. This is corrected. The Close now calls DISPID_CLOSE.
You should Save or SaveAs the workbook before closing, or set displayalerts to false if you wish to close without saving.

and Excel process still exists untl I close my app.
With DISPID_CLOSE I see that the process releases some memory (probably correctly closes sheet) but the process still exists (hangs?).
Yes, I don't why yet. Certainly a bug in the interface somewhere. It doesn't happen for OpenOffice, though. I'm still looking into it. In the meantime, there should be no remaining compiler warnings or hints, except in the Pas_Uno_Bridge (needs some investigating).

I hope this helps,
Best regards,
Thierry


--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] New Spreadsheet interface component available for Excel and OO Calc

faber
2011/2/13 Thierry Coq <[hidden email]>
New functions are introduced on the ISheet interface: GetLastColumn and GetLastRow. That should help you. It took me a little time: I coded them in both Excel and OpenOffice.



Thanks, GetLastColumn and GetLastRow works ok.
 

Yes, I don't why yet. Certainly a bug in the interface somewhere. It doesn't happen for OpenOffice, though. I'm still looking into it. In the meantime, there should be no remaining compiler warnings or hints, except in the Pas_Uno_Bridge (needs some investigating).


I'll try to check, maybe I can find something.

best regards
faber

--
_______________________________________________
Lazarus mailing list
[hidden email]
http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus