Fill xlsx file empty cell with results from a map using Java and Apache POI

Question

I basically have 2 xlsx files, one with image url's and a product id, and one with a empty image url's and a product id.

I already load the contents I need to parse in the other file correctly, the parameter with the map contains all the info required.

So my question is, how can I write in the new file with the results from the mappings? I basically need to put the image url on the first column's index which contains the image urls for all the products.

Here is some sample code,

    private static void writeFile(Map<String, String> products) throws IOException {
        File file = new File("./data/stock_zonder_imageurls.xlsx");
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(fis);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {
            Row currentRow = iterator.next();
            Iterator<Cell> cellIterator = currentRow.iterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();

                if(cell != null && cell.getCellTypeEnum().equals(CellType.STRING)) {
                    if(cell.getColumnIndex() == 2) {
                        String val = cell.getStringCellValue();

                        if(products.containsKey(val)) {
                            String image_url = products.get(val);
                            //here I need to write to the file the image url on column 0 of the specified $file with the $image_url
                        }
                    }
                }
            }
        }
    }

Answer 1

as per the question, your requirement is a update the existing excel file.

try with following steps.

//create a row id and initialized
int rowId = 0;

while (iterator.hasNext()) {
    ---- //another code
    rowId++;
    if(products.containsKey(val)) {
        String image_url = products.get(val);
        //assumed as first row is header
        Cell cell = datatypeSheet.getRow(rowId).getCell(0); //get the cell from relevant row (rowId) and first column (column index is a 0)
        cell.setCellValue(image_url); //set image_url into cell
    }
    ---- //another code
}