1

I am new in Java and i have problem on inserting dynamic data into my Excel file. Here is the follow code. If i delete my excel file and re-run my program. it will then create a new file and also insert the following data.(hello,goodbye,true,date).During the first run, the program still can insert the following data, but when i perform the next run, the data cannot be store to the next line. This is the following code to check for file exist. I hope i can get someone to help me as i am struggling this code for a few days.

    if (file.exists()) {
                        try{
                            fout = new FileOutputStream(fileName, true);
                            fin = new FileInputStream(fileName);
                            lPOIfs = new POIFSFileSystem(fin);
                            workbook = new HSSFWorkbook(lPOIfs);
                            worksheet = workbook.getSheet("POI Worksheet");
                            for (int i=0; i<workbook.getNumberOfSheets(); i++) {
                                System.out.println( workbook.getSheetName(i) );                                    
                            }
                            HSSFSheet sheet = workbook.getSheetAt(0);
                            last = sheet.getLastRowNum();
                        }catch (IOException e) {  
                            e.printStackTrace();  
                        }catch (NullPointerException e){
                            e.printStackTrace(); 
                        }
                    } else {
                        //create new file
                        try{
                        fout = new FileOutputStream(file);                            
                        }catch (IOException e) {  
                            e.printStackTrace();  
                        }
                        workbook = new HSSFWorkbook();                       
                        worksheet = workbook.createSheet("POI Worksheet");  
                    }

This is the code to check for the last number in line:

    if(last != 0){
          last = worksheet.getLastRowNum()+1;
    }else{
          last = worksheet.getLastRowNum();
    }

This is the full code of the write function:

    public void writeExcel(){
    String fileName = "C:\\Users\\blslyeoh\\Documents\\NetBeansProjects\\JavaApplication1\\poi-test.xls";       
    try {
                    int last=0;
                    File file = new File(fileName);
                    FileInputStream fin = null;  
                    HSSFWorkbook workbook = null;  
                    HSSFSheet worksheet = null;  
                    FileOutputStream fout = null;
                    POIFSFileSystem lPOIfs = null;
                    if (file.exists()) {
                        try{
                            fout = new FileOutputStream(fileName, true);
                            fin = new FileInputStream(fileName);
                            lPOIfs = new POIFSFileSystem(fin);
                            workbook = new HSSFWorkbook(lPOIfs);
                            worksheet = workbook.getSheet("POI Worksheet");
                            for (int i=0; i<workbook.getNumberOfSheets(); i++) {
                                System.out.println( workbook.getSheetName(i) );                                    
                            }
                            HSSFSheet sheet = workbook.getSheetAt(0);
                            last = sheet.getLastRowNum();
                        }catch (IOException e) {  
                            e.printStackTrace();  
                        }catch (NullPointerException e){
                            e.printStackTrace(); 
                        }
                    } else {
                        //create new file
                        try{
                        fout = new FileOutputStream(file);                            
                        }catch (IOException e) {  
                            e.printStackTrace();  
                        }
                        workbook = new HSSFWorkbook();                       
                        worksheet = workbook.createSheet("POI Worksheet");  
                    }
        // index from 0,0... cell A1 is cell(0,0)
                    if(last != 0){
                        last = worksheet.getLastRowNum()+1;
                    }else{
                        last = worksheet.getLastRowNum();
                    }
                    HSSFRow row = worksheet.createRow(last);                        
        HSSFCell cellA1 = row.createCell((short)0);
        cellA1.setCellValue("hello");   
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
        //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //cellA1.setCellStyle(cellStyle);
        HSSFCell cellB1 = row.createCell((short) 1);
        cellB1.setCellValue("goodbye");
        //cellStyle = workbook.createCellStyle();
        //cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //cellB1.setCellStyle(cellStyle);
        HSSFCell cellC1 = row.createCell((short) 2);
        cellC1.setCellValue(true);

        HSSFCell cellD1 = row.createCell((short) 3);
        cellD1.setCellValue(new Date());
        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat
                .getBuiltinFormat("m/d/yy h:mm"));
        cellD1.setCellStyle(cellStyle);
        workbook.write(fout);
        fout.flush();

        fout.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
4
  • Are you getting any stacktraces? Any errors thrown? It would be helpful to post those. Commented Jan 15, 2015 at 1:36
  • I got it, thanks for the help for this URL codepool.biz/excel/… hope this helps you all. By the way, fout = new FileOutputStream(fileName, true); should change to fout = new FileOutputStream(fileName); Commented Jan 15, 2015 at 2:25
  • btw, thanks @jcd for the concern Commented Jan 15, 2015 at 2:27
  • Can you please edit your code as working code? I need this code Commented Mar 14, 2016 at 10:58

1 Answer 1

1
  1. Read the excel file first!
  2. If you get worksheet successfully through
    worksheet = workbook.getSheet("POI Worksheet");
    then close the file input stream.
  3. If you can't get worksheet then create one through
    workbook = new HSSFWorkbook();
    worksheet = workbook.createSheet("POI Worksheet");
  4. you've got wooksheet which is existing or new one.
    append rows and write it to a file and close the file output stream through
    fout = new FileOutputStream(file);
    workbook.write(fout);
    fout.close();

I hope this can help you. Have a nice day :-)

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

1 Comment

the 4th suggestion helps~ Thanks alot :D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.