setCellType(CellType.STRING) is deprecated

Question

Issue: setCellType is deprecated.

 row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);

So far tried:

Searched for replacement. No useful sources for setting a cell type as STRING. Appreciate help!

paranoidAndroid · Answer 1 · 2020-08-23T07:35:16.007

3

You can just call row.setCellValue(String) you don't have to set the cell type beforehand.

From the docs:

@deprecated This method is deprecated and will be removed in POI 5.0.
     * Use explicit {@link #setCellFormula(String)}, <code>setCellValue(...)</code> or {@link #setBlank()}
     * to get the desired result.

edited Aug 23 '20 at 07:35

answered Aug 23 '20 at 07:26

paranoidAndroid

523
5
12

Can you explain how to call it for the above scenario? – Gladiator Aug 23 '20 at 07:36
What exactly are you trying to achieve? – paranoidAndroid Aug 23 '20 at 07:38
Just need to get rid of the error in line `row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(CellType.STRING);`? – Gladiator Aug 23 '20 at 07:40
That doesn't really answer the question.. If you have a Cell object, you can just set the string value with the setCellValue method. You don't need to explicitly set the type before. You can just skip your call. – paranoidAndroid Aug 23 '20 at 07:47
But here, i am trying to read excel to update db. No cell object is created just the row object is created and the cells in row is referenced by cell number. [in the above case, 0 is referenced] – Gladiator Aug 23 '20 at 07:55
If you are just reading an excel file and you would like to read a string value, you need to call cell.getStringCellValue. So you can call Row.getCell(int cellnum, MissingCellPolicy policy) which is not deprecated and then use getStringCellValue – paranoidAndroid Aug 23 '20 at 07:58
Apache POI does not seem to set the actual cell type of the excel document to string even if you have used a string type when calling setCellValue. A user editing this cell in excel will convert the cell to a number if it contains all numbers, so it seems the type is not actually set/locked for the cell in the output document. This means that e.g. importing the same sheet will fail because the string cells that was edited is now converted to number. Even strings like "10:00" which is supposed to be a time string, excel will convert to a number cell when edited. So this is still an issue. – Johncl Sep 30 '21 at 08:22
First part little bit confused me. After some research I found documentation page of poi then I got answer. After that I tried to answer to this question and so I read your answer again and here saw second part. I think you need to delete first part or move it after poi docs part. One more thing, thanks for the answer ! – Ulug'bek Apr 27 '23 at 20:44

score 1 · Accepted Answer · answered Aug 23 '20 at 09:17

Before:

       row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellType(                                
       contentValues.put(ITEMCODE, row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue());

After:

InputStream strm =getClass().getResourceAsStream("Sample.xls"));
Workbook wb = WorkbookFactory.create(strm);
DataFormatter Stringform = new DataFormatter();
FormulaEvaluator Formeval = new HSSFFormulaEvaluator((HSSFWorkbook) wb);

Sheet sheet= wb.getSheetAt(0);
Iterator<Row> rit = sheet.rowIterator();

while(rit.hasNext()){

    Row row = rit.next();
    Cell cellValue = row.getCell(0);
    Formeval.evaluate(row.getCell(0)); // Returns string
    String cellValueStr = Stringform.formatCellValue(row.getCell(0),Formeval);
    
    ContentValues contentValues = new ContentValues();

    contentValues.put(DNNO, Stringform.formatCellValue(row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK),Formeval));
                                                                 
}

score 1 · Answer 3 · answered Jul 01 '21 at 14:02

1

Instead of directly performing operation on getCell, do get & set operation separately using XSSFCell

XSSFCell cell = (XSSFCell) row.getCell(cellNumber, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);

answered Jul 01 '21 at 14:02

Harshad Panmand

410
5
19

score 0 · Answer 4 · answered Jun 22 '21 at 14:34

2021 UPDATE still no solution, so to achieve this (for example, set cell type to Decimal), you could use this code:

double someValue = 50.0;
CellStyle styleDecimal = workbook.createCellStyle();
styleDecimal.setDataFormat(workbook.createDataFormat().getFormat("0.00"));
row.getCell(0).setCellStyle(styleDecimal);
try{
    Double.valueOf(someValue);
    row.getCell(0).setCellValue(Double.valueOf(someValue));
}catch (Exception ex){}

setCellType(CellType.STRING) is deprecated

4 Answers4