Edit an XLS file already created

Question

Edit an XLS file already created

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

I need to edit an XLS file already created, but I'm only able to create a new file and delete the old file, so far it works, but I wonder if you can 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();

java xls

asked by anonymous 21.09.2016 / 14:29

3 answers

1

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. Home Once you have the workbook you can manipulate the spreadsheets, cells and other attributes as you wish, in the way you are already doing in the code snippet presented.

This reference has a link to a java class that exemplifies the reading of an existing file in "Reading or modifying an existing file": link

21.09.2016 / 15:17

0

this solves 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 stream

21.09.2016 / 15:25

Make item disappear according to system date Charts google returning empty php mysql

score 0 · Accepted Answer

I was able to edit a spreadsheet 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!");
    }