/**
* 导出一周数据报表
* 定时任务:每周一将一周要发送的催缴还款数据导出到excel
* @throws ApplicationException
*/
@Override
public void exportWeekData() throws ApplicationException {
// 获取一周要发送的催缴还款数据
String url = "contract/getWeekData";
List<MsgParamsDto> msgParamsDtoList = erpHeader.sendGetRequestToErpList(url, MsgParamsDto.class, CONT);
// logger.info("获取一周数据成功!!!");
// 导出数据到excel表
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
// 建立新的sheet对象(excel的表单)
HSSFSheet sheet = wb.createSheet("sheet1");
// 在sheet里创建第一行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row1 = sheet.createRow(0);
// 创建单元格(excel的单元格,参数为列索引,可以是0~255之间的任何一个
HSSFCell cell = row1.createCell(0);
// 设置单元格内容
cell.setCellValue("分期还款通知消息的一周数据表");
// 合并单元格CellRangeAddress构造参数依次表示起始行,截至行,起始列, 截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
// 实例化样式对象
HSSFCellStyle style1 = wb.createCellStyle();
HSSFCellStyle style2 = wb.createCellStyle();
HSSFCellStyle style3 = wb.createCellStyle();
// 水平居中
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 垂直居中
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// // 边框
// style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
// style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 字体
HSSFFont font1 = wb.createFont(); //标题字体
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font1.setFontHeightInPoints((short)18);
style1.setFont(font1);
HSSFFont font2 = wb.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font2.setFontHeightInPoints((short)12);
style2.setFont(font2);
// 将样式应用到行
cell.setCellStyle(style1);
// 在sheet里创建第二行,参数为行索引(excel的行),可以是0~65535之间的任何一个
HSSFRow row2 = sheet.createRow(1);
// 创建列
HSSFCell cell1 = row2.createCell(0);
HSSFCell cell2 = row2.createCell(1);
HSSFCell cell3 = row2.createCell(2);
HSSFCell cell4 = row2.createCell(3);
HSSFCell cell5 = row2.createCell(4);
HSSFCell cell6 = row2.createCell(5);
// 添加表头
cell1.setCellValue("序号");
cell2.setCellValue("姓名");
cell3.setCellValue("合同编号");
cell4.setCellValue("手机号");
cell5.setCellValue("还款类型");
cell6.setCellValue("还款到期时间");
cell1.setCellStyle(style2);
cell2.setCellStyle(style2);
cell3.setCellStyle(style2);
cell4.setCellStyle(style2);
cell5.setCellStyle(style2);
cell6.setCellStyle(style2);
// 设置行高
sheet.setDefaultRowHeight((short) 360);
// 设置列宽
sheet.setColumnWidth( cell1.getColumnIndex(),256 * 7);
sheet.setColumnWidth( cell2.getColumnIndex(),256 * 12);
sheet.setColumnWidth( cell3.getColumnIndex(),256 * 18);
sheet.setColumnWidth( cell4.getColumnIndex(),256 * 18);
sheet.setColumnWidth( cell5.getColumnIndex(),256 * 12);
sheet.setColumnWidth( cell6.getColumnIndex(),256 * 16);
sheet.setDefaultColumnStyle(0,style3);
sheet.setDefaultColumnStyle(1,style3);
sheet.setDefaultColumnStyle(2,style3);
sheet.setDefaultColumnStyle(3,style3);
sheet.setDefaultColumnStyle(4,style3);
sheet.setDefaultColumnStyle(5,style3);
// 遍历
for (int i = 0; i < msgParamsDtoList.size(); i++) {
HSSFRow rows = sheet.createRow(i + 2);
rows.createCell(0).setCellValue(i + 1);
rows.createCell(1).setCellValue(msgParamsDtoList.get(i).getName());
rows.createCell(2).setCellValue(msgParamsDtoList.get(i).getNumber());
rows.createCell(3).setCellValue(msgParamsDtoList.get(i).getMobile());
if (msgParamsDtoList.get(i).getMsgType() == 1) {
rows.createCell(4).setCellValue("临期");
} else {
rows.createCell(4).setCellValue("逾期");
}
rows.createCell(5).setCellValue(msgParamsDtoList.get(i).getRepaymentDate());
}
// 写入文件
try {
File f = new File("E:/分期还款通知消息的一周数据表.xls");
if (f.exists()) {
f.delete();
f.createNewFile();
}
FileOutputStream fos = new FileOutputStream(f);
wb.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
throw new ServiceException("导出excel发生异常", e);
}
// logger.info("数据导出excel表成功!!!");
}
数据导出到excel
最新推荐文章于 2023-03-27 21:21:26 发布