Create cell in Hours Format

[mouss4rs]

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.

[mouss4rs]

--
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.

[javakensai]

"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.

[Mark Beardsley]

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.

[mouss4rs]

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.

[Nick Burch]

Post by mouss4rs
12:00:00
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

[mouss4rs]

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.