Smalltalk › Pharo › Pharo Smalltalk Developers

Excel binding

‹ Previous Topic Next Topic ›
 
classic Classic list List threaded Threaded
♦
♦
 14 messages Options
Blondeau Vincent
Reply | Threaded
Open this post in threaded view
♦
♦
|

Excel binding

Blondeau Vincent

Hi,

 

Does someone know if there is a mean to access Excel sheets from Pharo to get data?

I don’t want to use .csv files but .xls/.xlsx files.

 

Thanks in advance

 

Cheers,

 

Vincent BLONDEAU

 



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.
Markus Fritsche-4
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Markus Fritsche-4
On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based
package.

Esteban A. Maringolo
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Esteban A. Maringolo
El Mon Dec 08 2014 at 11:41:23 AM, Markus Fritsche <[hidden email]> escribió:
On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based
package.


All of this assuming you're running Pharo in Windows.

 Regards!
Blondeau Vincent
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Blondeau Vincent

 

 

De : Pharo-dev [mailto:[hidden email]] De la part de Esteban A. Maringolo
Envoyé : lundi 8 décembre 2014 16:01
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

El Mon Dec 08 2014 at 11:41:23 AM, Markus Fritsche <[hidden email]> escribió:

On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

By unzip the file you actually see the xml, but I will complicated to use the values of the formula cells and to differentiate the sheets...



If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

It is only Excel > 2003.



Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based package.

 

I have tried.

But when I launch the DBXTalk tests it asked me to choose a DBXODBX*Facility... Which one should I choose? Should I install some plugins?

And in ConfigurationOfODBC there is no tests and doc so I don't know how to launch it...

 

I heard about OLE (Object Linking and Embedding) to interact with Windows objects. Do you know it and what is the difference?

 

All of this assuming you're running Pharo in Windows.

 

 Regards!

 

 

Of course !

 

Regards,

Vincent

 



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.
Blondeau Vincent
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Blondeau Vincent
In reply to this post by Esteban A. Maringolo

 

 

De : Pharo-dev [mailto:[hidden email]] De la part de Esteban A. Maringolo
Envoyé : lundi 8 décembre 2014 16:01
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

El Mon Dec 08 2014 at 11:41:23 AM, Markus Fritsche <[hidden email]> escribió:

On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

I see it here: http://ss3.gemstone.com/ss/COM.html/Overview?_s=nm1ShYEJFkyZYmBY&_k=rAHtcYsTEzWiJL5n

Could I have access to the code please?

Thanks in advance,

Vincent



Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based
package.

 

All of this assuming you're running Pharo in Windows.

 

 Regards!



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.
Markus Fritsche-4
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Markus Fritsche-4
In reply to this post by Blondeau Vincent
Replies inline
On 08.12.2014 17:07, Blondeau Vincent wrote:

 

 

De : Pharo-dev [[hidden email]] De la part de Esteban A. Maringolo
Envoyé : lundi 8 décembre 2014 16:01
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

El Mon Dec 08 2014 at 11:41:23 AM, Markus Fritsche <[hidden email]> escribió:

On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

By unzip the file you actually see the xml, but I will complicated to use the values of the formula cells and to differentiate the sheets...

Yup - I don't know the excel xml format, just remembered that it might be an option...



If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

It is only Excel > 2003.



Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based package.

 

I have tried.

But when I launch the DBXTalk tests it asked me to choose a DBXODBX*Facility... Which one should I choose?


here is how I connect to an access database on windows:

execute once per session:
OpenDBX current: NBPharoOpenDBX new.

then (access):

| aRow conn settings result |
settings := DBXConnectionSettings host: '<dsn name>' port: '' database: '<database filename>' userName: '' userPassword: ''.
conn := ((DBXConnection new settings: settings) platform: DBXOdbcBackend new) connect.
conn open.
result := conn execute: 'SELECT * FROM <table name>'.
aRow := result nextRow.
[ aRow notNil ] whileTrue: [
    aRow values do: [ :value | Transcript show: value; show: $; ]. Transcript cr. aRow := result nextRow ].
conn close.

Everything in <> brackets needs to be replaced by actual values. Also, the ODBC backend doesn't support many types => which will probably break too :-(

And you will need libopendbx-1.dll, libopendbxplus-1.dll and opendbx\libodbcbackend-1.dll in your image directory.

That all being said - before I'll dive into that, I would probably just whip up a vba script to convert the excel tables to text files in bulk.

Should I install some plugins?

And in ConfigurationOfODBC there is no tests and doc so I don't know how to launch it...

