java每日定时任务下载

查询数据库,后台定时产出excel文件

java 开启每天定时设定

@Component
@Slf4j
public class sendByMonitorTask {
    private static final Logger logger = LoggerFactory.getLogger(sendByMonitorTask.class);

    @Autowired
    private TaskService taskService;

    @Scheduled(cron = "0 0 0 * * ?")
    private void StudentTask() {
        logger.info("sendByMonitorTask----task::: 开始任务 ");
        taskService.sendInfoStudentRun();
        logger.info("sendByMonitorTask----task::: 结束任务 ");
    }
    @Scheduled(cron = "0 0 0 * * ?")
    private void teacherTask() {
        logger.info("sendByMonitorTask----task::: 开始任务 ");
        taskService.sendInfoTeacherRun();
        logger.info("sendByMonitorTask----task::: 结束任务 ");
    }
}

文件产出,设置下载条件,表头信息等

@Service
public class TaskService {
    private static final Logger logger = LoggerFactory.getLogger(TaskService.class);
    
    public void sendInfoStudentRun() {
        List<Map<String, Object>> maps = sendInfo(2);
        export(maps, 2);
    }
    public void sendInfoTeacherRun() {

        List<Map<String, Object>> maps = sendInfo(2);
        export(maps, 2);
    }

    public List<Map<String, Object>> sendInfo(Integer select) {
        List<Map<String, Object>> list = Lists.newArrayList();
        try {
            DateTime dateTime = DateTime.now();
            String minDay = dateTime.minusDays(1).toString("yyyy-MM-dd");//minusDays(1)==today-1
            String startTime = minDay + "00:00:00";
            String endTime = minDay + "23:59:59";
            String order = "asc";//排序
            Integer recordLength = Integer.parseInt("20000");
            Integer recordIndex = Integer.parseInt("0");

            Map<String, Object> param = new HashMap<String, Object>();
            param.put("startTime", startTime);
            param.put("endTime", endTime);
            param.put("order", order);
            param.put("recordLength", recordLength);
            param.put("recordIndex", recordIndex);
            param.put("select", select);

            list = getsqlmapper.sql(param);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("", e);
        }
        return list;
    }


    public void export(List<Map<String, Object>> list, Integer select) {

        try {
            if (list == null || list.size() == 0) {
                logger.info("TaskService-----export----导出为空");
            }
            if (1 == select) {
                create(list, select);
            } else if (2 == select) {
                create(list, select);
            }
        } catch (Exception e) {
            logger.info("TaskService-----export----导出失败" + e.getMessage());
        }
        logger.info("TaskService-----export----导出成功");
    }

    /**
     * 文件产出
     * @param list
     * @param select
     * @return
     */
    public Boolean create(List<Map<String, Object>> list, Integer select) {
        String fileNameHeader = "";
        String[] header = null;
        String[] rowCell = null;
        String path = "";

        if (1 == select) {
            fileNameHeader = "student ";
            header = new String[]{"name", "age", "class", "birthday"};
            rowCell = new String[]{"姓名", "年龄", "班级", "生日"};
            path = "D:\\A";
        } else if (2 == select) {
            fileNameHeader = "teacher ";
            header = new String[]{"name", "scoring"};
            rowCell = new String[]{"姓名", "评分"};
            path = "D:\\B";
        }
        if (StringUtils.isEmpty(path)) {
            logger.error("TaskService-----create----路径为空");
            return false;
        }
        try {
            String s = fileNameHeader + "_" + new DateTime().toString("yyyy-MM-dd") + ".xlsx";
            DownLoadToExcelUtil downLoadToExcelUtil = new DownLoadToExcelUtil();
            downLoadToExcelUtil.exportExcel(path, s, rowCell, handleData(list, header));

        } catch (Exception e) {
            logger.info("TaskService-----create----  ::  " + e.getMessage());
        }
        return true;
    }
    public List<List<String>> handleData(List<Map<String, Object>> list, String[] header) {
        List<List<String>> data = Lists.newArrayList();
        for (Map<String, Object> map : list) {
            List ent = Lists.newArrayList();
            for (String s : header) {
                if (map.get(s) instanceof Timestamp) {
                    ent.add(dateSDF(map.get(s)));

                } else {
                    ent.add(map.get(s));
                }
            }
            if (CollectionUtils.isEmpty(ent)) {//用来对集合null和空的判断
                data.add(ent);
            }
        }
        return data;
    }
    /**
     * 如果是时间，就转换成字符串
     *
     * @param o
     * @return
     */
    public String dateSDF(Object o) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss");
        return sdf.format(o);
    }
}

excel设置

@Slf4j
public class DownLoadToExcelUtil {


    public void exportExcel(String path, String s, String[] rowCell, List<List<String>> lists) {
        OutputStream out = null;
        try {
            s = new String(s.getBytes("gbk"), "UTF-8");
            File file = new File(path);

            if (!file.exists()) {
                file.mkdirs();
            }

            out = new FileOutputStream(path + s);

            Workbook workbook = new XSSFWorkbook();
            Sheet sheet1 = workbook.createSheet("Sheet1");
            sheet1.setDefaultColumnWidth(25);
            Row row = sheet1.createRow(0);
            for (int i = 0; i < rowCell.length; i++) {
                Cell cell = row.createCell((short) i);
                cell.setCellStyle(getHeaderStyle(workbook));
                XSSFRichTextString text = new XSSFRichTextString(rowCell[i]);
                cell.setCellValue(text);

            }
            int rowIndex = 1;//行
            for (List<String> list : lists) {
                //遍历集合数据,产生数据行
                if (list != null) {
                    row = sheet1.createRow(rowIndex);
                    int cellIndex = 0;//列
                    for (String e : list) {
                        Cell cell = row.createCell(cellIndex);
                        cell.setCellValue(e);
                        cellIndex++;
                    }
                }
                rowIndex++;
            }
            workbook.write(out);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private CellStyle getHeaderStyle(Workbook workbook) {
        //生产一个样式
        CellStyle style = workbook.createCellStyle();
        //设置这些样式
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //生成字体
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        //把字体应用到当前样式
        style.setFont(font);
        //指定当单元格内容提示不下时自动换行
        style.setWrapText(true);
        return style;

    }

}