@Componentpublic classScheduleServiceImpl {@Autowired TaskWordExceltaskWordExcel;/* * "0 0 12 * * ?" 每天中午十二点触发 "0 15 10 ? * *" 每天早上10：15触发 "0 15 10 * * ?" 每天早上10：15触发 "0 15 10 * * ? *" 每天早上10：15触发 "0 15 10 * * ? " 的每天早上10：15触发 "0 * 14 * * ?" 每天从下午2点开始到2点59分每分钟一次触发 "0 0/5 14 * * ?" 每天从下午2点开始到2：55分结束每5分钟一次触发 "0 0/5 14,18 * * ?" 每天的下午2点至2：55和6点至6点55分两个时间段内每5分钟一次触发 "0 0-5 14 * * ?" 每天14:00至14:05每分钟一次触发 "0 10,44 14 ? 3 WED" 三月的每周三的14：10和14：44触发 "0 15 10 ? * MON-FRI" 每个周一、周二、周三、周四、周五的10：15触发 * */ @Scheduled(cron="0 0 5 ? * *")public voidtaskWord() {taskWordExcel.taskWord();}@Scheduled(cron="0 0 4 ? * *")public voidtaskExcel() {taskWordExcel.taskExcel();}}
@Componentpublic classTaskWordExcel {@Autowired WordAccessDaowordAccessDao;@Autowired ExcelAccessDaoexcelAccessDao;@Autowired WordAccessServicewordAccessService;@Autowired AmsFileUtilServiceamsFileUtilService;@AutowiredprivateFileAccessWrapperfileAccessWrapper;@Autowired ExcelImportDaoexcelImportDao;@Autowired ExcelAccessServiceexcelAccessService;public voidtaskExcel(){String date=wordAccessDao.getFifthDate(sdf.format(newDate()),-1);Path path =fileAccessWrapper.getFileAccessor().rootPath("amsFile","excelReport","对账表"+date+".xls");String msg=excelAccessService.redoExcel(fdate, path);}}
packagecom.swsc.ams.service.wordAndExcel.impl;importcom.swsc.ams.batis.model.wordAndExcel.ExcelBean;importcom.swsc.ams.batis.model.wordAndExcel.FileBean;importcom.swsc.ams.core.utils.SwscSerialNumber;importcom.swsc.ams.jdbc.ExcelAccessDao;importcom.swsc.ams.jdbc.WordAccessDao;importcom.swsc.ams.service.wordAndExcel.ExcelAccessService;importorg.apache.poi.hssf.usermodel.*;importorg.apache.poi.hssf.util.HSSFColor;importorg.apache.poi.ss.usermodel.CellStyle;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importorg.springframework.transaction.annotation.Transactional;importjava.io.*;importjava.nio.file.Path;importjava.util.List;/*** Created by Administrator on -10-26.*/@Transactional@Servicepublic classExcelAccessServiceImplimplementsExcelAccessService{@Autowired ExcelAccessDaoexcelAccessDao;@Autowired WordAccessDaowordAccessDao;@OverridepublicList<ExcelBean> list(String date) {String FifthDate=wordAccessDao.getFifthDate(date.replace("-",""),-1);returnexcelAccessDao.getExcel(date,FifthDate);}@OverridepublicString redoExcel(String date,Path path) {String msg="";HSSFWorkbook wb =newHSSFWorkbook();//生成一个sheet1HSSFSheet sheet = wb.createSheet("sheet1");//为sheet1生成第一行，用于放表头信息HSSFRow row = sheet.createRow(0);//第一行的第一个单元格的值为 ‘序号’HSSFCell cell = row.createCell((short)0);// cell.setEncoding(HSSFCell.ENCODING_UTF_16); //设置编码cell.setCellValue("集合计划名称");cell = row.createCell((short)1);cell.setCellValue("估值系统代码");cell = row.createCell((short)2);cell.setCellValue("Ta代码");cell = row.createCell((short)3);cell.setCellValue("O32名称");cell = row.createCell((short)4);cell.setCellValue("O32可用资金(T+1交易可用)");cell = row.createCell((short)5);cell.setCellValue("O32可取资金(T+0交易可用)");cell = row.createCell((short)6);cell.setCellValue("赢时胜系统可用资金");cell = row.createCell((short)7);cell.setCellValue("赢时胜系统可取资金");cell = row.createCell((short)8);cell.setCellValue("差额可用资金");cell = row.createCell((short)9);cell.setCellValue("差额可取资金");cell = row.createCell((short)10);cell.setCellValue("O32可用资金明细");cell = row.createCell((short)11);cell.setCellValue("估值单位净值");cell = row.createCell((short)12);cell.setCellValue("O32单位净值");cell = row.createCell((short)13);cell.setCellValue("净值差额");cell = row.createCell((short)14);cell.setCellValue("上海T+1清算备付金");cell = row.createCell((short)15);cell.setCellValue("深圳T+1清算备付金");cell = row.createCell((short)16);cell.setCellValue("备注");List<ExcelBean> list =this.list(date);//获得List中的数据，并将数据放到Excel中for(inti=0;i<list.size();i++){ExcelBean bean = list.get(i);//数据每增加一行，表格就再生成一行row = sheet.createRow(i+1);//第一个单元格，放序号随着i的增加而增加cell = row.createCell((short)0);cell.setCellValue(bean.getVC_FUND_CAPTION());cell = row.createCell((short)1);cell.setCellValue(bean.getGZCODE());cell = row.createCell((short)2);cell.setCellValue(bean.getVC_FUND_CODE());cell = row.createCell((short)3);cell.setCellValue(bean.getO32NAME());cell = row.createCell((short)4);cell.setCellValue(bean.getEN_BEGIN_T1_ENABLE());cell = row.createCell((short)5);cell.setCellValue(bean.getEN_BEGIN_T0_ENABLE());cell = row.createCell((short)6);cell.setCellValue(bean.getFSSSJE());cell = row.createCell((short)7);cell.setCellValue(bean.getFZQCB());cell = row.createCell((short)8);if(!"0".equals(bean.getCEKYZJ())&&bean.getCEKYZJ()!=""){cell.setCellStyle(this.getStyle(wb));}cell.setCellValue(bean.getCEKYZJ());cell = row.createCell((short)9);if(!"0".equals(bean.getCEKQZJ())&&bean.getCEKQZJ()!=""){cell.setCellStyle(this.getStyle(wb));}cell.setCellValue(bean.getCEKQZJ());cell = row.createCell((short)10);cell.setCellValue(bean.getEN_CURRENT_CASH());cell = row.createCell((short)11);cell.setCellValue(bean.getFKMMC());cell = row.createCell((short)12);//cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);cell.setCellValue(bean.getEN_NAV_YESTERDAY());cell = row.createCell((short)13);if(0!=bean.getJZCE()&&bean.getJZCE()!=null){cell.setCellStyle(this.getStyle(wb));}cell.setCellValue(bean.getJZCE());cell = row.createCell((short)14);cell.setCellValue(bean.getSH102111());cell = row.createCell((short)15);cell.setCellValue(bean.getSZ102121());}ByteArrayOutputStream os =newByteArrayOutputStream();try{wb.write(os);}catch(IOException e){e.printStackTrace();}byte[] content = os.toByteArray();//String path="d:/templateDir/对账表"+date+".xls";File file = path.toFile();//Excel文件生成后存储的位置。OutputStream fos =null;try{fos =newFileOutputStream(file);fos.write(content);os.close();fos.close();FileBean fp=newFileBean();fp.setId(String.valueOf(SwscSerialNumber.getLong()));fp.setFile_name("对账表"+date);fp.setFile_path(file.getPath());this.excelAccessDao.save(fp,2);msg="生成成功";}catch(Exception e){e.printStackTrace();}returnmsg;}privateCellStyle getStyle(HSSFWorkbook wb){HSSFCellStyle style = wb.createCellStyle();style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);HSSFFont font = wb.createFont();font.setColor(HSSFColor.BLACK.index);style.setFont(font);returnstyle;}}
