使用POI导出Excel例子与常见问题

代码

function toExcel(){
    $("#formExcel").submit();
}

@RequestMapping(value="/toExcel",method=RequestMethod.POST)
    public String toExcel(HttpServletResponse response, String fileName, String tableName, String type, String where){
        if (StringUtils.isNotEmpty(fileName) && StringUtils.isNotEmpty(tableName) && StringUtils.isNotEmpty(type)) {
            inteQueryService.toExcel(response,fileName,tableName,type,where);
            return null;
        }
        return null;
    }

public static void toExcel(HttpServletResponse response,List<Map<String,Object>> lists,String name,String[] strArr){
        List[] totals = splitList(lists,65000);

        // 创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        // 由工作簿创建工作表
        for (int j = 0; j < totals.length; j++) {

            HSSFSheet sheet = wb.createSheet("sheet"+(j+1));
            // 在工作表中创建行
            HSSFRow row = sheet.createRow(0);
            // 创建单元格,设置每个单元格的字段名
            HSSFCell cell = null;
            for (int i = 0; i < strArr.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(strArr[i]);
            }

            List<Map<String,Object>> list = totals[j];
            for (int i = 0; i < list.size(); i++) {
                Iterator iter = list.get(i).entrySet().iterator();
                row = sheet.createRow(i+1);
                cell = row.createCell(0);
                cell.setCellValue(i+1);
                int index = 1;
                while (iter.hasNext()) {
                    Map.Entry entry = (Map.Entry) iter.next();
                    //Object key = entry.getKey();
                    Object val = entry.getValue();
                    cell = row.createCell(index);
                    cell.setCellValue(val.toString());
                    index++;
                }
            }
        }

        ServletOutputStream sos = null;

        try {
            String fileName = name+".xls";

            response.setContentType("application/vnk.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/msexcel;charset=utf-8");
            response.resetBuffer();

            sos = response.getOutputStream();
            wb.write(sos);
            sos.flush();

        } catch (IOException e) {
            logger.error("导出excel工具类报错",e);
        } finally {
            try {
                sos.close();
            } catch (IOException e) {
                logger.error("导出excel工具类报错",e);
            }
        }
    }

public static List[] splitList(List list, Integer pageSize) {
        if(pageSize == null){
            pageSize = 300;
        }
        int total = list.size();
        //总页数
        int pageCount = total % pageSize == 0 ? total / pageSize : total / pageSize + 1;
        List[] result = new List[pageCount];
        for(int i = 0; i < pageCount; i++) {
            int start = i * pageSize;
            //最后一条可能超出总数
            int end = start + pageSize > total ? total : start + pageSize;
            List subList = list.subList(start, end);
            result[i] = subList;
        }
        return result;
    }

常见问题

• 前台一定要使用表单的方式进行提交，ajax是不行的，因为ajax只能接受 xml、 json、html等类似字符串的返回，不能接受流作为返回值。否则程序不报错，就是不弹下载框。