How to Write data in Excel file using Java and Apache POI

Similar to reading data from an excel file, you would require to write data in excel file using java in your test automation. Read further to know using java write to excel files.

On this page, we have a list of functions for writing the capability Excel files using Java such as  Enter data in an excel file using Apache POI.

All the methods are written by utilizing the Apache POI library.

If you are not sure, “How to add APACHE POI library to your Selenium Automation Project?”, Please check out this guide on How to add Apache POI library in Selenium Automation Project.

Example code to Create an Excel file and Write data in the Excel using Apache POI

/**
 * This is a sample method for creating an excel workbook and write data to it.
 * In this method data is given in Treemap. It can be modified as per need.
 *  
 */
public static void writeExcel() {
  //Blank workbook
  XSSFWorkbook workbook = new XSSFWorkbook();

  //Create a blank sheet
  XSSFSheet sheet = workbook.createSheet("Employee Data");

  //This data needs to be written (Object[])
  Map < String, Object[] > data = new TreeMap < String, Object[] > ();
  data.put("1", new Object[] {
    "ID",
    "NAME",
    "LASTNAME"
  });
  data.put("2", new Object[] {
    1,
    "Amit",
    "Shukla"
  });
  data.put("3", new Object[] {
    2,
    "Lokesh",
    "Gupta"
  });
  data.put("4", new Object[] {
    3,
    "Gabbar",
    "Singh"
  });
  data.put("5", new Object[] {
    4,
    "Jetha",
    "Lal"
  });

  //Iterate over data and write to sheet
  Set < String > keyset = data.keySet();
  int rownum = 0;
  for (String key: keyset) {
    XSSFRow row = sheet.createRow(rownum++);
    Object[] objArr = data.get(key);
    int cellnum = 0;
    for (Object obj: objArr) {
      XSSFCell cell = row.createCell(cellnum++);
      if (obj instanceof String)
        cell.setCellValue((String) obj);
      else if (obj instanceof Integer)
        cell.setCellValue((Integer) obj);
    }
  }
  try {
    //Write the workbook in file system
    FileOutputStream out = new FileOutputStream(new File("C:\\Users\\automationScript\\Documents\\Automation\\writeTestDataFile.xlsx"));
    workbook.write(out);

    out.close();
    System.out.println("writeTestDataFile.xlsx written successfully on disk.");
  } catch (Exception e) {
    e.printStackTrace();
  }
}

Example code to Write data in an existing Excel File and Excel Sheet using Apache POI

/**
 * This method writes data to an excel file named fileName located at filePath and with sheetname =sheetName
 * @param filePath		Location of the file
 * @param fileName		Name of the file
 * @param sheetName	Name of the sheet
 * @param dataToWrite	Data to write
 * @throws IOException
 */
public void writeExcel(String filePath, String fileName, String sheetName, String[] dataToWrite) throws IOException {

  File file = new File(filePath + "\\" + fileName);
  FileInputStream inputStream = new FileInputStream(file);
  Workbook trackWorkbook = null;
  String fileExtensionName = fileName.substring(fileName.indexOf("."));
  if (fileExtensionName.equals(".xlsx")) {

    trackWorkbook = new XSSFWorkbook(inputStream);

  }

  Sheet sheet = trackWorkbook.getSheet(sheetName);

  int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();
  Row row = sheet.getRow(0);
  Row newRow = sheet.createRow(rowCount + 1);

  for (int j = 0; j < row.getLastCellNum(); j++) {

    Cell cell = newRow.createCell(j);
    cell.setCellValue(dataToWrite[j]);
  }

  inputStream.close();
  FileOutputStream outputStream = new FileOutputStream(file);
  trackWorkbook.write(outputStream);
  outputStream.close();

}

Write data in an Existing Excel File using Apache POI- Create the Sheet if not present

/**
 * This method can be used to write data in a 2d array to an existing excel file and sheet. 
 * If the sheet is not present, it will created at run time by the method
 *   
 * @param filePath Path of the existing Excel file
 * @param fileName Name of the existing excel file
 * @param sheetName Name of the Sheet 
 * @param dataToWrite Data to write in String[] format. 
 * @throws IOException
 */
public static void writeToExcel(String filePath, String fileName, String sheetName, String[] dataToWrite) throws IOException {
  Sheet sheet;
  int rowCount;
  Row row;
  Row newRow;
  Cell cell;
  int colCount;
  int sheetIndex;

  File file = new File(filePath + "\\" + fileName);
  FileInputStream inputStream = new FileInputStream(file);
  Workbook trackWorkbook = null;
  String fileExtensionName = fileName.substring(fileName.indexOf("."));

  if (fileExtensionName.equals(".xlsx")) {
    trackWorkbook = new XSSFWorkbook(inputStream);
  }

  sheetIndex = trackWorkbook.getSheetIndex(sheetName);

  if (sheetIndex >= 0) {

    sheet = trackWorkbook.getSheet(sheetName);
    rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();
    row = sheet.getRow(0);
    newRow = sheet.createRow(rowCount + 1);
    colCount = row.getLastCellNum();
  } else {

    //Create a blank sheet if it is not present
    sheet = trackWorkbook.createSheet(sheetName);
    rowCount = 0;
    newRow = sheet.createRow(0);
    colCount = dataToWrite.length;

  }

  for (int j = 0; j < colCount; j++) {

    cell = newRow.createCell(j);
    cell.setCellValue(dataToWrite[j]);
  }

  inputStream.close();
  FileOutputStream outputStream = new FileOutputStream(file);
  trackWorkbook.write(outputStream);
  outputStream.close();
}