I’m trying to read and write an XLS with Apache POI

Question

I am trying to make a class that reads a file. xls and writes another file in the same format, but my class gives the following error:

org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x3E7265746E65633C, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:144)
    at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:113)
    at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:301)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:413)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:394)
    at br.com.b2w.abertura.pi.onep.controller.ConverterArquivo.lerXLSESalvar(ConverterArquivo.java:21)
    at br.com.b2w.abertura.pi.onep.controller.TestConverterArquivo.converterXLSParaCSV(TestConverterArquivo.java:11)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:538)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:760)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:460)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:206)

---

public void lerXLSESalvar(String pathArquivoEntrada, String pathArquivoSaida) {
    try (InputStream inp = new FileInputStream(pathArquivoEntrada)) {

        HSSFWorkbook wb = new HSSFWorkbook(inp);
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(2);
        Cell cell = row.getCell(3);
        if (cell == null)
            cell = row.createCell(3);
        cell.setCellType(CellType.STRING);
        cell.setCellValue("a test");

        // Write the output to a file
        try (OutputStream fileOut = new FileOutputStream(pathArquivoSaida)) {
            wb.write(fileOut);
        }
        wb.close();
    } catch (EncryptedDocumentException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

---

@Test
public void converterXLSParaCSV() {
    ConverterArquivo converterArquivo = new ConverterArquivo();
    String pathArquivoEntrada = "C:\\Users\\wesley.costa\\Downloads\\teste\\tracking_lote_20180817.xls";
    String pathArquivoSaida = "C:\\Users\\wesley.costa\\Downloads\\teste\\tracking_lote.xls";
    converterArquivo.lerXLSESalvar(pathArquivoEntrada, pathArquivoSaida);
}

Answer 1

HSSF - is normally used for Windows Excel 97-2003 type files;

XSSF - is normally used for Windows Excel 2007 files - current.

reference: apache poi reading and writing tutorial using XSSF

In the code below is stored in a variable the position of the last line and the position of the last cell of that line, after the storage of the positions is created a line and a cell based on the stored positions, and then a data is inserted:

    private static int lastRow = 0;
    private static int lastCell = 0;

    public static void main(String[] args) throws IOException {
    final String FILE = "/home/ayowole/Desktop/test.xls";

    Workbook workbook = new HSSFWorkbook(new FileInputStream(new File(FILE)));

    Sheet sheet = workbook.getSheetAt(0);
    lastRow = sheet.getLastRowNum();

    sheet.forEach((row) -> {
        lastCell = row.getLastCellNum();

        row.forEach((cell) -> {
            System.out.println(cell.getStringCellValue()); 
        });
    });

    Cell cell = sheet.createRow(lastRow + 2)
            .createCell(lastCell++);
    cell.setCellType(CellType.STRING);
    cell.setCellValue("b5");

    workbook.write(new FileOutputStream(new File(FILE)));

    workbook.close();
}