java 导出数据到excal文件（HSSF、POI）

jar包：poi-3.7-20101029.jar     应该百度上有很多。

action文件中：

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

@Resource(name = "takecashService")
private TakecashService takecashService;

public void exporttest(){
        HttpServletResponse resp = getResponse(); 
        try {
            HSSFWorkbook hwb = takecashService.exportInfos(status);
            resp.setContentType("application/x-msdownload");
            resp.setHeader("Content-Disposition", "attachment;filename=\""
                    + new String("导出EXCAL表格的名称".getBytes("gb2312"), "ISO8859-1")+ ".xls" + "\"");
            OutputStream out = resp.getOutputStream();
            hwb.write(out);
            out.flush();
            out.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    } 

红色部分跳转到service文件中进行处理：

        

    
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

    @Resource(name = "takecashDao")
    private TakecashDao takecashDao;

    @Override
    public HSSFWorkbook exportInfos(String mName) {
        String[] cellname={
                "编号","认证商户名","用户名","提现卡号开户姓名","银行卡号","所属银行","提现金额","身份证号","申请状态","提现申请时间","提现同意时间"
                };
        HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFSheet sheet = hwb.createSheet("金额基础信息");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        HSSFCellStyle stycle = hwb.createCellStyle();
        stycle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < cellname.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(cellname[i]);
            cell.setCellStyle(stycle);
            //设置excal表格的宽度
             sheet.setColumnWidth((short) i, cellname[i].getBytes().length * 600);
        }
        //本部分是跳转到Dao页面进行查询返回list类型的数据。
        List<Map<String, String>> list = takecashDao.getExcept(mName);
        if (list != null) {
            for (int i = 0; i < list.size(); i++) {
                Map<String, String> info = list.get(i);
                row = sheet.createRow(i + 1);
                row.createCell(0).setCellValue(i+1+"");
                row.createCell(1).setCellValue(info.get("CA_NAME"));//认证商户名
                row.createCell(2).setCellValue(info.get("username"));//用户名
                row.createCell(3).setCellValue(info.get("name")); //提现卡号开户姓名
                row.createCell(4).setCellValue(info.get("cardcode"));//银行卡号
                row.createCell(5).setCellValue(info.get("blank"));//所属银行
                row.createCell(6).setCellValue(info.get("money"));//提现金额
                row.createCell(7).setCellValue(info.get("cardnum"));//身份证号
                row.createCell(8).setCellValue(info.get("state"));//申请状态
                row.createCell(9).setCellValue("".equals(info.get("tjtime"))?"":info.get("tjtime").toString().substring(0, info.get("tjtime").length()-2));//提现申请时间
                row.createCell(10).setCellValue("".equals(info.get("cldate"))?"":info.get("cldate").toString().substring(0, info.get("cldate").length()-2));//提现同意时间
            }
        }
        return hwb;
    }

Dao页面的方法：

//查询出数据 用来导出到Excal表格中。
public List<Map<String, String>> getExcept(String status) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select t.id,t.BankId,t.money,t.state ,mv.CA_NAME,t.CREATEDATE,d.username,mv.CA_CARDNO,t.cldate, ");
        sql.append(" bc.name,bc.card_code,bc.blank ");
        sql.append(" from takecash t ");
        sql.append(" LEFT JOIN dealer d on t.userid=d.ID ");
        sql.append(" LEFT JOIN md_vendor_auth  mv on mv.USER_ID=  d.ID ");
        sql.append(" left join blank_card bc on t.BankId = bc.id ");
        sql.append(" where 1=1 ");
        if(status!=null&&!"".equals(status)){
            if(status.equals("0")){//已认证
                sql.append(" AND t.state = '0' ");
            }else if(status.equals("1")){//已拒绝
                sql.append(" AND t.state = '1' ");
            }
        }
        sql.append("order by  t.CREATEDATE desc");
        List<Object[]> lite=this.getSession().createSQLQuery(sql.toString()).list();
        ArrayList data = new ArrayList();
        for (Object[] res : lite) {
            HashMap<String, Object> record = new HashMap<String, Object>();
            record.put("id", res[0]);
            record.put("BankId", res[1]);//
            record.put("money", res[2]);//
//            record.put("state", res[3]);//
            record.put("CA_NAME", res[4]);//
            if(null != res[3]){//认证状态
                if("0".equals(res[3].toString())){
                    record.put("state","已提交");
                }else if("1".equals(res[3].toString())){
                    record.put("state","审核通过");
                }
            }
            record.put("tjtime", (null)==res[5]?"":res[5].toString());//
            record.put("username", res[6]);//
            record.put("cardnum", res[7]);//
            record.put("cldate", (null)==res[8]?"":res[8].toString());//
            record.put("name", res[9]);//银行卡开户姓名
            record.put("cardcode", res[10]);//银行卡卡号
            record.put("blank", res[11]);//银行名称
            data.add(record);
        }
    
        return data;
    }