Formatting excel cells using Java from String to Number

Question

I'm trying to format Column Number 5 to number. I know that formatting can be done by setCellStyle but how can i implement the same in my below code?

FileInputStream ExcelFileToRead = null;
HSSFWorkbook wb = null;
FileOutputStream outputStream = null;
short colNum = 5;

try {
 ExcelFileToRead = new FileInputStream("config/test/test.xls");
} catch (FileNotFoundException e) {
 e.printStackTrace();
}
try {
 wb = new HSSFWorkbook(ExcelFileToRead);
} catch (IOException e) {
 e.printStackTrace();
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = null;
HSSFRow row = null;

sheet.createRow(0).createCell((short) 6).setCellValue("100");
sheet.createRow(3).createCell(colNum).setCellValue("120");
sheet.createRow(5).createCell(colNum).setCellValue("300");
sheet.createRow(11).createCell(colNum).setCellValue("500");
sheet.createRow(15).createCell(colNum).setCellValue("900");
sheet.createRow(18).createCell(colNum).setCellValue("1000");
sheet.createRow(23).createCell(colNum).setCellValue("10");
sheet.createRow(28).createCell(colNum).setCellValue("20");
sheet.createRow(30).createCell(colNum).setCellValue("30");
sheet.createRow(49).createCell(colNum).setCellValue("40");

outputStream = new FileOutputStream("config/test/test.xls");
wb.write(outputStream);
outputStream.close();

Well did you try to use ´setCellStyle´? There are numerous question on setting styles here on SO. For instance this stackoverflow.com/questions/5335285/… . Please read minimal reproducible example — jontro
– jontro, Commented Jan 22, 2018 at 0:30

Alex · Accepted Answer · 2018-01-22 01:13:14Z

1

You can set the default column style by passing a CellStyle object to the setDefaultColumnStyle() method for your sheet object. The format is parsed from the string that you pass. So "0" is an integer, "0.0" is a decimal to 1 decimal place, "0.00" is a decimal to 2 decimal places, etc. There's a lot more to this, and you can see the list of formats using HSSFDataFormat.getBuiltinFormats().

You should also pass your values in as a number type rather than a string, otherwise they cell values will be text rather than a number.

Here's an example setting the whole column as number.

public static void main(String[] args) throws Exception {
    short colNum = 5;

    FileInputStream workbookStream = new FileInputStream("config/test/test.xls");
    HSSFWorkbook workbook = new HSSFWorkbook(workbookStream);

    HSSFSheet worksheet =  workbook.getSheetAt(0);

    // set column style here
    DataFormat dataFormat = workbook.createDataFormat();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(dataFormat.getFormat("0"));
    worksheet.setDefaultColumnStyle(colNum, cellStyle);

    worksheet.createRow(0).createCell((short)6).setCellValue(100);
    //...
    worksheet.createRow(49).createCell(colNum).setCellValue(40);

    FileOutputStream outputStream = new FileOutputStream("config/test/test.xls");
    workbook.write(outputStream);
    outputStream.close();
}

edited Jan 22, 2018 at 1:13

answered Jan 22, 2018 at 0:49

Alex

6721 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Annie Jeba Over a year ago

Thank you for your reply. When i tried to import org.apache.poi.ss , i'm getting package org.apache.poi.ss does not exist compilation error.

Alex Over a year ago

No idea why that would be, those packages are part of the same .jar as HSSFWorkbook and the other classes you used. I just downloaded the latest version of the library for this, and it's all in the poi-3.17.jar file.

Collectives™ on Stack Overflow

Formatting excel cells using Java from String to Number

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related