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;
}
}