查询结果Excel的导出

最新推荐文章于 2022-10-27 00:00:00 发布

hc19870220

最新推荐文章于 2022-10-27 00:00:00 发布

阅读量568

点赞数

文章标签： excel string exception null import date

本文链接：https://blog.csdn.net/hc19870220/article/details/4439765

版权

import Excel导出要使用的包poi-3.2-FINAL-20081019.jar

public class ExcelAuditServlet extends HttpServlet {

/**
* @param bppl 要输出的列表
* @param sheet
*/
protected void createSheets(List<TotalAccount> tal,HSSFSheet sheet) {
  HSSFRow row = null;
  HSSFCell cell = null;
  row = sheet.createRow(NumberConstants.NUM_0);
  cell = row.createCell(NumberConstants.NUM_0);
  cell.setCellValue(new HSSFRichTextString("对账时间"));
  cell = row.createCell(NumberConstants.NUM_1);
  cell.setCellValue(new HSSFRichTextString("对账类型"));
  cell = row.createCell(NumberConstants.NUM_2);
  cell.setCellValue(new HSSFRichTextString("对账起始时间"));
  cell = row.createCell(NumberConstants.NUM_3);
  cell.setCellValue(new HSSFRichTextString("对账终止时间"));
  cell = row.createCell(NumberConstants.NUM_4);
  cell.setCellValue(new HSSFRichTextString("总笔数"));
  cell = row.createCell(NumberConstants.NUM_5);
  cell.setCellValue(new HSSFRichTextString("总金额（元）"));
  cell = row.createCell(NumberConstants.NUM_6);
  cell.setCellValue(new HSSFRichTextString("对账状态"));
  cell = row.createCell(NumberConstants.NUM_7);
  cell.setCellValue(new HSSFRichTextString("对账状态描述"));
  cell = row.createCell(NumberConstants.NUM_8);
  cell.setCellValue(new HSSFRichTextString("明细对账"));
  cell = row.createCell(NumberConstants.NUM_9);
  cell.setCellValue(new HSSFRichTextString("对账接口"));
  if(tal!=null){
   for(int i=0;i<tal.size();i++){
    TotalAccount ta = tal.get(i+1);
    if(ta==null){
     continue;
    }
    row = sheet.createRow(i+1);
    cell = row.createCell(NumberConstants.NUM_0);
    cell.setCellValue(new HSSFRichTextString(ta.getOperTime().toString()));
    cell = row.createCell(NumberConstants.NUM_1);
    cell.setCellValue(new HSSFRichTextString(ta.getAuditType().getValue()));
    cell = row.createCell(NumberConstants.NUM_2);
    cell.setCellValue(new HSSFRichTextString(ta.getBeginTime().toString()));
    cell = row.createCell(NumberConstants.NUM_3);
    cell.setCellValue(new HSSFRichTextString(ta.getEndTime().toString()));
    cell = row.createCell(NumberConstants.NUM_4);
    cell.setCellValue(new HSSFRichTextString(ta.getTotalCount().toString()));
    cell = row.createCell(NumberConstants.NUM_5);
    cell.setCellValue(new HSSFRichTextString(ta.getTotalAmount().toString()));
    cell = row.createCell(NumberConstants.NUM_6);
    cell.setCellValue(new HSSFRichTextString(ta.getAuditType().getValue()));
    cell = row.createCell(NumberConstants.NUM_7);
    cell.setCellValue(new HSSFRichTextString(ta.getDescr()));
    cell = row.createCell(NumberConstants.NUM_8);
    cell.setCellValue(new HSSFRichTextString(ta.getAuditId()));
    cell = row.createCell(NumberConstants.NUM_9);
    cell.setCellValue(new HSSFRichTextString(ta.getAuditInterf().getDisplayName()));
   }
  }
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
*      response)
*/
protected void doGet(HttpServletRequest request,HttpServletResponse response)
  throws ServletException, IOException {
  //没有操作权限时调用这个方法会抛出异常
  WebUtil.getMSC(request.getSession());
  request.setCharacterEncoding("gbk");
  String t = request.getParameter("time");
  Date time;
  try {
   time = new SimpleDateFormat("yyyy-MM").parse(t);
  } catch (ParseException e) {
   throw new RuntimeException(e);
  }
AuditStatus auditStatus = AuditStatus.getEnumByValue(new Byte(request.getParameter("auditStatus")));
  AuditType auditType = AuditType.getEnumByValue(request.getParameter("auditType"));
  BookInterface bookInterface = EliveUtilFactory.getBookInterfaceByInterf(request.getParameter("key"));
  int from = 0;
  int top = Utility.MAX_RETURN_ROWS;
  boolean hasCount = false;
  AuditQueryParam aqp=AuditFactory.getAuditQueryParam(time,auditStatus,
          auditType,bookInterface,from,top,hasCount);
  String filename = request.getParameter("filename");
  try {
   JPrincipal principal = WebUtil.getMJP(request.getSession());
   List<TotalAccount> tal = MBeanFactory.getAuditManagerMBean().queryAuditRecord(principal, aqp);
   HSSFWorkbook hwb = new HSSFWorkbook();
   HSSFSheet hst = hwb.createSheet();
   createSheets(tal,hst);
   response.setContentType("application/vnd.ms-excel");
   filename = new String(filename.getBytes(WebUtil.GBK_CHARSET),WebUtil.getDefaultCharsetName());
   response.setHeader("Content-disposition", "attachment; filename="+ filename + ".xls");
   OutputStream out = response.getOutputStream();
   hwb.write(out);
   out.close();
  } catch (Exception ex) {
   throw new ServletException("ExcelBackVisitMonthServlet", ex);
  }
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
*      response)
*/
protected void doPost(HttpServletRequest req,HttpServletResponse resp)
  throws ServletException, IOException {
  doGet(req, resp);
}

}