excel导出

excel导出

 @ResponseBody
    @RequestMapping(value = {"/test/export"})
    public void testExport(HttpServletRequest request, HttpServletResponse response
    ) throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook();//excel文档对象
        HSSFSheet sheet = wb.createSheet("export");//excel的sheet HSSFRow excel的行
        //设置标题头
        HSSFRow row = sheet.createRow(0);//创建Excel工作表的行
        sheet.createFreezePane(0, 1);
        String[] colNames = new String[]{"姓名", "性别", "年龄"};
        // 设置第一列
        setFirstClo(wb, row, colNames);
        List<Map> list = new ArrayList<Map>();
        Map map = new HashMap();
        map.put("name", "aaa");
        map.put("sex", "男");
        map.put("age", "30");
        list.add(map);
        Map map1 = new HashMap();
        map1.put("name", "bbb");
        map1.put("sex", "男");
        map1.put("age", "30");
        list.add(map1);
        if (null != list && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                Map tempMap = list.get(i);
                Object[] values = {map.get("name"), map.get("sex"), map.get("age")};
                // 填充数据
                HSSFRow rowIn = sheet.createRow(i + 1);
                setDataClo(wb, rowIn, values);
            }
        }

        String fileName = new String("测试.xls".getBytes("gbk"), "iso8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/ms-excel");
        wb.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }

/**
     * 设置excel第一行数据（列名）
     *
     * @param wb
     * @param row
     * @param colNames
     */
    public static void setFirstClo(HSSFWorkbook wb, HSSFRow row, String[] colNames) {
        for (int i = 0; i < colNames.length; i++) {
            createCell(wb, row, i, colNames[i]);
        }
    }

    /**
     * 设置excel数据
     *
     * @param wb
     * @param row
     * @param colValues
     */
    public static void setDataClo(HSSFWorkbook wb, HSSFRow row, Object[] colValues) {
        for (int i = 0; i < colValues.length; i++) {
            createCell(wb, row, i, String.valueOf(colValues[i]));
        }
    }

    /**
     * 创建一行
     *
     * @param wb
     * @param row
     * @param col
     * @param val
     */
    private static void createCell(HSSFWorkbook wb, HSSFRow row, int col, String val) {
        HSSFCell cell = row.createCell(col);
        val = val.replaceAll("&#39;", "'");
        val = val.replaceAll("&#40;", "\\(").replaceAll("&#41;", "\\)");
        val = val.replaceAll("&lt;", "<").replaceAll("&gt;", ">");

        cell.setCellValue(val);//设置Excel工作表的值
        HSSFCellStyle cellStyle = wb.createCellStyle();//创建单元格样式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
        cell.setCellStyle(cellStyle);
    }