Edit an already created XLS file

Question

I need to edit an XLS file already created, but I am only able to create a new file and delete the old one, so far it is working but I wonder if it is possible to edit the old file and not have to delete the old one and create a new one with the same name.

    String autEdit = request.getParameter("aut");
    response.setContentType("text/html;charset=UTF-8");
    File file = new File("C:\\Tratados\\4-600dpi.xls");
    Workbook workbook = null;
    try {
        workbook = Workbook.getWorkbook(new File("C:\\teste.xls"));
    } catch (BiffException ex) {
        Logger.getLogger(autorizacao.class.getName()).log(Level.SEVERE, null, ex);
    }
    WritableWorkbook copy = Workbook.createWorkbook(new File("C:\\Teste.xls"), workbook);

    Sheet sheet = workbook.getSheet(0);
    int linhas = sheet.getRows();
    int i = 0, x = 0;
    WritableSheet sheet2 = copy.getSheet(0);
    for (i = x; i < linhas; i++) {
        Cell a1 = sheet.getCell(0, i);
        WritableCell a2 = sheet2.getWritableCell(1, i);
        WritableCell a3 = sheet2.getWritableCell(2, i);

        String as1 = a1.getContents();
        String as2 = a2.getContents();
        String as3 = a3.getContents();

        if ("-".equals(as2)){
            Label l = (Label) a2;
            l.setString(autEdit);
            Label ll = (Label) a3;
            ll.setString("Editado");
            linhas = i - 1;

        }
    }
    copy.write();
    file.delete();
    try {
        copy.close();
    } catch (WriteException ex) {
        Logger.getLogger(autorizacao.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook.close();

Answer 1

I managed to edit a worksheet already created:

try {
        HSSFWorkbook workbook;
        try (FileInputStream file = new FileInputStream(new File("C:\\teste.xls"))) {
            workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {

                Row row = sheet.getRow(i);
                org.apache.poi.ss.usermodel.Cell a1 = row.getCell(1);
                org.apache.poi.ss.usermodel.Cell a2 = row.getCell(2);

                String as1 = a1.getStringCellValue();

                if (as1.equals("-")) {
                    a1.setCellValue("teste");
                    a2.setCellValue("EDITADO");
                    i = sheet.getPhysicalNumberOfRows();
                }
            }
            file.close();

            FileOutputStream outFile = new FileOutputStream(new File("C:\\teste.xls"));
            workbook.write(outFile);
            outFile.close();
            System.out.println("Arquivo Excel editado com sucesso!");
        }
    } catch (FileNotFoundException e) {
        System.out.println("Arquivo Excel não encontrado!");
    } catch (IOException e) {
        System.out.println("Erro na edição do arquivo!");
    }

Answer 2

Using the Apache Poi library you can do one of the following:

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(caminho));

OR

 Workbook wb = WorkbookFactory.create(new File(caminho));

Where caminho is a string with the path to your physical file.
Once you have the Workbook you can manipulate the worksheets, cells and other attributes as you wish, the way you are already doing in the presented code snippet.

This reference has a link to a java class that exemplifies reading an existing file in the "Reading or modifying an existing file": http://poi.apache.org/spreadsheet/how-to.html#user_api

Answer 3

That’ll fix what you need

    XSSFWorkbook workbook = null;
File file = new File("C:\\Teste.xls");
FileOutputStream out = null;
XSSFSheet excelSheet = null;
CellStyle style = null;
XSSFFont font = null;
Map<String, Object[]> excelData = new TreeMap<String, Object[]>();
// verifica se o arquivo existe
if (file.exists()) {
    FileInputStream inputStream = new FileInputStream(file);
    workbook = new XSSFWorkbook(inputStream);
    // vefirifica se planilha existe
    if (workbook.getSheet(excelSheetName) != null) {
        excelSheet = workbook.getSheet(excelSheetName);
    } else {
        excelSheet = workbook.createSheet(excelSheetName);
        addIntoCell(excelData, excelSheet, "0", style);
    }
} else {
    workbook = new XSSFWorkbook();
    if (workbook.getSheet(excelSheetName) != null) {
        excelSheet = workbook.getSheet(excelSheetName);
    } else {
        excelSheet = workbook.createSheet(excelSheetName);
       // vefirifica se planilha existe
        addIntoCell(excelData, excelSheet, "0", style);
    }
}

Then close the open streams