Exporting to Excel in Java using Apache POI

Question

I use Apache POI to export data in excel format. The import goes well, but when I try to open the document I have this message "Excel has encountered an unreadable content in the document "toto.xls". Would you want to recover the contents of this workbook? If the source of this workbook is reliable click yes. "

Below is the java code of export excel

    public void exportExcel() throws IOException {

    // Creation workbook vide
    XSSFWorkbook workbook = new XSSFWorkbook();

    // Creation d'une feuille vierge
    XSSFSheet sheet = workbook.createSheet("Participant");
    List<toto> totoList = this.getAllToto;
    int indiceMap = 2;
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[] { "name", "surname" });

    for (TotoBean l : totoList) {
        data.put(Integer.toString(indiceMap),
                new Object[] { l.getName(), l.getSurname() });
        indiceMap++;
    }
    // Iteration sur la map data et ecriture dans dans la feuille excel
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }

    DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
    String dateToday = dateFormat.format(new Date());

    // Ecriture du fichier excel comme attachement
    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
    workbook.write(outByteStream);
    byte[] outArray = outByteStream.toByteArray();
    String fileOut = "Liste-toto[" + this.getCity() + "]"
            + dateToday + ".xlsx";
    HttpServletResponse response = (HttpServletResponse) FacesContext
            .getCurrentInstance().getExternalContext().getResponse();

    response.setContentType("application/vnd.ms-excel");
    response.setContentLength(outArray.length);
    response.setHeader("Content-Disposition", "attachment; filename=\""
            + fileOut + "\"");

    OutputStream outStream = response.getOutputStream();
    outStream.write(outArray);

    outStream.flush();
    outStream.close();
}

I think XSSFSheet creates an .xslx document, not an .xls document — keuleJ
– keuleJ, Commented Oct 13, 2014 at 9:18
Thanks for your reply but xslx is not recognize as excel document — Franky
– Franky, Commented Oct 13, 2014 at 9:26
Yes I agree with you and it is also the extension of my generated file "Liste-toto.xlsx" , but the excel application is not able to open it without previously displayed the error message contained in my post. — Franky
– Franky, Commented Oct 13, 2014 at 9:32
Does your cell.setCellValue((String) obj); probably put some un-allowed characters into the cell? I think Excel is a bit confused, when you have , or . as cell content. — user166566
– user166566, Commented Oct 13, 2014 at 9:36

Gagravarr · Accepted Answer · 2014-10-13 10:06:41Z

4

Your problem is the mis-match of these two lines

XSSFWorkbook workbook = new XSSFWorkbook();

and

response.setContentType("application/vnd.ms-excel");

If you really want to generate a .xls older-style Excel workbook, you need to change the first line to be HSSFWorkbook rather than XSSFWorkbook

If you do mean to generate a .xlsx Excel workbook, then the content type on the second line needs to be the correct .xlsx one, which would be:

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

answered Oct 13, 2014 at 10:06

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-03-20 10:04:20Z

0

You might consider opening it "As Adminstrator". According to this ( https://superuser.com/questions/401714/how-do-i-resolve-the-error-excel-found-unreadable-content-in-filename ) its a work around.

edited Mar 20, 2017 at 10:04

CommunityBot

11 silver badge

answered Oct 13, 2014 at 10:08

user166566

Collectives™ on Stack Overflow

Exporting to Excel in Java using Apache POI

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related