# java导出excel表格demo

java导出excel代码

---

代码

/**
     * 系统告警导出excel
     * @param response
     * @param filename
     * @param dateType
     * @param startTime
     * @param endTime
     * @param entId
     * @param region
     * @param industry
     * @param energyConsume
     */
    @RequestMapping(value = "/alarm/export")
    public String exportData(HttpServletResponse response, 
            @RequestParam(required = false, defaultValue = "0") Integer userType,
            @RequestParam(required = true) long entId,
            @RequestParam(required = false, defaultValue = "") String content, 
            @RequestParam(required = false, defaultValue = "4") int disponseflag,
            @RequestParam(required = false, defaultValue = "0") int alarmLevel,
            @RequestParam(required = false, defaultValue = "") String alarmTime ){
        response.setContentType("application/vnd.ms-excel");
        String fileName = "系统告警统计" + DateTime.now().toString("yyyyMMddHHmmss") + ".xls";
        try {
            response.setHeader("Content-disposition", "attachment;filename=\""
                    + new String(fileName.getBytes("gb2312"), "ISO8859-1") + "\"");
        String queryConditaion = null;
        if (!"".equals(content)) {
            queryConditaion = "%" + content + "%";
        }
        List<BoilerAlarmRecord> listAlarm = null;
        if (userType == EnumBoilerUserType.BOILER_USER_PLAT.getValue()) {
            listAlarm = globalBoilerFacade.getExportAlarms(queryConditaion,disponseflag,alarmLevel,alarmTime);
        } else if (userType == EnumBoilerUserType.BOILER_USER_ENT.getValue()) {
            listAlarm = globalBoilerFacade.getExportVestedEntAlarms(queryConditaion, entId,disponseflag,alarmLevel,alarmTime);
        } else if (userType == EnumBoilerUserType.BOILER_USER_FAC.getValue()) {
            listAlarm = globalBoilerFacade.getExportFactoryEntAlarms(queryConditaion, entId,disponseflag,alarmLevel,alarmTime);
        } else {
            listAlarm = Lists.newArrayList();
        }
        int total = listAlarm.size();
        OutputStream ouputStream = response.getOutputStream();
        WritableWorkbook book = Workbook.createWorkbook(ouputStream);
        WritableSheet sheet1 = book.createSheet("系统告警统计报表"+ DateTime.now().toString("yyyyMMddHHmmss"), 0);

        //表格内容样式
        WritableFont body = new WritableFont(WritableFont.ARIAL, 10);
        WritableCellFormat bodyStyle = new WritableCellFormat(body);
        bodyStyle.setAlignment(Alignment.CENTRE);//内容水平居中
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTRE);

        //表头标题样式
        WritableFont titleHead= new WritableFont(WritableFont.ARIAL,12,WritableFont.BOLD);
        WritableCellFormat titleHeadStyle = new WritableCellFormat(titleHead);
        titleHeadStyle.setAlignment(Alignment.CENTRE);//内容水平居中
        titleHeadStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
        titleHeadStyle.setBackground(Colour.GRAY_25);//设置背景颜色为灰色

        //筛选条件样式
        WritableFont select= new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
        WritableCellFormat selectStyle = new WritableCellFormat(select);
        selectStyle.setAlignment(Alignment.CENTRE);//内容水平居中
        selectStyle.setVerticalAlignment(VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
        selectStyle.setBackground(Colour.YELLOW);//设置背景颜色为黄色

        Label label;  
        //筛选条件 
        String selectContent = "";
        if(!StringUtils.isEmpty(content)){
            selectContent += "告警对象："+content;
        }
        if(disponseflag != 4){
            selectContent += "    状态："+(disponseflag == BoilerCatalogConstants.ALARM_STATE_VERIFIER ?"已审核":(disponseflag == BoilerCatalogConstants.ALARM_STATE_REJECT ?"驳回":(disponseflag == BoilerCatalogConstants.ALARM_STATE_UNVERIFIER ?"未审核":"未确认")));
        }
        if(alarmLevel != 0){
            selectContent += "    告警优先级："+(alarmLevel == 3?"三级告警":(alarmLevel == 2?"二级告警":"一级告警"));
        }
        if(!StringUtils.isEmpty(alarmTime)){
            selectContent += "    告警时间："+alarmTime;
        }
        selectContent += "    共计："+total+" 条记录";
        sheet1.mergeCells(0, 0, 11, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
        sheet1.setRowView(0, 800);
        label = new Label(0,0,selectContent,selectStyle); 
        sheet1.addCell(label);

        //表头标题
        String[] title = {"告警时间","告警内容","告警对象","锅炉所属企业","锅炉制造企业","告警优先级","告警次数","状态","确认人","确认信息","确认时间","审核人"};    

        for(int i=0;i<title.length;i++){    
           label = new Label(i,1,title[i],titleHeadStyle); 
           sheet1.addCell(label);
           sheet1.setRowView(1, 600);
        }    
        //表格内容
        Object[] obj = null;
        for(int i = 0; i < listAlarm.size(); i++){
          sheet1.setRowView(i+2, 400);
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
          obj = new Object[12];
          obj[0] =  sdf.format(listAlarm.get(i).getAlarmTime());
          sheet1.setColumnView(0, 24);
          String alarmContent = "";
          if(listAlarm.get(i).getAlarmContent() != null){
              alarmContent = listAlarm.get(i).getAlarmContent();
          }
          obj[1] = alarmContent;
          sheet1.setColumnView(1, 26);
          String objName = "";
          if(listAlarm.get(i).getObjName() != null){
              objName = listAlarm.get(i).getObjName();
          }
          obj[2] = objName;
          sheet1.setColumnView(2, 30);
          String vestName = "";
          if(listAlarm.get(i).getVestedEntName() != null){
              vestName = listAlarm.get(i).getVestedEntName();
          }
          obj[3] = vestName;
          sheet1.setColumnView(3, 30);
          String factoryName = "";
          if(listAlarm.get(i).getFactoryName() != null){
              factoryName = listAlarm.get(i).getFactoryName();
          }
          obj[4] = factoryName;
          sheet1.setColumnView(4, 30);
          String alarmStatus = "一级告警";
          if( listAlarm.get(i).getAlarmLevel() == 2){
              alarmStatus = "二级告警";
          }else if( listAlarm.get(i).getAlarmLevel() == 3){
              alarmStatus = "三级告警";
          }
          obj[5] = alarmStatus;
          sheet1.setColumnView(5, 15);
          obj[6] = listAlarm.get(i).getAlarmNum();
          sheet1.setColumnView(6, 15);
          String status = "未确认";
          if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_UNVERIFIER){
              status = "未审核";
          }else if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_REJECT){
              status = "驳回";
          }else if(listAlarm.get(i).getDisposeFlag() == BoilerCatalogConstants.ALARM_STATE_VERIFIER){
              status = "审核";
          }
          obj[7] = status;
          sheet1.setColumnView(7, 15);
          String disposeName = "";
          if(listAlarm.get(i).getDisponseUserName() != null){
              disposeName = listAlarm.get(i).getDisponseUserName();
          }
          obj[8] = disposeName;
          sheet1.setColumnView(8, 15);
          String info = "";
          if(listAlarm.get(i).getDisponseInfo() != null){
              info = listAlarm.get(i).getDisponseInfo();
          }
          obj[9] = info;
          sheet1.setColumnView(9, 30);
          String disTime = "";
          if(listAlarm.get(i).getDisposeTime() != null){
             disTime = sdf.format(listAlarm.get(i).getDisposeTime());
          }
          obj[10] = disTime;
          sheet1.setColumnView(10, 24);
          String verifierName = "";
          if(listAlarm.get(i).getVerifierUserName() != null){
              verifierName = listAlarm.get(i).getVerifierUserName();
          }
          sheet1.setColumnView(11, 15);
          obj[11] = verifierName;
          for (int j = 0; j < obj.length; j++) {
              sheet1.addCell(new Label(j,i+2,obj[j].toString(),bodyStyle));
          }
        }
        book.write();
        book.close();
      }
      catch (Exception e) {
        e.printStackTrace();
      }
      return null;
    }