Java导出excel

大家都知道，很多企业都想把页面的数据能以excel的形式导出来，尤其是erp之类的管理系统，特别需要这个功能，所以导出excel程序员的必备技能之一，下面来看例子。

• Java导出excel表格

@RequestMapping("receiptExport")
    public void receiptExport(String groupCode,String statData,String endData,Integer aStatus){
        // *收款单列表
        Map<String,Object> mapCondition=new HashMap<String,Object>();
        mapCondition.put("groupCode", groupCode);
        mapCondition.put("billType", 1);
        mapCondition.put("statData", statData);
        mapCondition.put("endData", endData);
        mapCondition.put("aStatus", aStatus);

        List<Cost> inslist =costDao.costList(mapCondition);
        Cost mCostSum=costDao.selectSumCost(groupCode, 1,statData, endData);
        Map<Integer, List<Cost>> rmap = new HashMap<Integer, List<Cost>>();
        // 查询数据
        if (null != inslist && inslist.size() > 0) {
            for (int i = 0; i < inslist.size(); i++) {
                rmap.put(i + 2, inslist);// 把查询出来的对象放入map集合中
            }
        }
        ExcelWriter writer = new ExcelWriter("sheet");
        HSSFWorkbook workbook = writer.getWorkbook();
        // 字体
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFFont.SS_NONE);

        // 表头字体
        HSSFFont fontHead = workbook.createFont();

        fontHead.setColor(HSSFFont.SS_NONE);
        fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 表头单元格样式
        HSSFCellStyle headStyle = workbook.createCellStyle();

        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont fontNo = workbook.createFont();
        fontNo.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headStyle.setFont(fontNo);

        // 普通单元格样式
        HSSFCellStyle styleColor = workbook.createCellStyle();
        styleColor.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        // 取消数字过长自动转换成科学计数法的样式
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        cellStyle2.setDataFormat(format.getFormat("@"));// 此样式代表文本

        HSSFSheet sheet = workbook.getSheetAt(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));
        // 设置字体大小
        HSSFFont celltbNameFont = workbook.createFont();
        celltbNameFont.setFontHeightInPoints((short) 15);
        celltbNameFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        // 设置顶部样式
        HSSFCellStyle styleCell = workbook.createCellStyle();
        styleCell.setFont(celltbNameFont);
        styleCell.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFRow rows = sheet.createRow((short) 0);// 创建一行
        rows.setHeight((short) 600);

        HSSFCell cells = rows.createCell(0);
        styleCell.setWrapText(true);
        String title = "收款单列表";
        cells.setCellValue(new HSSFRichTextString(title));
        cells.setCellStyle(styleCell);

        // 第一行，表头
        writer.createCellNumeric(1, 0, "序号", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 1, "状态", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 2, "类型", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 3, "单据编号", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 4, "单据日期", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 5, "客户", headStyle, (int) (256 * 6));
        writer.createCellNumeric(1, 6, "收款合计", headStyle, (int) (256 * 6));

        // 写入数据
        for (Entry<Integer, List<Cost>> map : rmap.entrySet()) {
            int a = map.getKey();
            writer.createCellNumeric(a, 0, "" + (map.getKey() - 1), styleColor,(int) (256 * 6));// 里面的几个值以此代表：行数、列数、内容、样式、宽度 。 下面方法参数跟这个相同

            if (inslist.get(a - 2).getState()==0) {
                writer.createCellNumeric(a, 1,"已保存", styleColor, (int) (256 * 30));
            }else if (inslist.get(a - 2).getState()==1) {
                writer.createCellNumeric(a, 1,"一审已审", styleColor, (int) (256 * 30));
            }else if (inslist.get(a - 2).getState()==2) {
                if(inslist.get(a - 2).getBill_type()==0 || inslist.get(a - 2).getBill_type()==1){
                    writer.createCellNumeric(a, 1,"已二审", styleColor, (int) (256 * 30));
                }else if(inslist.get(a - 2).getBill_type()==2 || inslist.get(a - 2).getBill_type()==3){
                    writer.createCellNumeric(a, 1,"已审核", styleColor, (int) (256 * 30));
                }
            }

            if (inslist.get(a - 2).getBill_type()==0) {
                writer.createCellNumeric(a, 2,"付款单" , styleColor,(int) (256 * 30));
            }else if(inslist.get(a - 2).getBill_type()==1){
                writer.createCellNumeric(a, 2,"收款单" , styleColor,(int) (256 * 30));
            }else if(inslist.get(a - 2).getBill_type()==2){
                writer.createCellNumeric(a, 2,"其他收款单" , styleColor,(int) (256 * 30));
            }else{
                writer.createCellNumeric(a, 2,"其他付款单" , styleColor,(int) (256 * 30));
            }

            writer.createCell(a, 3,
                    inslist.get(a - 2).getBill_code() == null ? "" : inslist
                            .get(a - 2).getBill_code()
                            + "", cellStyle2, (int) (256 * 30));

            writer.createCell(a, 4,
                    inslist.get(a - 2).getReceive_date() == null ? "" : inslist
                            .get(a - 2).getReceive_date()
                            + "", cellStyle2, (int) (256 * 30));

            writer.createCellNumeric(a, 5,
                    inslist.get(a - 2).getOrgan_name() == null ? "" : inslist.get(
                            a - 2).getOrgan_name(), styleColor, (int) (256 * 30));
            writer.createCellNumeric(a, 6,
                    inslist.get(a - 2).getMoney_sum() == null ? "" : inslist.get(
                            a - 2).getMoney_sum()
                            + "", styleColor, (int) (256 * 30));
        }

        int num=inslist.size()+2;
        writer.createCellNumeric(num, 5, "(已二审)总计:", headStyle, (int) (256 * 30));
        writer.createCellNumeric(num, 6, String.valueOf(mCostSum.getMoney_sum()), headStyle, (int) (256 * 30));
        writer.createCellNumeric(num, 7, String.valueOf(mCostSum.getWriteoff_sum()), headStyle, (int) (256 * 30));
        writer.createCellNumeric(num, 8, String.valueOf(mCostSum.getZhekou()), headStyle, (int) (256 * 30));
        writer.createCellNumeric(num, 9, String.valueOf(mCostSum.getThis_payment()), headStyle, (int) (256 * 30));
        try {
            response().setContentType("application/vnd.ms-excel");
            response().setHeader("Content-Disposition",
                    "attachment;filename=\""
                            + URLEncoder.encode(title + ".xls", "UTF-8")
                            + "\"");
            response().setHeader("Cache-Control",
                    "must-revalidate,post-check=0,pre-check=0");
            response().setHeader("Pragma", "public");
            response().setDateHeader("Expires", 0);
            OutputStream os = response().getOutputStream();
            workbook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

注解：稍微解释一下，第一步，就是一个查询列表，该怎么查就怎么查，然后把查到的数据，按格式放入到下面的excel代码中，excel代码中也有注释，就不一点点解释了。

除了HSSFWorkbook这种导出excel方法以外，其实还有其他的类也能导出excel，感兴趣的可以百度一下，有什么问题，也欢迎大家给我留言。