Hutool优雅导出动态表头的excel文档

在项目开发中常遇到导出excel的处理,在开发中,发现如果是动态表头,原有poi处理起来麻烦不少,后来调研了下Hutool,发现处理起excel还是很方便的。话不多说,上代码。
一、excel导出

  1. controller层
    @ApiOperation(value="教学任务-成绩录入-导出当前表格", notes="教学任务-成绩录入-导出当前表格方法", httpMethod="POST")
    @RequestMapping(value="/exportresultinputtable", method=RequestMethod.POST)
    public ResponseEntity<byte[]> exportResultInputTable(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String currentUserId = "";
        Map<String, String> requestMap = RequestResponseUtils.getRequestBodyMap(request);// 获取请求页面的参数
        ResultInputExportExcel<TtTeachingTaskResultInputDto> exportExcel = iTtTeachingTaskResultInputService.exportResultInputTable(requestMap, currentUserId);//查询对应导出excel对象
        ExcelWriter writer = ExportExcelUtils.buildResultInputWorkBook(exportExcel);

        String fileName = "成绩录入-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
        // 下载显示的文件名,解决中文名称乱码问题
        String downloadFielName = URLEncoder.encode(fileName, "UTF-8");//new String(fileName.getBytes("UTF-8"),"iso-8859-1");
        // 下面开始设置HttpHeaders,使得浏览器响应下载
        HttpHeaders headers = new HttpHeaders();
        // 设置响应方式
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        // 设置响应文件
        headers.setContentDispositionFormData("attachment", downloadFielName);

        ByteArrayOutputStream outByteStream = null;
        try {
            outByteStream = new ByteArrayOutputStream();
            // 写出到文件
            writer.flush(outByteStream, true);
            // 关闭writer,释放内存
            writer.close();
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(outByteStream.toByteArray(), headers, HttpStatus.CREATED);
    }
  1. service层
public interface ITtTeachingTaskResultInputService {

    /**
     * 教学任务-成绩录入-导出当前表格
     * @param requestMap
     * @param currentUserId
     * @return
     */
    public ResultInputExportExcel<TtTeachingTaskResultInputDto> exportResultInputTable(Map<String, String> requestMap, String currentUserId);
    }

3.service实现层

@Service
public class TtTeachingTaskResultInputServiceImpl implements ITtTeachingTaskResultInputService {

    @Autowired
    private TtTeachingTaskResultInputMapper teachingTaskResultInputMapper;
    @Autowired
    private IOtOutlineTaskAssessModeService iOtOutlineTaskAssessModeService;
    @Autowired
    private IOtOutlineTaskAssessItemsService iOtOutlineTaskAssessItemsService;
    @Autowired
    private ISysUserService sysUserService;
    @Autowired
    private ISysClassService sysClassService;
    @Autowired
    private ITsTrainingSchemeRelationTaskClassService tsTrainingSchemeRelationTaskClassService;
    @Autowired
    private ITsTrainingSchemeAssignTaskService tsTrainingSchemeAssignTaskService;
    @Autowired
    private ITtTeachingTaskAchievementSumService ttTeachingTaskAchievementSumService;
    @Autowired
    private IOtOutlineTaskRelationPointTargetService iOtOutlineTaskRelationPointTargetService;
    @Autowired
    private IOtOutlineTaskTeachingTargetService iOtOutlineTaskTeachingTargetService;
    @Autowired
    private ITtTeachingTaskAssessModeReachService ttTeachingTaskAssessModeReachService;
    @Autowired
    private TsTrainingSchemeDocContentGraduateRelateCourseService tsTrainingSchemeDocContentGraduateRelateCourseService;
    @Autowired
    private IOtOutlineTaskRelationTetargetAsmodeWeightSettingService iOtOutlineTaskRelationTetargetAsmodeWeightSettingService;
    @Autowired
    private ITtTeachingTaskStudentIdxPointReachService ttTeachingTaskStudentIdxPointReachService;
    @Autowired
    private TsTrainingSchemeDocContentIdxPointMapper tsTrainingSchemeDocContentIdxPointMapper;
    @Autowired
    private OtOutlineTaskRelationPointTargetMapper otOutlineTaskRelationPointTargetMapper;
    @Autowired
    private OtOutlineTaskTeachingTargetMapper otOutlineTaskTeachingTargetMapper;
    @Autowired
    private OtOutlineTaskRelationTetargetAsmodeWeightSettingMapper otOutlineTaskRelationTetargetAsmodeWeightSettingMapper;
    @Autowired
    private OtOutlineTaskAssessItemsMapper otOutlineTaskAssessItemsMapper;
    @Autowired
    private OtOutlineTaskRelationTargetItemsMapper otOutlineTaskRelationTargetItemsMapper;

    /**
     * 教学任务-成绩录入-导出当前表格
     * @param requestMap
     * @param currentUserId
     * @return
     */
    @Override
    public ResultInputExportExcel<TtTeachingTaskResultInputDto> exportResultInputTable(Map<String, String> requestMap, String currentUserId) {
        String assignTaskId = (String) requestMap.get("assignTaskId");//分配任务id
        String trainingSchemaId = (String) requestMap.get("trainingSchemaId");//培养方案id
        String courseId = (String) requestMap.get("courseId");//课程id
        String classYear = (String) requestMap.get("classYear");//年级
        String scId = (String) requestMap.get("scId");//班级id
        String assessModeId = (String) requestMap.get("assessModeId");//考核方式id

        Map<String, Object> parameterMap = new HashMap<>();
        parameterMap.put("assignTaskId", assignTaskId);
        parameterMap.put("trainingSchemaId", trainingSchemaId);
        parameterMap.put("courseId", courseId);
        parameterMap.put("classYear", classYear);
        parameterMap.put("scId", scId);

        List<OtOutlineTaskAssessModeDto> assessModeDtoList = new ArrayList<>();
        //全部
        if("all".equals(assessModeId)){
            //考核方式
            List<OtOutlineTaskAssessMode> assessModeList = iOtOutlineTaskAssessModeService.selectAssessModeListBySchemaIdAndCourseId(parameterMap);
            //考核项
            if(!StringUtils.isEmpty(assessModeList)){
                assessModeList.stream().filter(assessMode -> {
                    OtOutlineTaskAssessModeDto assessModeDto = new OtOutlineTaskAssessModeDto();
                    assessModeDto.setId(assessMode.getId());
                    assessModeDto.setAssessMode(assessMode.getAssessMode());

                    Map<String, Object> pMap = new HashMap<>();
                    pMap.put("assessModeId", assessMode.getId());
                    List<OtOutlineTaskAssessItems> assessItemsList = iOtOutlineTaskAssessItemsService.selectAssessItemsByAssessModeId(pMap);
                    if(!StringUtils.isEmpty(assessItemsList)){
                        List<OtOutlineTaskAssessItemsDto> itemsDtoList = new ArrayList<>();
                        assessItemsList.stream().filter(assessItems -> {
                            OtOutlineTaskAssessItemsDto assessItemsDto = new OtOutlineTaskAssessItemsDto();
                            assessItemsDto.setId(assessItems.getId());
                            assessItemsDto.setAssessItems(assessItems.getAssessItems());
                            assessItemsDto.setMark(assessItems.getMark());
                            itemsDtoList.add(assessItemsDto);
                            return true;
                        }).collect(Collectors.toList());
                        assessModeDto.setItemsDtoList(itemsDtoList);
                    }
                    assessModeDtoList.add(assessModeDto);
                    return true;
                }).collect(Collectors.toList());
            }
        }else{
            //考核方式
            OtOutlineTaskAssessMode assessMode = iOtOutlineTaskAssessModeService.selectByPrimaryKey(assessModeId);
            //考核项
            if(!StringUtils.isEmpty(assessMode)){
                OtOutlineTaskAssessModeDto assessModeDto = new OtOutlineTaskAssessModeDto();
                assessModeDto.setId(assessMode.getId());
                assessModeDto.setAssessMode(assessMode.getAssessMode());

                Map<String, Object> pMap = new HashMap<>();
                pMap.put("assessModeId", assessMode.getId());
                List<OtOutlineTaskAssessItems> assessItemsList = iOtOutlineTaskAssessItemsService.selectAssessItemsByAssessModeId(pMap);
                if(!StringUtils.isEmpty(assessItemsList)){
                    List<OtOutlineTaskAssessItemsDto> itemsDtoList = new ArrayList<>();
                    assessItemsList.stream().filter(assessItems -> {
                        OtOutlineTaskAssessItemsDto assessItemsDto = new OtOutlineTaskAssessItemsDto();
                        assessItemsDto.setId(assessItems.getId());
                        assessItemsDto.setAssessItems(assessItems.getAssessItems());
                        assessItemsDto.setMark(assessItems.getMark());
                        itemsDtoList.add(assessItemsDto);
                        return true;
                    }).collect(Collectors.toList());
                    assessModeDto.setItemsDtoList(itemsDtoList);
                }
                assessModeDtoList.add(assessModeDto);
            }
        }

        String title = "成绩录入";//显示的导出表的标题
        Object[] headsName = new Object[]{"序号","姓名","学号",assessModeDtoList};//导出表的头
        String[] colsName = new String[]{"","userName","userNumber","scoreInformation"};//导出表的列

        List<TtTeachingTaskResultInputDto> resultInputDtoList = teachingTaskResultInputMapper.selectListResultInput(parameterMap); //查询集合
        if(!StringUtils.isEmpty(resultInputDtoList)){
            //全部
            if(!"all".equals(assessModeId)){
                resultInputDtoList.stream().filter(resultInputDto -> {
                    String scoreInformation = resultInputDto.getScoreInformation();
                    JSONObject jsonObject = JSON.parseObject(scoreInformation);
                    Set<String> set = jsonObject.keySet();
                    for(String key : set){
                        if(!jsonObject.containsKey(assessModeId)){
                            jsonObject.remove(key);
                        }
                    }
                    resultInputDto.setScoreInformation(jsonObject.toJSONString());
                    return true;
                });
            }
        }
        ResultInputExportExcel<TtTeachingTaskResultInputDto> exportExcel = new ResultInputExportExcel<TtTeachingTaskResultInputDto>(null, title, null, colsName, resultInputDtoList, headsName);
        return exportExcel;
    }
    }
public interface IOtOutlineTaskAssessModeService {

    /**
     * 获取考核方式信息
     * @param requestMap
     * @return
     */
    public List<OtOutlineTaskAssessMode> selectAssessModeListBySchemaIdAndCourseId(Map<String, Object> requestMap);

    /**
     *保存考核方式信息
     * @param modeDto
     * @param currentUserId
     * @return
     */
    public ResponseInfo insertOutlineTaskAssessMode(OtOutlineTaskAssessModeDto modeDto, String currentUserId);

    /**
     *修改考核方式信息
     * @param modeDto
     * @param currentUserId
     * @return
     */
    public ResponseInfo updateOutlineTaskAssessMode(OtOutlineTaskAssessModeDto modeDto, String currentUserId);

    /**
     *删除考核方式信息
     * @param modeDto
     * @param currentUserId
     * @return
     */
    public ResponseInfo deleteOutlineTaskAssessMode(OtOutlineTaskAssessModeDto modeDto, String currentUserId);

    /**
     * 根据id获取考核方式信息
     * @param id
     * @return
     */
    public OtOutlineTaskAssessMode selectByPrimaryKey(String id);

    /**
     * 根据培养方案id、课程id、任务id获取考核方式信息
     * @param requestMap
     * @return
     */
    public List<OtOutlineTaskAssessMode> selectAssessModeListBySchemaIdAndCourseIdAndAssignTaskId(Map<String, Object> requestMap);
}
package org.zkpk.module.teacher.service.impl;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.zkpk.module.common.enums.MessageEnum;
import org.zkpk.module.common.result.ResponseInfo;
import org.zkpk.module.common.utils.DataUUIDUtils;
import org.zkpk.module.teacher.domain.dto.OtOutlineTaskAssessModeDto;
import org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode;
import org.zkpk.module.teacher.mapper.OtOutlineTaskAssessModeMapper;
import org.zkpk.module.teacher.service.IOtOutlineTaskAssessModeService;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class OtOutlineTaskAssessModeServiceImpl implements IOtOutlineTaskAssessModeService {

    @Autowired
    private OtOutlineTaskAssessModeMapper otOutlineTaskAssessModeMapper;


    /**
     * 根据id获取考核方式信息
     * @param id
     * @return
     */
    @Override
    public OtOutlineTaskAssessMode selectByPrimaryKey(String id) {
        return otOutlineTaskAssessModeMapper.selectByPrimaryKey(id);
    }


}

package org.zkpk.module.teacher.service;

import org.zkpk.module.common.result.ResponseInfo;
import org.zkpk.module.teacher.domain.dto.OtOutlineTaskAssessItemsDto;
import org.zkpk.module.teacher.domain.dto.TTRAssessItemsSonDto;
import org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems;

import java.util.List;
import java.util.Map;

public interface IOtOutlineTaskAssessItemsService {

    /**
     * 大纲任务-获取考核项信息
     * @param requestMap
     * @return
     */
    public List<OtOutlineTaskAssessItems> selectAssessItemsByAssessModeId(Map<String, Object> requestMap);
    }
package org.zkpk.module.teacher.service.impl;

import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.zkpk.module.common.enums.MessageEnum;
import org.zkpk.module.common.result.ResponseInfo;
import org.zkpk.module.common.utils.DataUUIDUtils;
import org.zkpk.module.teacher.domain.dto.OtOutlineTaskAssessItemsDto;
import org.zkpk.module.teacher.domain.dto.TTRAssessItemsSonDto;
import org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems;
import org.zkpk.module.teacher.mapper.OtOutlineTaskAssessItemsMapper;
import org.zkpk.module.teacher.service.IOtOutlineTaskAssessItemsService;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class OtOutlineTaskAssessItemsServiceImpl implements IOtOutlineTaskAssessItemsService {

    @Autowired
    private OtOutlineTaskAssessItemsMapper otOutlineTaskAssessItemsMapper;

    /**
     * 大纲任务-获取考核项信息
     * @param requestMap
     * @return
     */
    @Override
    public List<OtOutlineTaskAssessItems> selectAssessItemsByAssessModeId(Map<String, Object> requestMap) {
        String assessModeId = (String) requestMap.get("assessModeId");//教学大纲任务—考核方式表主键
        //获取考核项信息
        List<OtOutlineTaskAssessItems> itemsList = otOutlineTaskAssessItemsMapper.selectByAssessModeId(assessModeId);
        return itemsList;
    }
}
  1. mapper
@Repository
public interface OtOutlineTaskAssessModeMapper {
    int deleteByPrimaryKey(String id);

    int insert(OtOutlineTaskAssessMode record);

    int insertSelective(OtOutlineTaskAssessMode record);

    OtOutlineTaskAssessMode selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(OtOutlineTaskAssessMode record);

    int updateByPrimaryKey(OtOutlineTaskAssessMode record);

    List<OtOutlineTaskAssessMode> selectBySchemaIdAndCourseId(Map<String, Object> parameterMap);

    int deleteBySchemaIdAndCourseId(String schemaId, String courseId);

    int batchInsert(List<OtOutlineTaskAssessMode> modeList);

    List<OtOutlineTaskAssessMode> selectAssessModeListBySchemaIdAndCourseIdAndAssignTaskId(Map<String, Object> requestMap);
}
package org.zkpk.module.teacher.mapper;

import org.springframework.stereotype.Repository;
import org.zkpk.module.teacher.domain.dto.OtOutlineTaskAssessItemsDto;
import org.zkpk.module.teacher.domain.dto.TTRAssessItemsSonDto;
import org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems;

import java.util.List;
import java.util.Map;

@Repository
public interface OtOutlineTaskAssessItemsMapper {
    int deleteByPrimaryKey(String id);

    int insert(OtOutlineTaskAssessItems record);

    int insertSelective(OtOutlineTaskAssessItems record);

    OtOutlineTaskAssessItems selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(OtOutlineTaskAssessItems record);

    int updateByPrimaryKey(OtOutlineTaskAssessItems record);

    List<OtOutlineTaskAssessItems> selectByAssessModeId(String assessModeId);

    int deleteByAssessModeId(String assessModeId);

    int batchInsert(List<OtOutlineTaskAssessItemsDto> itemsDtoList);

    int selectSumByAssessModeIds(String assessModeIds);

    List<OtOutlineTaskAssessItems> selectBySchemaIdAndCourseId(Map<String, Object> parameterMap);

    List<TTRAssessItemsSonDto> selectTargetAndItems(Map<String, Object> requestMap);
}
  1. xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.zkpk.module.teacher.mapper.OtOutlineTaskAssessModeMapper" >
  <resultMap id="BaseResultMap" type="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="assign_task_id" property="assignTaskId" jdbcType="VARCHAR" />
    <result column="training_schema_id" property="trainingSchemaId" jdbcType="VARCHAR" />
    <result column="course_id" property="courseId" jdbcType="VARCHAR" />
    <result column="assess_mode" property="assessMode" jdbcType="VARCHAR" />
    <result column="weight_ratio" property="weightRatio" jdbcType="VARCHAR" />
    <result column="sort" property="sort" jdbcType="INTEGER" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_by" property="updateBy" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="is_deleted" property="isDeleted" jdbcType="TINYINT" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, assign_task_id, training_schema_id, course_id, assess_mode, weight_ratio, sort, 
    create_by, create_time, update_by, update_time, is_deleted
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from ot_outline_task_assess_mode
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from ot_outline_task_assess_mode
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode" >
    insert into ot_outline_task_assess_mode (id, assign_task_id, training_schema_id, 
      course_id, assess_mode, weight_ratio, 
      sort, create_by, create_time, 
      update_by, update_time, is_deleted
      )
    values (#{id,jdbcType=VARCHAR}, #{assignTaskId,jdbcType=VARCHAR}, #{trainingSchemaId,jdbcType=VARCHAR}, 
      #{courseId,jdbcType=VARCHAR}, #{assessMode,jdbcType=VARCHAR}, #{weightRatio,jdbcType=VARCHAR}, 
      #{sort,jdbcType=INTEGER}, #{createBy,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, #{isDeleted,jdbcType=TINYINT}
      )
  </insert>
  <insert id="insertSelective" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode" >
    insert into ot_outline_task_assess_mode
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="assignTaskId != null" >
        assign_task_id,
      </if>
      <if test="trainingSchemaId != null" >
        training_schema_id,
      </if>
      <if test="courseId != null" >
        course_id,
      </if>
      <if test="assessMode != null" >
        assess_mode,
      </if>
      <if test="weightRatio != null" >
        weight_ratio,
      </if>
      <if test="sort != null" >
        sort,
      </if>
      <if test="createBy != null" >
        create_by,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
      <if test="updateBy != null" >
        update_by,
      </if>
      <if test="updateTime != null" >
        update_time,
      </if>
      <if test="isDeleted != null" >
        is_deleted,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="assignTaskId != null" >
        #{assignTaskId,jdbcType=VARCHAR},
      </if>
      <if test="trainingSchemaId != null" >
        #{trainingSchemaId,jdbcType=VARCHAR},
      </if>
      <if test="courseId != null" >
        #{courseId,jdbcType=VARCHAR},
      </if>
      <if test="assessMode != null" >
        #{assessMode,jdbcType=VARCHAR},
      </if>
      <if test="weightRatio != null" >
        #{weightRatio,jdbcType=VARCHAR},
      </if>
      <if test="sort != null" >
        #{sort,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isDeleted != null" >
        #{isDeleted,jdbcType=TINYINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode" >
    update ot_outline_task_assess_mode
    <set >
      <if test="assignTaskId != null" >
        assign_task_id = #{assignTaskId,jdbcType=VARCHAR},
      </if>
      <if test="trainingSchemaId != null" >
        training_schema_id = #{trainingSchemaId,jdbcType=VARCHAR},
      </if>
      <if test="courseId != null" >
        course_id = #{courseId,jdbcType=VARCHAR},
      </if>
      <if test="assessMode != null" >
        assess_mode = #{assessMode,jdbcType=VARCHAR},
      </if>
      <if test="weightRatio != null" >
        weight_ratio = #{weightRatio,jdbcType=VARCHAR},
      </if>
      <if test="sort != null" >
        sort = #{sort,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        create_by = #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        update_by = #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isDeleted != null" >
        is_deleted = #{isDeleted,jdbcType=TINYINT},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessMode" >
    update ot_outline_task_assess_mode
    set assign_task_id = #{assignTaskId,jdbcType=VARCHAR},
      training_schema_id = #{trainingSchemaId,jdbcType=VARCHAR},
      course_id = #{courseId,jdbcType=VARCHAR},
      assess_mode = #{assessMode,jdbcType=VARCHAR},
      weight_ratio = #{weightRatio,jdbcType=VARCHAR},
      sort = #{sort,jdbcType=INTEGER},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      is_deleted = #{isDeleted,jdbcType=TINYINT}
    where id = #{id,jdbcType=VARCHAR}
  </update>
</mapper>
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.zkpk.module.teacher.mapper.OtOutlineTaskAssessItemsMapper" >
  <resultMap id="BaseResultMap" type="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems" >
    <id column="id" property="id" jdbcType="VARCHAR" />
    <result column="assess_mode_id" property="assessModeId" jdbcType="VARCHAR" />
    <result column="training_schema_id" property="trainingSchemaId" jdbcType="VARCHAR" />
    <result column="course_id" property="courseId" jdbcType="VARCHAR" />
    <result column="assess_items" property="assessItems" jdbcType="VARCHAR" />
    <result column="mark" property="mark" jdbcType="INTEGER" />
    <result column="sort" property="sort" jdbcType="INTEGER" />
    <result column="create_by" property="createBy" jdbcType="VARCHAR" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_by" property="updateBy" jdbcType="VARCHAR" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
    <result column="is_deleted" property="isDeleted" jdbcType="TINYINT" />
  </resultMap>
  <sql id="Base_Column_List" >
    id, assess_mode_id, training_schema_id, course_id, assess_items, mark, sort, create_by, 
    create_time, update_by, update_time, is_deleted
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    from ot_outline_task_assess_items
    where id = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from ot_outline_task_assess_items
    where id = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems" >
    insert into ot_outline_task_assess_items (id, assess_mode_id, training_schema_id, 
      course_id, assess_items, mark, 
      sort, create_by, create_time, 
      update_by, update_time, is_deleted
      )
    values (#{id,jdbcType=VARCHAR}, #{assessModeId,jdbcType=VARCHAR}, #{trainingSchemaId,jdbcType=VARCHAR}, 
      #{courseId,jdbcType=VARCHAR}, #{assessItems,jdbcType=VARCHAR}, #{mark,jdbcType=INTEGER}, 
      #{sort,jdbcType=INTEGER}, #{createBy,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, 
      #{updateBy,jdbcType=VARCHAR}, #{updateTime,jdbcType=TIMESTAMP}, #{isDeleted,jdbcType=TINYINT}
      )
  </insert>
  <insert id="insertSelective" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems" >
    insert into ot_outline_task_assess_items
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="assessModeId != null" >
        assess_mode_id,
      </if>
      <if test="trainingSchemaId != null" >
        training_schema_id,
      </if>
      <if test="courseId != null" >
        course_id,
      </if>
      <if test="assessItems != null" >
        assess_items,
      </if>
      <if test="mark != null" >
        mark,
      </if>
      <if test="sort != null" >
        sort,
      </if>
      <if test="createBy != null" >
        create_by,
      </if>
      <if test="createTime != null" >
        create_time,
      </if>
      <if test="updateBy != null" >
        update_by,
      </if>
      <if test="updateTime != null" >
        update_time,
      </if>
      <if test="isDeleted != null" >
        is_deleted,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="assessModeId != null" >
        #{assessModeId,jdbcType=VARCHAR},
      </if>
      <if test="trainingSchemaId != null" >
        #{trainingSchemaId,jdbcType=VARCHAR},
      </if>
      <if test="courseId != null" >
        #{courseId,jdbcType=VARCHAR},
      </if>
      <if test="assessItems != null" >
        #{assessItems,jdbcType=VARCHAR},
      </if>
      <if test="mark != null" >
        #{mark,jdbcType=INTEGER},
      </if>
      <if test="sort != null" >
        #{sort,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isDeleted != null" >
        #{isDeleted,jdbcType=TINYINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems" >
    update ot_outline_task_assess_items
    <set >
      <if test="assessModeId != null" >
        assess_mode_id = #{assessModeId,jdbcType=VARCHAR},
      </if>
      <if test="trainingSchemaId != null" >
        training_schema_id = #{trainingSchemaId,jdbcType=VARCHAR},
      </if>
      <if test="courseId != null" >
        course_id = #{courseId,jdbcType=VARCHAR},
      </if>
      <if test="assessItems != null" >
        assess_items = #{assessItems,jdbcType=VARCHAR},
      </if>
      <if test="mark != null" >
        mark = #{mark,jdbcType=INTEGER},
      </if>
      <if test="sort != null" >
        sort = #{sort,jdbcType=INTEGER},
      </if>
      <if test="createBy != null" >
        create_by = #{createBy,jdbcType=VARCHAR},
      </if>
      <if test="createTime != null" >
        create_time = #{createTime,jdbcType=TIMESTAMP},
      </if>
      <if test="updateBy != null" >
        update_by = #{updateBy,jdbcType=VARCHAR},
      </if>
      <if test="updateTime != null" >
        update_time = #{updateTime,jdbcType=TIMESTAMP},
      </if>
      <if test="isDeleted != null" >
        is_deleted = #{isDeleted,jdbcType=TINYINT},
      </if>
    </set>
    where id = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKey" parameterType="org.zkpk.module.teacher.domain.entity.OtOutlineTaskAssessItems" >
    update ot_outline_task_assess_items
    set assess_mode_id = #{assessModeId,jdbcType=VARCHAR},
      training_schema_id = #{trainingSchemaId,jdbcType=VARCHAR},
      course_id = #{courseId,jdbcType=VARCHAR},
      assess_items = #{assessItems,jdbcType=VARCHAR},
      mark = #{mark,jdbcType=INTEGER},
      sort = #{sort,jdbcType=INTEGER},
      create_by = #{createBy,jdbcType=VARCHAR},
      create_time = #{createTime,jdbcType=TIMESTAMP},
      update_by = #{updateBy,jdbcType=VARCHAR},
      update_time = #{updateTime,jdbcType=TIMESTAMP},
      is_deleted = #{isDeleted,jdbcType=TINYINT}
    where id = #{id,jdbcType=VARCHAR}
  </update>


  <select id="selectByAssessModeId" resultMap="BaseResultMap" parameterType="java.lang.String" >
    select
    <include refid="Base_Column_List" />
    from ot_outline_task_assess_items
    where assess_mode_id = #{assessModeId,jdbcType=VARCHAR} and is_deleted = 0
  </select>
</mapper>
  1. bean信息
public class TtTeachingTaskResultInputDto extends TtTeachingTaskResultInput {

    private String suId;

    private String userName;

    private String userNumber;

    private String classYear;

    private String classId;

    private String className;

    private String scId;

    public String getSuId() {
        return suId;
    }

    public void setSuId(String suId) {
        this.suId = suId;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserNumber() {
        return userNumber;
    }

    public void setUserNumber(String userNumber) {
        this.userNumber = userNumber;
    }

    public String getClassYear() {
        return classYear;
    }

    public void setClassYear(String classYear) {
        this.classYear = classYear;
    }

    public String getClassId() {
        return classId;
    }

    public void setClassId(String classId) {
        this.classId = classId;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

    public String getScId() {
        return scId;
    }

    public void setScId(String scId) {
        this.scId = scId;
    }
}
public class TtTeachingTaskResultInput implements Serializable {
    private String id;

    private String assignTaskId;

    private String trainingSchemaId;

    private String courseId;

    private String studentId;

    private String createBy;

    private Date createTime;

    private String updateBy;

    private Date updateTime;

    private Integer isDeleted;

    private String scoreInformation;

    private static final long serialVersionUID = 1L;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getAssignTaskId() {
        return assignTaskId;
    }

    public void setAssignTaskId(String assignTaskId) {
        this.assignTaskId = assignTaskId == null ? null : assignTaskId.trim();
    }

    public String getTrainingSchemaId() {
        return trainingSchemaId;
    }

    public void setTrainingSchemaId(String trainingSchemaId) {
        this.trainingSchemaId = trainingSchemaId == null ? null : trainingSchemaId.trim();
    }

    public String getCourseId() {
        return courseId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId == null ? null : courseId.trim();
    }

    public String getStudentId() {
        return studentId;
    }

    public void setStudentId(String studentId) {
        this.studentId = studentId == null ? null : studentId.trim();
    }

    public String getCreateBy() {
        return createBy;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy == null ? null : createBy.trim();
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy == null ? null : updateBy.trim();
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Integer isDeleted) {
        this.isDeleted = isDeleted;
    }

    public String getScoreInformation() {
        return scoreInformation;
    }

    public void setScoreInformation(String scoreInformation) {
        this.scoreInformation = scoreInformation == null ? null : scoreInformation.trim();
    }
    }
public class OtOutlineTaskAssessMode implements Serializable {
    private String id;

    private String assignTaskId;

    private String trainingSchemaId;

    private String courseId;

    private String assessMode;

    private String weightRatio;

    private Integer sort;

    private String createBy;

    private Date createTime;

    private String updateBy;

    private Date updateTime;

    private Integer isDeleted;

    private static final long serialVersionUID = 1L;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getAssignTaskId() {
        return assignTaskId;
    }

    public void setAssignTaskId(String assignTaskId) {
        this.assignTaskId = assignTaskId == null ? null : assignTaskId.trim();
    }

    public String getTrainingSchemaId() {
        return trainingSchemaId;
    }

    public void setTrainingSchemaId(String trainingSchemaId) {
        this.trainingSchemaId = trainingSchemaId == null ? null : trainingSchemaId.trim();
    }

    public String getCourseId() {
        return courseId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId == null ? null : courseId.trim();
    }

    public String getAssessMode() {
        return assessMode;
    }

    public void setAssessMode(String assessMode) {
        this.assessMode = assessMode == null ? null : assessMode.trim();
    }

    public String getWeightRatio() {
        return weightRatio;
    }

    public void setWeightRatio(String weightRatio) {
        this.weightRatio = weightRatio == null ? null : weightRatio.trim();
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public String getCreateBy() {
        return createBy;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy == null ? null : createBy.trim();
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy == null ? null : updateBy.trim();
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Integer isDeleted) {
        this.isDeleted = isDeleted;
    }}
public class OtOutlineTaskAssessItems implements Serializable {
    private String id;

    private String assessModeId;

    private String trainingSchemaId;

    private String courseId;

    private String assessItems;

    private Integer mark;

    private Integer sort;

    private String createBy;

    private Date createTime;

    private String updateBy;

    private Date updateTime;

    private Integer isDeleted;

    private static final long serialVersionUID = 1L;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public String getAssessModeId() {
        return assessModeId;
    }

    public void setAssessModeId(String assessModeId) {
        this.assessModeId = assessModeId == null ? null : assessModeId.trim();
    }

    public String getTrainingSchemaId() {
        return trainingSchemaId;
    }

    public void setTrainingSchemaId(String trainingSchemaId) {
        this.trainingSchemaId = trainingSchemaId == null ? null : trainingSchemaId.trim();
    }

    public String getCourseId() {
        return courseId;
    }

    public void setCourseId(String courseId) {
        this.courseId = courseId == null ? null : courseId.trim();
    }

    public String getAssessItems() {
        return assessItems;
    }

    public void setAssessItems(String assessItems) {
        this.assessItems = assessItems == null ? null : assessItems.trim();
    }

    public Integer getMark() {
        return mark;
    }

    public void setMark(Integer mark) {
        this.mark = mark;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public String getCreateBy() {
        return createBy;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy == null ? null : createBy.trim();
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy == null ? null : updateBy.trim();
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Integer getIsDeleted() {
        return isDeleted;
    }

    public void setIsDeleted(Integer isDeleted) {
        this.isDeleted = isDeleted;
    }
    }
public class OtOutlineTaskAssessModeDto extends OtOutlineTaskAssessMode {
    /**
     * 考核项集合
     */
    private List<OtOutlineTaskAssessItemsDto> itemsDtoList;

    public List<OtOutlineTaskAssessItemsDto> getItemsDtoList() {
        return itemsDtoList;
    }

    public void setItemsDtoList(List<OtOutlineTaskAssessItemsDto> itemsDtoList) {
        this.itemsDtoList = itemsDtoList;
    }
}

8.具体工具类

import java.lang.reflect.Field;
import java.util.*;

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.zkpk.module.common.utils.DateUtils;
import org.zkpk.module.common.utils.StringUtils;

public class ExportExcelUtils {

	// 日志
	private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);


	/**
	 *教学任务-成绩录入-导出当前表格
	 * @param <T>
	 * @date 2022-02-14 16:53:57
	 * @param exportExcel
	 * @return
	 * @throws Exception
	 */
	public static <T> ExcelWriter buildResultInputWorkBook(ResultInputExportExcel<T> exportExcel) throws Exception {
		List<T> dataList = exportExcel.getDataList();// 导出的数据
		ExcelWriter writer = getExcelHead(exportExcel);

		//体数据
		int number = 0;
		int p = 3;
		for(T t : dataList){
			writer.writeCellValue(0, number + 2, number);//序号
			Field[] filedArray = getAllFields(t.getClass());
			for (int k = 0; k < filedArray.length; k++) {
				Field f = filedArray[k];
				f.setAccessible(true);
				if("userName".equals(f.getName())){
					String userName = (String) f.get(t);
					writer.writeCellValue(2, number + 2, userName);
				}
				if("userNumber".equals(f.getName())){
					String userNumber = (String) f.get(t);
					writer.writeCellValue(1, number + 2, userNumber).getCellStyle();
				}
				if("scoreInformation".equals(f.getName())){
					String scoreInformation = (String) f.get(t);
					JSONObject parentJsonObject = JSONObject.parseObject(scoreInformation);
					if(!StringUtils.isEmpty(parentJsonObject)){
						for (Map.Entry<String, Object> entry : parentJsonObject.entrySet()) {
							JSONArray jsonArray = (JSONArray) entry.getValue();
							for(Object sonJson : jsonArray){
								JSONObject sonJsonObject = JSONObject.parseObject(String.valueOf(sonJson));
								for (Map.Entry<String, Object> soneEntry : sonJsonObject.entrySet()) {
									JSONArray sonJsonArray = (JSONArray) soneEntry.getValue();
									Map scoreMap = (Map) sonJsonArray.get(0);
									String score = (String) scoreMap.get("score");//分数

									Map statusMap = (Map) sonJsonArray.get(1);
									String status = (String) statusMap.get("status");//状态  1:重新补考  2:重修 3:补考 4:缺考 5:正常
									writer.writeCellValue(p, number + 2, score);
									p ++;
								}
							}
						}
					}
				}
			}
			number ++;
		}

		//自适应列宽
		setSizeColumn(writer.getSheet(),6);
		return writer;
	}

	

	/**
	 * 获取excel头信息
	 * @param exportExcel
	 * @param <T>
	 * @return
	 */
	private static <T> ExcelWriter getExcelHead(ResultInputExportExcel<T> exportExcel) throws Exception{
		ExcelWriter writer = ExcelUtil.getWriter();
		Object[] objectHeadsName = exportExcel.getObjectHeadsName();// 导出表的表头

		// 输出excel表头
		int columnNum = objectHeadsName.length;
		//获取最后一个元素
		List<T> columnList = (List<T>) objectHeadsName[objectHeadsName.length - 1];
		columnNum = columnNum + columnList.size() -1;
		String[]headsName = {};
		String[] prefixHeadsName = {(String) objectHeadsName[0], (String) objectHeadsName[1], (String) objectHeadsName[2]};
		String[] suffixHeadsName = new String[columnList.size()];

		LinkedHashMap<String, Object> pMap = new LinkedHashMap();
		first : for(int j = 0; j < columnList.size(); j++){
			T t = columnList.get(j);
			LinkedList<Object> totalList = new LinkedList<>();
			for (Field f : getAllFields(t.getClass())) {
				f.setAccessible(true);
				Object value = f.get(t);
				//考核项集合
				LinkedList<String> suffixList = new LinkedList<>();
				if("java.util.List".equals(f.getType().getName())){
					List<T> sList = (List<T>) value;
					second : for(int k = 0; k < sList.size(); k++){
						T t1 = sList.get(k);
						String str = "";
						for (Field f1 : getAllFields(t1.getClass())) {
							f1.setAccessible(true);
							Object sonValue = f1.get(t1);

							if("assessItems".equals(f1.getName())){
								str = String.valueOf(sonValue);
							}
							if("mark".equals(f1.getName())){
								str += "(满分:"+String.valueOf(sonValue)+")";
								break;
							}
						}
						suffixList.add(str);
						totalList.add(suffixList);
					}
				}else{
					if("assessMode".equals(f.getName())){
						suffixHeadsName[j] = String.valueOf(value);
						pMap.put(suffixHeadsName[j], totalList.get(j));
						continue first;
					}
				}
			}
		}
		//合并数组
		headsName = StringUtils.concat(prefixHeadsName, suffixHeadsName);

		// 合并单元格后的标题行,使用默认标题样式
		int modeColumn = 0;
		int itemsColumn = 0;
		int firstColumn = 0;
		int lastColumn = 0;
		int x = 0;
		int y = 0;
		int m = 0;
		int n = 1;
		//处理excel头信息
		for(int i = 0; i < headsName.length; i++){
			if(i <= 2){
				writer.merge(0,1, firstColumn, lastColumn, headsName[i], true);
				firstColumn ++;
				lastColumn ++;
				x ++;
				m = x;
			}else{
				for(Map.Entry<String, Object> map : pMap.entrySet()){
					//考核方式相等
					if(headsName[i].equals(map.getKey())){
						LinkedList<Object> assessItemsList = (LinkedList<Object>) map.getValue();
						if(assessItemsList.size() > 0){
							itemsColumn = assessItemsList.size();
							modeColumn = assessItemsList.size() - 1;

							for(Object assessItems : assessItemsList){
								writer.writeCellValue(m, n, assessItems);
								m ++;
							}
						}
						break;
					}
				}

				if(itemsColumn == 1 || itemsColumn == 0){
					writer.writeCellValue(x, y, headsName[i]);
				}else{
					writer.merge(0,0, x, x + modeColumn, headsName[i], true);
				}
				x = x + modeColumn +1;
			}
		}
		return writer;
	}

	/**
	 * 自适应宽度
	 * @param sheet
	 * @param size 因为for循环从0开始,size值为 列数-1
	 */
	public static void setSizeColumn(Sheet sheet, int size) {
		for (int columnNum = 0; columnNum <= size; columnNum++) {
			int columnWidth = sheet.getColumnWidth(columnNum) / 256;
			for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row currentRow;
				//当前行未被使用过
				if (sheet.getRow(rowNum) == null) {
					currentRow = sheet.createRow(rowNum);
				} else {
					currentRow = sheet.getRow(rowNum);
				}

				if (currentRow.getCell(columnNum) != null) {
					Cell currentCell = currentRow.getCell(columnNum);
					if (currentCell.getCellType() == CellType.STRING) {
						int length = currentCell.getStringCellValue().getBytes().length;
						if (columnWidth < length) {
							columnWidth = length;
						}
					}
				}
			}
			sheet.setColumnWidth(columnNum, columnWidth * 256);
		}
	}
}

9.表信息

CREATE TABLE `ot_outline_task_assess_items` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `assess_mode_id` varchar(32) DEFAULT NULL COMMENT '考核方式表主键',
  `training_schema_id` varchar(32) DEFAULT NULL COMMENT '培养方案表主键',
  `course_id` varchar(32) DEFAULT NULL COMMENT '课程id',
  `assess_items` varchar(50) DEFAULT NULL COMMENT '考核项',
  `mark` int(3) DEFAULT NULL COMMENT '分数',
  `sort` int(4) DEFAULT NULL COMMENT '排序',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `is_deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除  0:未删除  1:删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考核项表';
CREATE TABLE `ot_outline_task_assess_mode` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `assign_task_id` varchar(32) DEFAULT NULL COMMENT '分配任务表主键',
  `training_schema_id` varchar(32) DEFAULT NULL COMMENT '培养方案表主键',
  `course_id` varchar(32) DEFAULT NULL COMMENT '课程id',
  `assess_mode` varchar(32) DEFAULT NULL COMMENT '考核方式',
  `weight_ratio` varchar(10) DEFAULT NULL COMMENT '权重比例',
  `sort` int(4) DEFAULT NULL COMMENT '排序',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `is_deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除  0:未删除  1:删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='考核方式表';
CREATE TABLE `tt_teaching_task_result_input` (
  `id` varchar(32) NOT NULL COMMENT '主键',
  `assign_task_id` varchar(32) DEFAULT NULL COMMENT '分配任务表主键',
  `training_schema_id` varchar(32) DEFAULT NULL COMMENT '培养方案表主键',
  `course_id` varchar(32) DEFAULT NULL COMMENT '课程id',
  `student_id` varchar(32) DEFAULT NULL COMMENT '学生id',
  `score_information` text DEFAULT NULL COMMENT '成绩信息 json信息',
  `create_by` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(32) DEFAULT NULL COMMENT '修改人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `is_deleted` tinyint(1) DEFAULT NULL COMMENT '是否删除  0:未删除  1:删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩录入表';

10.导出的excel表格
在这里插入图片描述表头是动态变换的,上面的大类是考核方式、小类是考核项。根据数据库表中数据动态变换。

Hutool是一个功能强大的Java工具包,它提供了一套便捷的操作Excel的功能。其中,导出动态多级表头通常涉及到使用`AutoPOI`库,它是Hutool Excel模块的一部分。动态多级表头意味着表头可以根据数据模型的层次结构自动生成,每一级都对应数据集的不同维度。 以下是使用Hutool创建动态多级表头的一般步骤: 1. **准备数据模型**:首先,你需要有一个递归的数据结构,比如树形结构,表示表头的层级关系。 2. **初始化工作簿**:使用`ExcelWriter`初始化一个新的Excel文件,并设置表头样式。 3. **遍历数据模型**:通过递归遍历数据模型,将每一层的数据添加到新的工作表中,并在适当的位置设置表头。 4. **设置多级表头**:利用`Hutool Excel`提供的API,如`setMultiLevelHeader()`,可以设置单元格为合并单元格并显示不同级别的标题。 5. **保存文件**:完成所有数据和表头的设置后,调用`finishWrite()`保存Excel文件。 以下是一个简单的示例代码片段: ```java Excel excel = new Excel(); DataTree tree = ... // 初始化你的数据模型 // 创建一个新工作簿 Workbook workbook = Workbook.create(); Sheet sheet = workbook.createSheet(); // 遍历数据模型,生成表头 generateHeaders(sheet, tree); // 设置多级表头 sheet.setMultiLevelHeader(0, 0, Arrays.asList(tree.getHeader())); // 写入数据 for (List<DataRow> rowData : tree.getData()) { excel.write(rowData, sheet); } // 保存文件 FileOutputStream outputStream = new FileOutputStream("output.xlsx"); workbook.write(outputStream); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值