Functional realization of data reports

The background that

Operation sister every half a month will find me to pull a data file, table head is fixed, so a few columns. The logic inside is to associate multiple tables, get the corresponding fields respectively, write them to Excel and export them to the little sister.

In the beginning, every time the little sister came to me, I would manually pull one for her. She usually talks to me in the morning, and I send it to her at lunch.

It takes a while to run the script, and the tables are in two separate libraries, one MongoDB and one MySQL. Except for one field that needs to be matched from MySQL, all other fields are taken from MongoDB.

First, execute the JS script inside MongoDB. The running time is longer. Run to manually export, and then import into MySQL, SQL execution is simple, manually associated with the following table, get the target field, the data report can be exported to cross the difference.

But something so regular should be done by a machine.

Automatic function

There are two interfaces (dataSync) for the scheduled tasks and download for the little sister. Perform scheduled tasks on the first and the early morning of each month to synchronize data. During the day, the little sister can download the data file directly with the browser. The dataSync interface doesn’t really have much to say about what it does. It is to use the project language to turn the js scripts and SQL scripts mentioned above into functional modules. Note that there are two databases involved, so multiple data sources need to be configured.

The download file

The file was downloaded using EasyExcel. Use HttpServletResponse Response directly to download directly through the browser link.

    @GetMapping("/dataList/download")
    public void download(HttpServletResponse response) throws IOException {
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("Data -" + LocalDate.now(), "UTF-8");
            response.setHeader("Content-disposition"."attachment; filename=" + fileName + ".xlsx");
            List<Entity> dataList = repository.findAll();
            logger.info("data size = {}", dataList.size());
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)12);
            headWriteCellStyle.setWriteFont(headWriteFont);
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            contentWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    newHorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); EasyExcel.write(response.getOutputStream(), Entity.class).autoCloseStream(Boolean.FALSE).sheet(LocalDate.now().toString()).registerWriteHandler(horizontalCellStyleS trategy) .doWrite(dataList); }catch (Exception e) {
            logger.error("Download failed ERROR MSG =>", e);
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status"."failure");
            map.put("message"."Download file failed"+ e.getMessage()); response.getWriter().println(JSON.toJSONString(map)); }} ` ` `Copy the code