Java POI edit excel file - cell.setCellValue not working

Question

I'm trying to modify an excel file but for some reason which I do not understand the method Cell.setCellValue does not work in my code. What I'm actually doing is: -I'm opening an excel file and saving the content that interests me in a HashMap. This works i can print the content of the hashmap. -Then I'm trying to modify another excel file with the data saved in the HashMap but this does not happen for some reason.

Here is my code:

    public File manipulateDocumentITM(File xlFile) {

// ADDING DATA FROM AN EXCEL FILE TO A HASHMAP

        HashMap<Integer, ArrayList<Date>> hashMap = new HashMap<>();
        try {
            FileInputStream inFile = new FileInputStream(xlFile);
            Workbook workbookInFile = new XSSFWorkbook(inFile);
            Sheet sheetInFile = workbookInFile.getSheetAt(0);
            Iterator<Row> rowIteratorInFile = sheetInFile.iterator();
            int rowCountInFile = 5, key = 0, countEmpty = 0, rowCountModelFile = 10;
            while (rowIteratorInFile.hasNext()) {
                ArrayList<Date> arrayList = new ArrayList<>();
                Row rowInFile = rowIteratorInFile.next();
                if (rowInFile.getRowNum() == rowCountInFile) {
                    key++;
                    Iterator<Cell> cellIteratorInFile = rowInFile.cellIterator();
                    arrayList = new ArrayList<>();
                    while (cellIteratorInFile.hasNext()) {
                        Cell cell = cellIteratorInFile.next();
                        if ((cell.getCellType() == CellType.NUMERIC) && (cell.getColumnIndex() != 0)) {
                            Date data = cell.getDateCellValue();
                            arrayList.add(data);
                        }
                    }
                    hashMap.put(key, arrayList);
                    rowCountInFile = rowCountInFile + 4;
                }
            }
    
        inFile.close();


// DATA SAVED IN HASHMAP ABOVE NEXT IM JUST PRINTING THE VALUES IN THE HASHMAP



            for (Integer I : hashMap.keySet()) {
                ArrayList<Date> replaceArray = hashMap.get(I);
                System.out.println("***");
                for (int i = 0; i < replaceArray.size(); i++) {
                    System.out.println(replaceArray.get(i).getHours());
                }
            }



// CODE THAT SUPPOSE TO MODIFY EXCEL FILE WITH THE DATA FROM THE HASHMAP



            String modelPath = "/home/h1dr0/Documents/unimineral/Model foaie de prezenta (another copy).xlsx";
            FileInputStream modelFile = new FileInputStream(modelPath);
            Workbook workbookModel = new XSSFWorkbook(modelFile);
            Sheet sheetModelFile = workbookModel.getSheetAt(0);
            Iterator<Row> rowIteratorModelFile = sheetModelFile.iterator();
            ArrayList<Date> replaceArray2 = new ArrayList<>();
                Iterator it = hashMap.entrySet().iterator();
                while (rowIteratorModelFile.hasNext()) {
                    Row rowModelFile = rowIteratorModelFile.next();
                    if (rowModelFile.getRowNum() == rowCountModelFile) {
                        Iterator<Cell> cellIteratorModelFile = rowModelFile.cellIterator();
                        Map.Entry pair = (Map.Entry)it.next();
                        replaceArray2 = (ArrayList<Date>) pair.getValue();
                        while (cellIteratorModelFile.hasNext()) {
                            Cell cell = cellIteratorModelFile.next();
                            if (replaceArray2.size() != 0) {
                                for (int i = 0; i < replaceArray2.size(); i++) {
                                    if ((replaceArray2.get(i).getHours() != 0) && replaceArray2.get(i).toString() != "" && (cell.getColumnIndex() != 18)) {
                                 
                                        

                                        // THIS DOES NOT WORK
                                        cell.setCellValue(replaceArray2.get(i).getHours());


                                    }
                                    else {
                                        cell.setCellValue(" ");
                                    }
                                }
                            } else {
                                cell.setCellValue(" ");
                            }
                        }
                    rowCountModelFile = rowCountModelFile + 3;
                    }
                }
            modelFile.close();
            //}
            FileOutputStream outputStream = new FileOutputStream("/home/h1dr0/Documents/unimineral/generate.xlsx",false);
            workbookModel.write(outputStream);
            outputStream.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

        return xlFile;
    }

I also checked with debugger and the cell values are modified to what it suppose to...

cell.setCellValue(8);
  if(cell.getCellType() == CellType.NUMERIC) {
                                System.out.println("cell: " + cell.getNumericCellValue());
                            }

prints 8

What i get is the same file .. no modification.

Please help , thank you !

Do you check the right file? Your code does not change `File xlFile`. It only reads from this file. It saves the changed workbook into file `/home/h1dr0/Documents/unimineral/generate.xlsx`. And that file should contain the changed data if `cell.setCellValue(replaceArray2.get(i).getHours());` gets executed at all (one of the many `if` statements may prevent this). But that can be debugged. — Axel Richter, Apr 14 '21 at 08:48
Hi thanks for the comment. I only read from the xlFile and add in a hashmap what i read. Then I'm trying to modify workbookModel according to the hashmap not to workbook( xlFile). I do not need workbook anymore ( the first excel file ) since i put the data in the hashmap. So the only thing left is to add data from the hashmap to the second excel file ( the one that should get modify). I know the code is not so clear because of so many if's, but thanks again for the answer. — h1dr0, Apr 14 '21 at 11:37

score 1 · Accepted Answer · answered Apr 19 '21 at 13:54

1

Excel is designed to work on huge tables. Only the used ones are stored in memory or the document. That means before you can populate a cell, it first has to be created.

In your code I only see that you iterate over the existing cells but you do not try to create them. Maybe that is the issue?

answered Apr 19 '21 at 13:54

Queeg

7,748
1
16
42

Yes I guess, I need to create the cells so that will be a pain to do. Thanks for answer. – h1dr0 Apr 20 '21 at 14:35

score 0 · Answer 2 · answered Apr 20 '21 at 14:42

0

I decided to try another approach in modifying excel files. I'm using UIPath for automation. It works good I managed to do this by using their excel activity dependencies in Studio ( their IDE let's say ).

answered Apr 20 '21 at 14:42

h1dr0

23
5

Java POI edit excel file - cell.setCellValue not working

2 Answers2