I suspect that the reason the cell is being reported as type String is because POI is detecting a String in that cell. If you think back to my previous reply, I indicated that it is possible to enter one of three different types of data into a cell and a Date is not actually a date value as we would read it but a specially formatted number. That is why it is common when reading a file using POI to test the cells type and, if it is numeric, call the DateUtil.isCellDateFormatted() method to see if you have a cell holding a date value. The problem you are facing now is that because you have a String of text in that header cell - Date Of Birth - you cannot use the DateUtil method (or at least I do not think you can as I have never come across this problem myself and it would certainly be worth experimenting with this test as it is only two or three lines of code) to test the cells type for you.
To my mind, there are two possible solutions. The first would be to read the label and see if it contains the word 'Date'. That way you would know what you were dealing with and could process the data accordingly, but this assumes the label may never change and it is not beyond the realms of possibility to imagine the user wanting DOB instead of Date Of Birth. The other option would be to modify your template a little. Just for the sake of discussion, imagine that row 1 - POI index 0 - contains the labels, then you could insert dummy data into row 2 (POI index 1). By dummy data, I mean values that are representative of those you would expect to see in the cells - text cells could just contain the word 'text' for example, any numeric cells 0 and dates something like 10/10/2009. When you open the template using POI, you could read this dummy row to determine what the types of the cells are and note each using a Map. The cells type could be represented by a simple word such as 'string', 'numeric' 'date' etc and the key for each would be the column index converted into a String. Then, when you populate the worksheet with 'real' data, it would be possible to use the column index, convert that into a String, use this as the ley into the map and from the value returned discover the cells type. Of course, you would overwrite the dummy values in the first row of cells. The further advantage this would offer is that you could set the formatting for each cell using Excel, read that using POI and then apply the style as each row is created possibly saving you some time and insulating the application from changes to the cells style. Alternatively, if you know the types in advance and do not expect them to change, you could create this map without the need to use a dummy row - in a constructor - and, finally, if you cannot know in advance, what about simply listing the types in a properties file that the application could read? You could pass the name of the properties file to the application at runtime making it possible to change the listing of data types on the fly. Yours Mark B PS I would be interested to discover what happens if you set the type of a column of cells to numeric and then enter a label such as 'Total' into the cell at the top of the column and then read this cell using POI; what type would POI report for this cell I wonder? Kalpesh Modi-2 wrote: > > Thanks for your reply. > > What I want to do is that I have a excel template. > > It has lot many columns with column names in the first row, and one of > them is labeled "Date Of Birth". The "Date Of Birth" column label is > itself a String but I have formatted the column type to be date. So any > value I enter in that column other than the first row, is formatted as a > date. > > User First Name DOB > Kal1 Kalpesh 01/01/1901 > > The "DOB" column is formatted as date. When the user uploads a file, I > want to first check for the header data types in the first row. > > What I am expecting is that when I check the data type (ONLY THE HEADER > LABEL FIRST ROW) for User it returns String, First Name returns String > and DOB also returns String. Actually I am expecting DOB to be Date. I > am not talking about the data type of the actual data from the second > row onwards. > > Thanks and regards, > -Kalpesh > > > -----Original Message----- > From: MSB [mailto:[email protected]] > Sent: Tuesday, July 13, 2010 2:39 AM > To: [email protected] > Subject: Re: Getting Data type > > > First some bad news; it is only possible to enter one of three different > types of value into a cell, a number, a String or a forumla which is > itslef > a type of String I guess. Then you apply a format to that value to > transform > the cells content into a Date value a currency value, etc. Therefore, it > is > likely that POI is reporting the type of the cell correctly - it is a > String > - that has then been formatted to look like a date. > > The answer to your question depends a lot on what you actually want to > do > with the value. If all you want to do is recover the contents of the > cell as > a String and formatted as they appeared on the worksheet, then try using > DataFormatter - > http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter. > html > - which will return the contents of the cell to you as a correctly > formatted > String. However, if you need the contents of the cell as a Date value, > you > will first need to make sure that the cell does contain a date and there > is > a class called DateUtil - > http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DateUtil.html > - to > help, simply call the static isCellDateFormatted(Cell) method and pass > across the cell. This method will return true if the cell is formatted > as a > date. Next, you will need to convert the String value the cell contains > into > a Date object and this should be possible using the methods in the core > Java > api - DateFormat.parse(String) for example - passing the cell's contents > as > a String by calling either cell.GetRichStringCellValue().getString() or > getStringCellValue(). It may be worth making a second check after the > isCellDateFormatted() for cell type String. It could also be worth > checking > to see whether you could use the DataFormatter class's methods to > convert > the cells contents into a String and then simply pass this value to the > Date > class constructor also. > > Hope this helps a little. > > Yours > > Mark B > > > Kalpesh Modi-2 wrote: >> >> Hi, >> >> >> >> I have created an Excel template with few columns: >> >> >> >> Name of the columns: User, First Name, Last Name, DOB >> >> >> >> The DOB column is formatted as Date when creating the workbook > template. >> >> >> >> When I read the cell type for the DOB field, i.e. the actual column >> header DOB, it returns as String, even though its type is set to be >> Date. >> >> >> >> Any ideas, how I can get the cell type as Date. >> >> >> >> Thanks and regards, >> >> -Kalpesh >> >> >> >> >> . >> The information contained in this e-mail message is intended only for > the >> personal >> and confidential use of the recipient(s) named above. This message is >> privileged >> and confidential. If the reader of this message is not the intended >> recipient or an >> agent responsible for delivering it to the intended recipient, you are >> hereby notified >> that you have received this document in error and that any review, >> dissemination, >> distribution, or copying of this message is strictly prohibited. >> >> > > -- > View this message in context: > http://old.nabble.com/Getting-Data-type-tp29141500p29147093.html > Sent from the POI - User mailing list archive at Nabble.com. > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > . > The information contained in this e-mail message is intended only for the > personal > and confidential use of the recipient(s) named above. This message is > privileged > and confidential. If the reader of this message is not the intended > recipient or an > agent responsible for delivering it to the intended recipient, you are > hereby notified > that you have received this document in error and that any review, > dissemination, > distribution, or copying of this message is strictly prohibited. > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > > -- View this message in context: http://old.nabble.com/Getting-Data-type-tp29141500p29150357.html Sent from the POI - User mailing list archive at Nabble.com. --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
