That proved to be a little more entertaining than I thought. Referring back
to the code I posted before, this should do the trick;
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package workbookprotection;
import java.io.*;
import java.util.*;
import java.text.DecimalFormat;
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;
DecimalFormat decFormat = null;
try {
decFormat = new DecimalFormat( "0.0;'-'0.0" );
file = new File(filename);
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
fis = null;
System.out.println("There are " + workbook.getNumberOfSheets() +
" sheets in the workbook.");
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());
double value = cell.getNumericCellValue();
String strValue = decFormat.format(value);
value = Double.parseDouble(strValue);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
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) {
}
}
}
}
}
Make sure to check the values carefully to ensure that they are all
calculating correctly. There is one thing that I noticed, a value of -0.01
was converted in -0.0. I guess that this is strictly accurate but it may not
be what you want.
Yours
Mark B
--
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-tp3404980p3406503.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]
