Trouble appending data to Excel

Hi,

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

Code java:

---

 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.

Are you talking about appending data to an existing file? Did you look at the API doc for the FileOutputStream class?

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?

I have no idea why setting the output file's constructor to append mode would cause the program to hang.

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.

What is a "java console box"? Are you talking about a command prompt window that is executing a java program? If so, how does the program read from the console?

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.

Quote:

---

The program does not fully compile

---

You need to fix the compiler errors BEFORE trying to execute the program.

What code are you using to read input from the user?

Quote:

---

right to a new row everytime the program is complied.

---

That does not make sense. The program does not execute when it is compiled.

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.

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.

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.

Quote:

---

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:

Code java:

---

 
          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?

Quote:

---

it throws an exceptions.

---

You need to post the full text of the error messages.

Quote:

---

Am headed in the right path at least?

---

I have no idea how to update an excel file.

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.

You're very welcome.