You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by phucbui <ph...@yahoo.com.vn> on 2010/04/28 17:31:11 UTC
Problem with date
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-tp28389526p28389526.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem with date
Posted by MSB <ma...@tiscali.co.uk>.
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem with date
Posted by MSB <ma...@tiscali.co.uk>.
No, the actual value is not '5/1/2010 9:08:50 AM' but a very large number,
what you see displayed is that number formatted so that it appears in a
specific way. There is no way to make the actual value be 5/1/2010 unless
you convert from the date value into a String but if you do this, you will
lose all ability to perform calculations using the date value. However, if
you need to convert from a date to a String look at Java's Calendar class.
It has methods that would allow you to recover the year, day of the month,
etc from a Date value and you can use these to asemble a date String which
can then be written into an Excel worksheet cell.
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-tp28389526p28419091.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem with date
Posted by phucbui <ph...@yahoo.com.vn>.
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-tp28389526p28418376.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Problem with date
Posted by MSB <ma...@tiscali.co.uk>.
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-tp28389526p28393066.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org