Append new row in excel file via java apache POI

Question

I have an existing xlsx file, on row 1 I have my headlines, what I need to is add new row at 2 and of course shift everything else down, and then add my data to this row. Can you please help me with this?

static void Append() throws EncryptedDocumentException, InvalidFormatException, IOException
{
    InputStream inp = new FileInputStream("C:/workbook.xlsx");

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);

    Row row = sheet.createRow((short) 2);
    sheet.shiftRows(1, sheet.getLastRowNum()+1, 1, true,true);
    row.createCell(0).setCellValue("A");
    row.createCell(1).setCellValue("B");
    row.createCell(2).setCellValue("This is a string");
    row.createCell(3).setCellValue(true);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("C:/workbook.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

Nicolas Filotto · Accepted Answer · 2016-06-20 10:27:01Z

3

Indeed the method shiftRows is a good candidate for this need, you simply need to better set the parameters, like this:

...
// Shift of one row all the rows starting from the 3th row
sheet.shiftRows(2, sheet.getLastRowNum(), 1, true, true);
// Create my new 3th row
Row row = sheet.createRow(2);
...

edited Jun 20, 2016 at 10:27

answered Jun 20, 2016 at 8:41

Nicolas Filotto

45.2k11 gold badges97 silver badges129 bronze badges

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

4 Comments

Dim Over a year ago

Exception in thread "main" java.lang.IllegalArgumentException: firstMovedIndex, lastMovedIndex out of order at org.apache.poi.ss.formula.FormulaShifter.<init>(FormulaShifter.java:81) at org.apache.poi.ss.formula.FormulaShifter.createForRowShift(FormulaShifter.java:126) at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2970) at MainClass.Append(MainClass.java:67) at MainClass.main(MainClass.java:21)

Nicolas Filotto Over a year ago

this works with a simple xlsx file, maybe you should provide more info about your file

Nicolas Filotto Over a year ago

you should also mention clearly in your question, the problem met

Dim Over a year ago

sheet.shiftRows(1, sheet.getLastRowNum(), 1, true,true); Row row = sheet.createRow(1);

Collectives™ on Stack Overflow

Append new row in excel file via java apache POI

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related