原理就是建一个与标题数对应的dto,然后通过反射依次填入。
public XSSFWorkbook exportByUtil() {
String[] title1 = {"日期","当前总订单数","01:00回款率","02:00回款率","03:00回款率","04:00回款率 ","05:00回款率","06:00回款率","07:00回款率","08:00回款率","09:00回款率","10:00回款率","11:00回款率",
"12:00回款率","13:00回款率","14:00回款率","15:00回款率","16:00回款率","17:00回款率","18:00回款率","19:00回款率","20:00回款率","21:00回款率","22:00回款率","23:00回款率","24:00回款率","差值"};
EveryBackMoneyExample example = new EveryBackMoneyExample();
example.setOrderByClause("gmt_create desc");
List<EveryBackMoney> list =everyBackMoneyMapper.selectByExample(example);
List<EveryBackMoneyExcelDto> copyList = new ArrayList<>();
for(EveryBackMoney every:list){
EveryBackMoneyExcelDto dto = new EveryBackMoneyExcelDto();
BeanUtils.copyProperties(every,dto);
copyList.add(dto);
}
String header="历史回款率统计";
XSSFWorkbook wb = ExcelUtil.createExcel(header,title1,copyList);
return wb;
}
public static XSSFWorkbook createExcel(String header,String[] title1,List<?>list) {
try{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(header);
sheet.setDefaultColumnWidth(40);
XSSFRow row = sheet.createRow(0);
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
XSSFCell cell;
//创建合并表头
for(int i =0;i<title1.length;i++){
cell = row.createCell((short) i);
cell.setCellValue(title1[i]);
cell.setCellStyle(style);
}
for(int y=0;y<list.size();y++){
XSSFRow rows = sheet.createRow(y+1);
Object dto = list.get(y);
Class clazz = dto.getClass();
Field[] fileds = clazz.getDeclaredFields();
for(int i=0;i<fileds.length;i++) {
char c = fileds[i].getName().charAt(0);
c -= 32;
String mname = c+fileds[i].getName().substring(1);
Method method = clazz.getMethod("get" + mname);
Object obj = method.invoke(dto, null);
if(obj!=null) {
if (fileds[i].getType() == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat(
fileds[i] .getAnnotation(DateTimeFormat.class).pattern());
obj = sdf.format(obj);
}
}else{
obj="";
}
rows.createCell((short) i).setCellValue(obj.toString());
}
}
return wb;
}catch (Exception e){
e.printStackTrace();
}
return null;
}