JAVA导出excel总结

一般导出excel分为2步，第一步为查询需要导出的数据，这个就不做阐述了，查询出一个list，然后开始导出excel：

首先，导出excel需要依赖一下jar包：

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi</artifactId>

<version>3.16</version>

</dependency>

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.16</version>

</dependency>

再把要导入的每一个title放到list里：

  public List<String> exportTitleMake(XSSFSheet sheet) {

    List<String> title = Lists.newArrayList();
    title.add("导出测试");

return title;

}

 

然后开始导出

XSSFWorkbook workbook = new XSSFWorkbook();
OutputStream outputStream = null;
try {
    workbook.createSheet();
    XSSFSheet sheet = workbook.getSheetAt(0); //主要表单
    CellStyle style = workbook.createCellStyle(); // 样式对象
    style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
    style.setAlignment(HorizontalAlignment.CENTER);// 水平
    style.setWrapText(true);//自适应

    List<String> title = exportTitleMake(sheet);
    ExcelTools.createRow(sheet, 0, title);
    int i = 1;
    for (int j = 0; j < 10; j++) {
        title.clear();
        title.add("");

        ExcelTools.createRow(sheet, i++, title);
    }
    ResponseHeaderForExcel.setResponseHeader(request, response, "导出测试");
    outputStream = response.getOutputStream();
    workbook.write(outputStream);
    return true;
} catch (Exception e) {
    log.error("failed to export, cause: {}", Throwables.getStackTraceAsString(e));
    throw new JsonResponseException("export.fail");
}finally {
    try {
        workbook.close();
        if (outputStream != null) {
            outputStream.flush();
            outputStream.close();
        }
    } catch (IOException io) {
        log.error("failed to close workbook, cause:{}", Throwables.getStackTraceAsString(io));
    }
}

for循环筛选出来的数据，然后title.add进去即可，注意每次add之前都要clear一下，最好不要使用clear，最好直接new 一个，就好了，下载excel的代码：

 

public abstract class ResponseHeaderForExcel {

    public static void setResponseHeader(HttpServletRequest request, HttpServletResponse response, String fileName)
            throws UnsupportedEncodingException {
        fileName = fileName + ".xlsx";

        final String userAgent = request.getHeader("USER-AGENT");

        String finalFileName;
        if(StringUtils.contains(userAgent.toUpperCase(), "MSIE")
                || StringUtils.contains(userAgent.toUpperCase(), "TRIDENT")
                || StringUtils.contains(userAgent.toUpperCase(), "EDGE")   ){//IE浏览器
            finalFileName = URLEncoder.encode(fileName,"UTF8");
        }else if(StringUtils.contains(userAgent.toUpperCase(), "Mozilla".toUpperCase())){//safari,火狐浏览器
            finalFileName = new String(fileName.getBytes(), "ISO8859-1");
        }else{
            finalFileName = URLEncoder.encode(fileName,"UTF8");//其他浏览器, 如chrome等
        }
        response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
        response.setContentType("application/octet-stream;charset=utf-8");
    }

}

 

给excel每个单元格赋值的方法：

public static void createRow(Sheet sheet, int rowNum, List<String> contents) {
    Workbook workbook = sheet.getWorkbook();
    CellStyle textStyle;
    if (workbook.getNumCellStyles() > 0) {
        textStyle = workbook.getCellStyleAt(0);
    } else {
        textStyle = workbook.createCellStyle();
    }
    DataFormat format = workbook.createDataFormat();
    textStyle.setDataFormat(format.getFormat("@"));
    CellStyle style;
    if (workbook.getNumCellStyles() == 0) {
        style = workbook.createCellStyle();

    } else {
        style = workbook.getCellStyleAt(0);
    }
    style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
    style.setAlignment(HorizontalAlignment.CENTER);// 水平
    style.setWrapText(true);//自适应
    Row row = sheet.createRow(rowNum);
    row.setRowStyle(style);
    for (int i = 0; i < contents.size(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellStyle(style);
        DataFormat df = workbook.createDataFormat();
        if (contents.get(i) == null) {
            cell.setCellValue("");
        } else if (isNumeric(contents.get(i))) {
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Double.parseDouble(contents.get(i)));
            textStyle.setDataFormat(df.getFormat("#,##0.00"));
        } else {
            cell.setCellType(CellType.STRING);
            cell.setCellValue(contents.get(i));
        }
    }
}

就ok了

ps:会有个问题，就是导出excel报错，但是可以正常导出，报的错是：org.eclipse.jetty.io.EofException: Closed

网上搜了一下说是内存不足，可是实际上我只导出来两三条数据，后来发现是接口设置了返回boolean类型，改成void就行了，原因是返回了一个文件，又返回了一个boolean值，跟http的header有关，具体没研究