Apache POI setCellData not actually setting cell data

Question

Using Apache POI 3.16 Eclipse IDE neon 3 Selenium 3.4 (not that it matters in this case)

I'm having an issue with writing values to an excel spreadsheet then reading back the value.

Here's what I want to do at a high level:

1. Open up an excel file
2. write to row 1 column 1 (we are using index starting at 0)
3. Read back what was written in that cell.

The cell contains the value "B2". In a setCellData() function, I write to the cell a "Hello World" and have the function return the contents of the cell. I also have a separate function that reads in the contents of a specified cell.

When I run the following code:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Udemy_Excel_Driven {

    public static XSSFWorkbook wb;
    public static XSSFSheet sheet;
    public static XSSFRow row;
    public static XSSFCell cell;
    public static FileInputStream fis;

    public static void main(String[] args) throws IOException, Exception
    {   

        System.out.println("Before cell edit value is:");

        System.out.println(getCellData(1,1));



        String value = setCellData(1,1,"Hello World");

        System.out.println("What's the value after setting it with setCellData()?");                
        System.out.println(value);
        System.out.println("What's the value using getCellData()?");
        System.out.println(getCellData(1,1));
    }

    public static String getCellData(int rowNum, int colNum) throws IOException
    {

        /*
         * Hierarchy of excel data:
         * 
         * Workbook - take control of this
         * Sheet - pick up the sheet of the workbook
         * Row - pick the row
         * Column - after picking the row, select the column
         * Value - grab the value from the cell 
         * 
         */


        //0. = identify the path to the excel file in the system.
        fis = new FileInputStream("C:\\data.xlsx");

        //1. Create a new XSSFWorkbook object.  You need to pass in a FileInputStream object into it, which you created earlier.
         wb = new XSSFWorkbook(fis);

        //2. Get the sheet in the workbook.  Create a new XSSFsheet object and set it to the sheet in the workbook
        // Access the workbook method "getSheet" and pass in the name of the sheet
        sheet = wb.getSheet("script");

        //3. Get the row and column.  We are going to access the data from row 2 column 2.  And remember the indices start at 0.  
         row = sheet.getRow(rowNum);
         cell = row.getCell(colNum);
        //get the value specified in the row and cell
         return cell.getStringCellValue();
    }



    public static String setCellData(int rowNum, int colNum, String data) throws IOException
    {

        fis = new FileInputStream("C:\\data.xlsx");
         wb = new XSSFWorkbook(fis);
        sheet = wb.getSheet("script");
         row = sheet.getRow(rowNum);
         cell = row.getCell(colNum);

          cell.setCellValue(data);
          String cellData = cell.getStringCellValue();
         return cellData;

    }

I get the following output:

Before cell edit value is:
B2
What's the value after setting it with setCellData()?
Hello World
What's the value using getCellData()?
B2

I don't think the write actually occurred since I opened up the excel file and the "Hello World" string wasn't in the specified cell. Any answers to this issue?

Answer 1

I don't see any part of your code that actually writes to your file. It should more or less look something like this:

FileOutputStream fileOut = new FileOutputStream("C:\\data.xlsx");
wb.write(fileOut);
fileOut.close();

You can also consult this guide for this issue as well as other functionality that you might be interested in implementing.