Java - Write excel only last data

202 views Asked by At
    int temp = 7;
    File folder = new File("//Users//" + usr + "//Desktop//TNA//input1//");
    File[] listOfFiles = folder.listFiles();
    if (listOfFiles != null) {
        for (int i = 0; i < listOfFiles.length; i++) {

            if (listOfFiles[i].isFile() && (listOfFiles[i].getName()).endsWith(".pdf")) {
                System.out.println(listOfFiles[i].getName());
                String fileName = "//Users//" + usr + "//Desktop//TNA//input1//" + listOfFiles[i].getName();
                try {
                    PdfReader reader = new PdfReader("//Users//gmuniandy//Desktop//TNA//input1//" + listOfFiles[i].getName());
                    PdfStamper stamper = new PdfStamper(reader, new FileOutputStream(RESULT2));
                    AcroFields form = stamper.getAcroFields();
                    String name = form.getField("Text1");//Check Box 1 
                    System.out.println(name);
                    stamper.close();
                    reader.close();
                    FileInputStream file = new FileInputStream(new File("//Users//"+ usr +"//Desktop//TNA//input//FR-OPS-030 Master Training Plan_Rev4.xls"));
                    HSSFWorkbook workbook = new HSSFWorkbook(file);
                    HSSFSheet sheet = workbook.getSheet("Sheet1");//    getSheetAt(0);
                    HSSFRow row = sheet.createRow((short) 0);
                    HSSFCellStyle style = workbook.createCellStyle();
                    style.setFillForegroundColor(HSSFColor.DARK_BLUE.index);
                    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                    HSSFRow row1 = sheet.createRow(temp);
                    HSSFCell name_c1 = row1.createCell(0);
                    name_c1.setCellValue(name);
                    name_c1.setCellStyle(style);
                    file.close();

                    FileOutputStream outFile =new FileOutputStream(new File("//Users//"+ usr +"//Desktop//TNA//output//FR-OPS-030 Master Training Plan_Rev41w.xls"));
                    workbook.write(outFile);
                    outFile.close();
                    temp++;
                } catch (Exception ex) {
                }

            }
        }
    }

I retrieve value from PDF and write in Excel. I managed to write in the Excel but only last data updated in in the Excel. Please advice where I did wrong.

EDIT

if (listOfFiles[i].isFile() && (listOfFiles[i].getName()).endsWith(".pdf")) {
                    System.out.println(listOfFiles[i].getName());
                    String fileName = "//Users//" + usr + "//Desktop//TNA//input1//" + listOfFiles[i].getName();
                    FileInputStream file = new FileInputStream(new File("//Users//"+ usr +"//Desktop//TNA//input//FR-OPS-030 Master Training Plan_Rev4.xls"));
                        HSSFWorkbook workbook = new HSSFWorkbook(file);
                    try {
                        PdfReader reader = new PdfReader("//Users//gmuniandy//Desktop//TNA//input1//" + listOfFiles[i].getName());
                        PdfStamper stamper = new PdfStamper(reader, new FileOutputStream(RESULT2));
                        AcroFields form = stamper.getAcroFields();
                        String name = form.getField("Text1");//Check Box 1 
                        System.out.println(name);
                        stamper.close();
                        reader.close();

                        HSSFSheet sheet = workbook.getSheet("Sheet1");//    getSheetAt(0);
                        HSSFRow row = sheet.createRow((short) 0);
                        HSSFCellStyle style = workbook.createCellStyle();
                        style.setFillForegroundColor(HSSFColor.DARK_BLUE.index);
                        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                        HSSFRow row1 = sheet.createRow(temp);
                        HSSFCell name_c1 = row1.createCell(0);
                        name_c1.setCellValue(name);
                        name_c1.setCellStyle(style);
                        file.close();


                        temp++;
                    } catch (Exception ex) {
                    }
                    finally{
                         FileOutputStream outFile =new FileOutputStream(new File("//Users//"+ usr +"//Desktop//TNA//output//FR-OPS-030 Master Training Plan_Rev41w.xls"));
                        workbook.write(outFile);
                        outFile.close();                        
                    }

Even i tried this, but it just same.

1

There are 1 answers

0
Scary Wombat On BEST ANSWER

The input file

FileInputStream file = new FileInputStream(new File("//Users//"+ usr +"//Desktop//TNA//input//FR-OPS-030 Master Training Plan_Rev4.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file);

is a different file to the output file

FileOutputStream outFile =new FileOutputStream(new File("//Users//"+ usr +"//Desktop//TNA//output//FR-OPS-030 Master Training Plan_Rev41w.xls"));
workbook.write(outFile);

So your are always appending to the original file, not to the updated file.

Why not just open it (and close it once)?