I think there are tests in the monticello repository next to the ODBC package (there's probably a script command to install them, but I am usually faster just installing the package from Monticello manually).

Kind regards
  Markus
blake watson
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

blake watson
In reply to this post by Blondeau Vincent
You could probably do it with Redline Smalltalk via Apache POI.

On Mon, Dec 8, 2014 at 6:22 AM, Blondeau Vincent <[hidden email]> wrote:

Hi,

 

Does someone know if there is a mean to access Excel sheets from Pharo to get data?

I don’t want to use .csv files but .xls/.xlsx files.

 

Thanks in advance

 

Cheers,

 

Vincent BLONDEAU

 



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.

Blondeau Vincent
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Blondeau Vincent
In reply to this post by Markus Fritsche-4

 

 

De : Pharo-dev [[hidden email]] De la part de Markus Fritsche
Envoyé : lundi 8 décembre 2014 19:26
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

Replies inline

On 08.12.2014 17:07, Blondeau Vincent wrote:

 

 

De : Pharo-dev [[hidden email]] De la part de Esteban A. Maringolo
Envoyé : lundi 8 décembre 2014 16:01
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

El Mon Dec 08 2014 at 11:41:23 AM, Markus Fritsche <[hidden email]> escribió:

On 2014-12-08 15:22, Blondeau Vincent wrote:
> Does someone know if there is a mean to access Excel sheets from Pharo
> to get data?

> I don't want to use .csv files but .xls/.xlsx files.

If you're just going to use .xlsx files, it might be possible to unzip
the file and peek the xml representation of that table (not that I've
done it though).

By unzip the file you actually see the xml, but I will complicated to use the values of the formula cells and to differentiate the sheets...

Yup - I don't know the excel xml format, just remembered that it might be an option...



If you're going to support .xls / Excel < 2003 files, you will probably
need a COM-bridge. I remember that I've seen it for Squeak in the past,
I don't recall having seen it for Pharo.

It is only Excel > 2003.



Another kind of hackish solution could be setting up an ODBC driver for
Excel files and then using DBXTalk/ODBC or the ConfigurationOfODBC-based package.

 

I have tried.

But when I launch the DBXTalk tests it asked me to choose a DBXODBX*Facility... Which one should I choose?


here is how I connect to an access database on windows:

execute once per session:
OpenDBX current: NBPharoOpenDBX new.

then (access):

| aRow conn settings result |
settings := DBXConnectionSettings host: '<dsn name>' port: '' database: '<database filename>' userName: '' userPassword: ''.
conn := ((DBXConnection new settings: settings) platform: DBXOdbcBackend new) connect.
conn open.
result := conn execute: 'SELECT * FROM <table name>'.
aRow := result nextRow.
[ aRow notNil ] whileTrue: [
    aRow values do: [ :value | Transcript show: value; show: $; ]. Transcript cr. aRow := result nextRow ].
conn close.

Everything in <> brackets needs to be replaced by actual values. Also, the ODBC backend doesn't support many types => which will probably break too :-(


And you will need libopendbx-1.dll, libopendbxplus-1.dll and opendbx\libodbcbackend-1.dll in your image directory.

That all being said - before I'll dive into that, I would probably just whip up a vba script to convert the excel tables to text files in bulk.

Thanks.



Should I install some plugins?

And in ConfigurationOfODBC there is no tests and doc so I don't know how to launch it...

I think there are tests in the monticello repository next to the ODBC package (there's probably a script command to install them, but I am usually faster just installing the package from Monticello manually).

Nope… Unfortunally there is nothing in http://smalltalkhub.com/mc/PharoExtras/ODBC/main …

 

ODBC seems actually not be a good solution. I have to create one ODBC connection by Excel file and I have many of them…

 

I thinks that in my case OLE seems better.

 

Regards,

 

Vincent



Kind regards
  Markus



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.
Esteban A. Maringolo
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Esteban A. Maringolo


El Tue Dec 09 2014 at 1:51:56 PM, Blondeau Vincent <[hidden email]> escribió:

ODBC seems actually not be a good solution. I have to create one ODBC connection by Excel file and I have many of them…

I don't know about the ODBC wrapper for Pharo, but usually you don't need a DSN defined at the user/system level, you can build your own connection string for the DSN, which will likely specify the path to the source file.

I did this in the past with Dolphin Smalltalk (Win32) with no issues whatsoever.

Regards,
Yuriy Mironenko
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Yuriy Mironenko
I'm working on XLSX / ODS support code just now.
Nothing done on reading the data yet, but it may be easily switched.

But my plans do not includes support of (binary) XLS format.

2014-12-09 20:05 GMT+03:00 Esteban A. Maringolo <[hidden email]>:


El Tue Dec 09 2014 at 1:51:56 PM, Blondeau Vincent <[hidden email]> escribió:

ODBC seems actually not be a good solution. I have to create one ODBC connection by Excel file and I have many of them…

I don't know about the ODBC wrapper for Pharo, but usually you don't need a DSN defined at the user/system level, you can build your own connection string for the DSN, which will likely specify the path to the source file.

I did this in the past with Dolphin Smalltalk (Win32) with no issues whatsoever.

Regards,
Esteban A. Maringolo
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Esteban A. Maringolo
El Tue Dec 09 2014 at 2:22:32 PM, Юрий Мироненко <[hidden email]> escribió:
I'm working on XLSX / ODS support code just now.
Nothing done on reading the data yet, but it may be easily switched.

But my plans do not includes support of (binary) XLS format.



Are you working on OpenXML (.xlsx) file generation?

Regards!
Yuriy Mironenko
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Yuriy Mironenko
Yes, including this, but not limited to this.

I.e. not only generation, but also reading. Generation is just somewhat simpler - so I started from this point.

I announced it some tim ago, it's http://ss3.gemstone.com/ss/Tabular.html

2014-12-09 20:49 GMT+03:00 Esteban A. Maringolo <[hidden email]>:
El Tue Dec 09 2014 at 2:22:32 PM, Юрий Мироненко <[hidden email]> escribió:
I'm working on XLSX / ODS support code just now.
Nothing done on reading the data yet, but it may be easily switched.

But my plans do not includes support of (binary) XLS format.



Are you working on OpenXML (.xlsx) file generation?

Regards!
Blondeau Vincent
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Blondeau Vincent

Nice!

 

Can I extend your project to add the .xlsx reading?

 

Regards,

 

Vincent

 

De : Pharo-dev [mailto:[hidden email]] De la part de ???? ?????????
Envoyé : mercredi 10 décembre 2014 01:16
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

Yes, including this, but not limited to this.

 

I.e. not only generation, but also reading. Generation is just somewhat simpler - so I started from this point.

 

I announced it some tim ago, it's http://ss3.gemstone.com/ss/Tabular.html

 

2014-12-09 20:49 GMT+03:00 Esteban A. Maringolo <[hidden email]>:

El Tue Dec 09 2014 at 2:22:32 PM, Юрий Мироненко <[hidden email]> escribió:

I'm working on XLSX / ODS support code just now.
Nothing done on reading the data yet, but it may be easily switched.

 

But my plans do not includes support of (binary) XLS format.

 

 

 

Are you working on OpenXML (.xlsx) file generation?

 

Regards!

 



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.
Yuriy Mironenko
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Excel binding

Yuriy Mironenko
Sorry, was mostly out of online.
Of course, your contribution is welcome.
Repository is globally writeable espesially for this kind of situations :)

2014-12-10 13:08 GMT+03:00 Blondeau Vincent <[hidden email]>:

Nice!

 

Can I extend your project to add the .xlsx reading?

 

Regards,

 

Vincent

 

De : Pharo-dev [mailto:[hidden email]] De la part de ???? ?????????
Envoyé : mercredi 10 décembre 2014 01:16
À : Pharo Development List
Objet : Re: [Pharo-dev] Excel binding

 

Yes, including this, but not limited to this.

 

I.e. not only generation, but also reading. Generation is just somewhat simpler - so I started from this point.

 

I announced it some tim ago, it's http://ss3.gemstone.com/ss/Tabular.html

 

2014-12-09 20:49 GMT+03:00 Esteban A. Maringolo <[hidden email]>:

El Tue Dec 09 2014 at 2:22:32 PM, Юрий Мироненко <[hidden email]> escribió:

I'm working on XLSX / ODS support code just now.
Nothing done on reading the data yet, but it may be easily switched.

 

But my plans do not includes support of (binary) XLS format.

 

 

 

Are you working on OpenXML (.xlsx) file generation?

 

Regards!

 



Ce message et les pièces jointes sont confidentiels et réservés à l'usage exclusif de ses destinataires. Il peut également être protégé par le secret professionnel. Si vous recevez ce message par erreur, merci d'en avertir immédiatement l'expéditeur et de le détruire. L'intégrité du message ne pouvant être assurée sur Internet, la responsabilité de Worldline ne pourra être recherchée quant au contenu de ce message. Bien que les meilleurs efforts soient faits pour maintenir cette transmission exempte de tout virus, l'expéditeur ne donne aucune garantie à cet égard et sa responsabilité ne saurait être recherchée pour tout dommage résultant d'un virus transmis.

This e-mail and the documents attached are confidential and intended solely for the addressee; it may also be privileged. If you receive this e-mail in error, please notify the sender immediately and destroy it. As its integrity cannot be secured on the Internet, the Worldline liability cannot be triggered for the message content. Although the sender endeavours to maintain a computer virus-free network, the sender does not warrant that this transmission is virus-free and will not be liable for any damages resulting from any virus transmitted.

Free forum by Nabble Edit this page