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