Excel Writer in Java

So, let’s begin.

In the previous post, Excel Reader in Java we used Apache POI dependency, which will remain the same for this tutorial, we don’t have to add any extra dependencies for Java Excel Writer.  Also, we created one ExcelReader.java . In this same java class, we are going to create another method writeIntoCell() but then the name of the class wouldn’t justify it, so let’s rename it to ExcelUtil .

Steps:

1. The whole  Java Excel Writer is just this one statement cell.setCellValue(assignCellValue). Here the setCellValue is predefined we don’t have to develop it and assignCellValue is the value that we want to type/give to the cell (Pass or Fail).

   • public void writeIntoCell(String excelPath, String sheetName, String colName, int rowNum, String assignCellValue) {
         cell.setCellValue(assignCellValue);
     }

2. However, to identify the required cell to which we want to put the data, we need a few things workbook, sheet, row, and cell. So let’s gather them one by one.
   • workbook = new XSSFWorkbook(excelPath);
   • sheet = workbook.getSheet(sheetName);
   • row = sheet.getRow(rowNum);
     These variables require three parameters “excelPath”, “sheetName” and “rowNum”, rowNum is the number of row which has the cell which needs to assigned value.
   • So in the end out method will look like this:

   • public void writeIntoCell(String excelPath, String sheetName, String colName, int rowNum, String assignCellValue) {
         try {
             workbook = new XSSFWorkbook(excelPath);
         } catch (IOException e1) {
             e1.printStackTrace();
         }
         sheet = workbook.getSheet(sheetName);
         row = sheet.getRow(rowNum);
         cell.setCellValue(assignCellValue);

     }

3. Our method is almost complete. The last thing that we need now is to find out which column needs to be updated. Our column name is “Status” but which column number is this. Let’s find out.

   • for (int i = 0; i < getColCount(); i++) {
         if (getCellDataStringData(0, i).trim().equalsIgnoreCase(colName)) {
             colNum = i;
             break;
         }
     }

   • What we are doing here is that we are visiting each column up to the total columns a sheet has getColCount() and if it is equal to colName we are looking for we will break the for loop and save the colNum value. Now our method will look like this.
     

     public void writeIntoCell(String excelPath, String sheetName, String colName, int rowNum, String assignCellValue) {
         try {
             workbook = new XSSFWorkbook(excelPath);
         } catch (IOException e1) {
             e1.printStackTrace();
         }
         sheet = workbook.getSheet(sheetName);
         row = sheet.getRow(rowNum);
         int colNum = 0;
         for (int i = 0; i < getColCount(); i++) {
             if (getCellDataStringData(0, i).trim().equalsIgnoreCase(colName)) {
                 colNum = i;
                 break;
             }
         }
         cell = row.getCell(colNum);
         cell.setCellValue(assignCellValue);
     }

4. Our method is ready. Now we just have to enter the value into the excel sheet, save the excel sheet and close it.

   • try {
         FileOutputStream fileOut = new FileOutputStream(excelPath, true);
         workbook.write(fileOut);
         workbook.close();
         fileOut.close();
     } catch (Exception e) {
         e.printStackTrace();
     }

5. Finally, combine all steps, our method is ready and it will look like this:
   

   public void writeIntoCell(String excelPath, String sheetName, String colName, int rowNum, String assignCellValue) {
       try {
           workbook = new XSSFWorkbook(excelPath);
       } catch (IOException e1) {
           e1.printStackTrace();
       }
       sheet = workbook.getSheet(sheetName);
       row = sheet.getRow(rowNum);
       int colNum = 0;
       for (int i = 0; i < getColCount(); i++) {
           if (getCellDataStringData(0, i).trim().equalsIgnoreCase(colName)) {
               colNum = i;
               break;
           }
       }
       cell = row.getCell(colNum);
       cell.setCellValue(assignCellValue);
       try {
           FileOutputStream fileOut = new FileOutputStream(excelPath, true);
           workbook.write(fileOut);
           workbook.close();
           fileOut.close();
       } catch (Exception e) {
           e.printStackTrace();
       }
   }