Append Excel file using Apache POI

Question

I have a java program that prints 1000 integer values each I run it. I want to copy the output to an excel file each time I run the program. I want to output the first run in the first column in excel file and then copy the next runs in the subsequent columns in the same excel file. For example:

Run: 1 value1 value2 . . value1000 Run:2 value1 value2 . . value1000 I want the first output in the first column of an excel file and the second output in the second column

Here is my code:

  int rownum=1;
  int cellnum=1;
  File file; 
  HSSFWorkbook workbook;
  HSSFSheet sheet;
  HSSFRow row;
  HSSFCell cell;

  HSSFWorkbook workbook = new HSSFWorkbook();
  HSSFSheet sheet = workbook.createSheet("Sample sheet");

  public void writeOutput(double meandispersion) {    

      String dispersion = Double.toString(meandispersion);

      HSSFRow row = sheet.createRow(rownum++);
      HSSFCell cell = row.createCell(cellnum);    
      cell.setCellValue("dispersion");

      try {
          FileOutputStream out = new FileOutputStream("newFile.xls");
          workbook.write(out);
          out.close();
        } catch (FileNotFoundException e) {
          e.printStackTrace();
        } catch (IOException e) {
          e.printStackTrace();
       }   

  }

There are a total of 1000 time steps in the main code and in each time step this method is called and the value of meandispersion is passed to it. It prints the 1000 values in 1000 rows in the first column. The problem is that when I run the program second time I want to copy the 1000 values in the second column, for third run 3rd column and so on. Currently it is not appending the values, it overwrites the entire file. Can anyone point out the problem?

Tanmay Patil · Accepted Answer · 2014-03-20 18:54:50Z

2

You have to read the existing file, append your new output to the existing data yourself (in correct column and then write it to the file.

Right now you are not reading the file at all, which would overwrite the existing contents.

Hope this helps.

answered Mar 20, 2014 at 18:54

Tanmay Patil

7,0752 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fvu · Accepted Answer · 2014-03-20 19:01:58Z

2

POI's Quick Guide aka the "Busy Developers' Guide to HSSF and XSSF Features" contains lots of code snippets, including one that talks about opening an existing workbook, and reading and writing and saving the modified workbook (example verbatim copied from that page, added some comments):

InputStream inp = new FileInputStream("workbook.xls");
// notice how the Workbook must be constructed from the existing file
Workbook wb = WorkbookFactory.create(inp);
// Navigating in POI always follows the same logic:
// 1. grab a sheet
// 2. grab a row from that sheet
// 3. grab a cell from that row
Sheet sheet = wb.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(3);
// a condition like the one that follows will be needed to know in what column
// you have to write your data:   
if (cell == null)
    cell = row.createCell(3);
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue("a test");

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

That, and the other examples on that page should get you up to speed quickly.

answered Mar 20, 2014 at 19:01

fvu

33.1k6 gold badges64 silver badges84 bronze badges

6 Comments

user3367327 Over a year ago

Hey thanks for your help but the problem is that whenever I enter the line InputStream inp = new FileInputStream("workbook.xls"); I get an error that resource leak, input is never closed.

fvu Over a year ago

No idea what tool you are using that gives that error but in fact the above sample code does not close the Inputstream explicitely. This should however not be difficult to fix, add inp.close(); and according to the documentation also wb.dispose(); (to dispose of temporary files backing this workbook on disk) after fileOut.close();.

user3367327 Over a year ago

I am using eclipse in Mac.It always give me the same error that resource leak, input is never closed. After that it does not allow me to use inp anywhere else. I cannot use Workbook wb = WorkbookFactory.create(inp);

fvu Over a year ago

Have you added the inp.close(); line I suggested? I'm still using Eclipse Indigo who doesn't have that leak detection feature, but according to the documentation that's a warning in its default configuration and not a fatal error...

user3367327 Over a year ago

I tried everything but I cannot fix it. I dont know what the problem is? Can you send me the above code with all the import library functions. Maybe there is some problem in the libraries that I am using

|

Collectives™ on Stack Overflow

Append Excel file using Apache POI

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related