CODEWITHSUNDEEP
javaapache-poi
Marta
Marta

Reputation: 291

POI Java - Numeric Cell with Decimals

I'm having a lot of problems with numeric formats in a Cell with POI.

I need to print in the cell 11 decimals for a number and also that the cell has a numeric format to make the sum when you select the data

And I have this code:

private void writeDecimal(HSSFRow row, Double data, int position) {
    String pattern = "#.0000000000";
    HSSFCell celda = row.createCell(position);
    CellStyle styleDecimal = styles.get(ITEM_DECIMAL); // Font and alignment
    styleDecimal.setDataFormat(libro.getCreationHelper().createDataFormat().getFormat(pattern));
    celda.setCellStyle(styleDecimal);
    celda.setCellType(Cell.CELL_TYPE_NUMERIC);
    celda.setCellValue(data);
}

But the result always print less decimal because Excel rounds the number:

Decimal Problem with POI

If I convert the Double to String, prints the 11 decimals but it doesn't make the sum if I select all the numbers.

Any idea how to resolve this?

Thanks

Upvotes: 2

Views: 16087

Answers (2)

Qualtagh
Qualtagh

Reputation: 721

This code snippet doesn't contain celda.setCellStyle( styleDecimal ). Is it missing in the real code too? Add it.

Edit:

Here is a simple example using your function. It works well.

private static HSSFWorkbook workbook;

public static void main( String args[] ) throws IOException {
  workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet( "sheet" );
  HSSFRow row = sheet.createRow( 0 );
  writeDecimal( row, 0.0781013, 0 );
  FileOutputStream fos = new FileOutputStream( "workbook.xls", false );
  workbook.write( fos );
  fos.close();
}

private static void writeDecimal( HSSFRow row, Double data, int position ) {
  String pattern = "#.0000000000";
  HSSFCell celda = row.createCell(position);
  CellStyle styleDecimal = workbook.createCellStyle(); // Font and alignment
  styleDecimal.setDataFormat(workbook.createDataFormat().getFormat(pattern));
  celda.setCellStyle(styleDecimal);
  celda.setCellType(Cell.CELL_TYPE_NUMERIC);
  celda.setCellValue(data);
}

What was the problem in your code? I don't know. Your style definitely wasn't applied to the page: zeros shouldn't be rendered with "#".

Upvotes: 3

Nirmal Dhara
Nirmal Dhara

Reputation: 2141

Open the xlsx file select the column , right click on cell -> Format cells...->custom->type = 0.00000000000 click ok.

now whatever you will write on that cell, it will print on that format and if you select also it will show the sum.

by code

import java.io.FileOutputStream;
import java.io.IOException;

    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.DataFormat;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;


    public class ChangeXlxsDataFormat {

        public static void main(String[] args) throws IOException {
            // TODO Auto-generated method stub

             Workbook wb = new HSSFWorkbook();
                Sheet sheet = (Sheet) wb.createSheet("format sheet");
                CellStyle style;
                DataFormat format = wb.createDataFormat();
                Row row;
                Cell cell;
                short rowNum = 0;
                short colNum = 0;

                row = sheet.createRow(rowNum);
                cell = row.createCell(colNum);

                style = wb.createCellStyle();
                        row = sheet.createRow(rowNum++);
                cell = row.createCell(colNum);

                style = wb.createCellStyle();
                style.setDataFormat(format.getFormat("0.00000000000"));
                cell.setCellStyle(style);
                cell.setCellValue(5.12345678908);
                row = sheet.createRow(rowNum++);
                cell = row.createCell(colNum);
                cell.setCellValue(2.12345678908);
                FileOutputStream fileOut = new FileOutputStream("test.xls");
                wb.write(fileOut);
                fileOut.close();


        }

    }

Upvotes: 5

Related Questions