Is it possible to write into excel file line by line using java and Apache POI libraries

Question

I am trying to write into my excel file line by line. Code will run fine with no error. but when i try to open the excel file, one popup appears and says that : "We found a problem with some content in the excel file. Do you want us to try to recover as much as we can?"

Normally I use FileOutputStream outside the loop in which I work with the data and use .setCellValue(Value). But when I use it inside the loop, the excel file wont get updated. In the code below, after writing the excel I am closing and reopening the excel file. I tried available solutions but didn't work. Any help is appreciated.

below is the code :

Package Excel;

import java.io.FileInputStream;    
import java.io.FileOutputStream;    
import java.io.IOException;    
import java.io.InputStream;    
import java.util.Properties;

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;    
import org.openqa.selenium.WebDriver;

public class testTool {

static WebDriver driver;
public static FileInputStream ACF;
public static FileOutputStream fos;

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

    Properties properties = new Properties();
    InputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/PACon_InputPath.properties");
    properties.load(inputStream);

    int i = 1;

    ACF = new FileInputStream(properties.getProperty("inputFilePath"));
    XSSFWorkbook workBk = new XSSFWorkbook(ACF);
    XSSFSheet Workable_Dump_Data = workBk.getSheet("Workable Dump Data");

    for (i=1;i<=5;i++)
    {

        System.out.println("\n````````````````````````````````````````````````````````````Row - " + i);

        XSSFRow Rw = Workable_Dump_Data.getRow(i);

        XSSFCell Account_Name = Rw.getCell(3); // Fetching Account name from the excel
        String accountName = Account_Name.getStringCellValue();
        System.out.println("Account Name : " + accountName);

        XSSFCell Physical_address = Rw.getCell(3); // Fetching Physical Address from the excel
        String physicalAddress = Physical_address.getStringCellValue();
        System.out.println("Physical Address : " + physicalAddress);

        boolean flag1 = false;
        if (accountName.equals("") || physicalAddress.equals(""))
        {

            XSSFCell str13 = Rw.getCell(15);
            str13.setCellValue("Empty Fields");
            fos = new FileOutputStream(properties.getProperty("inputFilePath"), true);
                workBk.write(fos); // writing to Excel and continue
                fos.close();
                workBk = new XSSFWorkbook(new FileInputStream(properties.getProperty("inputFilePath")));
                continue;
        }
        else
        {
            XSSFCell str13 = Rw.getCell(15);
            str13.setCellValue("Fields Are Available");
            fos = new FileOutputStream(properties.getProperty("inputFilePath"), true);
                workBk.write(fos); // writing to Excel and continue
                fos.close();
                workBk = new XSSFWorkbook(new FileInputStream(properties.getProperty("inputFilePath")));
                continue;
        }

    }

    System.out.println("Successfully writen in the excel sheet");

}

}

Answer 1

I got a work around for my requirement. We can put the FileOutputStream inside the loop, one thing we need to keep in mind is to save and open excel file after writing into it for every iteration.

steps will be like: 1. Open excel file (FileInputStream) 2. Set value to cells (.setCellValue()) 3. Write into excel (workBk.write(fos)) 4. Save the excel file (fos.close()) 5. Open excel again (See code below) 6. Increment loop AND repeat....

The change i made in my previous code :

1st (Removed True from the end)

fos = new FileOutputStream(properties.getProperty("inputFilePath"));

2nd (Added before continue;)

Workable_Dump_Data = workBk.getSheet("Workable Dump Data");

