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

Question

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

Navigation

#1 by (0 votes)

0

I'm trying to make a class that le a .xls file and write 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);
}

java apache-poi

asked by anonymous 17.08.2018 / 15:06

1 answer

When I'm running JUnit the RestController does not accept JSON Rewind HTML5 video after clicking without acceleration or lag

score 0 · Accepted Answer

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

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

reference: read and write tutorial with apache poi using XSSF

In the code below the position of the last row and the position of the last cell of that row are stored in a variable, after the storage of the positions, a row and a cell are created based on the stored positions, / p>

    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();
}