java使用jxl.jar导出Excel

时间:2021-06-27 09:14:11
/**  * 导出Excel  * @param listResult 数据集  * @param type 导出类型 1:通用 2:退回文件  */ public void jxlExcelExport(List listResult,Integer type) {
   OutputStream os = null;
   WritableWorkbook book = null;
   try {
      String curDate = DateUtil.format(new Date(), "yyyy-MM-dd");
      String fileName = this.title + curDate + ".xls";
      fileName = new String(fileName.getBytes(),"ISO_8859_1");
      HttpServletResponse response = ServletActionContext.getResponse();
      response.reset();
      response.setContentType("application/vnd.ms-excel");
      response.setHeader("Content-Disposition", "attachment;filename="+fileName);
      os = response.getOutputStream();
      book = Workbook.createWorkbook(os);
      WritableSheet sheet = book.createSheet("Sheet_1", 0);
      //标题样式  WritableFont wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 16,
            WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
            Colour.BLACK);
      WritableCellFormat wcfFC = new WritableCellFormat(wfont);
      wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
      wcfFC.setAlignment(Alignment.CENTRE);
      sheet.addCell(new Label(0, 0, title, wcfFC));
      if (type == 1)
          sheet.mergeCells(0, 0, 6, 0);
      else  sheet.mergeCells(0, 0, 5, 0);
      //表头样式  wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 12,
            WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
            Colour.BLACK);
      wcfFC = new WritableCellFormat(wfont);
      wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
      wcfFC.setAlignment(Alignment.CENTRE);
      int[] widths = null;
      if (type == 1)
         widths = new int[]{5,50,30,25,25,25,20};
      else  widths = new int[]{5,30,50,25,50,25};
      for (int i = 0; i < widths.length; i++) {
         sheet.setColumnView(i,widths[i]);
      }
      //标题名  String[] lableNames = null;
       if (type == 1)
         lableNames = new String[]{"序号","工作名称","承办科室","分管事项领导","开始时间","完成时限","完成情况"};
       else  lableNames = new String[]{"序号","文件类型","工作名称","承办科室","退回科室","退回时间"};
      for (int i = 0; i < lableNames.length; i++) {
         Label label = new Label(i, 1, lableNames[i], wcfFC);
         sheet.addCell(label);
      }
      wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 12,
            WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
            Colour.BLACK);
      wcfFC = new WritableCellFormat(wfont);
      wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);
      wcfFC.setAlignment(Alignment.CENTRE);
      for (int i = 0; i < listResult.size(); i++) {
         JbSw jbSw = listResult.get(i);
         if (type == 1) {
            Label lb = new Label(0, i+2, String.valueOf(i+1), wcfFC);
            sheet.addCell(lb);
            lb = new Label(1, i+2, jbSw.getFlsubject(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(2, i+2, jbSw.getSwUnit(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(3, i+2, jbSw.getDutyPerson(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(4, i+2, jbSw.getBeginTime(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(5, i+2, jbSw.getFinishTime(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(6, i+2, jbSw.getMemo(), wcfFC);
            sheet.addCell(lb);
         } else {
            Label lb = new Label(0, i+2, String.valueOf(i+1), wcfFC);
            sheet.addCell(lb);
            lb = new Label(1, i+2, jbSw.getWorkTypeName(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(2, i+2, jbSw.getFlsubject(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(3, i+2, jbSw.getSwUnit(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(4, i+2, jbSw.getReportMode(), wcfFC);
            sheet.addCell(lb);
            lb = new Label(5, i+2, jbSw.getLastRptTime(), wcfFC);
            sheet.addCell(lb);
         }

      }
      book.write();
      os.flush();
      book.close();
      os.close();
   } catch (Exception e) {
      e.printStackTrace();
      if(os!=null){
         try {
            os.close();
         } catch (Exception e1) {
            e1.printStackTrace();
         }
      }

      if(book!=null){
         try {
            book.close();
         } catch (Exception e1) {
            e1.printStackTrace();
         }
      }
   }
}