Java导出Excel简单实现<二>

pom依赖

<dependency>
      <groupId>org.apache.poi</groupId>
       <artifactId>poi</artifactId>
       <version>3.8</version>
</dependency>

controller接口方法：

   public void exportAppUserOrderRecordList(@RequestParam Map<String, Object> params, HttpServletResponse response) throws IOException {

        ServletOutputStream sos = null;

        try{

            List appUserDtoList = new ArrayList();
            response.reset();
            sos = response.getOutputStream();
            HSSFWorkbook workbook = orderRecordService.buildHSSFWorkbook(this.buildTableHead(),appUserDtoList);
            workbook.write(sos);
            response.setContentType("application/octet-stream;charset=UTF-8");
            String fileName = new StringBuilder("用户导出").append("-").append(DateUtils.format(new Date(),"yyyyMMddHH")).toString();
            response.setHeader("Content-Disposition","attachment; filename="+ URLEncoder.encode(fileName +".xls", "utf-8"));
//            response.setHeader("Pragma", "public");
//            response.setHeader("Cache-Control","public");
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try {
                sos.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
            sos.close();
        }
    }

service接口实现方法:

   public <T> HSSFWorkbook buildHSSFWorkbook(Map<String, String> map, List<T> dataset) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格  - sheet页的名称
        HSSFSheet sheet = workbook.createSheet("用户申请导出");
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(15);

        // 产生表格标题行  下标从0开始
        HSSFRow row = sheet.createRow(0);

        List<String> list = Arrays.asList(map.keySet().toArray(new String[map.keySet().size()]));

        for (int i = 0; i <list.size(); i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(list.get(i));
        }

        for(int i=0;i<dataset.size();i++){
            row = sheet.createRow(i+1);
            List<String> fileNameList = Arrays.asList(map.values().toArray(new String[map.values().size()]));
            for (int j = 0; j < fileNameList.size(); j++){
                HSSFCell cell = row.createCell(j);
                String value = null;
                try{
                    value = org.apache.commons.beanutils.BeanUtils.getProperty(dataset.get(i), fileNameList.get(j));
                    //value = dataset.get(i).get(fileNameList.get(j));
                }catch (Exception e){
                    e.printStackTrace();
                    value=null;
                    log.error("【用户申请记录导出】】异常" + fileNameList.get(j),e);
                }
                cell.setCellValue(value);
            }
        }
        log.info("【用户申请记录导出】条目：{}" + dataset.size());
        return workbook;
    }

 

private Map<String, String> buildTableHead() {
    Map<String,String> map = new LinkedHashMap<String,String>();
    map.put("用户ID","userId");
    map.put("用户名", "userName");
    map.put("手机号", "mobile");
    map.put("来源渠道", "channel");
    map.put("申请次数", "applyCount");
    map.put("创建时间", "createTimeStr");
    map.put("更新时间", "modifyTimeStr");
    map.put("业务状态", "statusStr");
    return map;
}

 

 

参考地址：

https://blog.csdn.net/jimmy609/article/details/41038537   更大数量看这里