B4J Question [Apache POI] Example to Update an Excel file - Can the XLUtils library edit the file?

[rtek1000]

XLUtils / jPOI 5 - Read and write MS Excel workbooks

As written here, I plan to make it easier to read and write Excel workbooks. The solution is based on three components: - Apache POI - https://poi.apache.org/ Large open source project that provides APIs for Microsoft documents. Note that the files are accessed directly, it doesn't depend on the...

www.b4x.com

This Java Excel tutorial shows you how to update an existing Microsoft Excel file using the Apache POI library. Here are the steps for updating an Excel file:

• Read the Excel file to an InputStreamand get the Workbook from this stream.
• Update new data to an existing Sheet or create a new Sheet.
• Close the InputStream.
• Write the workbook to an OutputStream. This will overwrite the existing file with updated data.

Suppose that we have an Excel 2003 file (JavaBooks.xls) looks like this:

package net.codejava.excel;
 
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
 
/**
 * This program illustrates how to update an existing Microsoft Excel document.
 * Append new rows to an existing sheet.
 *
 * @author www.codejava.net
 *
 */
public class ExcelFileUpdateExample1 {
 
 
    public static void main(String[] args) {
        String excelFilePath = "JavaBooks.xls";
        
        try {
            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            Workbook workbook = WorkbookFactory.create(inputStream);
 
            Sheet sheet = workbook.getSheetAt(0);
 
            Object[][] bookData = {
                    {"The Passionate Programmer", "Chad Fowler", 16},
                    {"Software Craftmanship", "Pete McBreen", 26},
                    {"The Art of Agile Development", "James Shore", 32},
                    {"Continuous Delivery", "Jez Humble", 41},
            };
 
            int rowCount = sheet.getLastRowNum();
 
            for (Object[] aBook : bookData) {
                Row row = sheet.createRow(++rowCount);
 
                int columnCount = 0;
                
                Cell cell = row.createCell(columnCount);
                cell.setCellValue(rowCount);
                
                for (Object field : aBook) {
                    cell = row.createCell(++columnCount);
                    if (field instanceof String) {
                        cell.setCellValue((String) field);
                    } else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    }
                }
 
            }
 
            inputStream.close();
 
            FileOutputStream outputStream = new FileOutputStream("JavaBooks.xls");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
            
        } catch (IOException | EncryptedDocumentException
                | InvalidFormatException ex) {
            ex.printStackTrace();
        }
    }
 
}

Source: https://www.codejava.net/coding/java-example-to-update-existing-excel-files-using-apache-poi


Can the XLUtils library edit the file?

 

[Erel]

No reason to post Java code in each of your threads. It doesn't help in any way.

Can the XLUtils library edit the file?

Yes. XLUtils can read existing workbooks and modify them.