按周统计数据并且导出案例-前端layui加后端POI

前端效果：

导出效果：

 后端代码：

controller层：

 @RequestMapping(value = "/exportResearchXls")
    @ResponseBody
    public void exportResearchXls(HttpServletRequest request) {
        User user = SuperKit.getLoginedUser(request);
        IContextDictionary dict = getContextDictionary();
        JSONObject query = marketResearchService.getmarketResearchDatas(dict);
        String widthsStr = dict.getString("widths");
        String titlesStr = dict.getString("titles");
        if (StringUtil.isEmpty(titlesStr)){
            return;
        }
        //防止顺序错乱
        LinkedHashMap jsonMap = JSON.parseObject(titlesStr,LinkedHashMap.class, Feature.OrderedField);
        JSONObject titles = new JSONObject(true);
        titles.putAll(jsonMap);
        //JSONObject titles = JSONUtil.parseObject(titlesStr);
        JSONObject widths =null;
        if(StringUtil.isNotEmpty(widthsStr)){
            widths = JSONUtil.parseObject(widthsStr);
        }
        JSONArray data = query.getJSONArray("data");
        String fileName = dict.getString("fileName");
        if(StringUtil.isEmpty(fileName)){
            fileName = SuperKit.getUUID();
        }
        marketResearchService.exportXls(getResponse(),fileName,titles,widths,data,dict);
    }

 service层：

 @Override
    public JSONObject getmarketResearchDatas(IContextDictionary dic) {
        String queryResearchTime = dic.getString("query_researchTime");
        String queryStart ="";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar cal = Calendar.getInstance();
        if (StringUtil.isNotBlank(queryResearchTime)){
            try {
                queryStart =queryResearchTime.substring(0,10);
                cal.setTime(sdf.parse(queryStart));
                cal.add(Calendar.DATE,6);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        JSONArray ja = new JSONArray();
        List<ExportEntity> exportEntities = new ArrayList<>();
        Map<String, String> weekDate = WeekUtils.getWeekDate(queryStart);
        DataTable table = marketResearchDao.getMarketResearchData(dic);
        ExportEntity amExportEntity = new ExportEntity();
        ExportEntity pmExportEntity = new ExportEntity();
        amExportEntity.setTime("上午");
        pmExportEntity.setTime("下午");
        if (table != null && table.getRows().size() > 0) {
            for (DataRow row : table) {
                //开始日期
                String cwrq = row.getString("CWRQ");
                //主题
                String bt = row.getString("BT");
                //参与人员
                String ztc = row.getString("ZTC");
                String startDate = parseStringDate(cwrq);
                //上午信息封装
                if (WeekUtils.isAmOrPm(cwrq) == 0) {
                    WeekUtils.getExportEntity(weekDate, startDate, amExportEntity, cwrq, bt, ztc, "\n");
                }
                //下午信息封装
                if (WeekUtils.isAmOrPm(cwrq) == 1) {
                    WeekUtils.getExportEntity(weekDate, startDate, pmExportEntity, cwrq, bt, ztc, "\n");
                }
            }
            exportEntities.add(amExportEntity);
            exportEntities.add(pmExportEntity);
        }
        //封装导出信息
        for (ExportEntity exportEntity : exportEntities) {
            JSONObject obj = new JSONObject();
            obj.put("time", exportEntity.getTime());
            obj.put("Monday", exportEntity.getMonday());
            obj.put("Tuesday", exportEntity.getTuesday());
            obj.put("Wednesday", exportEntity.getWednesday());
            obj.put("Thursday", exportEntity.getThursday());
            obj.put("Friday", exportEntity.getFriday());
            obj.put("Saturday", exportEntity.getSaturday());
            obj.put("Sunday", exportEntity.getSunday());
            ja.add(obj);
        }
        JSONObject obj2 = new JSONObject();
        obj2.put("code", "");
        obj2.put("count", 1);
        obj2.put("data", ja);
        return obj2;
    }

    @Override
    public void exportXls(HttpServletResponse response, String fileName, JSONObject titles, JSONObject widths, JSONArray data,IContextDictionary dict) {
        String queryResearchTime = dict.getString("query_researchTime");
        String queryStart ="";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar cal = Calendar.getInstance();
        if (StringUtil.isNotBlank(queryResearchTime)){
            try {
                queryStart =queryResearchTime.substring(0,10);
                cal.setTime(sdf.parse(queryStart));
                cal.add(Calendar.DATE,6);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        Map<String, String> weekDate = WeekUtils.getWeekDate(queryStart);
        String monday = weekDate.get("Monday");
        String sunday = weekDate.get("Sunday");
        if (titles == null || titles.isEmpty()) {
            return;
        }
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        //列表头样式
        HSSFFont font = ExcelUtils.getFont(workbook, "宋体", (short) 20, false);
        HSSFCellStyle titleStyle = ExcelUtils.getTitleStyle(workbook, font, HorizontalAlignment.CENTER);
        font = ExcelUtils.getFont(workbook, "宋体", (short) 14, false);
        HSSFCellStyle bodyStyle = ExcelUtils.getStyle(workbook, font, HorizontalAlignment.CENTER);
        Set<String> titlesKey = titles.keySet();
        int i = 0;
        for (String key : titlesKey) {
            //设置宽度
            Integer width = widths.getInteger(key);
            if (width == null)
                width = 180;

            width = (int) ((width / 6) * 256);
            if (width > 254 * 256)
                width = 254 * 256;
            sheet.setColumnWidth(i, width);
            //设置表头
            HSSFRow row = sheet.getRow(1);
            if (row == null) {
                row = sheet.createRow(1);
            }
            row.setHeightInPoints(30);
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(titles.getString(key));
            cell.setCellStyle(bodyStyle);
            //设置内容
            for (int j = 0; j < data.size(); j++) {
                JSONObject item = data.getJSONObject(j);
                row = sheet.getRow(j + 2);
                if (row == null) {
                    row = sheet.createRow(j + 2);
                }
                row.setHeightInPoints(200);
                cell = row.createCell(i);
                cell.setCellValue(item.getString(key));
                cell.setCellStyle(bodyStyle);
            }
            i++;
        }
        //设置表格最上面标题
        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell_10 = row1.createCell(0);
        row1.setHeightInPoints(40);
        cell_10.setCellStyle(titleStyle);
        cell_10.setCellValue("市内调研信息汇总表("+monday.substring(0,4)+"年"+monday.substring(5,7)+"月"+monday.substring(8,10)+"日-"+sunday.substring(0,4)+"年"+sunday.substring(5,7)+"月"+sunday.substring(8,10)+"日)");
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, --i);
        sheet.addMergedRegion(region);
        //合并后设置边框
//        setBorderStyle(HSSFCellStyle.BORDER_THIN, region, sheet, workbook);
        ExcelUtils.doDownload(workbook, fileName, response);
    }

日期工具类：

package cn.dreamit.p2047.module_201030153124oXgMmrAMZjrmGKQrpmi.util;

import cn.dreamit.dreamweb.util.StringUtil;
import cn.dreamit.p2047.module_201030153124oXgMmrAMZjrmGKQrpmi.entity.ExportEntity;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

public class WeekUtils {

    /**
     * 获取当前时间所在周的周一和周日的日期时间
     *
     * @return
     */
    public static Map<String, String> getWeekDate(String startTime) {
        Map<String, String> map = new HashMap();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

        Calendar cal = Calendar.getInstance();
        if (StringUtil.isNotBlank(startTime)) {
            try {
                cal.setTime(sdf.parse(startTime));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        // 设置一个星期的第一天，按中国的习惯一个星期的第一天是星期一
        cal.setFirstDayOfWeek(Calendar.MONDAY);
        // 获得当前日期是一个星期的第几天
        int dayWeek = cal.get(Calendar.DAY_OF_WEEK);

        cal.add(Calendar.DATE, cal.getFirstDayOfWeek() - dayWeek);// 根据日历的规则，给当前日期减去星期几与一个星期第一天的差值
        Date mondayDate = cal.getTime();
        String Monday = sdf.format(mondayDate);//周一

        cal.add(Calendar.DATE, 1);
        Date TuesdatDate = cal.getTime();
        String Tuesday = sdf.format(TuesdatDate);//周二

        cal.add(Calendar.DATE, 1);
        Date WednesdayDate = cal.getTime();
        String Wednesday = sdf.format(WednesdayDate);//周三

        cal.add(Calendar.DATE, 1);
        Date ThursdayDate = cal.getTime();
        String Thursday = sdf.format(ThursdayDate);//周四

        cal.add(Calendar.DATE, 1);
        Date FridayDate = cal.getTime();
        String Friday = sdf.format(FridayDate);//周五

        cal.add(Calendar.DATE, 1);
        Date SaturdayDate = cal.getTime();
        String Saturday = sdf.format(SaturdayDate);//周六

        cal.add(Calendar.DATE, 1);
        Date SundayDate = cal.getTime();
        String Sunday = sdf.format(SundayDate);//周日

        map.put("Monday", Monday);
        map.put("Tuesday", Tuesday);
        map.put("Wednesday", Wednesday);
        map.put("Thursday", Thursday);
        map.put("Friday", Friday);
        map.put("Saturday", Saturday);
        map.put("Sunday", Sunday);
        return map;
    }

    /**
     * 0:上午
     * 1：下午
     *
     * @param dateTime
     * @return
     */
    public static int isAmOrPm(String dateTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date parse = null;
        try {
            parse = sdf.parse(dateTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        GregorianCalendar ca = new GregorianCalendar();
        ca.setTime(parse);
        return ca.get(GregorianCalendar.AM_PM);
    }

    /**
     * 将精确到时分秒的日期精确到天
     *
     * @param dateStr
     * @return
     */
    public static String parseStringDate(String dateStr) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date parse = null;
        try {
            parse = sdf.parse(dateStr);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return sdf.format(parse);
    }

    /**
     * @param weekDate     周几集合
     * @param startDate    开始日期
     * @param exportEntity 实体类
     * @param cwrq         日期
     * @param bt           主题
     */
    public static void getExportEntity(Map<String, String> weekDate, String startDate, ExportEntity exportEntity, String cwrq, String bt, String ztc, String nextLine) {
        String content = cwrq.substring(11, 13) + "时" + cwrq.substring(14, 16) + "分：\n" + bt + "、" + ztc + nextLine;
        if (weekDate.get("Monday").equals(startDate)) {
            exportEntity.setMonday((StringUtil.isNotBlank(exportEntity.getMonday()) ? exportEntity.getMonday() : "") + content);
        } else if (weekDate.get("Tuesday").equals(startDate)) {
            exportEntity.setTuesday((StringUtil.isNotBlank(exportEntity.getTuesday()) ? exportEntity.getTuesday() : "") + content);
        } else if (weekDate.get("Wednesday").equals(startDate)) {
            exportEntity.setWednesday((StringUtil.isNotBlank(exportEntity.getWednesday()) ? exportEntity.getWednesday() : "") + content);
        } else if (weekDate.get("Thursday").equals(startDate)) {
            exportEntity.setThursday((StringUtil.isNotBlank(exportEntity.getThursday()) ? exportEntity.getThursday() : "") + content);
        } else if (weekDate.get("Friday").equals(startDate)) {
            exportEntity.setFriday((StringUtil.isNotBlank(exportEntity.getFriday()) ? exportEntity.getFriday() : "") + content);
        } else if (weekDate.get("Saturday").equals(startDate)) {
            exportEntity.setSaturday((StringUtil.isNotBlank(exportEntity.getSaturday()) ? exportEntity.getSaturday() : "") + content);
        } else if (weekDate.get("Sunday").equals(startDate)) {
            exportEntity.setSunday((StringUtil.isNotBlank(exportEntity.getSunday()) ? exportEntity.getSunday() : "") + content);
        }
    }
}