Writing to a existing xls file using POI

Question

The scenario is roughly this:

I have a java program with several methods getting called randomly.

The first method will create an xls file using apache POI and will put the headers for the columns.

All the other methods has to write a record into this file.

The final method will first mail the created xls and then delete the xls.

For above scenario is the below approach correct:

1) Create the file and put the header names in the first method:

Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("First Sheet");
Row row = sheet.createRow((short)0);
row.createCell(1).setCellValue(createHelper.createRichTextString("First Column"));
row.createCell(2).setCellValue(createHelper.createRichTextString("Second Column"));
row.createCell(3).setCellValue(createHelper.createRichTextString("Third Column"));

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

2) In the remaining methods put the records:

I am not sure of the code here. I know that I can reach the end of the sheet using getRowCount method and then add the new row. But I could not find any example code.

Also, how to access the existing xls file ?

3) In the last method, the file will be mailed and then deleted.

Do I need to perform any other steps before deleting the file ?

Answer 1

This was what I was looking for:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;   
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class PoiWriteExcelFile {

public void methodOne() {

    System.out.println("Into method one!");

    Workbook wb = new HSSFWorkbook();
    Font f = wb.createFont();
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);
    CellStyle cs = wb.createCellStyle();
    cs.setFont(f);

    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("First Sheet");
    Row row = sheet.createRow((short) 0);

    Cell c = null;

    c = row.createCell(0);
    c.setCellStyle(cs);
    c.setCellValue(createHelper.createRichTextString("First Column"));

    c = row.createCell(1);
    c.setCellStyle(cs);
    c.setCellValue(createHelper.createRichTextString("Second Column"));

    c = row.createCell(2);
    c.setCellStyle(cs);
    c.setCellValue(createHelper.createRichTextString("Third Column"));

    // Write the output to a file
    FileOutputStream fileOut;
    try {
        fileOut = new FileOutputStream("C:\\TestData\\POI\\poi-test.xls");
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("Out of method one!");

}

public void methodTwo() {

    System.out.println("Into method two!");
    InputStream inp;
    try {
        inp = new FileInputStream("C:\\TestData\\POI\\poi-test.xls");
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.createRow((short) (sheet.getLastRowNum() + 1));

        Cell c = null;
        CreationHelper createHelper = wb.getCreationHelper();

        c = row.createCell(0);
        c.setCellValue(createHelper.createRichTextString("First Row First value"));

        c = row.createCell(1);
        c.setCellValue(createHelper.createRichTextString("First Row Second value"));

        c = row.createCell(2);
        c.setCellValue(createHelper.createRichTextString("First Row Third value"));

        FileOutputStream fileOut = new FileOutputStream("C:\\TestData\\POI\\poi-test.xls");
        wb.write(fileOut);
        fileOut.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }


    System.out.println("Out of method two!");
}

public void methodThree() {

    System.out.println("Into method three!");
    InputStream inp;
    try {
        inp = new FileInputStream("C:\\TestData\\POI\\poi-test.xls");
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.createRow((short) (sheet.getLastRowNum() + 1));

        Cell c = null;
        CreationHelper createHelper = wb.getCreationHelper();

        c = row.createCell(0);
        c.setCellValue(createHelper.createRichTextString("Second Row First value"));

        c = row.createCell(1);
        c.setCellValue(createHelper.createRichTextString("Second Row Second value"));

        c = row.createCell(2);
        c.setCellValue(createHelper.createRichTextString("Second Row Third value"));

        FileOutputStream fileOut = new FileOutputStream("C:\\TestData\\POI\\poi-test.xls");
        wb.write(fileOut);
        fileOut.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    System.out.println("Out of method three!");

}

public void methodFour() {

    System.out.println("Into method four!");
    File file = new File("C:\\TestData\\POI\\poi-test.xls");
    // file.deleteOnExit();
    System.out.println("Out of method four!");

}

public static void main(final String[] args) {

    PoiWriteExcelFile myObj = new PoiWriteExcelFile();
    myObj.methodOne();
    myObj.methodTwo();
    myObj.methodThree();
    myObj.methodFour();

}

}