Thread: Trouble appending data to Excel

Hi,

I am having trouble appending data to excel i am using apache as my import.

 Workbook workbook = new HSSFWorkbook();
        Sheet sheet2 = workbook.createSheet();
      // the user inputs the RowNumber and the data is saved to that rowNumber
        Row row = sheet2.createRow(RowNumber);
        Cell cell = row.createCell(RowNumber);
       cell.setCellValue(CustomerName);
       String cellValue2 = cell.getRichStringCellValue().toString();
       System.out.println("ddd"+cellValue2);
        Cell cell2 = row.createCell(1);
        cell2.setCellValue(PriceOfItem);
        Cell cell3 =row.createCell(2);
        cell3.setCellValue(AmountSoldFor);
        Cell cell4 = row.createCell(3);
        cell4.setCellValue(USShipping);
        Cell cell5 = row.createCell(4);
        cell5.setCellValue(JapanShipping);
        String cellValue1 = cell.getRichStringCellValue().toString();
        System.out.print(cellValue1);
 
 
         try{
 
        FileOutputStream output = new FileOutputStream("SaoriBusiness.xls");
 
        workbook.write(output);
 
         output.close();
        }
        catch(Exception e){
            System.out.print("we have a problem");
        }
        }
 
 
 
 
 
 
 
 
 
    }

The code works as desired, but it deletes my existing data. I thought this would be a simple solution, but after looking through the internet I am beginning to think that apache doesn't have a method to append data. This is frustrating, and can someone please confirm my suspicion that apache can or can't append existing data. If it does, can you help figure out to get it to append my data.

Yes, I am talking about appending data to an existing file. See my wife is having a business we export a lot of stuff to Japan. I thought it would be great opportunity to create a java program, which will actually be of particular use. Everything was going find until this problem of appending data.

I am familiar with constructor and setting the boolean value to true, which would write data at the EOF, but i thought that was applicable to text files. I am using excel, but when in doubt try right?

--- Update ---

i set the FileOutPutStream constructor to true the program just hangs and does not work. Any suggestions?

it's very odd, in the Java console box I am able to type in data. I am not using any scanners, so in my opinion the program is hanging. If you have the time, can test my code. I have been working on this for quite some time.

I am using netbeans and when I set the Boolean to true the program hangs. The program does not fully compile, and when I investigate it you can actually type in the console. Keep in mind that I am not using any type of scanner. When I remove the Boolean, the program works fine in the sense that writes to excel but it deletes my preexisting data.

I just want to keep my data and right to a new row everytime the program is complied.

There are no compiler errors. It's a logical error with my code. What I mean is by "writing to a new row when the program is complied" is that if there is existing data it will write the data entries into a new row. What my program is doing correctly writing to a new row, but it is not saving the preexisting data. When I open excel to check the data, the new row is written perfectly but preexisting data is no longer there. As you can see this is my problem. I have tried to the Boolean solution to no avail. Is there any other suggestions? I have looked around the internet for severals hours to find an example or tutorial and nothing.

Hello loui345!

I have very little experience with Apache Poi and just tried your code with the same results as yours. But this stackoverflow thread and the Apache Poi Busy Developers' Guide seem to answer your question.
Note: I haven't tested the recommended solution.

Hope this helps.

Originally Posted by Norm

Have you tried executing the program without the IDE? By using the java command in a command prompt window?


Does your program read in the existing file, append the new data to the existing data and write out a new copy of the file with the old and new data?
If it only writes out the new data to a file, then I don't see how you expect the old data to be preserved.

Nope, it does not do that. Can you help me with doing that. See, I do not have much experience with this.

--- Update ---

I have looked at both those for a solution and I was unsuccessful. I will give it another look though.

--- Update ---

Hey Norm,

So here is my attempt:

 
          try{
           FileInputStream inp = new FileInputStream("SaoriBusiness.xls");
           Workbook workbook = new HSSFWorkbook(inp);
             FileOutputStream output = new FileOutputStream("SaoriBusiness2.xls");
        Sheet sheet2 = workbook.createSheet();
        Row row = sheet2.createRow(1);
        Cell cell = row.createCell(1);
        cell.setCellType(100);
        workbook.write(output);
 
         output.close();
          }
          catch(Exception Ex){
           System.out.print(    "sorry");
          }

Now it copies the file perfectly, but when I try to make a change it throws an exceptions. Am headed in the right path at least?

Hey Norm,

The problem has been fixed. I did not copy the data in the first place, which you successfully pointed out to me. Once you pointed that out to me everything was easy. Thanks for what you do man.