Must admit that I agree, and for more than one reason. You may have seen that the size of your original template file was quite large - this was down to the fact that you had set the format on the entire column. Setting the formats instead on just single cells within a column should mean that the template file will shrink down in size, not dramatically I admit, but somewhat.
All the best with your project and if you have any further questions, do not hesitate to post a message onto the list. Yours Mark B Kalpesh Modi-2 wrote: > > Thank you very much for your detailed explanation and solution. > > I think the second solution of creating a dummy row is more suitable for > my application. > > Thanks and regards, > -Kalpesh > > -----Original Message----- > From: MSB [mailto:[email protected]] > Sent: Tuesday, July 13, 2010 9:26 AM > To: [email protected] > Subject: RE: Getting Data type > > > 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] > > . > 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-tp29141500p29162997.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]
