java poi导出excel

public class ExportCn2CityDate {
    // 数据库地址
    private static String dbUrl="jdbc:oracle:thin:@//localhost:1521/orcl";
    // 用户名
    private static String dbUserName="root";
    // 密码
    private static String dbPassword="123";
    // 驱动名称
    private static String  jdbcName = "oracle.jdbc.driver.OracleDriver";

    public static void main(String[] args) throws Exception{
        Class.forName(jdbcName);
        Connection con=null;
        // 获取数据库连接
        con= DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
        System.out.println("cn2_city_data数据！");

        Scanner scanner=new Scanner(System.in);
        System.out.print("输入开始时间(yyyy-mm-dd)");
        String beginTime=scanner.nextLine();
        System.out.print("输入结束时间(yyyy-mm-dd)");
        String endTime=scanner.nextLine();

        Statement st = (Statement) con.createStatement();
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
        String d=beginTime;//"2018-11-01";
        Date bigtime= null;
        Date endtime= null;
        try {
            bigtime = sf.parse(d);
            endtime=sf.parse(endTime) ;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //定义一个接受时间的集合
        List<Date> lDate = new ArrayList<Date>();
        lDate.add(bigtime);
        Calendar calBegin = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        Calendar lendar=Calendar.getInstance();
        calBegin.setTime(bigtime);
        Calendar calEnd = Calendar.getInstance();
        // 使用给定的 Date 设置此 Calendar 的时间
        calEnd.setTime(endtime);
        // 测试此日期是否在指定日期之后
        while (endtime.after(calBegin.getTime()))  {
            // 根据日历的规则，为给定的日历字段添加或减去指定的时间量
            calBegin.add(Calendar.DAY_OF_MONTH, 1);
            lDate.add(calBegin.getTime());
        }
        List<Cn2City> dataList= null;
        List<List<Cn2City>> dataList1= new ArrayList<>();
        for (Date dd:lDate) {
            dataList= new ArrayList<>();
            Date de=new Date(dd.getTime());
            String beginTime1=sf.format(de)+" 00:00:00";
            String endTime1=sf.format(de)+" 23:59:59";
            String dataSql = "select SRC_CITY_NAME, DST_CITY_NAME, count(1) as num\n" +
                    "  from cn21\n" +
                    " where test_time between\n" +
                    "       to_date('"+beginTime1+"', 'yyyy-mm-dd hh24:mi:ss') and\n" +
                    "       to_date('"+endTime1+"', 'yyyy-mm-dd hh24:mi:ss')\n" +
                    "   and delay is not null\n" +
                    " group by SRC_CITY_NAME, DST_CITY_NAME";
            ResultSet dataRs = st.executeQuery(dataSql);
            Cn2City cn2City=null;
            while (dataRs.next()){
                cn2City=new Cn2City();
                cn2City.setSrcCityName(dataRs.getString("SRC_CITY_NAME"));
                cn2City.setDstCityName(dataRs.getString("DST_CITY_NAME"));
                cn2City.setNum(dataRs.getInt("num"));
                String str=sf.format(de);
                cn2City.setTime(str);
                dataList.add(cn2City);
            }
            dataList1.add(dataList);
        }

        String citySql="select CITY_NAME as cityName FROM CN2_CITY";
        List<City> citys=new ArrayList<>();
        ResultSet cityRs = st.executeQuery(citySql);
        while (cityRs.next()){
            City city=new City();
            city.setCityname(cityRs.getString("cityName"));
            citys.add(city);
        }
        List<Cn2City> cityList = new ArrayList<>();
        for(int i=0;i<citys.size();i++){
            String cityNameI = citys.get(i).getCityname();
            for(int j=0;j<citys.size();j++){
                String cityNameJ = citys.get(j).getCityname();
                Cn2City cn2City = new Cn2City();
                if(!cityNameI.equals(cityNameJ)){
                    cn2City.setSrcCityName(cityNameI);
                    cn2City.setDstCityName(cityNameJ);
                    cityList.add(cn2City);
                }
            }
        }
        //表头设置
        List<String> titles = new ArrayList();
        titles.add("起始城市");
        titles.add("终止城市");
        Map<String,Integer> map = new HashMap<>();
        for(int i = 0;i<dataList1.size();i++){
            String s3=null;
            List<Cn2City> cn2s=dataList1.get(i);
            for(int j=0;j<cn2s.size();j++){
//                System.out.print(sf1);
                Cn2City cn2Citys = cn2s.get(0);
                String time= cn2Citys.getTime();
                String s1=time.substring(5);
                String [] s2=s1.split("-");
                s3 =s2[0]+"月"+s2[1]+"日";
                for (Cn2City cn2City : cn2s) {
                    String key= cn2City.getSrcCityName()+"-"+cn2City.getDstCityName()+"-"+s3;
                    map.put(key,0);
                }
            }
            titles.add(s3);
        }
        titles.add("共计次数");

        for(int i = 0 ; i<dataList1.size();i++) {
            for(int j=0;j<dataList1.get(i).size();j++){
                Cn2City cn2City = dataList1.get(i).get(j);
                int count = cn2City.getNum();
                String time = cn2City.getTime();
                String [] arr=time.split("-");
                String title =arr[1]+"月"+arr[2]+"日";
                map.put(cn2City.getSrcCityName()+"-"+cn2City.getDstCityName()+"-"+title,count);
            }
        }
        //列表数据
        List<List<String>> parentList = new ArrayList<>();
        for(int i = 0 ; i<cityList.size();i++){
            Cn2City cn2City = cityList.get(i);
            List<String> rowData = new ArrayList<>();//行所需要的数据
            rowData.add(cn2City.getSrcCityName());
            rowData.add(cn2City.getDstCityName());
            int totalCount=0;
            for(int j=2;j< titles.size()-1;j++){
                int value=0;
                if(!StringUtils.isEmpty(map.get(cn2City.getSrcCityName()+"-"+cn2City.getDstCityName()+"-"+titles.get(j)))){
                    value=map.get(cn2City.getSrcCityName()+"-"+cn2City.getDstCityName()+"-"+titles.get(j));
                }
                rowData.add(String.valueOf(value));
                totalCount=value+totalCount;
            }
            rowData.add(String.valueOf(totalCount));
            parentList.add(rowData);
        }

        String fileName = DateFormatUtils.dateToString(new Date(),"yyyy-MM-dd")+"--Cn2CityData.xlsx";
        // 下载文件的默认名称
//        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
        XSSFWorkbook wb = new XSSFWorkbook();//创建Workbook对象，对应一个Excel文件
        String titleName = "中盈ping测数据";
        String sheetName = "sheet0";
        int sheetNum=0;//列表工作簿
        XSSFSheet sheet0 = ExportExcelUtils.createSheet(wb,sheetNum,titleName,sheetName,parentList,titles);
        //列宽
        sheet0.setColumnWidth(0,50*100);
        sheet0.setColumnWidth(1,50*100);
        sheet0.setColumnWidth(2,25*100);
        sheet0.setColumnWidth(3,25*100);
        sheet0.setColumnWidth(4,25*100);
        CellRangeAddress region = new CellRangeAddress(0,0,0,0);//合并单元格第一行前5列
        sheet0.addMergedRegion(region);
        sheetNum++;
        FileOutputStream fout = new FileOutputStream("/home/inms/ctginms/service/exportMain/ExportCn2CityData/"+fileName);
        wb.write(fout);
        fout.close();
//        OutputStream out = response.getOutputStream();
//        wb.write(out);
//        out.close();
        System.out.println("导出成功！");
        con.close();
    }
}
所需要jar包

 

所需要工具类

 新手记录学习，望各位指教！！！！