On Jun 8, 2006, at 7:19 AM, Joel Rees wrote:

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


On 2006.6.8, at 02:36 AM, Mike Schienle wrote:

Celeste Suliin Burris wrote:
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.

Why?

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

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

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 <[EMAIL PROTECTED]>
Organization: Custom Visuals, LLC
Date: Wed, 07 Jun 2006 16:51:36 +0100
To: [email protected]
Subject: Spreadsheet::WriteExcel and ODBC

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.

--
Mike Schienle







--
Mike Schienle




Mike Schienle

Reply via email to