Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 15 of 15

Thread: Trouble appending data to Excel

  1. #1
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default 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.


  2. #2
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    Are you talking about appending data to an existing file? Did you look at the API doc for the FileOutputStream class?
    If you don't understand my answer, don't ignore it, ask a question.

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    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?

  4. #4
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    I have no idea why setting the output file's constructor to append mode would cause the program to hang.
    If you don't understand my answer, don't ignore it, ask a question.

  5. #5
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    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.

  6. #6
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    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?
    If you don't understand my answer, don't ignore it, ask a question.

  7. #7
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    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.

  8. #8
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    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?

    right to a new row everytime the program is complied.
    That does not make sense. The program does not execute when it is compiled.
    If you don't understand my answer, don't ignore it, ask a question.

  9. #9
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    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.

  10. #10
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    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.
    If you don't understand my answer, don't ignore it, ask a question.

  11. #11
    Member
    Join Date
    Jan 2012
    Location
    Hellas
    Posts
    284
    Thanks
    11
    Thanked 59 Times in 57 Posts

    Default Re: Trouble appending data to Excel

    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.

  12. #12
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    Quote Originally Posted by Norm View Post
    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?

  13. #13
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    it throws an exceptions.
    You need to post the full text of the error messages.

    Am headed in the right path at least?
    I have no idea how to update an excel file.
    If you don't understand my answer, don't ignore it, ask a question.

  14. The Following User Says Thank You to Norm For This Useful Post:

    loui345 (November 26th, 2012)

  15. #14
    Member
    Join Date
    Jun 2012
    Posts
    105
    Thanks
    7
    Thanked 1 Time in 1 Post

    Default Re: Trouble appending data to Excel

    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.

  16. #15
    Super Moderator Norm's Avatar
    Join Date
    May 2010
    Location
    Eastern Florida
    Posts
    21,130
    Thanks
    52
    Thanked 2,286 Times in 2,257 Posts

    Default Re: Trouble appending data to Excel

    You're very welcome.
    If you don't understand my answer, don't ignore it, ask a question.

Similar Threads

  1. Importing excel data
    By supriya ramjee in forum File I/O & Other I/O Streams
    Replies: 11
    Last Post: October 20th, 2012, 09:39 AM
  2. [SOLVED] Merge two 1D array's into a new 1D array. Trouble appending last index
    By norske_lab in forum What's Wrong With My Code?
    Replies: 1
    Last Post: August 30th, 2012, 12:57 PM
  3. Replies: 3
    Last Post: September 1st, 2011, 10:49 AM
  4. Appending to excel using POI
    By krishnamohan in forum Java Theory & Questions
    Replies: 8
    Last Post: August 19th, 2010, 05:40 AM
  5. appending from shell script to excel sheet
    By krishnamohan in forum Java Theory & Questions
    Replies: 1
    Last Post: August 16th, 2010, 10:01 AM