Refer full code below :

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

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 testTool {

public static FileOutputStream fos;
public static FileInputStream ACF;

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

    Properties properties = new Properties();
    InputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/PACon_InputPath.properties");
    properties.load(inputStream);

    try {
        ACF = new FileInputStream(properties.getProperty("inputFilePath"));
        XSSFWorkbook workBk = new XSSFWorkbook(ACF);
        XSSFSheet Workable_Dump_Data = workBk.getSheet("Workable Dump Data");

        int i = 1;

        for (i = 1; i <= 10; i++) {

            System.out.println("\n````````````````````````````````````````````````````````````Row - " + i);

            XSSFRow Rw = Workable_Dump_Data.getRow(i);

            /*if (i == 6) // test if it update excel, if tool stops in between
            {
                String accountTest = "";
                XSSFCell Account_Name = Rw.getCell(22);
                accountTest = Account_Name.getStringCellValue();
            }*/

            XSSFCell Account_Name = Rw.getCell(3); // Fetching Account name from the excel
            String accountName = Account_Name.getStringCellValue();
            System.out.println("Account Name : " + accountName);

            XSSFCell Physical_address = Rw.getCell(3); // Fetching Physical Address from the excel
            String physicalAddress = Physical_address.getStringCellValue();
            System.out.println("Physical Address : " + physicalAddress);

            boolean flag1 = false;
            if (accountName.equals("") || physicalAddress.equals(""))
            {

                XSSFCell str13 = Rw.getCell(15);
                str13.setCellValue("Empty Fields");
                fos = new FileOutputStream(properties.getProperty("inputFilePath"));
                    workBk.write(fos); // writing to Excel and continue
                    fos.close();
                    workBk = new XSSFWorkbook(new FileInputStream(properties.getProperty("inputFilePath")));
                    Workable_Dump_Data = workBk.getSheet("Workable Dump Data");
                    continue;
            }
            else
            {
                XSSFCell str13 = Rw.getCell(15);
                str13.setCellValue("Fields Are Available" + i);
                fos = new FileOutputStream(properties.getProperty("inputFilePath"));
                    workBk.write(fos); // writing to Excel and continue
                    fos.close();
                    workBk = new XSSFWorkbook(new FileInputStream(properties.getProperty("inputFilePath")));
                    Workable_Dump_Data = workBk.getSheet("Workable Dump Data");
                    continue;
            }

        }

    } catch (Exception e) {
        //Do something better with the Exception
        e.printStackTrace();
    }
    finally{
        fos.close();
        System.out.println("Successfully writen in the excel sheet");
    }
}

}

Thanks for the help @Gagravarr and @sirandy :)

Answer 2

Just take away the true in the FileOutputStream constructor.

Also here is your code a little refactored:

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

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 testTool {

    public static FileInputStream ACF;
    public static FileOutputStream fos;

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

        Properties properties = new Properties();
        InputStream inputStream = new FileInputStream(System.getProperty("user.dir") + "/PACon_InputPath.properties");
        properties.load(inputStream);

        try {
            ACF = new FileInputStream(properties.getProperty("inputFilePath"));
            XSSFWorkbook workBk = new XSSFWorkbook(ACF);
            XSSFSheet Workable_Dump_Data = workBk.getSheet("Workable Dump Data");

            int i = 1;

            for (i = 1; i <= 5; i++) {

                System.out.println("\n````````````````````````````````````````````````````````````Row - " + i);

                XSSFRow Rw = Workable_Dump_Data.getRow(i);

                XSSFCell Account_Name = Rw.getCell(3); // Fetching Account name from the excel
                String accountName = Account_Name.getStringCellValue();
                System.out.println("Account Name : " + accountName);

                XSSFCell Physical_address = Rw.getCell(3); // Fetching Physical Address from the excel
                String physicalAddress = Physical_address.getStringCellValue();
                System.out.println("Physical Address : " + physicalAddress);

                XSSFCell str13 = Rw.getCell(15);
                boolean flag1 = false;
                if (accountName.equals("") || physicalAddress.equals("")) {
                    str13.setCellValue("Empty Fields");
                } else {
                    str13.setCellValue("Fields Are Available");
                }
            }
            FileOutputStream fos = new FileOutputStream(properties.getProperty("inputFilePath")); 
            workBk.write(fos); // writing to Excel and continue
            fos.close();
            ACF.close();
        } catch (Exception e) {
            //Do something better with the Exception
            e.printStackTrace();
        }
        finally{
            System.out.println("Successfully writen in the excel sheet");
        }
    }

}