java查询数据并导出excel

import com.mysql.jdbc.Driver;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class Main {

    public static List<ExcelPojo> getExcelPojo(String cityname, Integer start, Integer end) {

        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");

            connection = DriverManager.getConnection("jdbc:mysql://xxxxxxx:3306/wmw?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true", "root", "xxxxx");


            String sql = "select s.analyse_id," +
                    "f.name," +
                    "u.gender," +
                    "TIMESTAMPDIFF(year,STR_TO_DATE(u.birthday,'%Y-%m-%d'),CURDATE())," +
                    "s.score7," +
                    "s.score6," +
                    "s.score4," +
                    "s.score5," +
                    "s.score3," +
                    "s.score2," +
                    "s.score0," +
                    "s.score1 " +
                    "from  wmw_img_analyse_score s " +
                    "right join wmw_img_analyse a " +
                    "on s.analyse_id = a.analyse_id " +
                    "right join wmw_shop " +
                    "on  wmw_shop.id = a.shop_id " +
                    "left join wmw_city_info f " +
                    "on f.id = cast(wmw_shop.city as signed) " +
                    "left join wmw_client_user u " +
                    "on u.id= a.user_id " +
                    "where u.birthday is not null  " +
                    "and s.score0 is not null " +
                    "and s.score1 is not null " +
                    "and s.score2 is not null " +
                    "and s.score3 is not null " +
                    "and s.score4 is not null " +
                    "and s.score5 is not null " +
                    "and s.score6 is not null " +
                    "and s.score7 !=0 " +
                    "and u.gender = \"F\" " +
                    "and f.name = ? " +
                    "and TIMESTAMPDIFF( YEAR, STR_TO_DATE( u.birthday, '%Y-%m-%d' ), CURDATE( ) ) between ? " +
                    "and ? " +
                    "order by " +
                    "s.id desc " +
                    "limit 0,100; ";


            ps = connection.prepareStatement(sql);

       //     Date date1 = java.sql.Date.valueOf(start);
        //    Date date2 = java.sql.Date.valueOf(end);

            ps.setString(1, cityname);
            ps.setInt(2,start);
            ps.setInt(3,end);

            rs = ps.executeQuery();

            List<ExcelPojo> excels = new ArrayList<>();


            while (rs.next()) {
                ExcelPojo e = new ExcelPojo();

                if(start==15){
                    e.setAgegroup(1);

                }else if(start==26){
                    e.setAgegroup(2);
                }else {
                    e.setAgegroup(3);
                }

                int id = rs.getInt(1);
                e.setId(id);
        //        System.out.println(rs.getInt(1));

                String name = rs.getString(2);
                e.setCityname(name);
        //        System.out.println(rs.getString(2));

                String sex = rs.getString(3);
                e.setSex(sex);
         //       System.out.println(rs.getString(3));

                int age = rs.getInt(4);
                e.setAge(age);
         //       System.out.println(rs.getInt(4));

                int shuifen = rs.getInt(5);
                e.setShuifen(shuifen);
          //     System.out.println(rs.getInt(5));

                int fuse = rs.getInt(6);
                e.setFuse(fuse);
         //       System.out.println(rs.getInt(6));

                int fenci = rs.getInt(7);
                e.setFenci(fenci);
          //      System.out.println(rs.getInt(7));

                int youzhi = rs.getInt(8);
                e.setYouzhi(youzhi);
           //     System.out.println(rs.getInt(8));

                int anban = rs.getInt(9);
                e.setAnban(anban);
            //    System.out.println(rs.getInt(9));

                int mingan = rs.getInt(10);
                e.setMingan(mingan);
           //     System.out.println(rs.getInt(10));

                int maokong = rs.getInt(11);
                e.setMaokong(maokong);
            //    System.out.println(rs.getInt(11));

                int xiwen = rs.getInt(12);
                e.setXiwen(xiwen);
            //    System.out.println(rs.getInt(12));

                excels.add(e);
            }

            return excels;


        } catch (Exception e) {
            e.printStackTrace();
        }
  return null;
    }


    public static void main(String[] args) {


        List<String> list = new ArrayList<>();
        list.add("苏州市");
        list.add("广州市");
        list.add("郑州市");
        list.add("宜昌市");
        list.add("贵阳市");
        list.add("邢台市");
        list.add("上海市");
        list.add("石家庄市");
    //    list.add("济南市");
        list.add("深圳市");
        list.add("唐山市");
        list.add("衡水市");
        list.add("北京市");
        list.add("保定市");
        list.add("金华市");
        list.add("长春市");
        list.add("杭州市");
        list.add("武汉市");
        list.add("上饶市");
        list.add("南京市");
     //   list.add("沈阳市");
        list.add("南昌市");
        list.add("海口市");
        list.add("成都市");
        list.add("焦作市");
   //     list.add("重庆市");
    //    list.add("无锡市");
        list.add("沧州市");
    //    list.add("承德市");
   //     list.add("潍坊市");
    //    list.add("德州市");
        list.add("临沂市");
        list.add("宁波市");
    //    list.add("台州市");
   //     list.add("黄石市");
    //    list.add("西宁市");

        List<ExcelPojo> all = new ArrayList<>();

        String sheet[] = new String[list.size()];

        String[] celltitle =null;

        for (int i = 0; i <list.size() ; i++) {


            List<ExcelPojo>  list1 = Main.getExcelPojo(list.get(i),15,25);
            System.out.println("===========list1======="+list1.size());
            List<ExcelPojo>  list2 = Main.getExcelPojo(list.get(i),26,30);
            System.out.println("===========list2======="+list2.size());
            List<ExcelPojo>  list3 = Main.getExcelPojo(list.get(i),31,40);
            System.out.println("===========list3======="+list3.size());
            all.addAll(list1);
            all.addAll(list2);
            all.addAll(list3);

          list.toArray(sheet);

            celltitle = new String[]{"分析编号","城市","性别","年龄段(1:15-25，2:26-30，3:31-40)","年龄","保湿补水","肤色净白","粉刺祛除","油脂控制","斑点淡化","敏感修复","毛孔细致","细纹抚平"};

        }

        DataExportManagerV2.exportExcel(sheet,celltitle,all);


    }


}

 

 

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/**
 *   利用反射机制写一个通用型数据导出方法
 *   范型实体类需跟titlename顺序一致
 *   ssm
 *   2018/7/13
 */

