Writing a string to .xlsm file

Question

wef : write to xlsm (Excel 2007) using apache poi

When i write a simple string to the file, I am not able to open the file. Error - "Excel cannot open the file 'Test1.xlsm' because the file format or file extension is not valid"

try {

    Workbook workbook;
    workbook = new XSSFWorkbook(OPCPackage.open("C:\\temp\\Test.xlsm"));

    String content = "This is the text content";
    byte[] contentInBytes = content.getBytes();

    FileOutputStream out = new FileOutputStream("C:\\temp\\Test1.xlsm");
    out.write(contentInBytes);

    workbook.write(out);
    out.close(); 

    System.out.println("xlsm created successfully..");
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (InvalidFormatException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

What do you think `out.write()` does and why do you think so? I mean, you're writing non-Excel bytes to the beginning of the file, and you then expect the file to be readable by Excel? Why would you expect that? Where in the Excel document did you expect that text to go? Cell A1? If so, why would you expect that, when you haven't specified that's where you want it? — Andreas, Sep 12 '16 at 16:38

user1454829 · Answer 1 · 2016-09-12T21:34:00.890

I had to use a FileInputStream to read and FileOutputStream to write. It worked like a charm !! Writing recordset to xlsm

   try {
        File file = new File("C://temp//test.xslm");
        FileInputStream inputStream = new FileInputStream(file);
        Workbook wb = new XSSFWorkbook(OPCPackage.open(inputStream));

        Sheet sheet = wb.getSheet("Sheet1");
        for (int x = 1; x < 5; x++) {
            for (int y = 0; y < 4; y++) {
                Cell c = sheet.getRow(x).getCell(y);
                c.setCellValue(recs.getValueAt(x - 1, y).toString());
            }
        }

        FileOutputStream fileOut = new FileOutputStream("C://temp//test.xslm");
        wb.write(fileOut);
        fileOut.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

Writing a string to .xlsm file

1 Answers1