CODEWITHSUNDEEP
excelapache-poixssf
MM1122
MM1122

Reputation: 23

Change cell type of excel cells using Apache Poi 5.1

I am trying to change the cell type from general to numeric of an excel sheet using Apache Poi. I have made the .xlsx file on my own from a .csv file using Poi itself. Now I need to change the cell_type of "E" and "F" column of the xlsx file to numeric. This is what I've tried:

        FileInputStream fis = new FileInputStream(final_report_name);
        XSSFWorkbook workBook = new XSSFWorkbook(fis);
        XSSFSheet new_sheet = workBook.getSheetAt(0);
        
        XSSFDataFormat format = workBook.createDataFormat();
        XSSFCellStyle style = workBook.createCellStyle();
        style.setDataFormat(format.getFormat("#"));
        
        new_sheet.setDefaultColumnStyle(4, style); //These don't work
        new_sheet.setDefaultColumnStyle(5, style);  //They keep the cell type general
        
        XSSFRow row = new_sheet.getRow(3);
        Cell cell = row.getCell(5);  //This changed the cell type to "#"
        cell.setCellStyle(style);   // But the number is still displayed as text

        FileOutputStream fileOutputStream =  new FileOutputStream(final_report_name);
        workBook.write(fileOutputStream);
        workBook.close();

The column styling does not work at all, and the cell styling worked, but the number is still displayed as text. I still need to manually go to the hovering icon next to it which says "The number in this cell is formatted as text or preceded by an apostrophe". then I need to click on "Convert to number" menu (see image). What could be the problem here and how do I achieve this?

Upvotes: 0

Views: 4970

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

Setting the cell style does not automatically change the cell type. The cell type depends on the cell content. Not even explicitly setting the cell type using Cell.setCellType will always automatically change the cell type if content don't match that type. So you need setting the cell content aka cell value correct.

There are various setCellValue methods in Cell. If setCellValue(java.lang.String value) or setCellValue(RichTextString value) is used, the cell type will always be text. Do using setCellValue(double value) for numeric values, setCellValue(boolean value) for boolean values (TRUE or FALSE) and one of the various setCellValue(DATETYPE) (setCellValue(java.util.Calendar value), setCellValue(java.time.LocalDateTime value), ...) for date values.

Upvotes: 1

Related Questions