java 实现自定义数据模板下载Execl

先看看我们要实现的效果图

1:导入maven依赖

		<dependency>
			<groupId>cn.hutool</groupId>
			<artifactId>hutool-all</artifactId>
			<version>5.5.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>4.1.2</version>
		</dependency>

2:请求类

@Slf4j
@Api(tags = "文档下载",value = "文档下载")
@RestController
@RequestMapping("/api/download/file")
public class DownloadFileController {

    @PostMapping("/word")
    @ApiOperation("下载word模板")
    public void word(HttpServletRequest request, HttpServletResponse response){
        //自定义标题行1
        String attendanceGroup="考勤组别:  10167-风控中心  (注意:导入模板必须严格按照现有格式，否则无法识别到PeopleSoft考勤管理后台)";
        //自定义标题行2
        String shift="排班信息:  班次Z1(总部周六班)：08:30-18:30  班次B4(班次4)：08:30-17:30  班次C4(班次14)：20:00-次日08:00  班次E7(班次37)：21:00-次日09:00  班次H7(班次67)：15:00-22:00 班次WY(物业假期班)：08:30-17:30  班次B0(班次206)：08:30-次日09:00  班次B1(班次207)：15:00-次日06:00";
        WordUtil.createWord(request, response,attendanceGroup,shift);
    }
}

3:逻辑处理层

@Slf4j
public class WordUtil {

    /**
     * 创建排班word文档
     * @param request
     * @param response
     */
    public static void createWord(HttpServletRequest request, HttpServletResponse response,String attendanceGroup,String shift){
        //日期
        List<String> allDay = getMonthAllDay(LocalDate.now());
        //所有行数据
        List<Map<String,Object>> rows = new ArrayList<>();
        //自定义模板数据
        for (int i = 1; i <= 5; i++) {
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("id",i);
            map.put("employeeId","XC0000".concat(i+""));
            map.put("employeeRecord","0");
            map.put("employeeName","张".concat(i+""));
            map.put("employeeProject","新城控股");
            map.put("departmentName","开发部");
            map.put("positionName","Java开发工程师");
            for (String day : allDay) {
                String name = "date".concat(day);
                map.put(name,"");
            }
            rows.add(map);
        }
        int row = 2 + rows.size();
        ExcelWriter writer = null;
        ServletOutputStream out = null;
        //表结构
        try {
            writer = ExcelUtil.getWriter();
            //自定义列名别名
            writer.addHeaderAlias("id", "序号");
            writer.addHeaderAlias("employeeId", "员工ID");
            writer.addHeaderAlias("employeeRecord", "员工记录");
            writer.addHeaderAlias("employeeName", "员工姓名");
            writer.addHeaderAlias("employeeProject", "员工项目");
            writer.addHeaderAlias("departmentName", "部门名称");
            writer.addHeaderAlias("positionName", "岗位名称");
            //读取数据作为列名
            for (String day : allDay) {
                String name = "date".concat(day);
                writer.addHeaderAlias(name,day.concat("(").concat(getWeekOfDate(day)).concat(")"));
            }
            writer.setOnlyAlias(true);
            int size = 6 + allDay.size();
            //合并单元格后的标题行，使用默认标题样式  6+allDay.size():合并单元格的长度
            writer.merge(size,attendanceGroup);
            writer.merge(size,shift);
            //一次性写出内容，强制输出标题
            writer.write(rows, true);
            //设置样式
            setWordStyle(writer,size,row);
            // 设置文件后缀
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
            String fn = "排版导入模板" + sdf.format(new java.util.Date()) + ".xls";
            // 读取字符编码
            String utf = "UTF-8";
            //转流下载
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode(fn, utf));
            out = response.getOutputStream();
            //将Excel 刷出到输出流
            writer.flush(out, true);
        }catch (Exception e){
            log.error("create word error:{}",e);
        }finally {
            //关闭writer，释放内存
            if (ObjectUtil.isNotNull(writer)){
                writer.close();
            }
            //关闭输出Servlet流
            if (ObjectUtil.isNotNull(out)){
                IoUtil.close(out);
            }
        }
        log.info("create word success");
    }

    /**
     * 获取当前时间本月的所有日期（yyyy-MM-dd）
     * @param date 当前时间
     * @return List<String>
     */
    private static List<String> getMonthAllDay(LocalDate date){
        List<String> allDates = CollUtil.newArrayList();
        int year = date.getYear();
        log.info("year:{}",year);
        int month = date.getMonthValue();
        log.info("month:{}",month);
        Calendar cal = Calendar.getInstance();
        cal.clear();
        cal.set(Calendar.YEAR,year);
        cal.set(Calendar.MONTH,month-1);
        cal.set(Calendar.DAY_OF_MONTH,1);
        int maxDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        log.info("maxDay:{}",maxDay);
        for (int i = 1; i <= maxDay; i++) {
            String day = new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime());
            allDates.add(day);
            cal.add(Calendar.DAY_OF_MONTH,1);
        }
        return allDates;
    }

    /**
     * 设置表格样式
     * @param writer
     */
    private static void setWordStyle(ExcelWriter writer,int size,int row) throws Exception{
        CellStyle headCellStyle = writer.getHeadCellStyle();
        //设置表头左对齐
        headCellStyle.setAlignment(HorizontalAlignment.LEFT);
        //设置表格内容自适应
        writer.autoSizeColumnAll();
        Sheet sheet = writer.getSheet();
        //默认取第一行数据，解决中文自适应宽度不足问题
        if (sheet != null && sheet.getRow(1) != null) {
            int physicalNumberOfCells = sheet.getRow(1).getPhysicalNumberOfCells();
            for (int i = 0; i < physicalNumberOfCells; i++) {
                // 调整每一列宽度
                sheet.autoSizeColumn((short) i);
                // 解决自动设置列宽中文失效的问题
                sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
            }
        }
        //自定义下拉框数据 可读取数据库塞入
        List<String> strings=new ArrayList<>();
        strings.add("A1");
        strings.add("A2");
        strings.add("A3");
        strings.add("B1");
        String string=strings.stream().collect(Collectors.joining(","));//结果 "A1,A2,A3,B1"
        CellRangeAddressList list=new CellRangeAddressList(3,row,7,size);//3到row行、7到size列添加下拉框
        writer.addSelect(list,string);
    }

    /**
     * 获取日期星期
     * @param dateStr
     * @return
     */
    private static String getWeekOfDate(String dateStr) {
        String[] weekDays = { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
        Calendar cal = Calendar.getInstance();
        cal.setTime(DateUtil.parse(dateStr));
        int w = cal.get(Calendar.DAY_OF_WEEK) - 1;
        return w<0?weekDays[0]:weekDays[w];
    }
}