Apache poi + Java: Write to a worksheet without deleting existing data

Question

I need to check if the worksheet exists. If it exists, you must type in the next existing row and do not create a new worksheet.

You are currently deleting the current spreadsheet and I always get only 1 line written in the spreadsheet.

How do I solve this?

public class ApachePOIExcelWrite {
    private static final String FILE_NAME = "c:/viagem.xlsx";

    XSSFWorkbook workbook = new XSSFWorkbook();

    //name Sheet
    XSSFSheet sheet = workbook.createSheet("Viagem");

    Object[][] datatypes = {

        //head colums
        {
            "Destino",
            "Valor por pessoa",
            "Aeroporto",
            "Hotel",
            "data Pesquisa",
            "Hora Pesquisa"
        },


        {
            destino,
            pega_valor,
            aeroporto.replace("GRU", "Guarulhos").replace("CGH", "Congonhas"),
            hotel.replaceAll("Pontos", "Estrelas"),
            data_da_pesquisa.format(d),
            hora_da_pesquisa.format(d)

        }
    };

    int rowNum = 0;

    System.out.println("Creating excel");

    for (Object[] datatype: datatypes) {
        Row row = sheet.createRow(rowNum++);
        int colNum = 0;
        for (Object field: datatype) {
            Cell cell = row.createCell(colNum++);
            if (field instanceof String) {
                cell.setCellValue((String) field);
            } else if (field instanceof Integer) {
                cell.setCellValue((Integer) field);
            }
        }
    }

    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

}

Ivan Huang Ivan Huang · Accepted Answer · 2018-03-21T03:51:01

/**
 * Opens an existing sheet or creates a new one if the given sheet name doesn't exist. 
 * Appends values after the last existing row.
 */
public static void main(String[] args) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\test.xlsx");
    Sheet sheet = workbook.getSheet("Sheet1");
    if (sheet == null)
        sheet = workbook.createSheet("Sheet1");

    Object[][] values = {{"A2", "B2", "C2"}, {"A3","B3","C3","D3"}};
    int initRow = sheet.getLastRowNum() + 1;
    int initCol = 0;
    for (int i = 0; i < values.length; i++) {
        Object[] rowValues = values[i];
        for (int j = 0; j < rowValues.length; j++) {
            Object value = rowValues[j];
            writeValueToCell(value, initRow + i, initCol + j, sheet);
        }
    }

    try {
        FileOutputStream fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\output.xlsx");
        workbook.write(fos);
        fos.close();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

public static void writeValueToCell(Object value, int rowIdx, int colIdx, Sheet sheet) {
    Row row = sheet.getRow(rowIdx);
    Cell cell;
    if (row == null) {
        cell = sheet.createRow(rowIdx).createCell(colIdx);
    } else {
        cell = row.getCell(colIdx);
        if (cell == null)
            cell = row.createCell(colIdx);
    }

    if (value == null)
        cell.setCellType(Cell.CELL_TYPE_BLANK);
    else if (value instanceof String)
        cell.setCellValue(value.toString());
    else if (value instanceof Integer)
        cell.setCellValue((Integer) value);
    else if (value instanceof Double)
        cell.setCellValue((Double) value);
    else if (value instanceof Date) {
        cell.setCellValue((Date) value);
        CellStyle style = sheet.getWorkbook().createCellStyle();
        style.setDataFormat(sheet.getWorkbook().getCreationHelper().createDataFormat().getFormat(("yyyy/m/d")));
        cell.setCellStyle(style);
    } else {
        cell.setCellValue("Unknown type");
    }
}

Apache poi + Java: Write to a worksheet without deleting existing data

1 Answers