You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by mouss4rs <ou...@gmail.com> on 2012/02/25 23:11:06 UTC
Create cell in Hours Format
Hi,
I would like to write cell in Hours format like this:
hh:mm in .xlsx files.
So, I did this:
cell2 = row2.createCell(2);//on l'écrit à la colonne 2 (semaine 51)
cell2.setCellValue(tabheures[1][e]);
cellStyle = wb2.createCellStyle();
*cellStyle.setDataFormat(fmt.getFormat("hh:mm"));*cell2.setCellStyle(cellStyle);
System.out.println("c'est écrit"+tabheures[1][e]);
But, it create this in my cell:
10/01/1900 12:00:00
whereas that:
tabheures[1][e] equal 10.5
What's happen ?
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5515970.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: Create cell in Hours Format
Posted by mouss4rs <ou...@gmail.com>.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5519541.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: Create cell in Hours Format
Posted by mouss4rs <ou...@gmail.com>.
Thank you very much !!
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5528191.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: Create cell in Hours Format
Posted by Nick Burch <ni...@alfresco.com>.
On Thu, 1 Mar 2012, mouss4rs wrote:
> With hh:MM format i have:
> 12:00:00
> when i did this:
> cell.setCellValue(10.5);
>
> I must have 10:30.
If you set a value of 10.5, then getting a time of 12:00 is entirely to be
expected. Times in excel are stored as fractions of a day, so you've set a
time of 0.5 => 12:00. This is how Excel works, nothing special about POI
You probably want something like cell.setCellValue(10.5 / 24.0) to get
10:30
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Create cell in Hours Format
Posted by mouss4rs <ou...@gmail.com>.
With hh:MM format i have:
12:00:00
when i did this:
cell.setCellValue(10.5);
So, it's not normal !!
I must have 10:30.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5527458.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: Create cell in Hours Format
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Sorry to say this but I cannot reproduce the problem. When I run this piece
of code;
public FormatTest(String filename) throws IOException {
DataFormat format = null;
CellStyle cellStyle = null;
Workbook workbook = null;
Sheet sheet = null;
Row row = null;
Cell cell = null;
FileOutputStream fos = null;
BufferedOutputStream bos = null;
StringBuilder refersToFormula = null;
if(filename.endsWith(".xlsx")) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
try {
format = workbook.createDataFormat();
sheet = workbook.createSheet("Format Test");
row = sheet.createRow(0);
cell = row.createCell(0);
cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(format.getFormat("hh:mm"));
//cell.setCellStyle(cellStyle);
cell.setCellValue(10.5);
cell.setCellStyle(cellStyle);
fos = new FileOutputStream(filename);
bos = new BufferedOutputStream(fos);
workbook.write(bos);
}
finally {
if(bos != null) {
bos.close();
}
}
}
I see a workbook with ne sheet, that sheet has a single cell the contains
the value 12:00 which I am guessing is what you wanted to see.
Canm I ask one question though, are you referring to the value you see in
the forumla bar - as I think it is called. It is the area you see Excel
reflect the cells contents and where you can edit the contents of a cell? If
so, that is perfectly normal, that bar will display the raw contents of the
cell so to speak and you will only see the formatted value displayed in the
cell itself.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5521164.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: Create cell in Hours Format
Posted by javakensai <ja...@gmail.com>.
"hh:mm" is hours:months in the SimpleDateFormat. Perhaps you meant to use
"hh:MM"?
http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html
http://docs.oracle.com/javase/6/docs/api/java/text/SimpleDateFormat.html
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Create-cell-in-Hours-Format-tp5515970p5519609.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