Excel file doesn't change after adding new rows

Question

I'm starting to do a new process to add rows from an excel file to another excel file, in other words: append rows to an existing excel sheet.

To make that, I was testing the following code, based in a code that I found on Internet:

Private global declarations:

private HSSFRow row;
private HSSFCell cell;
private FileInputStream inFile;
private POIFSFileSystem poiFile;
private HSSFSheet excelSheet = null;
private HSSFWorkbook excelBook = null;
private FileOutputStream outFile = null;
private static String outputFileName;

I start everything with the main method:

public static void main(String args[]){
    outputFileName = "C:\\test1.xls";
    List<String> newContent = new ArrayList<>();
    newContent.add("Test Val1");
    newContent.add("Test Val2");
    newContent.add("Test Val3");
    testingClass test = new testingClass();
    test.overwriteExcel(outputFileName,newContent);
}

With this I created the file, and with the same I pretend update it:

public void overwriteExcel(String outputFileName, List<String> newContent){
    try{
    if(new File(outputFileName).exists()){  //--If the file exists/is already created
            outFile = new FileOutputStream(new File(outputFileName),true);  /*With the "true" parameter, the change must been applied but, nothing happens...*/
            inFile = new FileInputStream(new File(outputFileName));
            poiFile = new POIFSFileSystem(inFile);
            excelBook = new HSSFWorkbook(poiFile);
            excelSheet = excelBook.getSheetAt(0);
        }else{ //--Create the file
            outFile = new FileOutputStream(new File(outputFileName));
            excelBook = new HSSFWorkbook();
            excelSheet = excelBook.createSheet("Sheet1");
        }
        int i = 0;
        for(Iterator iter = newContent.iterator(); iter.hasNext();){
            Object val = iter.next();
            if(val!=null){
                row = excelSheet.createRow(excelSheet.getLastRowNum()+1);
                cell = row.createCell(0);  //--Temporaly, I change this val manually
                cell.setCelval(newContent.get(i++));
            }
        }
        excelBook.write(outFile);
        outFile.flush();
        outFile.close();
    }catch(Exception ex){
        JOptionPane.showMessageDialog(null,"Error: "+ex.getMessage(),"Warning!",JOptionPane.ERROR_MESSAGE);
    }
}

When the class ends his process, I saw the file and I can perceive the file size change, but when I open it, everything is the same, after I close the file, this returns to his previous size...

Trying to understand what happens, I changed the "0" by "1" in row.createCell(0), I delete the file and repeat the process, everything right, but after when I change the value again to "0", changes hasn't been applied... I don´t know if it's some file permissions...

I think there's something wrong with the code, this isn't the first time that I work with excel files, but it's the first time that I'm trying to modified it.

In advance, thanks!!!

StoopidDonut · Accepted Answer · 2013-12-28 21:20:23Z

I had struggled with the same situation and as strange as it is, you don't have to open your outputstream in an append mode:

In short; change

outFile = new FileOutputStream(new File(outputFileName),true);

To

outFile = new FileOutputStream(new File(outputFileName));

The reason is that the write method tends to write the complete workbook to the outputstream, and opening in an append mode would mean 'appending' the existing workbook with the a new workbook thus created and it does not happen (which seems to 'fail' without exception of sorts).

Instead output stream should open in the 'new' mode (without the boolean true) so that there is a single latest-and-the-greatest workbook which is written as a whole to the stream.

Hope it made sense! :)

I have made a few changes to your code aligned to my understanding of the version of POI below and this works perfectly fine:

private FileInputStream inFile;
private POIFSFileSystem poiFile;
private XSSFRow row;
private XSSFCell cell;
private XSSFSheet excelSheet = null;
private XSSFWorkbook excelBook = null;

private static String outputFileName;

public void overwriteExcel(File file, List<String> newContent) {
    try {
        if (file.exists()) {
            inFile = new FileInputStream(file);
            // poiFile = new POIFSFileSystem(inFile);
            excelBook = new XSSFWorkbook(inFile);
            excelSheet = excelBook.getSheetAt(0);
            System.out.println("Here -> " + excelSheet.getLastRowNum());
        } else { // --Create the file
            excelBook = new XSSFWorkbook();
            excelSheet = excelBook.createSheet("Sheet1");
        }
        int i = 0;
        for (String val : newContent.toArray(new String[newContent.size()])) {
            if (val != null) {
                System.out.println("Row " + excelSheet.getLastRowNum());
                row = excelSheet.createRow(excelSheet.getLastRowNum() + 1);
                cell = row.createCell(0);
                cell.setCellValue(newContent.get(i++));

            }
        }
        FileOutputStream outFile = new FileOutputStream(file);
        excelBook.write(outFile);
        // outFile.flush();
        outFile.close();
    } catch (Exception ex) {
        /*
         * JOptionPane.showMessageDialog(null, "Error: " + ex.getMessage(),
         * "Warning!", JOptionPane.ERROR_MESSAGE);
         */
        ex.printStackTrace();
    }
}

public static void main(String args[]) {
    outputFileName = "demo4.xlsx";
    List<String> newContent = new ArrayList<>();
    newContent.add("Test Val1");
    newContent.add("Test Val2");
    newContent.add("Test Val3");
    WriteExcel test = new WriteExcel();
    test.overwriteExcel(new File(outputFileName), newContent);
}

It has a lot of sense, I will probe it and later I'll tell you how it works! Thanks a lot!!!

Collectives™ on Stack Overflow

Excel file doesn't change after adding new rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related