Re: [PHP] import spreadsheet
Richard Lynch wrote: On Fri, July 27, 2007 4:01 am, Angelo Zanetti wrote: Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. I didn't actually import multiple sheets, but the tool I used returned an array sheets and the 0-th element was the only sheet in there, so I suspect a second sheet would have been the next element... It's a PECL package up on sourceforge: http://sourceforge.net/projects/phpexcelreader/ As far as columns and rows goes, it just built an array the right size to hold everything in the spreadsheet. Though if you've got a stray space in column ZZZ row 65535, then I suppose you could be in trouble... It's got a bug as of a couple weeks ago where it tries to 'include' a file that doesn't exist... Change that to include the similarly-named file that's actually there and bob's your uncle. YMMV PS Watch out for those serial killer dates... :-) I blogged a little bit about this here: http://richardlynch.blogspot.com/2007/07/php-microsoft-excel-reader-and-serial.html hi guys. Thanks to those that responded, I will check out all the responses! Much appreciated! regards Angelo -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] import spreadsheet
Angelo Zanetti wrote:
Hi guys
Does anyone have any resources or links as to how to import a
spreadsheet but it might have different number of columns and many
sheets (those tab things at the bottom).
What I thought of doing was creating a table that has 10 fields and if
the file thats being imported only has 4 fields then the remaining six
fields are blank.
So basically my script must dynamically take the format (which changes)
and try save it in the database in a semi standard format.
If you're trying to be completely generic, why not have a table like:
cells {
id, - Auto increment, auto assign by DB
file, - The file the sheet came from, if you're going to
store more than one
sheet, - The name of the sheet the cell is on
column, - The column the cell is in
row, - The row the cell is in
value - The value or formula of the cell
primary key(id), key (file, sheet, column, row), key(file,
sheet), etc.
}
Then you can write your importer to go through every sheet/row/column
and add cells to your database for each. Obviously, you don't bother to
add empty cells. Once this is done, you can do things like:
Get a cell directly:
select * from cells where file='f' and sheet='x' and column='y' and
row=z
Get an entire column:
select * from cells where file='f' and sheet='x' and row=z
Get an entire row:
select * from cells where file='f' and sheet='x' and column='y'
Get a list of the available columns in a sheet:
select distinct column from cells where file='f' and sheet='x' order
by column
Get a list of the sheets in use:
select distinct sheet from cells where file='f' and order by sheet
And so forth. The nice thing about this format is that it makes it
really easy to do interesting things like write a Web front-end to
spreadsheet data. You could have a little form that queries the list of
files, and lets the user pick which they want. Then, for that file, you
get the list of sheets. Once they select those, you get a list of all
rows/columns in the sheet and use it to set up your table, and populate
your grid with cells. With the above data structure, that's a few
minutes' work.
Regards,
Chad
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP] import spreadsheet
Hi guys Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. THanks in advance -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 552 9799 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] import spreadsheet
[snip] Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. [/snip] Sure, it is possible but which data goes in which columns? A four column spreadsheet does not have the same textual context unless you make everyone start with the same 10 column spreadsheet and place their data in the right columns. If you are trying to save individual spreadsheets as data that are independent of the data in the other spreadsheets then that sort of eliminates the need for a database, but it can be done. You have to have a column or two that would contain the spreadsheet identifier and a date. Then your table could have as many columns (generically headed) for data. spreadsheetName date col1 col2 col3 col4 ... sales 2007-07-25 data data data data ... sales 2007-07-25 data data data data ... mileage 2007-07-25 data data mileage 2007-07-25 data data For each spreadsheet you would have to insert a row to hold column headers for that spreadsheet, so you may need a third column to designate what kind of row type (header, data) this is for that spreadsheet. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] import spreadsheet
On Fri, July 27, 2007 4:01 am, Angelo Zanetti wrote: Does anyone have any resources or links as to how to import a spreadsheet but it might have different number of columns and many sheets (those tab things at the bottom). What I thought of doing was creating a table that has 10 fields and if the file thats being imported only has 4 fields then the remaining six fields are blank. So basically my script must dynamically take the format (which changes) and try save it in the database in a semi standard format. If anyone thinks its possible or not please advise. Any help or advice would be greatly appreciated. I didn't actually import multiple sheets, but the tool I used returned an array sheets and the 0-th element was the only sheet in there, so I suspect a second sheet would have been the next element... It's a PECL package up on sourceforge: http://sourceforge.net/projects/phpexcelreader/ As far as columns and rows goes, it just built an array the right size to hold everything in the spreadsheet. Though if you've got a stray space in column ZZZ row 65535, then I suppose you could be in trouble... It's got a bug as of a couple weeks ago where it tries to 'include' a file that doesn't exist... Change that to include the similarly-named file that's actually there and bob's your uncle. YMMV PS Watch out for those serial killer dates... :-) I blogged a little bit about this here: http://richardlynch.blogspot.com/2007/07/php-microsoft-excel-reader-and-serial.html -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/browse/from/lynch Yeah, I get a buck. So? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
