java - appending excel values

Question

right now whenever i call this function the value in B3 cell of excel file workbook.xls is updated. i need to change this function such that whenever i call this function , new excel file should not be created but cell values should be appended in the order A1 on first call , A2 on 2nd call, A3 on 3rd call and so on . Can you help me with this.

private static void readFromFile(String filename) {
     // TODO Auto-generated method stub
     BufferedReader bufferedReader = null;

        try {

            //Construct the BufferedReader object
            bufferedReader = new BufferedReader(new FileReader(filename));

            String line = null;

            while ((line = bufferedReader.readLine()) != null) {
                //Process the data, here we just print it out

                HSSFWorkbook wb = new HSSFWorkbook();
                HSSFSheet sheet = wb.createSheet("new sheet");
                HSSFRow row = sheet.createRow(2);
                int s_row=1;
                row.createCell(s_row).setCellValue(line);
                s_row++;
            //   row.createCell(1).setCellValue(new Date());
                FileOutputStream fileOut = new FileOutputStream("c:\\workbook.xls");
                wb.write(fileOut);
                fileOut.close();



               // System.out.println(line);
            }

        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            //Close the BufferedReader
            try {
                if (bufferedReader != null)
                    bufferedReader.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }

Answer 1

Hi,
I see that each time you call the function you are creating a new file. Instead, you need to open the existing file, get the sheet that you needs to modify and then modify the values. Bellow is the code for opening the file and getting the first sheet:

try {
  inputStream = new FileInputStream(InputFile);
 }catch (FileNotFoundException e) {
  System.out.println ("File not found in the specified path.");
  e.printStackTrace ();
 }

 try {
  fileSystem = new POIFSFileSystem (inputStream);
 } catch (IOException e1) {
  System.out.println("Cannot create POIFSFileSystem object using the file specified!");
  e1.printStackTrace();
 }  

 try {
  workBook = new HSSFWorkbook(fileSystem);
 } catch (IOException e) {
  System.out.println("Cannot create HHSFWorkbook from POIFSFileSystem object");
  e.printStackTrace();
 }

 HSSFSheet sheet = workBook.getSheetAt(0);

After this you will have to modify the cells you require and save the file.

Answer 2

Please see below for fully functioning (if a bit clumsy) implementation of what you request above. In particular, please note: (1) while loop only creates and populates cells; (2) initialization of HSSFWorkbook, Sheet, Row, and s_row occur outside the while loop; (3) file is written to disk once, at the end; (4) cells begin with index 0 instead of index 1; (5) you need to specify CreateRow(0) to get the first row in the spreadsheet; and (6) I used Sheet and Row instead...your mileage may vary! :)

Happy coding:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;

public class SpreadSheet {

    /**
     * Reads text from a file line by line
     */
    public void readFromFile(String filename) {

        BufferedReader bufferedReader = null;
        HSSFWorkbook wb;
        Sheet sheet;
        Row row;
        Cell aCell;
        int s_row;
        FileOutputStream fileOut = null;

        try {

            //Construct the BufferedReader object
            bufferedReader = new BufferedReader(new FileReader(filename));

            String line = null;

            s_row = 0;
            wb = new HSSFWorkbook();
            sheet = wb.createSheet("new sheet");
            row = sheet.createRow(0);
            fileOut = new FileOutputStream("workbook.xls");

            while ((line = bufferedReader.readLine()) != null) 
            {        
                    aCell = row.createCell(s_row++);
                    aCell.setCellValue(line);
            }

            // Do this once
            wb.write(fileOut);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        } finally {
            //Close the BufferedReader
            try {
                if (bufferedReader != null)
                    bufferedReader.close();
                if (fileOut != null)
                    fileOut.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        new SpreadSheet().readFromFile("testinput.txt");
    }
}

My testinput.txt file contained the following data:

1
2
3
4
5
6
7

After running the above Java code, the Microsoft Excel worksheet contained the data in the file in cells A1 through G1.