导入，导出20220506

最新推荐文章于 2024-10-16 10:13:45 发布
最新推荐文章于 2024-10-16 10:13:45 发布
阅读量231 收藏
点赞数
分类专栏： 笔记 文章标签： java java-ee 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47006244/article/details/124613822
版权

cotroller:

 /**
     * 手机上网导入
     *
     * @param file
     * @param time
     * @return
     * @throws Exception
     */

    @RequestMapping(value = "/importMobileOnelinTemplate", method = RequestMethod.POST, produces = "text/html;charset=utf-8")
    @ResponseBody
    public String importMobileOnelinTemplate(@RequestParam(value = "file",required = false) MultipartFile file,
                                 @RequestParam(value = "time",required = false) String time
    ) throws Exception {
        try{
            if(null==file){
                return Ajax.responseString(CST.RES_EXCEPTION,"请上传文件!");
            }
            return satisfactionReportService.importMobileOnelinTemplate(file,time);
        }catch (Exception e){
            logger.error(e.toString());
            return Ajax.responseString(CST.RES_EXCEPTION, "操作失败!");
        }
    }

service:

   /**
     * 手机上网导入明细
     * @param file file
     * @param time time
     * @return String
     */
    String importMobileOnelinTemplate(MultipartFile file, String time);

serviceImpl:

 /**
     * 手机上网导入
     *
     * @param file file
     * @param time time
     * @return
     */
    @Override
    public String importMobileOnelinTemplate(MultipartFile file, String time) {
        //解析文件内容
        //获取流对象和文件名
        InputStream inputStream = null;
        Connection connection=null;
        PreparedStatement ps = null;
        PreparedStatement deletePs = null;
        CallableStatement cstmt=null;
        long start = 0;
        try {
            inputStream = file.getInputStream();
            String filename = file.getOriginalFilename();
            Excel2PhoneOnelineInfo excel2PhoneOnelineInfo =new Excel2PhoneOnelineInfo();
            Workbook wb = excel2PhoneOnelineInfo.getWorkbook(inputStream, filename);
            List<MobileReportDetailBean> list = excel2PhoneOnelineInfo.parseExcel(wb);
            try {
                //测试 todo 存入mysql
                start = System.currentTimeMillis();
//                connection = DBUtilMySql.getConnection();
                connection = DBUtilMySql.getConnection();
                //1.设置为不自动提交数据
                connection.setAutoCommit(false);
                //先删除当前日期的数据（防止数据重复）
                StringBuilder deleteSql=new StringBuilder();
                deleteSql.append("delete from md_grp_survey_phone_sjsw_input_data where STAT_MONTH=?");
                deletePs=connection.prepareStatement(deleteSql.toString());
                deletePs.setInt(1,Integer.parseInt(time));
                deletePs.executeUpdate();
                //导入
                StringBuilder sql=new StringBuilder();
                sql.append("INSERT INTO `md_grp_survey_phone_sjsw_input_data`(`stat_month`, `user_id`, `city_name`, `A1`, `A2`, `A3`, `A4`, `A5`, `A6_1`, `A6_2`, `A6_3`, `A6_4`, `A6_5`, `A6_6`, `A6_7`, `A6_98`, `A6_98open`, `A7_1`, `A7_2`, " +
                        "`A7_3`, `A7_4`, `A7_98`, `A7_98open`, `A7_1_1`, `A7_1_2`, " +
                        "`A7_1_3`, `A7_1_4`, `A7_1_5`, `A7_1_98`, " +
                        "`A7_1_98open`, `A7_2_1`, `A7_2_2`, `A7_2_3`," +
                        " `A7_2_4`, `A7_2_5`, `A7_2_6`, `A7_2_7`, `A7_2_8`," +
                        " `A7_2_9`, `A7_2_98`, `A7_2_98open`, `A7_2_99`, `A7_3_1`," +
                        " `A7_3_2`, `A7_3_3`, `A7_3_4`, `A7_3_5`, `A7_3_6`, `A7_3_7`," +
                        " `A7_3_8`, `A7_3_9`, `A7_3_10`, `A7_3_98`, `A7_3_98open`, `A7_3_99`," +
                        " `A7_4_1`, `A7_4_2`, `A7_4_3`, `A7_4_4`, `A7_4_5`, `A7_4_6`, `A7_4_7`, `A7_4_8`," +
                        " `A7_4_98`, `A7_4_98open`, `A7_4_99`, `IS_5G_package`, `IS_5GKH`, `DY500M`) " +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                        "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
                        " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
                        " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                        "?, ?, ?, ?, ?, ?, ?, ?, ?)"
                );
                ps = connection.prepareStatement(sql.toString());
                for (int i = 0; i <list.size(); i++) {
                    ps.setInt(1,Integer.parseInt(time));
                    ps.setLong(2,list.get(i).getUserId());
                    ps.setString(3,list.get(i).getCityName());
                    ps.setString(4,list.get(i).getA1());
                    ps.setString(5,list.get(i).getA2());
                    ps.setString(6,list.get(i).getA3());
                    ps.setString(7,list.get(i).getA4());
                    ps.setString(8,list.get(i).getA5());
                    ps.setString(9,list.get(i).getA6_1());
                    ps.setString(10,list.get(i).getA6_2());
                    ps.setString(11,list.get(i).getA6_3());
                    ps.setString(12,list.get(i).getA6_4());
                    ps.setString(13,list.get(i).getA6_5());
                    ps.setString(14,list.get(i).getA6_6());
                    ps.setString(15,list.get(i).getA6_7());
                    ps.setString(16,list.get(i).getA6_98());
                    ps.setString(17,list.get(i).getA6_98open());
                    ps.setString(18,list.get(i).getA7_1());
                    ps.setString(19,list.get(i).getA7_2());
                    ps.setString(20,list.get(i).getA7_3());
                    ps.setString(21,list.get(i).getA7_4());
                    ps.setString(22,list.get(i).getA7_98());
                    ps.setString(23,list.get(i).getA7_98open());
                    ps.setString(24,list.get(i).getA7_1_1());
                    ps.setString(25,list.get(i).getA7_1_2());
                    ps.setString(26,list.get(i).getA7_1_3());
                    ps.setString(27,list.get(i).getA7_1_4());
                    ps.setString(28,list.get(i).getA7_1_5());
                    ps.setString(29,list.get(i).getA7_1_98());
                    ps.setString(30,list.get(i).getA7_1_98open());
                    ps.setString(31,list.get(i).getA7_2_1());
                    ps.setString(32,list.get(i).getA7_2_2());
                    ps.setString(33,list.get(i).getA7_2_3());
                    ps.setString(34,list.get(i).getA7_2_4());
                    ps.setString(35,list.get(i).getA7_2_5());
                    ps.setString(36,list.get(i).getA7_2_6());
                    ps.setString(37,list.get(i).getA7_2_7());
                    ps.setString(38,list.get(i).getA7_2_8());
                    ps.setString(39,list.get(i).getA7_2_9());
                    ps.setString(40,list.get(i).getA7_2_98());
                    ps.setString(41,list.get(i).getA7_2_98open());
                    ps.setString(42,list.get(i).getA7_2_99());
                    ps.setString(43,list.get(i).getA7_3_1());
                    ps.setString(44,list.get(i).getA7_3_2());
                    ps.setString(45,list.get(i).getA7_3_3());
                    ps.setString(46,list.get(i).getA7_3_4());
                    ps.setString(47,list.get(i).getA7_3_5());
                    ps.setString(48,list.get(i).getA7_3_6());
                    ps.setString(49,list.get(i).getA7_3_7());
                    ps.setString(50,list.get(i).getA7_3_8());
                    ps.setString(51,list.get(i).getA7_3_9());
                    ps.setString(52,list.get(i).getA7_3_10());
                    ps.setString(53,list.get(i).getA7_3_98());
                    ps.setString(54,list.get(i).getA7_3_98open());
                    ps.setString(55,list.get(i).getA7_3_99());
                    ps.setString(56,list.get(i).getA7_4_1());
                    ps.setString(57,list.get(i).getA7_4_2());
                    ps.setString(58,list.get(i).getA7_4_3());
                    ps.setString(59,list.get(i).getA7_4_4());
                    ps.setString(60,list.get(i).getA7_4_5());
                    ps.setString(61,list.get(i).getA7_4_6());
                    ps.setString(62,list.get(i).getA7_4_7());
                    ps.setString(63,list.get(i).getA7_4_8());
                    ps.setString(64,list.get(i).getA7_4_98());
                    ps.setString(65,list.get(i).getA7_4_98open());
                    ps.setString(66,list.get(i).getA7_4_99());
                    ps.setString(67,list.get(i).getIs5gPackage());
                    ps.setString(68,list.get(i).getIs5gkh());
                    ps.setString(69,list.get(i).getDY500M());
                    ps.addBatch();

                }
                //2.执行
                ps.executeBatch();

                //2.提交数据
                connection.commit();
            } catch (SQLException throwables) {
                //回滚
                connection.rollback();
                throwables.printStackTrace();
                return Ajax.responseString(CST.RES_LOGIC_ERROR, "导入失败");
            }

            long end = System.currentTimeMillis();
            System.out.println("花费的时间为：" + (end - start));
            ThreadTaskUtils.run(()->{
                //todo 异步
                Connection conn=null;
                CallableStatement ctmt=null;
                try {
                    conn = DBUtilMySql.getConnection();
                    //生产 todo 存入gbase 调用存储过程
                    String sqlCall = "{call sp_mkt_dtl_grp_survey_phone_sjsw_mon(?,?,?)}";
                    ctmt = conn.prepareCall(sqlCall);
                    ctmt.setInt("in_stat_month", Integer.parseInt(time));
                    ctmt.execute();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                    logger.error("存储过程调用失败",throwables);
                }finally {
                    try {
                        if(null != conn){
                            conn.close();
                        }
                        if (null != ctmt){
                            ctmt.close();
                        }
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            });

            return Ajax.responseString(CST.RES_SECCESS, "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("导入失败");
        }finally {
            DBUtilMySql.close(connection, ps, null);
        }
    }

类：

/**
 * 手机上网导入明细
 *
 */
public class Excel2PhoneOnelineInfo extends Excel2Object {
    @Override
    public MobileReportDetailBean convertRowToData(Row row) throws ParseException {
        MobileReportDetailBean mobileReportDetail = new MobileReportDetailBean();
        Cell cell;
        //当前列号，默认值0
        int cellNum = 0;
        //获取最大列号，
        row.getPhysicalNumberOfCells();
        //通过行获取列 获取第一列
        cell = row.getCell(cellNum++);
        String a = convertCellValueToString(cell);
        //通过行获取列 获取第二列
        cell = row.getCell(cellNum++);
        String b = convertCellValueToString(cell);
        //通过行获取列 获取第三列
        cell = row.getCell(cellNum++);
        String c = convertCellValueToString(cell);
        //通过行获取列 获取第四列
        cell = row.getCell(cellNum++);
        String d = convertCellValueToString(cell);
        //通过行获取列 获取第五列
        cell = row.getCell(cellNum++);
        String e = convertCellValueToString(cell);
        //通过行获取列 获取第六列
        cell = row.getCell(cellNum++);
        String f = convertCellValueToString(cell);
        //通过行获取列 获取第七列
        cell = row.getCell(cellNum++);
        String g = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String h = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String i = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String j = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String k = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String m = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String n = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String o = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String p = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String q = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String r = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String s = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String t = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String u = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String v = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String w = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String x = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String y = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String z = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String aa = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ab = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ac = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ad = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ae = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String af = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ag = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ah = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qw = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qe = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qr = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qt = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qy = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qu = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qi = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qo = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String qp = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wq = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String we = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wr = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wt = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wy = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wu = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wi = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wo = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wp = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wa = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String ws = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wd = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wf = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wg = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wh = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wj = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wk = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wl = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wz = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wx = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wc = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wv = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String wb = convertCellValueToString(cell);


        cell = row.getCell(cellNum++);
        String fd = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String fg = convertCellValueToString(cell);
        //通过行获取列 获取第八列
        cell = row.getCell(cellNum++);
        String fh = convertCellValueToString(cell);



//        mobileReportDetail.setStatMonth(null== a ? null : Integer.parseInt(a));
        mobileReportDetail.setUserId(null== a ? null : Long.parseLong(a));
        if (!b.contains("市")) {
            b=b+"市";
        }
        mobileReportDetail.setCityName(b);
        mobileReportDetail.setA1(c);
        mobileReportDetail.setA2(d);
        mobileReportDetail.setA3(e);
        mobileReportDetail.setA4(f);
        mobileReportDetail.setA5(g);
        mobileReportDetail.setA6_1(h);
        mobileReportDetail.setA6_2(i);
        mobileReportDetail.setA6_3(j);
        mobileReportDetail.setA6_4(k);
        mobileReportDetail.setA6_5(m);
        mobileReportDetail.setA6_6(n);
        mobileReportDetail.setA6_7(o);
        mobileReportDetail.setA6_98(p);
        mobileReportDetail.setA6_98open(q);
        mobileReportDetail.setA7_1(r);
        mobileReportDetail.setA7_2(s);
        mobileReportDetail.setA7_3(t);
        mobileReportDetail.setA7_4(u);
        mobileReportDetail.setA7_98(v);
        mobileReportDetail.setA7_98open(w);
        mobileReportDetail.setA7_1_1(x);
        mobileReportDetail.setA7_1_2(y);
        mobileReportDetail.setA7_1_3(z);
        mobileReportDetail.setA7_1_4(aa);
        mobileReportDetail.setA7_1_5(ab);
        mobileReportDetail.setA7_1_98(ac);
        mobileReportDetail.setA7_1_98open(ad);
        mobileReportDetail.setA7_2_1(ae);
        mobileReportDetail.setA7_2_2(af);
        mobileReportDetail.setA7_2_3(ag);
        mobileReportDetail.setA7_2_4(ah);
        mobileReportDetail.setA7_2_5(qw);
        mobileReportDetail.setA7_2_6(qe);
        mobileReportDetail.setA7_2_7(qr);
        mobileReportDetail.setA7_2_8(qt);
        mobileReportDetail.setA7_2_9(qy);
        mobileReportDetail.setA7_2_98(qu);
        mobileReportDetail.setA7_2_98open(qi);
        mobileReportDetail.setA7_2_99(qo);
        mobileReportDetail.setA7_3_1(qp);
        mobileReportDetail.setA7_3_2(wq);
        mobileReportDetail.setA7_3_3(we);
        mobileReportDetail.setA7_3_4(wr);
        mobileReportDetail.setA7_3_5(wt);
        mobileReportDetail.setA7_3_6(wy);
        mobileReportDetail.setA7_3_7(wu);
        mobileReportDetail.setA7_3_8(wi);
        mobileReportDetail.setA7_3_9(wo);
        mobileReportDetail.setA7_3_10(wp);
        mobileReportDetail.setA7_3_98(wa);
        mobileReportDetail.setA7_3_98open(ws);
        mobileReportDetail.setA7_3_99(wd);
        mobileReportDetail.setA7_4_1(wf);
        mobileReportDetail.setA7_4_2(wg);
        mobileReportDetail.setA7_4_3(wh);
        mobileReportDetail.setA7_4_4(wj);
        mobileReportDetail.setA7_4_5(wk);
        mobileReportDetail.setA7_4_6(wl);
        mobileReportDetail.setA7_4_7(wz);
        mobileReportDetail.setA7_4_8(wx);
        mobileReportDetail.setA7_4_98(wc);
        mobileReportDetail.setA7_4_98open(wv);
        mobileReportDetail.setA7_4_99(wb);
        mobileReportDetail.setIs5gkh(fd);
        mobileReportDetail.setIs5gkh(fg);
        mobileReportDetail.setDY500M(fh);
return mobileReportDetail;
    }
}

实体类：

/**
 * 手机导出明细实体
 */
public class MobileReportDetailBean implements Serializable {
    private Integer statMonth;
    private Long userId;
    private String parentnameL1;
    private String parentnameL2;
    private String parentnameL3;
    private Integer isInter;
    private String is5gPackage;
    private String is5gkh;
    private String a1;
    private String a2;
    private String a3;
    private String a4;
    private String a5;
    private String a6_1;
    private String a6_2;
    private String a6_3;
    private String a6_4;
    private String a6_5;
    private String a6_6;
    private String a6_7;
    private String a6_98;
    private String a6_98open;
    private String a7_1;
    private String a7_2;
    private String a7_3;
    private String a7_4;
    private String a7_98;
    private String a7_98open;
    private String a7_1_1;
    private String a7_1_2;
    private String a7_1_3;
    private String a7_1_4;
    private String a7_1_5;
    private String a7_1_98;
    private String a7_1_98open;
    private String a7_2_1;
    private String a7_2_2;
    private String a7_2_3;
    private String a7_2_4;
    private String a7_2_5;
    private String a7_2_6;
    private String a7_2_7;
    private String a7_2_8;
    private String a7_2_9;
    private String a7_2_98;
    private String a7_2_98open;
    private String a7_2_99;
    private String a7_3_1;
    private String a7_3_2;
    private String a7_3_3;
    private String a7_3_4;
    private String a7_3_5;
    private String a7_3_6;
    private String a7_3_7;
    private String a7_3_8;
    private String a7_3_9;
    private String a7_3_10;
    private String a7_3_98;
    private String a7_3_98open;
    private String a7_3_99;
    private String a7_4_1;
    private String a7_4_2;
    private String a7_4_3;
    private String a7_4_4;
    private String a7_4_5;
    private String a7_4_6;
    private String a7_4_7;
    private String a7_4_8;
    private String a7_4_98;
    private String a7_4_98open;
    private String a7_4_99;
    private String DY500M;
    private String cityName;

    public Integer getStatMonth() {
        return statMonth;
    }

    public void setStatMonth(Integer statMonth) {
        this.statMonth = statMonth;
    }

    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getParentnameL1() {
        return parentnameL1;
    }

    public void setParentnameL1(String parentnameL1) {
        this.parentnameL1 = parentnameL1;
    }

    public String getParentnameL2() {
        return parentnameL2;
    }

    public void setParentnameL2(String parentnameL2) {
        this.parentnameL2 = parentnameL2;
    }

    public String getParentnameL3() {
        return parentnameL3;
    }

    public void setParentnameL3(String parentnameL3) {
        this.parentnameL3 = parentnameL3;
    }

    public Integer getIsInter() {
        return isInter;
    }

    public void setIsInter(Integer isInter) {
        this.isInter = isInter;
    }

    public String getIs5gPackage() {
        return is5gPackage;
    }

    public void setIs5gPackage(String is5gPackage) {
        this.is5gPackage = is5gPackage;
    }

    public String getIs5gkh() {
        return is5gkh;
    }

    public void setIs5gkh(String is5gkh) {
        this.is5gkh = is5gkh;
    }

    public String getA1() {
        return a1;
    }

    public void setA1(String a1) {
        this.a1 = a1;
    }

    public String getA2() {
        return a2;
    }

    public void setA2(String a2) {
        this.a2 = a2;
    }

    public String getA3() {
        return a3;
    }

    public void setA3(String a3) {
        this.a3 = a3;
    }

    public String getA4() {
        return a4;
    }

    public void setA4(String a4) {
        this.a4 = a4;
    }

    public String getA5() {
        return a5;
    }

    public void setA5(String a5) {
        this.a5 = a5;
    }

    public String getA6_1() {
        return a6_1;
    }

    public void setA6_1(String a6_1) {
        this.a6_1 = a6_1;
    }

    public String getA6_2() {
        return a6_2;
    }

    public void setA6_2(String a6_2) {
        this.a6_2 = a6_2;
    }

    public String getA6_3() {
        return a6_3;
    }

    public void setA6_3(String a6_3) {
        this.a6_3 = a6_3;
    }

    public String getA6_4() {
        return a6_4;
    }

    public void setA6_4(String a6_4) {
        this.a6_4 = a6_4;
    }

    public String getA6_5() {
        return a6_5;
    }

    public void setA6_5(String a6_5) {
        this.a6_5 = a6_5;
    }

    public String getA6_6() {
        return a6_6;
    }

    public void setA6_6(String a6_6) {
        this.a6_6 = a6_6;
    }

    public String getA6_7() {
        return a6_7;
    }

    public void setA6_7(String a6_7) {
        this.a6_7 = a6_7;
    }

    public String getA6_98() {
        return a6_98;
    }

    public void setA6_98(String a6_98) {
        this.a6_98 = a6_98;
    }

    public String getA6_98open() {
        return a6_98open;
    }

    public void setA6_98open(String a6_98open) {
        this.a6_98open = a6_98open;
    }

    public String getA7_1() {
        return a7_1;
    }

    public void setA7_1(String a7_1) {
        this.a7_1 = a7_1;
    }

    public String getA7_2() {
        return a7_2;
    }

    public void setA7_2(String a7_2) {
        this.a7_2 = a7_2;
    }

    public String getA7_3() {
        return a7_3;
    }

    public void setA7_3(String a7_3) {
        this.a7_3 = a7_3;
    }

    public String getA7_4() {
        return a7_4;
    }

    public void setA7_4(String a7_4) {
        this.a7_4 = a7_4;
    }

    public String getA7_98() {
        return a7_98;
    }

    public void setA7_98(String a7_98) {
        this.a7_98 = a7_98;
    }

    public String getA7_98open() {
        return a7_98open;
    }

    public void setA7_98open(String a7_98open) {
        this.a7_98open = a7_98open;
    }

    public String getA7_1_1() {
        return a7_1_1;
    }

    public void setA7_1_1(String a7_1_1) {
        this.a7_1_1 = a7_1_1;
    }

    public String getA7_1_2() {
        return a7_1_2;
    }

    public void setA7_1_2(String a7_1_2) {
        this.a7_1_2 = a7_1_2;
    }

    public String getA7_1_3() {
        return a7_1_3;
    }

    public void setA7_1_3(String a7_1_3) {
        this.a7_1_3 = a7_1_3;
    }

    public String getA7_1_4() {
        return a7_1_4;
    }

    public void setA7_1_4(String a7_1_4) {
        this.a7_1_4 = a7_1_4;
    }

    public String getA7_1_5() {
        return a7_1_5;
    }

    public void setA7_1_5(String a7_1_5) {
        this.a7_1_5 = a7_1_5;
    }

    public String getA7_1_98() {
        return a7_1_98;
    }

    public void setA7_1_98(String a7_1_98) {
        this.a7_1_98 = a7_1_98;
    }

    public String getA7_1_98open() {
        return a7_1_98open;
    }

    public void setA7_1_98open(String a7_1_98open) {
        this.a7_1_98open = a7_1_98open;
    }

    public String getA7_2_1() {
        return a7_2_1;
    }

    public void setA7_2_1(String a7_2_1) {
        this.a7_2_1 = a7_2_1;
    }

    public String getA7_2_2() {
        return a7_2_2;
    }

    public void setA7_2_2(String a7_2_2) {
        this.a7_2_2 = a7_2_2;
    }

    public String getA7_2_3() {
        return a7_2_3;
    }

    public void setA7_2_3(String a7_2_3) {
        this.a7_2_3 = a7_2_3;
    }

    public String getA7_2_4() {
        return a7_2_4;
    }

    public void setA7_2_4(String a7_2_4) {
        this.a7_2_4 = a7_2_4;
    }

    public String getA7_2_5() {
        return a7_2_5;
    }

    public void setA7_2_5(String a7_2_5) {
        this.a7_2_5 = a7_2_5;
    }

    public String getA7_2_6() {
        return a7_2_6;
    }

    public void setA7_2_6(String a7_2_6) {
        this.a7_2_6 = a7_2_6;
    }

    public String getA7_2_7() {
        return a7_2_7;
    }

    public void setA7_2_7(String a7_2_7) {
        this.a7_2_7 = a7_2_7;
    }

    public String getA7_2_8() {
        return a7_2_8;
    }

    public void setA7_2_8(String a7_2_8) {
        this.a7_2_8 = a7_2_8;
    }

    public String getA7_2_9() {
        return a7_2_9;
    }

    public void setA7_2_9(String a7_2_9) {
        this.a7_2_9 = a7_2_9;
    }

    public String getA7_2_98() {
        return a7_2_98;
    }

    public void setA7_2_98(String a7_2_98) {
        this.a7_2_98 = a7_2_98;
    }

    public String getA7_2_98open() {
        return a7_2_98open;
    }

    public void setA7_2_98open(String a7_2_98open) {
        this.a7_2_98open = a7_2_98open;
    }

    public String getA7_2_99() {
        return a7_2_99;
    }

    public void setA7_2_99(String a7_2_99) {
        this.a7_2_99 = a7_2_99;
    }

    public String getA7_3_1() {
        return a7_3_1;
    }

    public void setA7_3_1(String a7_3_1) {
        this.a7_3_1 = a7_3_1;
    }

    public String getA7_3_2() {
        return a7_3_2;
    }

    public void setA7_3_2(String a7_3_2) {
        this.a7_3_2 = a7_3_2;
    }

    public String getA7_3_3() {
        return a7_3_3;
    }

    public void setA7_3_3(String a7_3_3) {
        this.a7_3_3 = a7_3_3;
    }

    public String getA7_3_4() {
        return a7_3_4;
    }

    public void setA7_3_4(String a7_3_4) {
        this.a7_3_4 = a7_3_4;
    }

    public String getA7_3_5() {
        return a7_3_5;
    }

    public void setA7_3_5(String a7_3_5) {
        this.a7_3_5 = a7_3_5;
    }

    public String getA7_3_6() {
        return a7_3_6;
    }

    public void setA7_3_6(String a7_3_6) {
        this.a7_3_6 = a7_3_6;
    }

    public String getA7_3_7() {
        return a7_3_7;
    }

    public void setA7_3_7(String a7_3_7) {
        this.a7_3_7 = a7_3_7;
    }

    public String getA7_3_8() {
        return a7_3_8;
    }

    public void setA7_3_8(String a7_3_8) {
        this.a7_3_8 = a7_3_8;
    }

    public String getA7_3_9() {
        return a7_3_9;
    }

    public void setA7_3_9(String a7_3_9) {
        this.a7_3_9 = a7_3_9;
    }

    public String getA7_3_10() {
        return a7_3_10;
    }

    public void setA7_3_10(String a7_3_10) {
        this.a7_3_10 = a7_3_10;
    }

    public String getA7_3_98() {
        return a7_3_98;
    }

    public void setA7_3_98(String a7_3_98) {
        this.a7_3_98 = a7_3_98;
    }

    public String getA7_3_98open() {
        return a7_3_98open;
    }

    public void setA7_3_98open(String a7_3_98open) {
        this.a7_3_98open = a7_3_98open;
    }

    public String getA7_3_99() {
        return a7_3_99;
    }

    public void setA7_3_99(String a7_3_99) {
        this.a7_3_99 = a7_3_99;
    }

    public String getA7_4_1() {
        return a7_4_1;
    }

    public void setA7_4_1(String a7_4_1) {
        this.a7_4_1 = a7_4_1;
    }

    public String getA7_4_2() {
        return a7_4_2;
    }

    public void setA7_4_2(String a7_4_2) {
        this.a7_4_2 = a7_4_2;
    }

    public String getA7_4_3() {
        return a7_4_3;
    }

    public void setA7_4_3(String a7_4_3) {
        this.a7_4_3 = a7_4_3;
    }

    public String getA7_4_4() {
        return a7_4_4;
    }

    public void setA7_4_4(String a7_4_4) {
        this.a7_4_4 = a7_4_4;
    }

    public String getA7_4_5() {
        return a7_4_5;
    }

    public void setA7_4_5(String a7_4_5) {
        this.a7_4_5 = a7_4_5;
    }

    public String getA7_4_6() {
        return a7_4_6;
    }

    public void setA7_4_6(String a7_4_6) {
        this.a7_4_6 = a7_4_6;
    }

    public String getA7_4_7() {
        return a7_4_7;
    }

    public void setA7_4_7(String a7_4_7) {
        this.a7_4_7 = a7_4_7;
    }

    public String getA7_4_8() {
        return a7_4_8;
    }

    public void setA7_4_8(String a7_4_8) {
        this.a7_4_8 = a7_4_8;
    }

    public String getA7_4_98() {
        return a7_4_98;
    }

    public void setA7_4_98(String a7_4_98) {
        this.a7_4_98 = a7_4_98;
    }

    public String getA7_4_98open() {
        return a7_4_98open;
    }

    public void setA7_4_98open(String a7_4_98open) {
        this.a7_4_98open = a7_4_98open;
    }

    public String getA7_4_99() {
        return a7_4_99;
    }

    public void setA7_4_99(String a7_4_99) {
        this.a7_4_99 = a7_4_99;
    }

    public String getDY500M() {
        return DY500M;
    }

    public void setDY500M(String DY500M) {
        this.DY500M = DY500M;
    }

    public String getCityName() {
        return cityName;
    }

    public void setCityName(String cityName) {
        this.cityName = cityName;
    }
}

工具类：


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;


/**
 * @author 陈浩
 */
public abstract class Excel2Object<T> {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    private static Logger log = LoggerFactory.getLogger(Excel2Object.class);

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {
        Workbook workbook = null;
        if (fileName.endsWith(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileName.endsWith(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 读取Excel文件内容
     *
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public List<T> readExcel(String fileName) {

        Workbook workbook = null;

        FileInputStream inputStream = null;

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            // 读取excel中的数据
            List<T> resultDataList = parseExcel(workbook);

            return resultDataList;
        } catch (Exception e) {
            log.info("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {

                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.info("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    public List<T> parseExcel(Workbook workbook) {
        List<T> resultDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                log.info("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 1;
            int rowEnd = sheet.getPhysicalNumberOfRows();
            for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (isRowEmpty(row)) {
                    continue;
                }
                if (null == row) {
                    continue;
                }
                //读取当前行的数据，该方法需要重写
                T resultData = null;
                try {
                    resultData = convertRowToData(row);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if (null == resultData) {
                    log.info("第 " + row.getRowNum() + "行数据不合法，已忽略！");
                    continue;
                }
                resultDataList.add(resultData);
            }
        }

        return resultDataList;
    }

    private boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    public static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case Cell.CELL_TYPE_STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case Cell.CELL_TYPE_BLANK:     // 空值
                break;
            case Cell.CELL_TYPE_FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

    /**
     * 为了灵活使用该工具类，抽取泛型来完成
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    public abstract T convertRowToData(Row row) throws ParseException;
}

~~ ~~

导出

**
导出controller：

  /**
     * 手机上网满导出明细
     *
     * @param time time
     * @param city city
     * @param type type
     * @param response response
     * @throws Exception Exception
     */
    @RequestMapping(value = "/exportMobileSatisfactionInfo", method = RequestMethod.POST, produces = "text/html;charset=utf-8")
    @ResponseBody
    public void exportMobileSatisfactionInfo(@RequestParam(value = "time", required = true) String time,
                              @RequestParam(value = "city", required = true) String city,
                              @RequestParam(value = "type", required = true) String type,
                              HttpServletResponse response) throws Exception {
        try{
            satisfactionReportService.exportMobileSatisfactionInfo(time,city,type,response);
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }

导出service：

    /**
     * 手机导出明细
     *
     * @param time time
     * @param city city
     * @param type type
     * @param response response
     */
    void exportMobileSatisfactionInfo(String time, String city, String type, HttpServletResponse response);

导出serviceImpl:

/**
     * 手机导出明细
     *
     * @param time time
     * @param city city
     * @param type type
     * @param response response
     */
    @Override
    public void exportMobileSatisfactionInfo(String time, String city, String type, HttpServletResponse response) {
        try {
            List<MobileReportDetailBean> list = this.getMobileReportDetail(time, city, type);
            String tableName = "导出手机满意度明细报表.xlsx";
            String[] headArr = {"测评时间(格式说明:年月)",
                    "USER-ID",
                    "地市",
                    "区县",
                    "网格",
                    "是否宽带用户(是 1 否 0)",
                    "是否5G套餐",
                    "是否5G客户",
                    "A6_1",
                    "A6_2",
                    "A6_3",
                    "A6_4",
                    "A6_5",
                    "A6_6",
                    "A6_7",
                    "A6_98",
                    "A6_98open",
                    "A7_1",
                    "A7_2",
                    "A7_3",
                    "A7_4",
                    "A7_98",
                    "A7_98open",
                    "A7_1_1",
                    "A7_1_2",
                    "A7_1_3",
                    "A7_1_4",
                    "A7_1_5",
                    "A7_1_98",
                    "A7_1_98open",
                    "A7_2_1",
                    "A7_2_2",
                    "A7_2_3",
                    "A7_2_4",
                    "A7_2_5",
                    "A7_2_6",
                    "A7_2_7",
                    "A7_2_8",
                    "A7_2_9",
                    "A7_2_98",
                    "A7_2_98open",
                    "A7_2_99",
                    "A7_3_1",
                    "A7_3_2",
                    "A7_3_3",
                    "A7_3_4",
                    "A7_3_5",
                    "A7_3_6",
                    "A7_3_7",
                    "A7_3_8",
                    "A7_3_9",
                    "A7_3_10",
                    "A7_3_98",
                    "A7_3_98open",
                    "A7_3_99",
                    "A7_4_1",
                    "A7_4_2",
                    "A7_4_3",
                    "A7_4_4",
                    "A7_4_5",
                    "A7_4_6",
                    "A7_4_7",
                    "A7_4_8",
                    "A7_4_98",
                    "A7_4_98open",
                    "A7_4_99"
            };

            List<String> listNew = new ArrayList<String>();

            for (MobileReportDetailBean s : list) {
                StringBuilder stringBuilder=new StringBuilder();
                stringBuilder.append(s.getStatMonth()).append(",")
                        .append(s.getUserId()).append(",")
                        .append(s.getParentnameL1()).append(",")
                        .append(s.getParentnameL2()).append(",")
                        .append(s.getParentnameL3()).append(",")
                        .append(s.getIsInter()).append(",")
                        .append(s.getIs5gPackage()).append(",")
                        .append(s.getIs5gkh()).append(",")
                        .append(s.getA6_1()).append(",")
                        .append(s.getA6_2()).append(",")
                        .append(s.getA6_3()).append(",")
                        .append(s.getA6_4()).append(",")
                        .append(s.getA6_5()).append(",")
                        .append(s.getA6_6()).append(",")
                        .append(s.getA6_7()).append(",")
                        .append(s.getA6_98()).append(",")
                        .append(s.getA6_98open()).append(",")
                        .append(s.getA7_1()).append(",")
                        .append(s.getA7_2()).append(",")
                        .append(s.getA7_3()).append(",")
                        .append(s.getA7_4()).append(",")
                        .append(s.getA7_98()).append(",")
                        .append(s.getA7_98open()).append(",")
                        .append(s.getA7_1_1()).append(",")
                        .append(s.getA7_1_2()).append(",")
                        .append(s.getA7_1_3()).append(",")
                        .append(s.getA7_1_4()).append(",")
                        .append(s.getA7_1_5()).append(",")
                        .append(s.getA7_1_98()).append(",")
                        .append(s.getA7_1_98open()).append(",")
                        .append(s.getA7_2_1()).append(",")
                        .append(s.getA7_2_2()).append(",")
                        .append(s.getA7_2_3()).append(",")
                        .append(s.getA7_2_4()).append(",")
                        .append(s.getA7_2_5()).append(",")
                        .append(s.getA7_2_6()).append(",")
                        .append(s.getA7_2_7()).append(",")
                        .append(s.getA7_2_8()).append(",")
                        .append(s.getA7_2_98()).append(",")
                        .append(s.getA7_2_98open()).append(",")
                        .append(s.getA7_2_99()).append(",")
                        .append(s.getA7_3_1()).append(",")
                        .append(s.getA7_3_2()).append(",")
                        .append(s.getA7_3_3()).append(",")
                        .append(s.getA7_3_4()).append(",")
                        .append(s.getA7_3_5()).append(",")
                        .append(s.getA7_3_6()).append(",")
                        .append(s.getA7_3_7()).append(",")
                        .append(s.getA7_3_8()).append(",")
                        .append(s.getA7_3_9()).append(",")
                        .append(s.getA7_3_98()).append(",")
                        .append(s.getA7_3_98open()).append(",")
                        .append(s.getA7_3_9()).append(",")
                        .append(s.getA7_3_99()).append(",").append(s.getA7_4_1()).append(",").append(s.getA7_4_2()).append(",").append(s.getA7_4_3())
                        .append(s.getA7_4_4())
                        .append(s.getA7_4_5())
                        .append(s.getA7_4_6())
                        .append(s.getA7_4_7())
                        .append(s.getA7_4_8())
                        .append(s.getA7_4_98())
                        .append(s.getA7_4_98open())
                        .append(s.getA7_4_99());

                listNew.add(stringBuilder.toString());
            }
            XSSFWorkbook wb = this.outExcel(tableName, headArr, listNew);
            OutputStream os = response.getOutputStream();
            tableName = URLEncoder.encode(tableName, "UTF-8");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-disposition", "attachment;filename=" + tableName);
            response.setHeader("Pragma", "No-cache");
            wb.write(os);
            os.close();
        } catch (IOException e) {
            logger.error(e.toString());
            e.printStackTrace();
        }

    }


    private XSSFWorkbook outExcel(String tableName, String[] headArr, List<String> dataList){
        int cellWidth = 2340;//单元格宽度
        short cellHeight = (short)260;//单元格高度
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFRow row = null;
        XSSFCell cell = null;
        XSSFSheet sheet= wb.createSheet(tableName);
        row = sheet.createRow(0);
        row.setHeight(cellHeight);//设置单元格高度
        for(int i = 0; i < headArr.length; i++){
            cell = row.createCell(i);
            sheet.setColumnWidth(i, cellWidth);//设置sheet单元格宽度
            cell.setCellValue(headArr[i]);
        }
        for(int i = 0; i < dataList.size(); i++){
            String[] dataArr = dataList.get(i).split(",");
            row = sheet.createRow(i + 1);
            row.setHeight(cellHeight);//设置单元格高度
            for(int j = 0; j < dataArr.length; j++){
                cell = row.createCell(j);
                sheet.setColumnWidth(j, cellWidth);//设置sheet单元格宽度
                cell.setCellValue(dataArr[j]);
            }
        }
        return wb;
    }

   // 获取手机报表明细数据
    private List<MobileReportDetailBean> getMobileReportDetail(String time, String city, String type) {
        List<MobileReportDetailBean>list=new ArrayList<>();
        Connection connection=null;
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        CallableStatement cstmt=null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        try {
            long start = System.currentTimeMillis();
//            connection = DBUtilMySql.getConnection();
            connection = DBUtilMySql.getConnection();
            //1.设置为不自动提交数据
            StringBuilder sql=new StringBuilder();
            StringBuilder stringBuilder = new StringBuilder();
            sql.append("select * from mkt_dtl_grp_survey_phone_sjsw_mon where STAT_MONTH = ?");
            ps = connection.prepareStatement(sql.toString());
            ps.setInt(1, Integer.parseInt(time));

            stringBuilder.append(" select orgalevel from cas_crm_mm_org_rel_day where orgaid = ? and stat_date = (select max(stat_date) from cas_crm_mm_org_rel_day )");
            ps2 = connection.prepareStatement(stringBuilder.toString());
            ps2.setInt(1, Integer.parseInt(city));
            rs2 = ps2.executeQuery();
            String level = null;
            while (rs2.next()) {
                 level = rs2.getString("orgalevel");
            }
            int i = Integer.parseInt(level) + 1;
            String s = String.valueOf(i);
            if (!city.equals("2000250")) {
                sql.append(" and PARENTID_L"+s +"=?");
                ps = connection.prepareStatement(sql.toString());
                ps.setInt(1, Integer.parseInt(time));
                ps.setInt(2, Integer.parseInt(city));
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                MobileReportDetailBean mobileReportDetailBean = new MobileReportDetailBean();
                mobileReportDetailBean.setA1(rs.getString("A1"));
                mobileReportDetailBean.setA2(rs.getString("A2"));
                mobileReportDetailBean.setA3(rs.getString("A3"));
                mobileReportDetailBean.setA4(rs.getString("A4"));
                mobileReportDetailBean.setA5(rs.getString("A5"));
                mobileReportDetailBean.setA6_1(rs.getString("A6_1"));
                mobileReportDetailBean.setA6_2(rs.getString("A6_2"));
                mobileReportDetailBean.setA6_3(rs.getString("A6_3"));
                mobileReportDetailBean.setA6_4(rs.getString("A6_4"));
                mobileReportDetailBean.setA6_5(rs.getString("A6_5"));
                mobileReportDetailBean.setA6_6(rs.getString("A6_6"));
                mobileReportDetailBean.setA6_7(rs.getString("A6_7"));
                mobileReportDetailBean.setA6_98(rs.getString("A6_98"));
                mobileReportDetailBean.setA6_98open(rs.getString("A6_98open"));
                mobileReportDetailBean.setA7_1(rs.getString("A7_1"));
                mobileReportDetailBean.setA7_2(rs.getString("A7_2"));
                mobileReportDetailBean.setA7_3(rs.getString("A7_3"));
                mobileReportDetailBean.setA7_4(rs.getString("A7_4"));
                mobileReportDetailBean.setA7_98(rs.getString("A7_98"));
                mobileReportDetailBean.setA7_98open(rs.getString("A7_98open"));
                mobileReportDetailBean.setA7_1_1(rs.getString("A7_1_1"));
                mobileReportDetailBean.setA7_1_2(rs.getString("A7_1_2"));
                mobileReportDetailBean.setA7_1_3(rs.getString("A7_1_3"));
                mobileReportDetailBean.setA7_1_4(rs.getString("A7_1_4"));
                mobileReportDetailBean.setA7_1_5(rs.getString("A7_1_5"));
                mobileReportDetailBean.setA7_1_98(rs.getString("A7_1_98"));
                mobileReportDetailBean.setA7_1_98open(rs.getString("A7_1_98open"));
                mobileReportDetailBean.setA7_2_1(rs.getString("A7_2_1"));
                mobileReportDetailBean.setA7_2_2(rs.getString("A7_2_2"));
                mobileReportDetailBean.setA7_2_3(rs.getString("A7_2_3"));
                mobileReportDetailBean.setA7_2_4(rs.getString("A7_2_4"));
                mobileReportDetailBean.setA7_2_5(rs.getString("A7_2_5"));
                mobileReportDetailBean.setA7_2_6(rs.getString("A7_2_6"));
                mobileReportDetailBean.setA7_2_7(rs.getString("A7_2_7"));
                mobileReportDetailBean.setA7_2_8(rs.getString("A7_2_8"));
                mobileReportDetailBean.setA7_2_9(rs.getString("A7_2_9"));
                mobileReportDetailBean.setA7_2_98(rs.getString("A7_2_98"));
                mobileReportDetailBean.setA7_2_98open(rs.getString("A7_2_98open"));
                mobileReportDetailBean.setA7_3_1(rs.getString("A7_3_1"));
                mobileReportDetailBean.setA7_3_2(rs.getString("A7_3_2"));
                mobileReportDetailBean.setA7_3_3(rs.getString("A7_3_3"));
                mobileReportDetailBean.setA7_3_4(rs.getString("A7_3_4"));
                mobileReportDetailBean.setA7_3_5(rs.getString("A7_3_5"));
                mobileReportDetailBean.setA7_3_6(rs.getString("A7_3_6"));
                mobileReportDetailBean.setA7_3_7(rs.getString("A7_3_7"));
                mobileReportDetailBean.setA7_3_8(rs.getString("A7_3_8"));
                mobileReportDetailBean.setA7_3_9(rs.getString("A7_3_9"));
                mobileReportDetailBean.setA7_3_10(rs.getString("A7_3_10"));
                mobileReportDetailBean.setA7_3_98(rs.getString("A7_3_98"));
                mobileReportDetailBean.setA7_3_98open(rs.getString("A7_3_98open"));
                mobileReportDetailBean.setA7_3_99(rs.getString("A7_3_99"));
                mobileReportDetailBean.setA7_4_1(rs.getString("A7_4_1"));
                mobileReportDetailBean.setA7_4_2(rs.getString("A7_4_2"));
                mobileReportDetailBean.setA7_4_3(rs.getString("A7_4_3"));
                mobileReportDetailBean.setA7_4_4(rs.getString("A7_4_4"));
                mobileReportDetailBean.setA7_4_5(rs.getString("A7_4_5"));
                mobileReportDetailBean.setA7_4_6(rs.getString("A7_4_6"));
                mobileReportDetailBean.setA7_4_7(rs.getString("A7_4_7"));
                mobileReportDetailBean.setA7_4_8(rs.getString("A7_4_8"));
                mobileReportDetailBean.setA7_4_98(rs.getString("A7_4_98"));
                mobileReportDetailBean.setA7_4_98open(rs.getString("A7_4_98open"));
                mobileReportDetailBean.setA7_4_99(rs.getString("A7_4_99"));
                mobileReportDetailBean.setStatMonth(rs.getInt("STAT_MONTH"));




                mobileReportDetailBean.setUserId(rs.getLong("user_id"));
                mobileReportDetailBean.setParentnameL1(rs.getString("PARENTNAME_L1"));
                mobileReportDetailBean.setParentnameL2(rs.getString("PARENTNAME_L2"));
                mobileReportDetailBean.setParentnameL3(rs.getString("PARENTNAME_L3"));
                mobileReportDetailBean.setIsInter(rs.getInt("IS_INTER"));
                mobileReportDetailBean.setIs5gkh(rs.getString("IS_5GKH"));
                mobileReportDetailBean.setIs5gPackage(rs.getString("IS_5G_package"));
                list.add(mobileReportDetailBean);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtilMySql.close(connection, ps, rs);
        }
        return  null ;
    }
weixin_47006244
关注
专栏目录
Docker (二)：镜像、容器导入导出与私有仓库搭建
✨ 欢迎来到【Seal ^_^ 的CSDN博客】！这里是我记录技术心得、分享经验的地方。✨
08-17 5万+
🟢 Docker (二)：镜像、容器导入导出与私有仓库搭建
vue中query与params传参的区别
鹏仔工作室的秘密基地
08-13 485
query传参方式 this.$router.push({ path: '/home', query: { id: '19961022' } }) query取值方式 mounted(){ this.id = this.$route.query.id; } param传参方式 this.$router.push({ name: 'Home', params: { id: '19961022' } }) param取值方式 mounted.
导入·导出
lc1662076的博客
08-09 203
学习心得（3） SQL Server 数据类型详解导入·导出 一．整数 整数型数据包括bigint型、int型、smallint型和tinyint型。 ·bigint型数据的存储大小为8个字节，共64位。其中63位用于表示数值的大小，1位用于表示符号。bigint型数据可以存储的数值范围是-263～263-1，即-9 223 372 036 854 775 808 ～ 9 223 372 036 854 775 807 int型数据的存储大小为4个字节，共32位。其中31位用于表示数值的大小，1位用于表示符
数据库导入导出详解
彷徨的博客
11-30 9041
Sql格式：.sql格式的文件，可用文本编辑器查看，通用性比较好，效率不如第一种，适合小数据量导入导出。Pde格式：.pde格式的文件，.pde为PL/SQL Developer自有的文件格式，只能用PL/SQL Developer工具导入导出，不能用文本编辑器查看；优点：代码书写简单易，从本地即可直接导入，不用在服务器中操作，降低难度，减少服务器上的操作也就保证了服务器上数据文件的安全性。导出步骤：工具，导出表，选好表以后，下方有Oracle导出是对应的dmp文件，SQL插入是对应的SQL文件。
oracle数据库导入导出
热门推荐
m0_37642477的博客
11-03 1万+
oracle数据库导入导出
ORACLE导入导出
qq_35479076的博客
04-19 7984
oracle11g用exp命令导出数据库表时，有时会发现只导出了一部分表时而且不会报错，原因是有空表没有进行导出，之前一直没有找到方法于是用最笨的方法重新建这些空表，当然在我们实际当中表的数量大时我们该怎么办？Imp\impdp是导入命令，exp/expdp是导出命令。Impdp:创建目录存放导入的文件----创建表空间[和导出的表空间一致]-----创建用户[如果已存在同名用户需要先删除，删除时先从dbcnt删除，再在sqlplus执行drop命令]----用户授权----cmd命令导入。
MaxCompute数据导入导出
qq_39963880的博客
09-18 5156
MaxCompute数据导入导出1.Tunnel命令导入数据2.MaxCompute Studio导入数据3.Tunnel SDK4.其他导入方式详细介绍请见 MaxCompute提供多种数据导入导出方式，如下所示： 直接在客户端使用Tunnel命令。 通过MaxCompute Studio工具可视化方式实现本地数据文件导入导出，详情请参见导入导出数据。 通过Tunnel提供的SDK自行编写Ja...
vue3导入导出
weixin_44368048的博客
03-24 4029
vue3+ts封装导入、导出、上传文件组件以及hooks自动化函数
rbd导入和导出
吴业亮的专栏
07-18 7489
作者：【吴业亮】 博客：http://blog.csdn.net/wylfengyujiancheng 将rbd1导出 rbd export pool/test1 /opt/test1 导入新的rbd rbd import /opt/test1 pool/test2 --image-format 2 导入和导出功能可作为rbd简单的备份 ...
VSCode 插件导入导出
qq_50203525的博客
01-12 2814
把输出结构放在其他机子vscode复制到终端运行即可。
UAP标准导入导出功能 UAP标准导入导出功能
10-30
《UAP标准导入导出功能详解》 在IT领域，数据的导入与导出是日常工作中不可或缺的一部分。尤其是在企业级应用系统中，如NC65这样的企业管理软件，UAP（通用应用平台）提供了标准化的导入导出功能，使得数据管理更为...
详解JS模块导入导出
10-18
### JS模块导入导出的概念与方法 #### 模块化开发背景 在前端开发中，模块化已经成为一种普遍的实践，其目的是将复杂的系统分解成一个个小的模块，便于维护和复用。模块化不仅可以提高代码的可读性，还可以在不同...
execl导入导出
12-27
"Excel导入导出"这一主题涵盖了如何将数据从外部源导入到Excel工作表，以及如何将Excel数据导出到其他格式或系统中。以下是对这个主题的详细阐述： **一、Excel数据导入** 1. **CSV导入**：CSV（逗号分隔值）是一...
Oracle 19c 备份恢复-导入导出
07-10
Oracle 19c 备份恢复-导入导出 Oracle 19c 备份恢复-导入导出是指使用 Oracle 数据库提供的工具 exp 和 imp 实现数据库的备份恢复和数据的导入导出。本文将详细介绍 Oracle 19c 备份恢复-导入导出的基本命令、工作...
Allegro 软件中的光绘层的导入导出设置
03-15
Allegro 软件中的光绘层的导入导出设置 Allegro 软件中的光绘层的导入导出设置是 CAD 设计中非常重要的一个方面。由于每个人的设计习惯不同，因此在设计中查看的光绘层也不同。为了解决这个问题，我们可以使用 ...
【C++刷题】力扣-#121-买卖股票的最佳时机
最新发布
会写代码的饭桶
10-16 323
给定一个数组 prices，其中 prices[i] 表示第 i 天的股票价格。假设你可以在第 i 天买入并在第 j 天卖出股票（i ≤ j），设计一个算法来计算你所能获取的最大利润。注意你只能持有一股股票，并且你不能同时参与多笔交易（即在再次买入前必须卖出股票）。
基于SSM班级事务管理系统的设计
2401_87849773的博客
10-15 390
管理员账户功能包括：系统首页，个人中心，学生管理，班委管理，班会组织管理，健康档案管理，党员发展管理，党员培训管理，学生成绩管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。班委账号功能包括：系统首页，学生管理，学生成绩管理，活动信息管理，班费通知管理。服务器：SpringBoot自带 apache tomcat。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发系统：Windows。
Java分布式锁
juanmiao的专栏
10-12 699
每种方法都有其优点和适应的场景，数据库通常简单，但可能存在性能问题，redis非常快，但需要第三方库，并且可能引入网诺问题，Zookeeper提供了更复杂的同步原语，但需要额外学习曲线。分布式锁的实现比线程锁和进程锁要复杂得多，因为它需要在网诺中的多个节点之间进行协调，以保证锁的唯一性和一致性。在实现分布式锁时，通常会有一个中心节点（或者称为锁服务），所有需要获取锁的节点都需要向这个中心节点申请。这个中心节点负责协调和管理所有节点的锁请求，确保锁的唯一性和一致性。创建临时节点，执行业务逻辑，释放锁。
Java安卓开发之Fragment开发（通俗易懂版）——第8章
一个脚本小子的博客
10-13 690
FragmentManager中的getSupportFragmentManager()方法不支持Activity，不论是旧版的FragmentManager还是新版的，因此我们要把继承Activity类改成继承AppCompatActivity类。
Oracle导入导出操作指南
"Oracle导入导出操作手册，由孙海鹰在快钱支付清算信息有限公司编写，旨在指导开发人员进行数据库的导入导出操作。该手册适用于DBA和SCM内部，不对外公开，旨在提高工作效率。" Oracle的导入导出操作是数据库管理中...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值