public class DataExportManagerV2 {


    public static String exportExcel(String[] sheetnames, String[] celltitle, List<ExcelPojo> datacity) {

        String pathname_excelname = "/Users/songsm/Downloads/cel/c.xls";

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();

        hssfCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
     //   hssfCellStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
      //  hssfCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
      //  cellStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
        HSSFCellStyle cellStyle1 = hssfWorkbook.createCellStyle();
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle1.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);

        HSSFCellStyle cellStyle2 = hssfWorkbook.createCellStyle();
        cellStyle2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle2.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);


        List<ExcelPojo> datas = new ArrayList<>();

        for (int s = 0; s < sheetnames.length; s++) {



            datas = getdatecity(sheetnames[s], datacity);

            String nums = getnum(sheetnames[s],datas);

            HSSFSheet hssfSheet = hssfWorkbook.createSheet(sheetnames[s]+"("+nums+")");


            for (int i = 0; i <= datas.size(); i++) {

                hssfSheet.setColumnWidth((short) i, 20 * 256);

                Field[] fields = datas.get(0).getClass().getDeclaredFields();

                HSSFRow hssfRow = hssfSheet.createRow(i);


                for (int j = 0; j < celltitle.length; j++) {

                    if (i == 0) {

                        HSSFCell cell = hssfRow.createCell(j);
                        cell.setCellStyle(hssfCellStyle);
                        cell.setCellValue(celltitle[j]);

                    } else {

                        HSSFCell cell = hssfRow.createCell(j);
                     try{
                         String name = fields[3].getName();

                         name = name.substring(0, 1).toUpperCase() + name.substring(1);

                         String type = fields[3].getGenericType().toString();


                             Method m = datas.get(i-1).getClass().getMethod("get" + name);
                             Integer value = (Integer) m.invoke(datas.get(i-1));
                             if (value != null) {
                                 if(value ==1){
                                     cell.setCellStyle(cellStyle);
                                 }else if(value ==2){
                                     cell.setCellStyle(cellStyle1);
                                 }else if(value ==3){
                                     cell.setCellStyle(cellStyle2);
                                 }

                             }



                     }catch (Exception e){

                         e.printStackTrace();
                     }

                        try {

                            String name = fields[j].getName();

                            name = name.substring(0, 1).toUpperCase() + name.substring(1);

                            String type = fields[j].getGenericType().toString();
                            if (type.equals("class java.lang.String")) {

                                Method m = datas.get(i - 1).getClass().getMethod("get" + name);
                                String value = (String) m.invoke(datas.get(i - 1));
                                if (value == null) {
                                    m = datas.get(i - 1).getClass().getMethod("set" + name, String.class);
                                    m.invoke(datas.get(i - 1), "");
                                }
                                cell.setCellValue(value);
                            }

                            if (type.equals("class java.lang.Integer")) {
                                Method m = datas.get(i - 1).getClass().getMethod("get" + name);
                                Integer value = (Integer) m.invoke(datas.get(i - 1));
//                                if(value != null){
//                                    if(name.equals("Agegroup")){
//                                        if(value ==1){
//                                            hssfRow.setRowStyle(cellStyle);
//                                        }
//
//
//                                    }
//
//                                }
                                if (value == null) {
                                    m = datas.get(i - 1).getClass().getMethod("set" + name, Integer.class);
                                    m.invoke(datas.get(i - 1), 0);
                                }
                                cell.setCellValue(value);
                            }


                        } catch (Exception e) {

                            e.printStackTrace();
                        }


                    }

                }


            }
        }

        fileOutputStreamOperate(pathname_excelname, hssfWorkbook);

        return null;
    }


//     public String exportExcelWithImage(String[] sheetnames, String[] celltitle, List<T> datas){
//
//        exportExcel(sheetnames,celltitle, datas);
//
//        //下载图片到文件夹
//
//
//         return null;
//     }


    private static void fileOutputStreamOperate(String pathname_excelname, HSSFWorkbook hssfWorkbook) {

        FileOutputStream fos = null;

        try {
            fos = new FileOutputStream(pathname_excelname);
            hssfWorkbook.write(fos);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {

                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    private static List<ExcelPojo> getdatecity(String cityname, List<ExcelPojo> datas) {

        List<ExcelPojo> citys = new ArrayList<>();
        for (ExcelPojo e : datas) {

            if (e.getCityname().equals(cityname)) {

                citys.add(e);

            }

        }

        return  citys;


    }


    private static String getnum(String cityname, List<ExcelPojo> datas){

        int k1 = 0;
        int k2 = 0;
        int k3 = 0;

        for (ExcelPojo e : datas) {

            if(e.getAgegroup()==1){

                k1+=1;
            }else if(e.getAgegroup()==2){

                k2+=1;

            }else if(e.getAgegroup()==3){

                k3+=1;
            }


            }


        return k1+","+k2+","+k3;

    }

}