Java – appending excel values

java

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();
            }
        }

Best Answer

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:

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

Related Solutions

Java – What are the differences between a HashMap and a Hashtable in Java

There are several differences between HashMap and Hashtable in Java:

Hashtable is synchronized, whereas HashMap is not. This makes HashMap better for non-threaded applications, as unsynchronized Objects typically perform better than synchronized ones.
Hashtable does not allow null keys or values. HashMap allows one null key and any number of null values.
One of HashMap's subclasses is LinkedHashMap, so in the event that you'd want predictable iteration order (which is insertion order by default), you could easily swap out the HashMap for a LinkedHashMap. This wouldn't be as easy if you were using Hashtable.

Since synchronization is not an issue for you, I'd recommend HashMap. If synchronization becomes an issue, you may also look at ConcurrentHashMap.

Java – Is Java “pass-by-reference” or “pass-by-value”

Java is always pass-by-value. Unfortunately, when we deal with objects we are really dealing with object-handles called references which are passed-by-value as well. This terminology and semantics easily confuse many beginners.

It goes like this:

public static void main(String[] args) {
    Dog aDog = new Dog("Max");
    Dog oldDog = aDog;

    // we pass the object to foo
    foo(aDog);
    // aDog variable is still pointing to the "Max" dog when foo(...) returns
    aDog.getName().equals("Max"); // true
    aDog.getName().equals("Fifi"); // false
    aDog == oldDog; // true
}

public static void foo(Dog d) {
    d.getName().equals("Max"); // true
    // change d inside of foo() to point to a new Dog instance "Fifi"
    d = new Dog("Fifi");
    d.getName().equals("Fifi"); // true
}

In the example above aDog.getName() will still return "Max". The value aDog within main is not changed in the function foo with the Dog "Fifi" as the object reference is passed by value. If it were passed by reference, then the aDog.getName() in main would return "Fifi" after the call to foo.

Likewise:

public static void main(String[] args) {
    Dog aDog = new Dog("Max");
    Dog oldDog = aDog;

    foo(aDog);
    // when foo(...) returns, the name of the dog has been changed to "Fifi"
    aDog.getName().equals("Fifi"); // true
    // but it is still the same dog:
    aDog == oldDog; // true
}

public static void foo(Dog d) {
    d.getName().equals("Max"); // true
    // this changes the name of d to be "Fifi"
    d.setName("Fifi");
}

In the above example, Fifi is the dog's name after call to foo(aDog) because the object's name was set inside of foo(...). Any operations that foo performs on d are such that, for all practical purposes, they are performed on aDog, but it is not possible to change the value of the variable aDog itself.

For more information on pass by reference and pass by value, consult the following SO answer: https://stackoverflow.com/a/430958/6005228. This explains more thoroughly the semantics and history behind the two and also explains why Java and many other modern languages appear to do both in certain cases.

Best Answer

Related Solutions

Java – What are the differences between a HashMap and a Hashtable in Java

Java – Is Java “pass-by-reference” or “pass-by-value”

Related Topic