0

I am trying to write data in an existing excel (xlsx) file using Apache POI. The code does not give any error and the sysout shows the updated data in the cell. However the same is not reflected in the excel workbook when I open it. This is my code snippet -

 try {
        InputStream is = new FileInputStream(excelFileName);
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheet(sheetName);
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);

        if (cell == null) {
            cell = row.createCell(3);
        }
        is.close();

        cell.setCellType(Cell.CELL_TYPE_STRING);
        System.out.println(cell.getStringCellValue());
        FileOutputStream os = new FileOutputStream(excelFileName);

        cell.setCellValue("test");            

        wb.write(os);
        os.close();
        wb.close();

    } catch (IOException | InvalidFormatException e) {            
        e.printStackTrace();
    }
Saikat
  • 14,222
  • 20
  • 104
  • 125
  • I have tried the solutions from https://coderanch.com/t/537168/java/Writing-existing-excel-xls-file – Saikat Apr 03 '17 at 08:58
  • looks ok to me :/ – Erich Kitzmueller Apr 03 '17 at 09:02
  • 1
    Your sysout is placed before the setCellValue, so you don't even know if you have modified your cell. Try to print the real value after the modification. Moreover, I think the problem is linked to your save. Are you trying to save it in a new .xlsx file ? Why not on the same one, just to try ? – Antoine Apr 03 '17 at 09:08
  • Another thing : you should use XSSFWorkbook (or HSSFWorkbook) to create a new one (or open an existing one), it's way easier. Take a look at the API ==> https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFWorkbook.html – Antoine Apr 03 '17 at 09:29
  • @Antoine I am using the same file to write data back. When I run the test 2nd time the sysout shows me the value that I added in 1st run. – Saikat Apr 03 '17 at 09:46
  • I was advised to use WorkbookFactory so that HSSF or XSSF can be used as and when required (i.e. program with interface) – Saikat Apr 03 '17 at 09:47
  • @takias : Yes, i saw it but too late to edit my first message. So if the second run is seeing the value, but you don't when open the file by yourself, it's maybe because of the cell type. Did you try to modify the cell directly in Excel after your run ? (just the color, font or cell type) – Antoine Apr 03 '17 at 09:58

0 Answers0