CODEWITHSUNDEEP
javaapache-poi
Naveen
Naveen

Reputation: 3

Write excel data in end of column in the Excel Sheet

I want to add value in the end of the column and but if i run the class it overrides the excel

JAVA , Apache POI:

    // Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();
    // Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("MyPolicies");
    // Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;
    for (Integer key : keyset) {
        // this creates a new row in the sheet
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {

            // this line creates a cell in the next column of that row
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) {
                cell.setCellValue((String) obj);
            }

            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }
    try {
        // this Writes the workbook
        FileOutputStream out = new FileOutputStream(new File("extract.xlsx"));
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

The Excel overrides every time and new data alone is added. Note : If both the ID's are same , we need to add the second map data in the same row

Upvotes: 0

Views: 235

Answers (1)

Docteur
Docteur

Reputation: 1275

The reason why your document is replaced every time is that your workbook is blank. workbook.write(out) means you write everything in the workbook to a location (extract.xlsx), not that you write the data of your workbook into a file!

What you should be doing is open the workbook you're trying to edit:

FileInputStream xlsFile = new FileInputStream("extract.xls");
XSSFWorkbook workbook = new XSSFWorkbook(xlsFile);

... get the last row or column

XSSFSheet sheet = workbook.getSheetAt(0);
int lastRow = sheet.getLastRowNum();

... and write your data from there. Then, as before, you can use workbook.write(out) to save your workbook.

Upvotes: 2

Related Questions