Mysql -> Perl - MS-Excel ?

me · Nov 25, 2008

A customer of mine wants to create an excel spreadsheet directly form
a MYSQL database. From the poking around I did, it seems like I would
need to have the package Spreadsheet::WriteExcel installed.

Are there any other alternative packages/options, or is that the only
choice?

Thanks,

Ted Zlatanov · Nov 25, 2008

m> A customer of mine wants to create an excel spreadsheet directly form
m> a MYSQL database. From the poking around I did, it seems like I would
m> need to have the package Spreadsheet::WriteExcel installed.

m> Are there any other alternative packages/options, or is that the only
m> choice?

It's even easier to export to CSV from a query, you never have to touch
Perl or any other glue. Of course, if you have to massage the data
that's another issue.

Ted

me · Nov 26, 2008

It's even easier to export to CSV from a query, you never have to touch
Perl or any other glue. Of course, if you have to massage the data
that's another issue.

Ted

Unfortunately this client wants a pure Excel file - no csv import...
so I'm stuck with a specific MS-Excel output. I agree, csv is the
better way to go.

Dr.Ruud · Nov 26, 2008

me schreef:

Unfortunately this client wants a pure Excel file - no csv import...
so I'm stuck with a specific MS-Excel output. I agree, csv is the
better way to go.

If you don't need any special formatting,
and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
then just save the resulting CSV (with proper headers) under an xls-name
like data.xls,
and most users won't even notice that the file isn't a native Excel
file.

Dr.Ruud · Nov 26, 2008

Dr.Ruud schreef:

me schreef:

If you don't need any special formatting,
and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
then just save the resulting CSV (with proper headers) under an
xls-name like data.xls,
and most users won't even notice that the file isn't a native Excel
file.

More tips:

Make the CSV tab separated (and without any tabs inside the data of
course).

If you need a bit more formating (like bold etc.), then create a very
basic html-file (and save it as data.xls again).

<html>
<body>
<tr><td>date</td><td>value</td><td>location</td></tr>
<tr><td>2008-12-25</td><td>XXXL-mas</td><td><u><b>Texas!!!<b></u></td></
tr>
</body>
</html>

The mysql-client can create a simple html-file from any query:

mysql database -He'SELECT * FROM Table LIMIT 5' >data.xls

Peter J. Holzer · Nov 29, 2008

me schreef:

If you don't need any special formatting,
and you output dates as "yyyy-mm-dd HH:MM:SS" strings,
then just save the resulting CSV (with proper headers) under an xls-name
like data.xls,
and most users won't even notice that the file isn't a native Excel
file.

Be careful, though:

* CSV import depends on the locale. You have to get the decimal
character ("." or ",") right.

* If something looks like a number, it is treated as a number. So
if you have strings like "012.890" or "123E5", they will be converted
to "12.89" and "12300000" respectively.

* And sometimes the results are just weird, like a number being
recognized as a date ...

CSV is nice and simple, but Excel just isn't very good at importing it.
It the goal is to get the data into Excel, it is much preferable to
create an Excel file in the first place.

hp

damjensen · Dec 4, 2008

Actually, there is (yet) another path. Ready for the big secret.. ?
Excel can
read from SQL directly ! Yes, you can actually do this directly with a
few lines of VBA scripting,
some ODBC connection and a button press.

This has the added bonus that Excel can (somewhat itelligently) read
the data format from the database,
so it will get the dates, numbers, decimals etc right.

And, of course, that you can make it dynamic and make an online
selection of what you need, to create different
data each time.

This should guide you:
http://www.dicks-clicks.com/excel/ExternalData.htm

CPAN Write Excel	5	Jan 11, 2007
Spreadsheet::Parse & Write Excel	1	Oct 30, 2007
Perl OLE Excel 2003 Problem with freezepane,	2	May 4, 2009
Can not locate object method "AddCell" via package "Spreadsheet::	10	May 30, 2010
[ANN] spreadsheet-excel-0.3.5.1	0	Feb 8, 2007
excel export	2	Feb 10, 2004
ASP .NET 2.5 - VB - Excel 2003 - Spreadsheet population	1	Nov 26, 2009
adding an excel worksheet to a generetad excel file via perl	1	Apr 17, 2007

Mysql -> Perl - MS-Excel ?

me

Ted Zlatanov

me

Dr.Ruud

Dr.Ruud

Peter J. Holzer

damjensen

Ask a Question

Similar Threads

Members online

Forum statistics

Latest Threads