Further to my previous post, I have been messing about a little with dates
recently - to date stamp files not as cell contents but this technique might
still be useful.
The only way to make sure that the cell holds the actual value you want -
2010/05/01 - is to write a String into that cell not a date which you then
try to format. One way to do this is to create the cell and then get the
date value something like this;
String formatString = "yyyy/mm/dd";
SimpleDateFormat dateFormat = new SimpleDateFormat(formatString);
Calendar cal = Calendar.getInstance();
String cellContents = dateFormat.format(cal.getTime());
and then you would pass the String - cellContents - to the setCellValue()
method of the cell.
Remember though that this is only String. You will not be able to use it to
perform any date based calculations - such as determining if it falss before
or after another date for instance.
Yours
Mark B
phucbui wrote:
>
> Great, that worked :jumping:
> But I still have a problem.
> Its display is 2010/05/01
> but actual value is 5/1/2010 9:08:50 AM
> How can we make the actual value is 2010/05/01 too (or 5/1/2010 ) not
> include the time.
>
> Thanks,
> Phuc Bui
>
>
> MSB wrote:
>>
>> Well, as you have just discovered, to Excel a date is nothing more than a
>> large number. To actually see the date formatted in the familar fashion,
>> you need to create an apply a style to the cell and in order to best show
>> you how to do that, I really needed to know which version of the file
>> format you were targetting, binary with HSSF, OOXML with XSSF or both
>> with the classes in the SS stream.
>>
>> As I do not, I will have to explain with an example that uses the HSSF
>> stream - I have not tested this exact piece of code as I do not have an
>> editor open currently but am pretty confident it will work;
>>
>> // Just some basic code to create a new workbook, add a sheet,
>> // a row to the sheet and a cell to the row.
>> HSSFWorkbook workbook = new HSSFWorkbook();
>> HSSFSheet sheet = workbook.createSheet("Date Test");
>> HSSFRow row = sheet.createRow(0);
>> HSSFCell cell = row.createCell(0);
>>
>> // Now to use your code to set the date value into the cell.
>> cell.setCellValue(Calendar.getInstance());
>>
>> HSSFCellStyle dateStyle = workbook.createCellStyle();
>> HSSFDataFormat formatter = workbook.createDataFormat();
>> // This should work as a date format. PLay with the String and
>> // see what happens.
>> dateStyle.setDataFormat(formatter.getFormat("dd/mm/yyyy"));
>>
>> cell.setCellStyle(dateStyle);
>>
>> FileOutputStream fos = new FileOutputStream(new File(".............."));
>> workbook.write(fos);
>>
>> and that ought to do the trick. Have a look here -
>> http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html
>> - and you wuill see a list of the buil-in formats that ship as standard
>> with Excel. Obviously, you can create your own and if you want to so
>> this, it is often best to experiment directly with Excel.
>>
>> Yours
>>
>> Mark B
>>
>>
>>
>>
>> phucbui wrote:
>>>
>>> This is my code
>>> cell.setCellValue(Calendar.getInstance());
>>> And this is result :confused:
>>> 40296.92606
>>> I have read many instructions to get correctly date format from
>>> available cells, but I do not know how to create a cell with correct
>>> date format yet.
>>> Please help me that :-)
>>>
>>> Phuc Bui
>>>
>>
>>
>
>
--
View this message in context:
http://old.nabble.com/Problem-with-date-tp28389526p28425511.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]
