Spreadsheet::WriteExcel and ODBC

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Schienle - 07 Jun 2006 16:51 GMT

Hi all -

I need to write some Excel files with ODBC access to a MySQL database.
I've used Spreadsheet::WriteExcel in the past for writing formulas,
formatting, etc., with no problems, but the ODBC connection is new to
me. I tried it manually (just opening a blank spreadsheet and connecting
to a remote MySQL server) last night using Actual's ODBC client demo and
that worked fine. The WriteExcel docs are a bit vague [to me] on
connecting to an ODBC source, though.

Has anyone done this via Spreadsheet::WriteExcel? Do you mind passing
along a couple hints? Would this require any customer/client to have an
ODBC client on their system for this to work? If so, I may just go with
static data and have the customer pick up a new spreadsheet each time.

Also, as mentioned above I'm using Actual's ODBC client. Is there a
better client out there? Or an Open Source one that's competitive?

Thanks.

Signature

Mike Schienle

Reply to this Message

Celeste Suliin Burris - 07 Jun 2006 18:25 GMT

I'm a bit confused as to why you need to use ODBC. I just connect to the
remote MySQL server via the DBI when I'm using Perl. I have my Linux machine
running MySQL 5.0.

> From: Mike Schienle <mgs@customvisuals.com>
> Organization: Custom Visuals, LLC
[quoted text clipped - 21 lines]
>
> Thanks.

Reply to this Message

Mike Schienle - 07 Jun 2006 18:36 GMT

> I'm a bit confused as to why you need to use ODBC. I just connect to the
> remote MySQL server via the DBI when I'm using Perl. I have my Linux machine
> running MySQL 5.0.
>

Hi Celeste -

I'm working on a program that will update a database throughout the day
and provide the customer with a spreadsheet of the results. I'd rather
have the customer be able to open the spreadsheet any time and get the
latest data rather than having to create a new spreadsheet each time the
data updates. I should be able to accomplish that by putting the ODBC
connections directly in the spreadsheet.

I have no problem doing the DBI connection from Perl, which is what I've
been doing for quite some time. What I'm after is a way to do the
connection from an Excel spreadsheet that has been written by
WriteExcel. The program is for multiple customers, so I want/need to be
able to write a spreadsheet that is for the particular customer. I might
be able to do this as an Excel template, also. That would be Plan B or C.

>
>> From: Mike Schienle <mgs@customvisuals.com>
[quoted text clipped - 24 lines]
>
>

Signature

Mike Schienle

Reply to this Message

Joel Rees - 08 Jun 2006 12:19 GMT

Up front caveat, I don't have a lot of experience with this kind of
thing, but, ...

>> I'm a bit confused as to why you need to use ODBC. I just connect to
>> the
[quoted text clipped - 10 lines]
> each time the data updates. I should be able to accomplish that by
> putting the ODBC connections directly in the spreadsheet.

Why?

Would it make more sense, perchance, to use a web browser as your
front-end instead of MSOffice?

> I have no problem doing the DBI connection from Perl, which is what
> I've been doing for quite some time. What I'm after is a way to do the
> connection from an Excel spreadsheet that has been written by
> WriteExcel.

How often does this spreadsheet need to be (mechanically, I assume)
rebuilt? (That's the only reason I can think of for building such a
spreadsheet from a perl script.)

> The program is for multiple customers, so I want/need to be able to
> write a spreadsheet that is for the particular customer. I might be
> able to do this as an Excel template, also. That would be Plan B or C.

Oh. That's another reason, I suppose.

My guess is you're going to be flying by the seat of your pants on this
project with no radio.

I think I'd try to sell the customers on pushing the interface to
MSOffice back a ways, doing the tables in HTML on a local-access-only
server, and only dumping the relatively static results to Excel at the
stage where things go to the archive. (Nursing the customers off of
MSOffice as an archive format is also something I'd recommend, but one
thing at a time.)

>> From: Mike Schienle <mgs@customvisuals.com>
>>> Organization: Custom Visuals, LLC
[quoted text clipped - 27 lines]
>>>
>>> Thanks.

Reply to this Message

Mike Schienle - 08 Jun 2006 23:08 GMT

> Up front caveat, I don't have a lot of experience with this kind of
> thing, but, ...
[quoted text clipped - 19 lines]
> Would it make more sense, perchance, to use a web browser as your
> front-end instead of MSOffice?

Hi Joel -

There is already a web-based charting package being used (Visual
Engineering's KavaChart), but I know/expect some of the customers
will want to do some additional analysis of the results beyond what
the charting on the web page will provide. I did a similar package
with a different data set for a customer a few years ago and have a
decent idea of what they did with it in Excel.

Basically, I'm just trying to anticipate the customer's needs on
this, as well as remove a potential load on the server. The KavaChart
package can dynamically build the data using multiple URLs, so I
don't have to build the results for it each time the data needs
updating. I'm trying to figure out how to get Excel to do the same
kind of thing.

>> I have no problem doing the DBI connection from Perl, which is
>> what I've been doing for quite some time. What I'm after is a way
[quoted text clipped - 4 lines]
> rebuilt? (That's the only reason I can think of for building such a
> spreadsheet from a perl script.)

Every 15 minutes if I rebuild it each time data gets processed. If I
provide a spreadsheet they can open whenever they want and it grabs
the latest data off the server, I suspect it would probably happen a
couple times a day, or thereabouts. I can also just build the
spreadsheet from the web interface on demand and have them download
it. So, this isn't a hard requirement, more of a wish list item.

>> The program is for multiple customers, so I want/need to be able
>> to write a spreadsheet that is for the particular customer. I
[quoted text clipped - 5 lines]
> My guess is you're going to be flying by the seat of your pants on
> this project with no radio.

And no compass and poor visibility :-)

> I think I'd try to sell the customers on pushing the interface to
> MSOffice back a ways, doing the tables in HTML on a local-access-
> only server, and only dumping the relatively static results to
> Excel at the stage where things go to the archive. (Nursing the
> customers off of MSOffice as an archive format is also something
> I'd recommend, but one thing at a time.)

The current plan is to keep things on the server and provide a couple
ways for them to view the results. The basic view would be with
KavaChart. Excel would be used for some additional things like trend
analysis and other statistical stuff.

>>> From: Mike Schienle <mgs@customvisuals.com>
>>>> Organization: Custom Visuals, LLC
[quoted text clipped - 29 lines]
>>>>
>>>> Thanks.

Mike Schienle

Reply to this Message

Mac Forum / Programming / Perl / June 2006

Spreadsheet::WriteExcel and ODBC