<%--
  Created by IntelliJ IDEA.
  User: mc
  Date: 2021/7/7
  Time: 15:12
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import=" java.io.BufferedInputStream" %>
<%@ page import=" java.io.BufferedOutputStream" %>
<%@ page import=" java.io.File" %>
<%@ page import=" java.io.FileInputStream" %>
<%@ page import=" java.io.FileOutputStream" %>
<%@ page import=" java.io.IOException" %>
<%@ page import=" java.io.InputStream" %>
<%@ page import=" java.io.OutputStream" %>
<%@ page import=" java.text.SimpleDateFormat" %>
<%@ page import=" javax.servlet.http.HttpServletResponse" %>
<%@ page import="com.censoft.common.base.Pub" %>
<%@ page import="com.censoft.common.db.ConnectionFactory" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.util.*" %>
<%@ page import="org.apache.poi.xssf.usermodel.*" %>
<%@ page import="org.apache.poi.ss.util.CellRangeAddress" %>
<%!
    private void download(String path, HttpServletResponse response) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            response.addHeader("Content-Disposition", "attachment;filename="+ new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
    public static String getTel(ConnectionFactory cf,Connection conn,String user_name) {
        String tel="";
        Vector userList = cf.doQuery(conn,"select tel from zb_user where user_name=? "
                ,null,new Object[]{user_name});
        if(userList!=null && userList.size()>0){
            Hashtable ht1 = (Hashtable) userList.get(0);
            tel = (String) ht1.get("tel");
        }
        return tel;
    }
%>
<%
    request.setCharacterEncoding("UTF-8");
    response.setCharacterEncoding("UTF-8");
    ConnectionFactory cf = new ConnectionFactory();
    Pub pub = new Pub();
    Connection conn = null;
    String year = pub.trimNull(request.getParameter("exportDate"));
    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    String fileName = sdf.format(new Date()) + ".xlsx";
    // 文件路径
    String filePath = fileName;
    try {
        conn = com.censoft.db.DBUtil.getConnection();
        StringBuffer sb = new StringBuffer();
        sb.append("select * from zb_user_gl where year=? ");
        sb.append(" order by order_num asc");
        Vector v_list = cf.doQuery(conn, sb.toString(), null, new Object[]{year});
        if (v_list == null) {
            System.out.println("查询出错"+cf.getMsg());
            return;
        }
        // 输出流
        OutputStream os = new FileOutputStream(filePath);
        // 工作区
        XSSFWorkbook wb = new XSSFWorkbook();
        // 工作区名
        XSSFSheet sheet = wb.createSheet(year+"值班统计");
        XSSFCellStyle style=wb.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//水平垂直
        XSSFCellStyle cs=wb.createCellStyle();
        cs.setWrapText(true);
        // 创建表头
        String values="日期,班种,姓名及电话";
        XSSFRow row = sheet.createRow(0);
        String[] value=values.split(",");
        for(int i=0;i<3;i++){
            XSSFCell row_0=row.createCell(i);
            row_0.setCellValue(value[i]);
            row_0.setCellStyle(style);
        }
        // 遍历数据装进table
        for (int i = 0; i < v_list.size(); i++) {
            // sheet.createRow(i+1) 因为表头是0
            Hashtable ht = (Hashtable) v_list.get(i);
            String user_name=(String)ht.get("zb_user");
            String[] user_names=user_name.split(",");

            XSSFRow rows = sheet.createRow(i + 1);

            String id = pub.trimNull((String) ht.get("id"));
            rows.createCell(0).setCellValue((String)ht.get("year"));
            rows.createCell(1).setCellValue((String)ht.get("zb_type"));
            String userTel="";
            for(int n=0;n<user_names.length;n++) {
                String telphone = getTel(cf, conn, user_names[n]);
                userTel=user_names[n]+"："+telphone+"\n"+userTel;
            }
            rows.createCell(2).setCellValue(userTel);
        }
        for(int i=1;i<=v_list.size();i=i+3){
            sheet.addMergedRegion(new CellRangeAddress(i,i+2,0,0));//合并单元格
        }
        wb.write(os);
        // 关闭输出流
        os.close();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
            }
        }
    }
    download(filePath, response);
%>