Sep 5, 2012 at 6:45 PM
Edited Sep 5, 2012 at 7:00 PM
Couple of notes:
setReadDataOnly(): Tells the reader to skip empty cells (only cells that contain data are read).
getValue(): Reads the raw data as stored by the spreadsheet (example: dates are stored as an integer by the spreadsheet but are displayed as defined by the format code).
getFormattedValue(): Returns the data after applying the spreadsheet format code to it.
Are you trying to normalize the data supplied by the user for processing?
I've written code for my company to specifically do this kind of thing. My solution is to take a snapshot of the data (first 10 rows of the first worksheet) and display it to the user in spreadsheet fashion (with row and columns headers) and show a
form where they select the column that contains the data you are trying to collect.
For example, I would have form field labeled 'Birthdate' with a drop down list beside it which lists all the column letters displayed in the sample data. The customer selects the column letter of the column that contains the birth dates.
This way the customer tells you which column contains each specific piece of data you are trying to collect.
From there, you can write your code to handle specific exceptions of formatting differences.
For example, sometimes the birth dates in the spreadsheet only contain month and year (text field usually) and others may only contain the day values (text or numeric field usually) for the current month. I wrote my code to look at the sample data and if
there is a single integer in that column I then provide a Month selector (January - December) that the customer uses to specify with month those days belong to (for our application the Birth Year isn't necessary so I fill that in with current year if it is
Side note: I also used an internal PHP Excel function to determine if the column is storing the date as an integer internally and apply a format code to it to normalize those dates to mm/dd/yyyy automatically.
Dates are the most work for normalizing, but I've been able to code things so the application is pretty robust for handling differently formatted types of data.
If you have more specific questions on how I handle things just ask.
- Christopher Mullins