0

I am reading some cell value using POI in java. Now suppose I enter a date like 10.10.2015 in that case when I set celltype Numeric and say getDatecellValue() it returns me date in calendar format like Nov 10, 2015 mm:ss xyz. Apart from it I tried using below code snippet

new DataFormatter().formatCellValue(cell)

and this return 10/10/15. But I want to read date in POI(in java code) as it is like text that mean 10.10.2015.

Mateusz Sroka
  • 2,255
  • 2
  • 17
  • 19
Crazy-Coder
  • 107
  • 1
  • 1
  • 9
  • 1
    `DataFormatter` should be returning the value formatted exactly like it's displayed in Excel. – Andreas Jan 21 '16 at 08:46
  • Thanks for reply new DataFormatter().formatCellValue(cell) is returning 10/10/15 not exactly as in excel. I also dont want any date formatting just want to pickup and show that :- "fomat 10.10.2015 that u entered in excel is Invalid" Not like 10/1/15 or DateFormatted-calenderFormatted – Crazy-Coder Jan 21 '16 at 08:49
  • 1
    Now I'm confused. Do you want the date value *as a string* in the same format as displayed by Excel, or do you want the date value itself so you can display it in whatever format you choose? – Andreas Jan 21 '16 at 08:51
  • Andreas, I want date value as it is, in same format, as displayed by excel. Thats it!! if date in excel is 10/10/ 2015 I want to get in Java(POI) 10/10/2015 as it is. Kindly help. Thanks a lot – Crazy-Coder Jan 21 '16 at 08:58
  • 1
    If you open your Excel file on a copy of Excel in the US-English locale, does it show `10.10.2015` or `10/10/2015`? (Excel has some formats that magically change how they look depending on the locale of the computer opening it) – Gagravarr Jan 21 '16 at 09:10
  • 10.10.2015 only.Even if i enter 10/10/2015 it converts automatically into 10.10.2015 with cell format as Date. but that's Ok..what so ever!! but when I fetch it using POI. I don't want any formatting on that..That is not happening . All DateUtil methods inside POI is formatting in some other format that's pain. I want "as it is" as entered in Excel sheet – Crazy-Coder Jan 21 '16 at 09:15
  • 1
    Please read http://stackoverflow.com/questions/34900605/excell-cell-style-issue/34902174#34902174 – Axel Richter Jan 21 '16 at 10:07
  • Thank you so much.This helped me But I am working on German Window where date format is dd.mm.yyyyy, strange thing is I am entering dd.mm.yyy but getDataFormatString() is returning dd/mm/yyyy. It will be the best if I could as it is like dd.mm.yyyy.is there any way.. – Crazy-Coder Jan 21 '16 at 10:51
  • Format the cell with an explicit locale, and it'll behave the same everywhere. If you leave it without an explicit locale, Excel will store it as US format and try to be "helpful" on reading – Gagravarr Jan 21 '16 at 11:18
  • @Gagravarr: No, without an explicit locale, Excel will store it as system dependent date format with format ID 14. POI will assume en-us as it has neither knowledge nor access to the system settings of the system from which the Excel file is generated or on which the POI generated Excel file will running later. – Axel Richter Jan 21 '16 at 12:44
  • @Himanshu Tripathi: So your DataFormatString if `getDataFormat() == 14` is `dd.mm.yyyy`. Since POI has no access to any system settings `Region and Language` you must define your own defaults for number format ID 14. – Axel Richter Jan 21 '16 at 12:52
  • 1
    @HimanshuTripathi Please stop contradicting yourself. *"I want date value as it is, **in same format**, as displayed by excel"*, but also *"when I fetch it using POI. I **don't want any formatting** on that"* – Andreas Jan 21 '16 at 16:54
  • @ Axel Richter Thank you very much sir for your link that worked in my case I have taken default format of date is dd.MM.yyyy as excel has default German format(for code==14). It worked Thanks a ton – Crazy-Coder Jan 28 '16 at 12:15
  • @Andreas Thank you for your kind replies n help.probably sound like contradictory though my statement was: I want date value as it is, in same format, as displayed by excel means::--> like 10.10.2016 as it is ... I don't want any formatting on that means": - it was getting converted into Calendar formats or any other En_US format that was not desired while retrieving in POI – Crazy-Coder Jan 28 '16 at 12:16

0 Answers0