Handling .xls files with PHP - can it be done?

[Joshua Beall]

Hi All,

Up until now, I have been importing data from Excel spreadsheets into MySQL
by exporting them to either .csv files, or tab-delimited .txt files, then
using PHP scripts to insert each row. This works fine, but just adds
another step (open xls, save as csv, then import into MySQL). I am
wondering, has there been any work done on handling xls files, natively, in
PHP? The files I work with are very simple; just rows of data divided into
columns. No cells are calculated based on the value of other cells. It is
all text fields, with one field that holds a date value (not sure if Excel
stores that as text or some sort of timestamp?).

I am well aware of all the tools you can use to convert .xls to something
else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
least using a program that I can kick off from within PHP. That way I can
upload an .xls file, and let my scripts worry about converting it to
something easily inserted in MySQL.

Any thoughts?

Sincerely,
-Josh

[Allan Rydberg]

Re: Handling .xls files with PHP - can it be done?

Joshua Beall wrote:
[color=blue]
> Hi All,
>
> Up until now, I have been importing data from Excel spreadsheets into MySQL
> by exporting them to either .csv files, or tab-delimited .txt files, then
> using PHP scripts to insert each row. This works fine, but just adds
> another step (open xls, save as csv, then import into MySQL). I am
> wondering, has there been any work done on handling xls files, natively, in
> PHP? The files I work with are very simple; just rows of data divided into
> columns. No cells are calculated based on the value of other cells. It is
> all text fields, with one field that holds a date value (not sure if Excel
> stores that as text or some sort of timestamp?).
>
> I am well aware of all the tools you can use to convert .xls to something
> else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
> least using a program that I can kick off from within PHP. That way I can
> upload an .xls file, and let my scripts worry about converting it to
> something easily inserted in MySQL.
>
> Any thoughts?
>
> Sincerely,
> -Josh
>
>[/color]

good idea! write a php function set for this! we're all looking forward
to use it... ;)

ar.

[Joshua Beall]

Re: Handling .xls files with PHP - can it be done?

"Allan Rydberg" <alrdbg@southte ch.net> wrote in message
news:cc1bpi$bp1 $2@newshispeed. ch...[color=blue]
> Joshua Beall wrote:
> good idea! write a php function set for this! we're all looking forward
> to use it... ;)[/color]

There are actually some commercial solutions for this, but I was hoping for
something Open Source.

There is also a PEAR package that lets you write XLS files, but none (afaik)
that let you read. Hmm.

Any pointers?

[CJ Llewellyn]

Re: Handling .xls files with PHP - can it be done?

"Joshua Beall" <jbeall@donotsp am.remove.me.he raldic.us> wrote in message
news:yXWEc.2276 8$x9.3651@nwrdd c01.gnilink.net ...[color=blue]
> "Allan Rydberg" <alrdbg@southte ch.net> wrote in message
> news:cc1bpi$bp1 $2@newshispeed. ch...[color=green]
> > Joshua Beall wrote:
> > good idea! write a php function set for this! we're all looking forward
> > to use it... ;)[/color]
>
> There are actually some commercial solutions for this, but I was hoping[/color]
for[color=blue]
> something Open Source.
>
> There is also a PEAR package that lets you write XLS files, but none[/color]
(afaik)[color=blue]
> that let you read. Hmm.
>
> Any pointers?[/color]

It is possible on Windos platforms by using COM objects, but I bet you want
NIX compatiblity don't you?

[Joshua Beall]

Re: Handling .xls files with PHP - can it be done?

> It is possible on Windos platforms by using COM objects, but I bet you
want[color=blue]
> NIX compatiblity don't you?[/color]

You're a mind reader ;-)

[Michael Vilain]

Re: Handling .xls files with PHP - can it be done?

In article <VAVEc.23764$DT 5.8432@nwrddc03 .gnilink.net>,
"Joshua Beall" <jbeall@donotsp am.remove.me.he raldic.us> wrote:
[color=blue]
> Hi All,
>
> Up until now, I have been importing data from Excel spreadsheets into MySQL
> by exporting them to either .csv files, or tab-delimited .txt files, then
> using PHP scripts to insert each row. This works fine, but just adds
> another step (open xls, save as csv, then import into MySQL). I am
> wondering, has there been any work done on handling xls files, natively, in
> PHP? The files I work with are very simple; just rows of data divided into
> columns. No cells are calculated based on the value of other cells. It is
> all text fields, with one field that holds a date value (not sure if Excel
> stores that as text or some sort of timestamp?).
>
> I am well aware of all the tools you can use to convert .xls to something
> else (e.g., OpenOffice), but I would like to do it *natively* in PHP, or at
> least using a program that I can kick off from within PHP. That way I can
> upload an .xls file, and let my scripts worry about converting it to
> something easily inserted in MySQL.
>
> Any thoughts?
>
> Sincerely,
> -Josh[/color]

