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
