java - 使用 Apache POI 在 Java 中导出到 Excel

Question

我使用 Apache POI 以 Excel 格式导出数据。导入进行得很顺利，但是当我尝试打开文档时，我收到此消息“Excel 在文档“toto.xls”中遇到了无法读取的内容。您想要恢复此工作簿的内容吗？如果此工作簿的源是否可靠，请单击"is"。”

下面是导出excel的java代码

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

Answer 1

您的问题是这两行不匹配

XSSFWorkbook workbook = new XSSFWorkbook();

和

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

如果您确实想要生成 .xls 旧式 Excel 工作簿，则需要将第一行更改为 HSSFWorkbook 而不是 XSSFWorkbook

如果您确实要生成 .xlsx Excel 工作簿，则第二行的内容类型需要是正确的 .xlsx 类型，即:

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