You'll have to 'roll your own' here. The various formats of Excel are
documented (Google showed the following):

http://chicago.sourceforge.net/devel/docs/excel

http://sc.openoffice.org/excelfileformat.pdf

The top link has two links to php code to WRITE excel, but they don't
work very well. I found the following to be much better:

http://www.phpclasses.org/browse/package/767.html

None of the code out there does native reading of Excel. You'll have to
do it yourself.

Now I ask you:

How important is this to you and your project?
How much time do you have to spend on this?
What would be the "return on investment" of having the feature?

Get 3-6 months to code this up and build it into php?

--
DeeDee, don't press that button! DeeDee! NO! Dee...

[Chris Hope]

Re: Handling .xls files with PHP - can it be done?

Joshua Beall wrote:
[color=blue]
> Up until now, I have been importing data from Excel spreadsheets into
> MySQL by exporting them to either .csv files, or tab-delimited .txt files,
> then using PHP scripts to insert each row.  This works fine, but just adds
> another step (open xls, save as csv, then import into MySQL).  I am
> wondering, has there been any work done on handling xls files, natively,
> in PHP?  The files I work with are very simple; just rows of data divided
> into columns.  No cells are calculated based on the value of other cells. 
> It is all text fields, with one field that holds a date value (not sure if
> Excel stores that as text or some sort of timestamp?).
>
> I am well aware of all the tools you can use to convert .xls to something
> else (e.g., OpenOffice), but I would like to do it natively in PHP, or at
> least using a program that I can kick off from within PHP.  That way I can
> upload an .xls file, and let my scripts worry about converting it to
> something easily inserted in MySQL.[/color]

http://pear.php.net/package-info.php?package=Spreadsheet_Excel_Writer

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Chris Hope]

Re: Handling .xls files with PHP - can it be done?

Chris Hope wrote:
[color=blue]
> Joshua Beall wrote:
> [color=green]
>> Up until now, I have been importing data from Excel spreadsheets into
>> MySQL by exporting them to either .csv files, or tab-delimited .txt
>> files, then using PHP scripts to insert each row.  This works fine, but
>> just adds another step (open xls, save as csv, then import into MySQL). 
>> I am wondering, has there been any work done on handling xls files,
>> natively, in PHP?  The files I work with are very simple; just rows of
>> data divided into columns.  No cells are calculated based on the value of
>> other cells. It is all text fields, with one field that holds a date
>> value (not sure if Excel stores that as text or some sort of timestamp?).
>>
>> I am well aware of all the tools you can use to convert .xls to something
>> else (e.g., OpenOffice), but I would like to do it natively in PHP, or at
>> least using a program that I can kick off from within PHP.  That way I
>> can upload an .xls file, and let my scripts worry about converting it to
>> something easily inserted in MySQL.[/color]
>
> http://pear.php.net/package-info.php...t_Excel_Writer[/color]

I'm still half asleep. There I was misreading what you wrote and thought you
were trying to write an xls not read one...

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Joshua Beall]

Re: Handling .xls files with PHP - can it be done?

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:1088709715 _10465@216.128. 74.129...[color=blue]
> http://pear.php.net/package-info.php...t_Excel_Writer[/color]

I'm still half asleep. There I was misreading what you wrote and thought you
were trying to write an xls not read one...

I am actually using that package right now to dump the contents, after
processing, back to an xls that the user can download.

I think, however (As Michael Vilain pointed out in another post), that if it
comes down to either writing my own .xls reader, or simply insisting that
users export to .csv before uploading, the latter is probably the better
solution, due to the narrow scope of the project I am working on. There
will not be more than a handful of people using this tool, and they too
would probably rather it be done now, and they have to export to .csv once
per week to upload to the website. Perhaps more to the point, they would
rather do that than *pay* me to roll my own .xls reader.

So, I guess that is the end of that. Although, if someone knows where I
should be looking, do let me know!

-jb

[Jeffrey Silverman]

Re: Handling .xls files with PHP - can it be done?

On Thu, 01 Jul 2004 16:27:10 +0000, Joshua Beall wrote:
[color=blue]
> "Allan Rydberg" <alrdbg@southte ch.net> wrote in message
> news:cc1bpi$bp1 $2@newshispeed. ch...[color=green]
>> Joshua Beall wrote:
>> good idea! write a php function set for this! we're all looking forward
>> to use it... ;)[/color]
>
> There are actually some commercial solutions for this, but I was hoping for
> something Open Source.
>
> There is also a PEAR package that lets you write XLS files, but none (afaik)
> that let you read. Hmm.
>
> Any pointers?[/color]

xls2csv

Haven't used it, but it might do what you want by calling it within PHP
with system() or the like.

http://www.45.free.net/~vitus/ice/catdoc/

later...

--
Jeffrey Silverman
jeffrey@pantsjh u.edu
Drop "pants" to reply by email