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
                        }
                    }
                }
            }
        }
    }

What have you tried? I would try currentRow.createCell(0).setCellValue(image_url);. Of course you also need writing out the workbook using workbook.write after all. — Axel Richter
– Axel Richter, Commented Aug 29, 2020 at 16:24
So adding the creation of the cell together with the value, and writing the workbook out to the same file wil modify it and update the file for all occurences that match the key. — Kathy Juffer
– Kathy Juffer, Commented Aug 29, 2020 at 22:09
do you want to create a new file or update the existing file ?. your requirement is not clear. — Lakshan
– Lakshan, Commented Aug 30, 2020 at 6:22
Update the existing file with the image urls that match the product key. — Kathy Juffer
– Kathy Juffer, Commented Aug 30, 2020 at 14:01

Lakshan · Accepted Answer · 2020-08-31 04:04:57Z

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
}

edited Aug 31, 2020 at 4:04

answered Aug 30, 2020 at 6:53

Lakshan

1,4613 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Kathy Juffer Over a year ago

But will this keep all the other contents in the excel file too? Im looking to modify and update the file, not create new files.

Kathy Juffer Over a year ago

I assume in that case ill have to copy all the other elements in aswell?

Lakshan Over a year ago

@KathyJuffer please refer above modified answer.

Kathy Juffer Over a year ago

So the setCellValue will actually modify and update the file?

Lakshan Over a year ago

@KathyJuffer yep, try with above solution

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related