This morning, I had the opportunity to play with some code - lost my voice
and am confined to the office - and both of these approaches works perfectly
well for me. I am running Office 2007 under Windows XP and the code was
compiled against an early beat release of version 3.8 of POI and using Java
1.6.
This version simply gets the existing style from the cell and modifes it's
format;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package workbookprotection;
import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
/**
*
* @author win user
*/
public class FormatTest {
public FormatTest(String filename) throws IOException {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
Iterator rowIter = null;
Iterator cellIter = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFCellStyle style = null;
HSSFDataFormat format = null;
try {
file = new File(filename);
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
fis = null;
format = workbook.createDataFormat();
sheet = workbook.getSheetAt(0);
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
row = (HSSFRow)rowIter.next();
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
cell = (HSSFCell)cellIter.next();
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
System.out.println("Got a blank cell.");
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println("Got a boolean cell. " +
cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.println("Got an error cell. " +
cell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println("Got a formula cell. " +
cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellInternalDateFormatted(cell)) {
System.out.println("Got a date cell. " +
cell.getDateCellValue());
break;
}
else {
System.out.println("Got a numeric cell. " +
cell.getNumericCellValue());
cell.getCellStyle().setDataFormat(format.getFormat("0.0"));
break;
}
case HSSFCell.CELL_TYPE_STRING:
System.out.println("Got a text cell. " +
cell.getStringCellValue());
break;
}
fos = new FileOutputStream(file);
workbook.write(fos);
}
}
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
}
}
if(fos != null) {
try {
fos.close();
fos = null;
}
catch(IOException ioEx) {
}
}
}
}
}
Whilst this one creates a new HSSFCellStyle object with the appropriate data
format and uses that to overwrite the cells existing style object;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package workbookprotection;
import java.io.*;
import java.util.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
/**
*
* @author win user
*/
public class FormatTest {
public FormatTest(String filename) throws IOException {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
Iterator rowIter = null;
Iterator cellIter = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFCell cell = null;
HSSFCellStyle style = null;
HSSFDataFormat format = null;
try {
file = new File(filename);
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
fis = null;
format = workbook.createDataFormat();
style = workbook.createCellStyle();
style.setDataFormat(format.getFormat("0.0"));
sheet = workbook.getSheetAt(0);
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
row = (HSSFRow)rowIter.next();
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
cell = (HSSFCell)cellIter.next();
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_BLANK:
System.out.println("Got a blank cell.");
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println("Got a boolean cell. " +
cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.println("Got an error cell. " +
cell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
System.out.println("Got a formula cell. " +
cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellInternalDateFormatted(cell)) {
System.out.println("Got a date cell. " +
cell.getDateCellValue());
break;
}
else {
System.out.println("Got a numeric cell. " +
cell.getNumericCellValue());
cell.setCellStyle(style);
break;
}
case HSSFCell.CELL_TYPE_STRING:
System.out.println("Got a text cell. " +
cell.getStringCellValue());
break;
}
fos = new FileOutputStream(file);
workbook.write(fos);
}
}
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
}
}
if(fos != null) {
try {
fos.close();
fos = null;
}
catch(IOException ioEx) {
}
}
}
}
}
The only other thing I can think of is that you hve cells that are set as
text even though they do contain a numeric value. Try running you file
through 'my' test code to see what happens. It will report the type of each
cell it encounters.
Yours
Mark B
PS. To run the test code, all you need do is something like new
FormatTest("C:/temp/my file.xls") where you supply the path to and name of
the file you want to modify.
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Round-to-one-Decimal-place-not-happenning-using-style-setDataFormat-format-getFormat-0-0-tp3404980p3406311.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]
