Apache POI to keep predefined macros after editing

Question

I have XLSX and XLSM files which contains some predefined macros. I try to use Apache POI to edit these files, but macros are removed automatically. I get a notification window when I try to open file after modification, that files are removed, because file is broken:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error066080_01.xml</logFileName>
    <summary>Error in file (C:\_privat\completeReport.xlsm)</summary>
    <removedRecords>
        <removedRecord>/xl/worksheets/sheet1.xml</removedRecord>
        <removedRecord>/xl/calcChain.xml</removedRecord>
    </removedRecords>
</recoveryLog>

I use following code to handle fle, Vars.XLS contains path to a XLSX/XLSM file.

File file = new File(Vars.XLS);
FileInputStream inputStream = new FileInputStream(file);
XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.createRow(recordcount+4);
Cell cell;

cell = row.createCell(0);
cell.setCellValue(recordcount);

inputStream.close();
FileOutputStream fileOut=new FileOutputStream(Vars.XLS);
workbook.write(fileOut);
fileOut.close();

Answer 1

You are trying to update an excel sheet. In case of updation, you should not create row or cell, rather you have to read the cell. I provide below the brief code snippet, you can try.

XSSFRow row = sheet.getRow(some number);
Cell cell = row.getCell(some number);
cell.setCellValue(recordcount);