按日按月按季度导出数据（Excel）

来源：互联网发布：ubuntu nginx php fpm 编辑：程序博客网时间：2024/05/21 06:50

处理的jsp页面：

<%
//连接数据库
Connection con = null;
Statement stm = null;
ResultSet rs2 = null;
FileInputStream fis=null;
String path= new java.io.File(application.getRealPath("config")).getParent()+"\\";
      fis = new FileInputStream(new File(path+"config.properties"));
      Properties prop = new Properties();
      prop.load(fis);
      String driverName=prop.getProperty("driverName");
   String url=prop.getProperty("url");
   String user = prop.getProperty("user");
      String pasword = prop.getProperty("pasword");

       Class.forName(driverName).newInstance();
       con = DriverManager.getConnection(url, user, pasword);
       stm = con.createStatement();

//日期
        int eyear,emonth,eday;
  Calendar cal=Calendar.getInstance();
  eyear=cal.get(Calendar.YEAR);
  emonth=cal.get(Calendar.MONTH);
  eday=cal.get(Calendar.DATE);


       int flag=Integer.parseInt(request.getParameter("flag"));
       String start="";
    String end="";
    String sql="";
    int tempMonth=0;
    int tempDay=0;

switch(flag){

    case 1:
        start=Integer.toString(eyear)+"-"+Integer.toString(emonth)+"-"+Integer.toString(eday-1)+" 00:00:01";
        end=Integer.toString(eyear)+"-"+Integer.toString(emonth)+"-"+Integer.toString(eday-1)+" 23:59:59";
     sql="select t.orderno, t.productid, w.brand, t.price,t.Name, t.num, t.tel, t.dept, t.email, t.message, t.confirmstr,decode(t.isconfirm,'0','未确认','1','已确认',t.isconfirm) isconfirm, to_char(t.confirmdate,'yyyy-MM-dd') confirmdate,rownum rownum_ from EB_OrderForm t,wcmdocument w where t.confirmdate between to_date('"+start+"', 'yyyy-mm-dd HH24:MI:SS') and to_date('"+end+"', 'yyyy-mm-dd HH24:MI:SS') and w.docid=t.productid";
     rs2=stm.executeQuery(sql);
    break;
    case 2:
        start=Integer.toString(eyear)+"-"+Integer.toString(emonth-1)+"-1"+" 00:00:01";
        tempMonth=emonth-1;
         if(tempMonth==2)
      {
         tempDay=28;
      }

      if(tempMonth==4 || tempMonth==6 || tempMonth==9 || tempMonth==11)
      {
         tempDay=30;
      }
      else
      {
         tempDay=31;
      }
        end=Integer.toString(eyear)+"-"+Integer.toString(emonth-1)+"-"+Integer.toString(tempDay)+" 23:59:59";
     sql="select t.orderno, t.productid, w.brand, t.price,t.Name, t.num, t.tel, t.dept, t.email, t.message, t.confirmstr,decode(t.isconfirm,'0','未确认','1','已确认',t.isconfirm) isconfirm, to_char(t.confirmdate,'yyyy-MM-dd') confirmdate,rownum rownum_ from EB_OrderForm t,wcmdocument w where t.confirmdate between to_date('"+start+"', 'yyyy-mm-dd HH24:MI:SS') and to_date('"+end+"', 'yyyy-mm-dd HH24:MI:SS') and w.docid=t.productid";
     rs2=stm.executeQuery(sql);
    break;
    case 3:
        if(emonth>=1 && emonth<=3)
        {
            start=Integer.toString(eyear)+"-10-01 00:00:01";
            end=Integer.toString(eyear)+"-12-31 23:59:59";

        }

        if(emonth>=4 && emonth<=6)
        {
            end=Integer.toString(eyear)+"-03-31 23:59:59";

        }

        if(emonth>=7 && emonth<=9)
        {
            start=Integer.toString(eyear)+"-04-01 00:00:01";
            end=Integer.toString(eyear)+"-06-30 23:59:59";

        }

        if(emonth>=10 && emonth<=12)
        {
            start=Integer.toString(eyear)+"-07-01 00:00:01";
            end=Integer.toString(eyear)+"-09-30 23:59:59";
        }

        sql="select t.orderno, t.productid, w.brand, t.price,t.Name, t.num, t.tel, t.dept, t.email, t.message, t.confirmstr,decode(t.isconfirm,'0','未确认','1','已确认',t.isconfirm) isconfirm, to_char(t.confirmdate,'yyyy-MM-dd') confirmdate,rownum rownum_ from EB_OrderForm t,wcmdocument w where t.confirmdate between to_date('"+start+"', 'yyyy-mm-dd HH24:MI:SS') and to_date('"+end+"', 'yyyy-mm-dd HH24:MI:SS') and w.docid=t.productid";
     rs2=stm.executeQuery(sql);
    break;
}




        OutputStream os = response.getOutputStream();// 取得输出流
  response.reset();// 清空输出流
        response.setHeader("Content-disposition", "attachment; filename=orderPool.xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
        String tmptitle = "订单汇总"; // 标题
        WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称

  // 设置excel标题
  WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD,
                         false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
  WritableCellFormat wcfFC = new WritableCellFormat(wfont);
  wcfFC.setBackground(Colour.AQUA);
  wsheet.addCell(new Label(1, 0, tmptitle, wcfFC));
  wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD,
                     false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
  wcfFC = new WritableCellFormat(wfont);

  int i= 0;
      wsheet.addCell(new Label(0, i, "订单号"));
      wsheet.addCell(new Label(1, i, "商品名"));
      wsheet.addCell(new Label(2, i, "价格"));
      wsheet.addCell(new Label(3, i, "日期"));
      wsheet.addCell(new Label(4, i, "顾客姓名"));
      wsheet.addCell(new Label(5, i, "数量"));
      wsheet.addCell(new Label(6, i, "电话"));
      wsheet.addCell(new Label(7, i, "部门"));
      wsheet.addCell(new Label(8, i, "Email"));
      wsheet.addCell(new Label(9, i, "留言"));
  while(rs2!=null&&rs2.next()){
  i++;
      wsheet.addCell(new Label(0, i, rs2.getString("OrderNo")==null?"":rs2.getString("OrderNo")));
      wsheet.addCell(new Label(1, i, rs2.getString("brand")==null?"":rs2.getString("brand")));
      wsheet.addCell(new Label(2, i, rs2.getString("Price")==null?"":rs2.getString("Price")));
      wsheet.addCell(new Label(3, i, rs2.getString("ConfirmDate")==null?"":rs2.getString("ConfirmDate")));
      wsheet.addCell(new Label(4, i, rs2.getString("Name")==null?"":rs2.getString("Name")));
      wsheet.addCell(new Label(5, i, rs2.getString("Num")==null?"":rs2.getString("Num")));
      wsheet.addCell(new Label(6, i, rs2.getString("Tel")==null?"":rs2.getString("Tel")));
      wsheet.addCell(new Label(7, i, rs2.getString("Dept")==null?"":rs2.getString("Dept")));
      wsheet.addCell(new Label(8, i, rs2.getString("Email")==null?"":rs2.getString("Email")));
      wsheet.addCell(new Label(9, i, rs2.getString("Message")==null?"":rs2.getString("Message")));


  }
  // 主体内容生成结束
  wbook.write(); // 写入文件
  wbook.close();
  os.close(); // 关闭流
  out.clear();
  out = pageContext.pushBody();

%>