AdministerServiceImpl.java

package com.subsidy.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.subsidy.common.RedisPrefixConstant;
import com.subsidy.common.exception.HttpException;
import com.subsidy.dto.administer.ClassDailyInfoDTO;
import com.subsidy.dto.administer.ClassDetailDTO;
import com.subsidy.dto.administer.ClassVodDailyInfoDTO;
import com.subsidy.dto.administer.ClassVodInfoDTO;
import com.subsidy.dto.administer.MemberStudyLogDTO;
import com.subsidy.dto.administer.OperatorsDTO;
import com.subsidy.dto.member.ImportMemberDTO;
import com.subsidy.mapper.AdministerMapper;
import com.subsidy.mapper.AnsweringQuestionMapper;
import com.subsidy.mapper.ClassDictMapper;
import com.subsidy.mapper.ClassMemberMappingMapper;
import com.subsidy.mapper.CompanyDictMapper;
import com.subsidy.mapper.CourseDictMapper;
import com.subsidy.mapper.DepartmentDictMapper;
import com.subsidy.mapper.ExerciseDoneResultMapper;
import com.subsidy.mapper.MemberDepartmentMappingMapper;
import com.subsidy.mapper.MemberMapper;
import com.subsidy.mapper.OprAdmDictMapper;
import com.subsidy.mapper.RoleAdministerMappingMapper;
import com.subsidy.mapper.VodDictMapper;
import com.subsidy.mapper.VodPlayHistoryMapper;
import com.subsidy.model.AdministerDO;
import com.subsidy.model.AnsweringQuestionDO;
import com.subsidy.model.ClassDictDO;
import com.subsidy.model.ClassMemberMappingDO;
import com.subsidy.model.CompanyDictDO;
import com.subsidy.model.CourseDictDO;
import com.subsidy.model.DepartmentDictDO;
import com.subsidy.model.ExerciseDoneHistoryDO;
import com.subsidy.model.ExerciseDoneResultDO;
import com.subsidy.model.MemberDO;
import com.subsidy.model.MemberDepartmentMappingDO;
import com.subsidy.model.RoleAdministerMappingDO;
import com.subsidy.model.SignInRecordDO;
import com.subsidy.model.VodDictDO;
import com.subsidy.model.OprAdmDictDO;
import com.subsidy.service.AdministerService;
import com.subsidy.service.FieldDictService;
import com.subsidy.util.ConstantUtils;
import com.subsidy.util.DateFormatUtil;
import com.subsidy.util.ExcelFormatUtils;
import com.subsidy.util.JwtUtil;
import com.subsidy.util.Localstorage;
import com.subsidy.util.MathUtil;
import com.subsidy.util.RedisUtil;
import com.subsidy.util.excel.ExcelUtil;
import com.subsidy.vo.administer.AdministerPermissionVO;
import com.subsidy.vo.administer.ClassDailyInfoVO;
import com.subsidy.vo.administer.ClassSummaryVO;
import com.subsidy.vo.administer.ClassVodDailyInfoItemVO;
import com.subsidy.vo.administer.ClassVodInfoVO;
import com.subsidy.vo.administer.ExerciseTestVO;
import com.subsidy.vo.administer.GetMemberPapersVO;
import com.subsidy.vo.administer.GetPaperDetailVO;
import com.subsidy.vo.administer.LoginVO;
import com.subsidy.vo.administer.MemberStudyLogVO;
import com.subsidy.vo.administer.OperatorsVO;
import com.subsidy.vo.administer.PermissionsVO;
import com.subsidy.vo.classdict.ClassDetailVO;
import com.subsidy.vo.done.GetMaxScoreVO;
import com.subsidy.vo.done.TestScoreInfoVO;
import com.subsidy.vo.member.ClassSignVO;
import com.subsidy.vo.sign.AnswerRecordVO;
import com.subsidy.vo.vod.ClassMemberPlayLengthVO;
import com.subsidy.vo.vod.ClassVodCompleteInfoVO;
import com.subsidy.vo.vod.DayInfoItemVO;
import com.subsidy.vo.vod.GetMemberStudyInfoVO;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;


/**
 * <p>
 * 管理平台用户 服务实现类
 * </p>
 *
 * @author DengMin
 * @since 2021-10-11
 */
@Service
public class AdministerServiceImpl extends ServiceImpl<AdministerMapper, AdministerDO> implements AdministerService {

    @Autowired
    private ClassDictMapper classDictMapper;

    @Autowired
    private VodPlayHistoryMapper vodPlayHistoryMapper;

    @Autowired
    private ExerciseDoneResultMapper exerciseDoneResultMapper;

    @Autowired
    private AnsweringQuestionMapper answeringQuestionMapper;

    @Autowired
    private ClassMemberMappingMapper classMemberMappingMapper;

    @Autowired
    private CourseDictMapper courseDictMapper;

    @Autowired
    private CompanyDictMapper companyDictMapper;

    @Autowired
    private RoleAdministerMappingMapper roleAdministerMappingMapper;

    @Autowired
    private MemberMapper memberMapper;

    @Autowired
    private DepartmentDictMapper departmentDictMapper;

    @Autowired
    private MemberDepartmentMappingMapper memberDepartmentMappingMapper;

    @Autowired
    private VodDictMapper vodDictMapper;

    @Autowired
    private RedisUtil redisUtil;

    @Autowired
    private OprAdmDictMapper oprAdmDictMapper;

    @Autowired
    private FieldDictService fieldDictService;

    //@Autowired
    //private MongoTemplate mongoTemplate;

    public LoginVO login(AdministerDO administerDO) {

        LoginVO loginVO = new LoginVO();

        /**
         * 先从redis里拿
         */
        AdministerDO administerDO1 = (AdministerDO) redisUtil.get(RedisPrefixConstant.SUBSIDY_ADMINISTER_PREFIX + administerDO.getAccountName());
        if (null == administerDO1) {
            /**
             *  查表,并将数据写入到redis
             */
            administerDO1 = this.baseMapper.selectOne(new QueryWrapper<AdministerDO>()
                    .lambda()
                    .eq(AdministerDO::getAccountName, administerDO.getAccountName()));
            redisUtil.set(RedisPrefixConstant.SUBSIDY_ADMINISTER_PREFIX + administerDO.getAccountName(), administerDO1);
        }

        if ("0".equals(administerDO1.getStatus())) {
            throw new HttpException(10013);
        }
        /**
         * 最近5次都输入失败，5分钟后再验证
         */
        List<OprAdmDictDO> oprAdmDictDOS = oprAdmDictMapper.getLoginInfo(administerDO1.getId());

        //查mysql逻辑
        //List<OprAdmDictDO> oprAdmDictDOS = oprAdmDictMapper.getLoginInfo(administerDO1.getId());

        boolean flag = false;
        for (int i = 0; i < oprAdmDictDOS.size(); i++) {
            if (oprAdmDictDOS.get(i).getResult() == 1) {
                flag = true;
                break;
            }
        }

        if (!flag && oprAdmDictDOS.size() >= 4) {
            throw new HttpException(10016);
        }

        if (null != administerDO1) {
            if (administerDO.getPassword().equals(administerDO1.getPassword())) {
                String token = JwtUtil.generateToken(administerDO1.getId(), ConstantUtils.ADMINISTER_TERMINATE);
                //登录成功，记录日志
                OprAdmDictDO oprAdmDictDO = new OprAdmDictDO();
                oprAdmDictDO.setUserId(administerDO1.getId());
                oprAdmDictDO.setOprType("登录");
                oprAdmDictDO.setResult(1);
                //oprAdmDictDO.setCreateDate(System.currentTimeMillis() + "");
                //oprAdmDictDO.setLoginDateFormat(DateFormatUtil.format(new Date(), "yyyyMMdd"));
                //oprAdmDictDO.setUserName(administerDO1.getUserName());
                //oprAdmDictDO.setCompanyId(administerDO1.getCompanyId());
                oprAdmDictMapper.insert(oprAdmDictDO);
                //mongoTemplate.insert(oprAdmDictDO);
                loginVO.setToken(token);
                return loginVO;
            } else {
                //登录失败，记录日志
                OprAdmDictDO oprAdmDictDO = new OprAdmDictDO();
                oprAdmDictDO.setUserId(administerDO1.getId());
                oprAdmDictDO.setOprType("登录");
                oprAdmDictDO.setResult(0);
                oprAdmDictMapper.insert(oprAdmDictDO);
                //oprAdmDictDO.setCreateDate(System.currentTimeMillis() + "");
                //oprAdmDictDO.setLoginDateFormat(DateFormatUtil.format(new Date(), "yyyyMMdd"));
                //oprAdmDictDO.setUserName(administerDO1.getUserName());
                //oprAdmDictDO.setCompanyId(administerDO1.getCompanyId());
                //mongoTemplate.insert(oprAdmDictDO);

                int i = 0;

                for (OprAdmDictDO oad : oprAdmDictDOS) {
                    if (oad.getResult() == 1) {
                        break;
                    }
                    i++;
                }

                if (i == 0) {
                    throw new HttpException(10024);
                } else if (i == 1) {
                    throw new HttpException(10023);
                } else if (i == 2) {
                    throw new HttpException(10022);
                } else if (i == 3) {
                    throw new HttpException(10021);
                } else {
                    throw new HttpException(10016);
                }

            }
        } else {
            throw new HttpException(10011);
        }
    }

    public AdministerPermissionVO getPermissions() {

        AdministerPermissionVO administerPermissionVO = new AdministerPermissionVO();
        AdministerDO administerDO = (AdministerDO) Localstorage.getUser();
        if (administerDO == null) {
            throw new HttpException(10012);
        }
        BeanUtils.copyProperties(administerDO, administerPermissionVO);

        List<PermissionsVO> list = this.baseMapper.getPermissions(administerDO.getId());
        List<PermissionsVO> treeList = new ArrayList();
        if (list != null) {
            list.forEach(permission -> {
                if (permission.getParentId() == null) {
                    treeList.add(permission);
                }

                list.forEach(p -> {
                    if (null != p.getParentId() && p.getParentId().equals(permission.getId())) {
                        if (permission.getChildren() == null) {
                            permission.setChildren(new ArrayList<>());
                        }
                        permission.getChildren().add(p);
                    }
                });
            });
        }
        administerPermissionVO.setPermissions(treeList);
        return administerPermissionVO;
    }

    public IPage<OperatorsVO> operators(OperatorsDTO operatorsDTO) {
        Page pager = new Page(operatorsDTO.getPageNum(), operatorsDTO.getPageSize());

        //判断该用户的角色
        Integer count = roleAdministerMappingMapper.selectCount(new QueryWrapper<RoleAdministerMappingDO>()
                .lambda()
                .eq(RoleAdministerMappingDO::getAdministerId, operatorsDTO.getId())
                .eq(RoleAdministerMappingDO::getRoleId, 1));

        if (count > 0) {
            operatorsDTO.setId(null);
        }

        IPage<OperatorsVO> operatorsVOIPage = companyDictMapper.operators(pager, operatorsDTO.getCompanyName(), operatorsDTO.getId(), operatorsDTO.getUserName(), 0);

        List<OperatorsVO> operatorsVOS = operatorsVOIPage.getRecords();
        for (OperatorsVO operatorsVO : operatorsVOS){
            //operatorsVO.getField()
        }

        return operatorsVOIPage;
    }

    public String addAdminister(AdministerDO administerDO) {

        int count = this.baseMapper.selectCount(new QueryWrapper<AdministerDO>()
                .lambda()
                .eq(AdministerDO::getAccountName, administerDO.getAccountName()));

        if (count > 0) {
            throw new HttpException(20002);
        }

        administerDO.setRole(0);
        administerDO.setPassword("admin123");
        administerDO.setStatus("1");
        this.baseMapper.insert(administerDO);

        RoleAdministerMappingDO roleAdministerMappingDO = new RoleAdministerMappingDO();
        roleAdministerMappingDO.setAdministerId(administerDO.getId());
        roleAdministerMappingDO.setRoleId(1L);
        roleAdministerMappingMapper.insert(roleAdministerMappingDO);

        return ConstantUtils.ADD_SUCCESS;
    }

    public String deleteAdminister(AdministerDO administerDO) {
        this.baseMapper.deleteById(administerDO.getId());

        roleAdministerMappingMapper.delete(new QueryWrapper<RoleAdministerMappingDO>()
                .lambda()
                .eq(RoleAdministerMappingDO::getAdministerId, administerDO.getId()));
        return ConstantUtils.DELETE_SUCCESS;
    }

    public String updateAdminister(AdministerDO administerDO) {

        int count = this.baseMapper.selectCount(new QueryWrapper<AdministerDO>()
                .lambda()
                .eq(AdministerDO::getAccountName, administerDO.getAccountName())
                .ne(AdministerDO::getId, administerDO.getId()));

        if (count > 0) {
            throw new HttpException(20002);
        }
        this.baseMapper.updateById(administerDO);
        AdministerDO administerDO1 = this.baseMapper.selectById(administerDO.getId());
        //更新redis里该老师的数据
        redisUtil.set(RedisPrefixConstant.SUBSIDY_ADMINISTER_PREFIX + administerDO1.getAccountName(), administerDO1);
        return ConstantUtils.SET_SUCCESS;
    }

    public ClassSummaryVO classSummary(ClassDetailDTO classDetailDTO) {

        ClassSummaryVO classSummaryVO = new ClassSummaryVO();

        ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());
        classSummaryVO.setClassId(classDetailDTO.getId());
        classSummaryVO.setClassName(classDictDO.getClassName());
        classSummaryVO.setStartDate(classDictDO.getStartDate());
        classSummaryVO.setEndDate(classDictDO.getEndDate());

        CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());
        classSummaryVO.setCourseName(courseDictDO.getCourseName());

        //班级人数
        List<ClassMemberMappingDO> classMemberMappingDOS = classMemberMappingMapper.selectList(new QueryWrapper<ClassMemberMappingDO>()
                .lambda()
                .eq(ClassMemberMappingDO::getClassId, classDetailDTO.getId()));
        classSummaryVO.setMemberCount(classMemberMappingDOS.size());

        //课程总数
        List<VodDictDO> vodDictDOS = classDictMapper.getClassVods(classDetailDTO.getId());
        classSummaryVO.setTotalVodCounts(vodDictDOS.size());

        /**
         *  学生平均学习时长和平均学习次数
         */
        int totalStudyVods = 0;
        int totalStudyLength = 0;
        //学生总共学习课时数
        for (VodDictDO vodDictDO : vodDictDOS) {
            for (ClassMemberMappingDO classMemberMappingDO : classMemberMappingDOS) {
                int totalLength = vodPlayHistoryMapper.memberVodTotalLength(classMemberMappingDO.getMemberId(), vodDictDO.getId());
                if (totalLength >= vodDictDO.getVodLength()) {
                    totalStudyVods++;
                }
                totalStudyLength+=totalLength;
            }
        }
        classSummaryVO.setStudyVodCounts(MathUtil.intDivCeil(totalStudyVods, classMemberMappingDOS.size()));

        //平均完成时长  学生观看课程视频的全部时长/总人数
        int avg = MathUtil.intDivCeil(totalStudyLength, classMemberMappingDOS.size());
        classSummaryVO.setAvgVodPlayLength(avg);

        //测试通过率
        Integer passNum = exerciseDoneResultMapper.getClassTestPassRate(classDictDO.getId());
        classSummaryVO.setPassRate(MathUtil.intDivFloorPercent(passNum, classSummaryVO.getMemberCount()));

        //签到总次数
        long signCount = redisUtil.scan("subsidySignInfo*:classId:" + classDictDO.getId() + "*").stream().count();
        classSummaryVO.setAvgSignCount(MathUtil.intDivCeil(signCount, classSummaryVO.getMemberCount()));
        //答疑数
        Integer count = answeringQuestionMapper.selectCount(new QueryWrapper<AnsweringQuestionDO>()
                .lambda()
                .eq(AnsweringQuestionDO::getClassId, classDictDO.getId()));
        classSummaryVO.setAnswerCount(count);
        return classSummaryVO;
    }


    public IPage<ClassDetailVO> classDetail(ClassDetailDTO classDetailDTO) {

        Page pager = new Page(classDetailDTO.getPageNum(), classDetailDTO.getPageSize());

        IPage<ClassDetailVO> classDetailVOIPage = this.baseMapper.classMembers(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ClassDetailVO> classDetailVOS = classDetailVOIPage.getRecords();

        //课程由多少个视频
        List<VodDictDO> vodDictDOS = classDictMapper.getClassVods(classDetailDTO.getId());

        //课程详情
        ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());

        for (ClassDetailVO classDetailVO : classDetailVOS) {

            /**
             *  学生平均学习时长和平均学习次数
             */
            //该学生完成了多少个
            int i = 0;
            int playLength = 0;
            //该成员完成了几个视频
            for (VodDictDO vodDictDO : vodDictDOS) {
                int totalPlayLength = vodPlayHistoryMapper.memberVodTotalLength(classDetailVO.getId(), vodDictDO.getId());
                if (totalPlayLength >= vodDictDO.getVodLength()) {
                    i++;
                }
                playLength += totalPlayLength;
            }
            //培训时长
            classDetailVO.setTrainingLength(playLength);

            //学生测试完成情况  多套卷子各返回最高成绩
            List<GetMaxScoreVO> getMaxScoreVOS = exerciseDoneResultMapper.getMaxScore(classDictDO.getId(), classDetailVO.getId());
            classDetailVO.setGetMaxScoreVOS(getMaxScoreVOS);

            Boolean flag = true;

            if (getMaxScoreVOS.size() > 0) {
                for (GetMaxScoreVO getMaxScoreVO : getMaxScoreVOS) {
                    if (getMaxScoreVO.getScore() < 60) {
                        flag = false;
                    }
                }
            } else {
                flag = false;
            }

            //总评价
            classDetailVO.setResult(flag && i == vodDictDOS.size() ? "合格" : "不合格");

            //课程进度
            classDetailVO.setClassProcess(i + "/" + vodDictDOS.size());

            //答疑
            Integer count = answeringQuestionMapper.selectCount(new QueryWrapper<AnsweringQuestionDO>()
                    .lambda()
                    .eq(AnsweringQuestionDO::getAskId, classDetailVO.getId())
                    .eq(AnsweringQuestionDO::getClassId, classDetailDTO.getId()));
            classDetailVO.setAskCounts(count);
            //签到次数
            long set = redisUtil.scan(RedisPrefixConstant.SUBSIDY_SIGN_INFO_PREFIX + classDetailVO.getId() + ":classId:" + classDetailDTO.getId() + "*").stream().count();
            classDetailVO.setSignCounts(set);

        }
        classDetailVOIPage.setRecords(classDetailVOS);
        return classDetailVOIPage;
    }

    public List<ClassDetailVO> exportClassDetail(ClassDetailDTO classDetailDTO) throws Exception {
        Page pager = new Page(1, -1L);

        IPage<ClassDetailVO> classDetailVOIPage = this.baseMapper.classMembers(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ClassDetailVO> classDetailVOS = classDetailVOIPage.getRecords();

        //课程由多少个视频
        List<VodDictDO> vodDictDOS = classDictMapper.getClassVods(classDetailDTO.getId());

        //课程详情
        ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());

        CompanyDictDO companyDictDO = companyDictMapper.selectById(classDictDO.getCompanyId());
        for (ClassDetailVO classDetailVO : classDetailVOS) {

            //该学生完成了多少个
            int i = 0;
            int playLength = 0;
            //该成员完成了几个视频
            for (VodDictDO vodDictDO : vodDictDOS) {
                int totalPlayLength = vodPlayHistoryMapper.memberVodTotalLength(classDetailVO.getId(), vodDictDO.getId());
                if (totalPlayLength >= vodDictDO.getVodLength()) {
                    i++;
                }
                playLength += totalPlayLength;
            }
            //课程进度
            classDetailVO.setClassProcess(i + "/" + vodDictDOS.size());

            //学生测试完成情况  多套卷子各返回最高成绩
            List<GetMaxScoreVO> getMaxScoreVOS = exerciseDoneResultMapper.getMaxScore(classDictDO.getId(), classDetailVO.getId());
            classDetailVO.setGetMaxScoreVOS(getMaxScoreVOS);

            Boolean flag = true;

            StringBuilder stringBuilder = new StringBuilder();

            if (getMaxScoreVOS.size() > 0) {
                for (GetMaxScoreVO getMaxScoreVO : getMaxScoreVOS) {
                    stringBuilder.append(getMaxScoreVO.getPaperName() + ":" + getMaxScoreVO.getScore());
                    if (getMaxScoreVO.getScore() < 60) {
                        flag = false;
                    }
                    stringBuilder.append(" ");
                }

            } else {
                flag = false;
            }

            //总评价
            classDetailVO.setResult(flag && i == vodDictDOS.size() ? "合格" : "不合格");

            classDetailVO.setScore(stringBuilder.toString());

            //培训时长
            classDetailVO.setTrainingLengthStr(MathUtil.secToTime(playLength));

            //答疑
            Integer count = answeringQuestionMapper.selectCount(new QueryWrapper<AnsweringQuestionDO>()
                    .lambda()
                    .eq(AnsweringQuestionDO::getAskId, classDetailVO.getId())
                    .eq(AnsweringQuestionDO::getClassId, classDetailDTO.getId()));
            classDetailVO.setAskCounts(count);

            //签到次数
            long set = redisUtil.scan(RedisPrefixConstant.SUBSIDY_SIGN_INFO_PREFIX + classDetailVO.getId() + ":classId:" + classDetailDTO.getId() + "*").stream().count();
            classDetailVO.setSignCounts(set);

        }

        if (classDetailDTO.getFlag()) {
            CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());
            String studyDate = classDictDO.getStartDate() + " 至 " + classDictDO.getEndDate();
            ExcelUtil.writeMemberExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "授课记录汇总表", classDetailVOS, ExcelFormatUtils.memberList);
        }
        return classDetailVOS;
    }

    public IPage<ClassSignVO> signDetail(ClassDetailDTO classDetailDTO) {

        Page pager = new Page(classDetailDTO.getPageNum(), classDetailDTO.getPageSize());

        IPage<ClassSignVO> classSignVOIPage = this.baseMapper.classSign(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ClassSignVO> classSignVOS = classSignVOIPage.getRecords();

        //课程由多少个视频
        List<VodDictDO> vodDictDOS = classDictMapper.getClassVods(classDetailDTO.getId());

        for (ClassSignVO classSignVO : classSignVOS) {

            //该学生完成了多少个
            int i = 0;
            int playLength = 0;
            //该成员完成了几个视频
            for (VodDictDO vodDictDO : vodDictDOS) {
                int totalPlayLength = vodPlayHistoryMapper.memberVodTotalLength(classSignVO.getId(), vodDictDO.getId());
                if (totalPlayLength >= vodDictDO.getVodLength()) {
                    i++;
                }
                playLength += totalPlayLength;
            }
            //课程进度
            classSignVO.setClassProcess(i + "/" + vodDictDOS.size());

            //完成率
            String percent = MathUtil.getPercentAvgIndexWithPercent(new BigDecimal(i), new BigDecimal(vodDictDOS.size()));
            classSignVO.setPercent(percent);

            //培训时长
            classSignVO.setTrainingLength(playLength);
            classSignVO.setTrainingLengthStr(MathUtil.secToTime(playLength));

            Set<String> sortSet = new TreeSet<String>(new Comparator<String>() {
                @Override
                public int compare(String o1, String o2) {
                    return -o2.compareTo(o1);//降序排列
                }
            });

            //签到
            Set<String> set = redisUtil.scan(RedisPrefixConstant.SUBSIDY_SIGN_INFO_PREFIX + classSignVO.getId() + ":classId:" + classDetailDTO.getId() + "*");
            classSignVO.setSignCounts(set.size());

            StringBuilder stringBuilder = new StringBuilder();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            sortSet.addAll(set);
            //签到时间
            for (String signInRecordDO : sortSet) {
                String[] array = signInRecordDO.split(":");
                //时间戳转时间
                stringBuilder.append(sdf.format(new Date(Long.valueOf(array[array.length - 1])))).append(";");
            }
            if (StringUtils.isNotBlank(stringBuilder.toString())) {
                String signInDate = stringBuilder.toString().substring(0, stringBuilder.length() - 1);
                classSignVO.setSignInDateList(signInDate);
            }

        }
        classSignVOIPage.setRecords(classSignVOS);
        return classSignVOIPage;
    }

    public List<ClassSignVO> exportSignDetail(ClassDetailDTO classDetailDTO) throws Exception {
        Page pager = new Page(1, -1L);

        IPage<ClassSignVO> classSignVOIPage = this.baseMapper.classSign(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ClassSignVO> classSignVOS = classSignVOIPage.getRecords();

        //课程由多少个视频
        List<VodDictDO> vodDictDOS = classDictMapper.getClassVods(classDetailDTO.getId());

        for (ClassSignVO classSignVO : classSignVOS) {

            //该学生完成了多少个
            int i = 0;
            int playLength = 0;
            //该成员完成了几个视频
            for (VodDictDO vodDictDO : vodDictDOS) {
                int totalPlayLength = vodPlayHistoryMapper.memberVodTotalLength(classSignVO.getId(), vodDictDO.getId());
                if (totalPlayLength >= vodDictDO.getVodLength()) {
                    i++;
                }
                playLength += totalPlayLength;
            }
            classSignVO.setClassProcess(i + "/" + vodDictDOS.size());

            //完成率
            String percent = MathUtil.getPercentAvgIndexWithPercent(new BigDecimal(i), new BigDecimal(vodDictDOS.size()));
            classSignVO.setPercent(percent);

            //培训时长
            classSignVO.setTrainingLengthStr(MathUtil.secToTime(playLength));

            //签到
            Set<String> set = redisUtil.scan(RedisPrefixConstant.SUBSIDY_SIGN_INFO_PREFIX + classSignVO.getId() + ":classId:" + classDetailDTO.getId() + "*");
            classSignVO.setSignCounts(set.size());

            Set<String> sortSet = new TreeSet<String>(new Comparator<String>() {
                @Override
                public int compare(String o1, String o2) {
                    return -o2.compareTo(o1);//降序排列
                }
            });

            sortSet.addAll(set);

            StringBuilder stringBuilder = new StringBuilder();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //签到时间
            for (String signInRecordDO : sortSet) {
                String[] array = signInRecordDO.split(":");
                //时间戳转时间
                stringBuilder.append(sdf.format(new Date(Long.valueOf(array[array.length - 1])))).append(";");
            }
            if (StringUtils.isNotBlank(stringBuilder.toString())) {
                String signInDate = stringBuilder.toString().substring(0, stringBuilder.length() - 1);
                classSignVO.setSignInDateList(signInDate);
            }

        }

        if (classDetailDTO.getFlag()) {
            ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());
            CompanyDictDO companyDictDO = companyDictMapper.selectById(classDictDO.getCompanyId());
            CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());
            String studyDate = classDictDO.getStartDate() + " 至 " + classDictDO.getEndDate();
            ExcelUtil.writeSignExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "注册签到表", classSignVOS, ExcelFormatUtils.signList);
        }

        return classSignVOS;
    }

    public IPage<ExerciseTestVO> exerciseTest(ClassDetailDTO classDetailDTO) {

        Page pager = new Page(classDetailDTO.getPageNum(), classDetailDTO.getPageSize());

        IPage<ExerciseTestVO> exerciseTestVOIPage = this.baseMapper.exerciseTest(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ExerciseTestVO> classSignVOS = exerciseTestVOIPage.getRecords();

        for (ExerciseTestVO exerciseTestVO : classSignVOS) {
            //学生测试完成情况
            TestScoreInfoVO testScoreInfoVO = exerciseDoneResultMapper.testScoreInfo(classDetailDTO.getPaperId(), exerciseTestVO.getId());
            exerciseTestVO.setPaperId(exerciseTestVO.getPaperId());
            if (null != testScoreInfoVO) {
                exerciseTestVO.setScore(testScoreInfoVO.getScore());
                exerciseTestVO.setResult(testScoreInfoVO.getResult());
                //测评次数
                exerciseTestVO.setCount(testScoreInfoVO.getCnt());
            } else {
                exerciseTestVO.setScore(0);
                exerciseTestVO.setResult("不合格");
                exerciseTestVO.setCount(0);
            }
        }

        exerciseTestVOIPage.setRecords(classSignVOS);
        return exerciseTestVOIPage;
    }

    public List<ExerciseTestVO> exportExerciseTest(ClassDetailDTO classDetailDTO) throws Exception {
        Page pager = new Page(0, -1L);

        IPage<ExerciseTestVO> exerciseTestVOIPage = this.baseMapper.exerciseTest(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<ExerciseTestVO> exerciseTestVOS = exerciseTestVOIPage.getRecords();

        //课程详情
        ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());

        for (ExerciseTestVO exerciseTestVO : exerciseTestVOS) {
            //学生测试完成情况
            TestScoreInfoVO testScoreInfoVO = exerciseDoneResultMapper.testScoreInfo(classDetailDTO.getPaperId(), exerciseTestVO.getId());
            exerciseTestVO.setPaperId(exerciseTestVO.getPaperId());
            if (null != testScoreInfoVO) {
                exerciseTestVO.setScore(testScoreInfoVO.getScore());
                exerciseTestVO.setResult(testScoreInfoVO.getResult());
                //测评次数
                exerciseTestVO.setCount(testScoreInfoVO.getCnt());
            } else {
                exerciseTestVO.setScore(0);
                exerciseTestVO.setResult("不合格");
                exerciseTestVO.setCount(0);
            }
        }

        if (classDetailDTO.getFlag()) {
            CompanyDictDO companyDictDO = companyDictMapper.selectById(classDictDO.getCompanyId());
            CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());
            String studyDate = classDictDO.getStartDate() + " 至 " + classDictDO.getEndDate();
            ExcelUtil.writeTestExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "测试成绩表", exerciseTestVOS, ExcelFormatUtils.scoreList);
        }
        return exerciseTestVOS;
    }

    public List<GetMemberPapersVO> getMemberPapers(ExerciseDoneResultDO exerciseDoneResultDO) {
        return exerciseDoneResultMapper.queryExerciseDoneResult(exerciseDoneResultDO.getMemberId(), null, exerciseDoneResultDO.getPaperId());
    }

    public List<GetPaperDetailVO> getPaperDetail(ExerciseDoneHistoryDO exerciseDoneHistoryDO) {
        return exerciseDoneResultMapper.getPaperDetail(exerciseDoneHistoryDO.getMemberId(), exerciseDoneHistoryDO.getDoneId());
    }

    public IPage answerRecord(ClassDetailDTO classDetailDTO) {
        Page pager = new Page(classDetailDTO.getPageNum(), classDetailDTO.getPageSize());
        return this.baseMapper.answerRecord(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
    }

    public List<AnswerRecordVO> exportAnswerRecord(ClassDetailDTO classDetailDTO) throws Exception {
        Page pager = new Page(1, -1L);
        IPage iPage = this.baseMapper.answerRecord(pager, classDetailDTO.getId(), classDetailDTO.getUserName());
        List<AnswerRecordVO> answerRecordVOS = iPage.getRecords();

        if (classDetailDTO.getFlag()) {
            ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());
            CompanyDictDO companyDictDO = companyDictMapper.selectById(classDictDO.getCompanyId());
            CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());
            String studyDate = classDictDO.getStartDate() + " 至 " + classDictDO.getEndDate();
            ExcelUtil.writeAnswerExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "答疑记录表", answerRecordVOS, ExcelFormatUtils.answersList);
        }
        return answerRecordVOS;
    }

    public List<ClassVodInfoVO> classVodInfo(ClassVodInfoDTO classVodInfoDTO) {

        List<ClassVodInfoVO> result = new ArrayList<>();
        //获取班级成员
        List<Long> memberIds = classMemberMappingMapper.getClassMembers(classVodInfoDTO.getClassId());

        //获取课程下的视频
        List<VodDictDO> vodDictDOS = vodDictMapper.getCourseVods(classVodInfoDTO.getCourseId(), classVodInfoDTO.getVodName());
        for (VodDictDO vodDictDO : vodDictDOS) {
            //获取这个视频的学习人数和平均学习时长
            ClassVodCompleteInfoVO classVodCompleteInfoVO = vodDictMapper.classVodCompleteInfo(classVodInfoDTO.getClassId(), vodDictDO.getId(), memberIds, classVodInfoDTO.getVodName());
            ClassVodInfoVO classVodInfoVO = new ClassVodInfoVO();
            classVodInfoVO.setVodName(vodDictDO.getVodName());
            classVodInfoVO.setStudyCnt(classVodCompleteInfoVO.getCnt());
            classVodInfoVO.setStudyLength(classVodCompleteInfoVO.getStudyLength());
            result.add(classVodInfoVO);
        }
        return result;
    }

    public IPage<ClassDailyInfoVO> classDailyInfo(ClassDailyInfoDTO classDailyInfoDTO) {

        Page pager = new Page(classDailyInfoDTO.getPageNum(), classDailyInfoDTO.getPageSize());

        //获取班级成员
        IPage<ClassDailyInfoVO> classDailyInfoVOIPage = classMemberMappingMapper.getClassMembersWithName(pager, classDailyInfoDTO.getClassId(), classDailyInfoDTO.getUserName());
        //整理出这些学员id
        List<ClassDailyInfoVO> classDailyInfoVOS = classDailyInfoVOIPage.getRecords();
        List<Long> memberIds = new ArrayList<>();
        for (ClassDailyInfoVO classDailyInfoVO : classDailyInfoVOS) {
            memberIds.add(classDailyInfoVO.getId());
        }

        //这些成员完成情况
        List<GetMemberStudyInfoVO> getMemberStudyInfoVOS = vodPlayHistoryMapper.getMemberStudyInfo(classDailyInfoDTO.getClassId(), memberIds, classDailyInfoDTO.getStartDate(), classDailyInfoDTO.getEndDate());

        for (ClassDailyInfoVO classDailyInfoVO : classDailyInfoVOS) {
            List<DayInfoItemVO> dayInfoItemVOS = new ArrayList<>();
            int total = 0;
            for (GetMemberStudyInfoVO getMemberStudyInfoVO : getMemberStudyInfoVOS) {
                if (classDailyInfoVO.getId().equals(getMemberStudyInfoVO.getMemberId())) {
                    DayInfoItemVO dayInfoItemVO = new DayInfoItemVO();
                    dayInfoItemVO.setStudyDate(getMemberStudyInfoVO.getStudyDate());
                    dayInfoItemVO.setTotalLength(getMemberStudyInfoVO.getPlayLength());
                    total += getMemberStudyInfoVO.getPlayLength();
                    dayInfoItemVOS.add(dayInfoItemVO);
                }
            }
            classDailyInfoVO.setTotal(total);
            classDailyInfoVO.setDayInfoItemVOS(dayInfoItemVOS);
        }
        return classDailyInfoVOIPage;
    }

    public void exportClassDailyInfo(ClassDailyInfoDTO classDailyInfoDTO) {
        Page pager = new Page(1, -1L);

        // 表头
        List<String> title = new ArrayList<>();
        title.add("序号");
        title.add("姓名");
        title.addAll(findDaysStr(classDailyInfoDTO.getStartDate(), classDailyInfoDTO.getEndDate()));
        title.add("合计时长");

        //获取班级成员
        IPage<ClassDailyInfoVO> classDailyInfoVOIPage = classMemberMappingMapper.getClassMembersWithName(pager, classDailyInfoDTO.getClassId(), classDailyInfoDTO.getUserName());
        //整理出这些学员id
        List<ClassDailyInfoVO> classDailyInfoVOS = classDailyInfoVOIPage.getRecords();
        List<Long> memberIds = new ArrayList<>();
        for (ClassDailyInfoVO classDailyInfoVO : classDailyInfoVOS) {
            memberIds.add(classDailyInfoVO.getId());
        }

        //这些成员完成情况
        List<GetMemberStudyInfoVO> getMemberStudyInfoVOS = vodPlayHistoryMapper.getMemberStudyInfo(classDailyInfoDTO.getClassId(), memberIds, classDailyInfoDTO.getStartDate(), classDailyInfoDTO.getEndDate());

        HashMap<Long, HashMap<String, Integer>> hashMap = new HashMap();

        for (GetMemberStudyInfoVO getMemberStudyInfoVO : getMemberStudyInfoVOS) {

            if (!hashMap.containsKey(getMemberStudyInfoVO.getMemberId())) {
                HashMap<String, Integer> hashMap1 = new HashMap<>();
                hashMap1.put(getMemberStudyInfoVO.getStudyDate(), getMemberStudyInfoVO.getPlayLength());
                hashMap.put(getMemberStudyInfoVO.getMemberId(), hashMap1);
            } else {
                HashMap<String, Integer> hashMap1 = hashMap.get(getMemberStudyInfoVO.getMemberId());
                hashMap1.put(getMemberStudyInfoVO.getStudyDate(), getMemberStudyInfoVO.getPlayLength());
                hashMap.put(getMemberStudyInfoVO.getMemberId(), hashMap1);
            }
        }

        writeExcel(memberIds, hashMap, title);

    }

    public IPage<ClassDailyInfoVO> classVodDailyInfo(ClassVodDailyInfoDTO classVodDailyInfoDTO) {

        //查看班级里有多少人
        Page pager = new Page(classVodDailyInfoDTO.getPageNum(), classVodDailyInfoDTO.getPageSize());

        //获取班级成员
        IPage<ClassDailyInfoVO> classDailyInfoVOIPage = classMemberMappingMapper.getClassMembersWithName(pager, classVodDailyInfoDTO.getClassId(), classVodDailyInfoDTO.getUserName());

        List<ClassDailyInfoVO> classDailyInfoVOS = classDailyInfoVOIPage.getRecords();

        //获取班级、每个学员维度的视频播放总时长
        List<ClassMemberPlayLengthVO> vodPlayHistoryDOS = vodPlayHistoryMapper.classMemberPlayLength(classVodDailyInfoDTO.getClassId());

        HashMap<String, Integer> hashMap = new HashMap<>();

        //存在一个map里
        for (ClassMemberPlayLengthVO vodPlayHistoryDO : vodPlayHistoryDOS) {
            hashMap.put(vodPlayHistoryDO.getMemberId() + "-" + vodPlayHistoryDO.getVodId(), vodPlayHistoryDO.getPlayLength());
        }

        //查询这个班级的所有视频
        ClassDictDO classDictDO = classDictMapper.selectById(classVodDailyInfoDTO.getClassId());
        List<VodDictDO> vodDictDOS = vodDictMapper.getCourseVods(classDictDO.getCourseId(), null);

        for (ClassDailyInfoVO classDailyInfoVO : classDailyInfoVOS) {
            //查看这个人某个视频的长度
            int total = 0;
            int playLengthTmp = 0;
            List<ClassVodDailyInfoItemVO> classVodDailyInfoItemVOS = new ArrayList<>();
            for (VodDictDO vodDictDO : vodDictDOS) {
                Integer playLength = hashMap.get(classDailyInfoVO.getId() + "-" + vodDictDO.getId());
                ClassVodDailyInfoItemVO classVodDailyInfoItemVO = new ClassVodDailyInfoItemVO();
                classVodDailyInfoItemVO.setVodId(vodDictDO.getId());
                classVodDailyInfoItemVO.setVodName(vodDictDO.getVodName());
                if (null != playLength) {
                    classVodDailyInfoItemVO.setPlayLength(playLength);
                    classVodDailyInfoItemVOS.add(classVodDailyInfoItemVO);
                    if (playLength >= vodDictDO.getVodLength()) {
                        total++;
                    }
                    playLengthTmp += playLength;
                } else {
                    classVodDailyInfoItemVO.setPlayLength(0);
                    classVodDailyInfoItemVOS.add(classVodDailyInfoItemVO);
                }
            }
            classDailyInfoVO.setStudyLength(playLengthTmp);
            classDailyInfoVO.setTotal(MathUtil.intDivFloorPercent(total, vodDictDOS.size()));
            classDailyInfoVO.setClassVodDailyInfoItemVOS(classVodDailyInfoItemVOS);
        }
        return classDailyInfoVOIPage;

    }

    public void exportClassVodDailyInfo(ClassVodDailyInfoDTO classVodDailyInfoDTO) {

        //查看班级里有多少人
        Page pager = new Page(1, -1L);

        ClassDictDO classDictDO = classDictMapper.selectById(classVodDailyInfoDTO.getClassId());
        //查询所有视频
        List<VodDictDO> vodDictDOS = vodDictMapper.getCourseVods(classDictDO.getCourseId(), null);

        HashMap<String, Integer> vodHashmap = new HashMap<>();
        for (VodDictDO vodDictDO : vodDictDOS) {
            vodHashmap.put(vodDictDO.getVodName(), vodDictDO.getVodLength());
        }

        // 表头
        List<String> title = new ArrayList<>();
        title.add("序号");
        title.add("姓名");
        title.add("完成率");
        title.add("学习时长");
        for (VodDictDO vodDictDO : vodDictDOS) {
            title.add(vodDictDO.getVodName());
        }

        //获取班级成员
        IPage<ClassDailyInfoVO> classDailyInfoVOIPage = classMemberMappingMapper.getClassMembersWithName(pager, classVodDailyInfoDTO.getClassId(), classVodDailyInfoDTO.getUserName());

        List<ClassDailyInfoVO> classDailyInfoVOS = classDailyInfoVOIPage.getRecords();

        //获取班级、每个学员维度的视频播放总时长
        List<ClassMemberPlayLengthVO> classMemberPlayLengthVOS = vodPlayHistoryMapper.classMemberPlayLength(classVodDailyInfoDTO.getClassId());

        HashMap<String, Integer> hashMap = new HashMap<>();

        //存在一个map里
        for (ClassMemberPlayLengthVO classMemberPlayLengthVO : classMemberPlayLengthVOS) {
            hashMap.put(classMemberPlayLengthVO.getMemberId() + "-" + classMemberPlayLengthVO.getVodName(), classMemberPlayLengthVO.getPlayLength());
        }

        writeVodExcel(classDailyInfoVOS, hashMap, title, vodHashmap);

    }


    public <T> void writeVodExcel(List<ClassDailyInfoVO> classDailyInfoVOS, HashMap<String, Integer> hashMap, List<String> title, HashMap<String, Integer> vodHashmap) {

        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        AtomicInteger ai = new AtomicInteger();

        Row row = sheet.createRow(ai.getAndIncrement());
        AtomicInteger at = new AtomicInteger();
        title.forEach(field -> {
            Cell cell = row.createCell(at.getAndIncrement());
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            Font font = wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(field);
        });

        int i = 1;
        if (hashMap != null) {
            for (ClassDailyInfoVO classDailyInfoVO : classDailyInfoVOS){
                Row r = sheet.createRow(ai.getAndIncrement());
                AtomicInteger a = new AtomicInteger();
                Cell cell = r.createCell(at.getAndIncrement());
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
                cell.setCellStyle(cellStyle);
                Cell seqcell = r.createCell(a.getAndIncrement());
                seqcell.setCellValue(i++);

                Cell namecell = r.createCell(a.getAndIncrement());
                namecell.setCellValue(classDailyInfoVO.getUserName());

                Cell totalCell = r.createCell(a.getAndIncrement());
                Cell studyLengthCell = r.createCell(a.getAndIncrement());

                Integer total = 0;
                int studyLength = 0;
                for (String tt : title) {

                    if (!"姓名".equals(tt) && !"完成率".equals(tt) && !"学习时长".equals(tt)&& !"序号".equals(tt)) {
                        Cell cell1 = r.createCell(a.getAndIncrement());
                        if (null != hashMap) {

                            Integer playLength = hashMap.get(classDailyInfoVO.getId() + "-" + tt);
                            if (null != playLength) {
                                cell1.setCellValue(MathUtil.secToTime(playLength));
                                if (vodHashmap.get(tt) <= playLength) {
                                    total++;
                                }
                                studyLength += playLength;
                            } else {
                                cell1.setCellValue("00:00:00");
                            }
                        }
                    }
                }
                studyLengthCell.setCellValue(MathUtil.secToTime(studyLength));
                totalCell.setCellValue(MathUtil.intDivFloorPercent(total, vodHashmap.keySet().size()) + "%");
            }
        }
        String fileName = String.valueOf(new Date().getTime());
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            log.error(String.format("downLoad excel exception"), e);
        }

    }


    /**
     * 导出excel文件
     */
    public <T> void writeExcel(List<Long> memberIds, HashMap<Long, HashMap<String, Integer>> hashMap, List<String> dates) {
        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        AtomicInteger ai = new AtomicInteger();

        Row row = sheet.createRow(ai.getAndIncrement());
        AtomicInteger at = new AtomicInteger();
        dates.forEach(field -> {
            Cell cell = row.createCell(at.getAndIncrement());
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            Font font = wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(field);
        });

        if (hashMap != null) {
        int i= 1;
        for (Long lg : memberIds){
            Row r = sheet.createRow(ai.getAndIncrement());
            AtomicInteger a = new AtomicInteger();
            Cell cell = r.createCell(at.getAndIncrement());
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
            cell.setCellStyle(cellStyle);
            HashMap<String, Integer> hashMap1 = hashMap.get(lg);
            Cell seqcell = r.createCell(a.getAndIncrement());
            seqcell.setCellValue(i++);
            Cell namecell = r.createCell(a.getAndIncrement());
            namecell.setCellValue(memberMapper.selectById(lg).getUserName());
            int total = 0;
            for (String date : dates) {
                if (!date.equals("姓名") && !"合计时长".equals(date)&& !"序号".equals(date)) {
                    Cell cell1 = r.createCell(a.getAndIncrement());
                    if (null != hashMap1) {
                        total += null == hashMap1.get(date) ? 0 : hashMap1.get(date);
                        cell1.setCellValue(null == hashMap1.get(date) ? "00:00:00" : MathUtil.secToTime(hashMap1.get(date)));
                    } else {
                        cell1.setCellValue("00:00:00");
                    }
                }
            }
            Cell totalCell = r.createCell(a.getAndIncrement());
            totalCell.setCellValue(MathUtil.secToTime(total));
        }

        }
        String fileName = String.valueOf(new Date().getTime());
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            log.error(String.format("downLoad excel exception"), e);
        }

    }

    //JAVA 获取时间段内的每一天
    public static List<String> findDaysStr(String begintTime, String endTime) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dBegin = null;
        Date dEnd = null;
        try {
            dBegin = sdf.parse(begintTime);
            dEnd = sdf.parse(endTime);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<String> daysStrList = new ArrayList<String>();
        daysStrList.add(sdf.format(dBegin));
        Calendar calBegin = Calendar.getInstance();
        calBegin.setTime(dBegin);
        Calendar calEnd = Calendar.getInstance();
        calEnd.setTime(dEnd);
        while (dEnd.after(calBegin.getTime())) {
            calBegin.add(Calendar.DAY_OF_MONTH, 1);
            String dayStr = sdf.format(calBegin.getTime());
            daysStrList.add(dayStr);
        }
        return daysStrList;
    }


    public void export(ClassDetailDTO classDetailDTO) throws Exception {

        ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletResponse response = servletRequestAttributes.getResponse();
        ServletOutputStream sos = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(sos);
        String zipname = "summary.zip";
        response.reset();
        response.setHeader("Access-Control-Allow-Origin", "*");
        response.setContentType("application/zip;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((zipname).getBytes(StandardCharsets.UTF_8), "ISO8859-1"));

        ClassDictDO classDictDO = classDictMapper.selectById(classDetailDTO.getId());
        CompanyDictDO companyDictDO = companyDictMapper.selectById(classDictDO.getCompanyId());
        CourseDictDO courseDictDO = courseDictMapper.selectById(classDictDO.getCourseId());

        /**
         * 班级成员
         */
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        classDetailDTO.setFlag(false);
        List<ClassDetailVO> classDetailVOS = exportClassDetail(classDetailDTO);
        String studyDate = classDictDO.getStartDate() + " 至 " + classDictDO.getEndDate();
        membersListExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "授课记录汇总表", ExcelFormatUtils.memberList, classDetailVOS, null, 0, baos);
        compressFileToZipStream(zipOutputStream, baos, "member.xlsx");

        /**
         * 注册签到
         */
        ByteArrayOutputStream baos1 = new ByteArrayOutputStream();
        classDetailDTO.setFlag(false);
        List<ClassSignVO> classSignVOS = exportSignDetail(classDetailDTO);
        signListExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "注册签到", ExcelFormatUtils.signList, classSignVOS, null, 0, baos1);
        compressFileToZipStream(zipOutputStream, baos1, "sign.xlsx");


        /**
         * 测试成绩
         */
        ByteArrayOutputStream baos2 = new ByteArrayOutputStream();
        classDetailDTO.setFlag(false);
        List<ExerciseTestVO> exerciseTestVOS = exportExerciseTest(classDetailDTO);
        testListExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "测试成绩", ExcelFormatUtils.scoreList, exerciseTestVOS, null, 0, baos2);
        compressFileToZipStream(zipOutputStream, baos2, "test.xlsx");


        /**
         * 答疑记录
         */
        ByteArrayOutputStream baos3 = new ByteArrayOutputStream();
        List<AnswerRecordVO> answerRecordVOS = exportAnswerRecord(classDetailDTO);
        askListExcel(companyDictDO.getCompanyName(), studyDate, courseDictDO.getCourseName(), "答疑记录", ExcelFormatUtils.answersList, answerRecordVOS, null, 0, baos3);
        compressFileToZipStream(zipOutputStream, baos3, "answer.xlsx");

        zipOutputStream.flush();
        zipOutputStream.closeEntry();

        baos.close();

        zipOutputStream.flush();
        zipOutputStream.close();

        sos.close();

    }

    public IPage memberStudy(MemberStudyLogDTO memberStudyLogDTO) {
        Page pager = new Page(memberStudyLogDTO.getPageNum(), memberStudyLogDTO.getPageSize());

        IPage iPage = vodPlayHistoryMapper.memberStudy(pager, memberStudyLogDTO.getClassId(), memberStudyLogDTO.getMemberId());
        List<MemberStudyLogVO> memberStudyLogVOS = iPage.getRecords();
        for (MemberStudyLogVO memberStudyLogVO : memberStudyLogVOS) {
            memberStudyLogVO.setPlayLength(MathUtil.secToTime(Integer.valueOf(memberStudyLogVO.getPlayLength())));
            memberStudyLogVO.setTotalLength(MathUtil.secToTime(Integer.valueOf(memberStudyLogVO.getTotalLength())));
        }
        return iPage;
    }

    public void memberStudyLog(MemberStudyLogDTO memberStudyLogDTO) throws Exception {

        MemberDO memberDO = memberMapper.selectById(memberStudyLogDTO.getMemberId());

        List<MemberStudyLogVO> memberStudyLog = vodPlayHistoryMapper.memberStudyLog(memberStudyLogDTO.getClassId(), memberStudyLogDTO.getMemberId());
        for (MemberStudyLogVO memberStudyLogVO : memberStudyLog) {
            memberStudyLogVO.setPlayLength(MathUtil.secToTime(Integer.valueOf(memberStudyLogVO.getPlayLength())));
            memberStudyLogVO.setTotalLength(MathUtil.secToTime(Integer.valueOf(memberStudyLogVO.getTotalLength())));
        }
        ExcelUtil.writeMemberStudyLog(memberDO, "学习记录", memberStudyLog, ExcelFormatUtils.studyLogList);

    }

    @Transactional(rollbackFor = Exception.class)
    public String importMember(Long companyId, MultipartFile multipartFile) {

        try {

            HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
            //拼音小写
            format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
            //不带声调
            format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);

            ExcelUtil.readExcel(null, ImportMemberDTO.class, multipartFile).forEach(s -> {

                MemberDO memberDO = memberMapper.selectOne(new QueryWrapper<MemberDO>()
                        .lambda()
                        .eq(MemberDO::getTelephone, s.getTelephone()));

                if (null != memberDO) {
                    //memberDOS.add(memberDO);
                } else {
                    MemberDO memberDO1 = new MemberDO();
                    memberDO1.setUserName(s.getUserName());
                    memberDO1.setGender(s.getGender());
                    try {
                        String accountName = PinyinHelper.toHanYuPinyinString(s.getUserName(), format, "", true);
                        List<MemberDO> memberDOS = memberMapper.selectList(new QueryWrapper<MemberDO>()
                                .lambda()
                                .eq(MemberDO::getAccountName, accountName)
                                .eq(MemberDO::getCompanyId, companyId));
                        if (memberDOS.size() > 0) {
                            String usernames = userName(accountName, 0, companyId);
                            memberDO1.setAccountName(usernames.replace("u:", "v"));
                        } else {
                            memberDO1.setAccountName(accountName.replace("u:", "v"));
                        }
                    } catch (BadHanyuPinyinOutputFormatCombination ex) {

                    }
                    memberDO1.setCompanyId(companyId);
                    memberDO1.setFirstLogin(0);
                    memberDO1.setTelephone(s.getTelephone());
                    memberDO1.setIdCard(s.getIdCard());
                    memberDO1.setStatus("启用");
                    memberDO1.setPassword("123456");
                    memberMapper.insert(memberDO1);

                    //找到该公司最大的部门
                    DepartmentDictDO departmentDictDO = departmentDictMapper.selectOne(new QueryWrapper<DepartmentDictDO>()
                            .lambda()
                            .isNull(DepartmentDictDO::getParentId)
                            .eq(DepartmentDictDO::getCompanyId, companyId));

                    MemberDepartmentMappingDO memberDepartmentMappingDO = new MemberDepartmentMappingDO();
                    memberDepartmentMappingDO.setDepartmentId(departmentDictDO.getId());
                    memberDepartmentMappingDO.setMemberId(memberDO1.getId());
                    memberDepartmentMappingMapper.insert(memberDepartmentMappingDO);
                }
            });
        } catch (Exception e) {
            throw new HttpException(10001);
        }
        return ConstantUtils.ADD_SUCCESS;
    }

    public String userName(String originName, int i, Long companyId) {
        i++;
        List<MemberDO> memberDOS = memberMapper.selectList(new QueryWrapper<MemberDO>()
                .lambda()
                .eq(MemberDO::getAccountName, originName + i)
                .eq(MemberDO::getCompanyId, companyId));
        if (memberDOS.size() > 0) {
            return userName(originName, i, companyId);
        } else {
            return originName + i;
        }
    }

    public static void membersListExcel(String companyName, String studyDate, String courseName, String title, List<String> headerList, List<ClassDetailVO> classDetailVOS, String datePattern, int colWidth, OutputStream out) {

        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        setStyle(titleStyle);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);

        //第二行
        CellStyle secondStyle = workbook.createCellStyle();
        secondStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        secondStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        secondStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        secondStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font secondFont = workbook.createFont();
        secondStyle.setFont(secondFont);

        //第三行
        CellStyle thirdStyle = workbook.createCellStyle();
        thirdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        thirdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        thirdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        thirdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font thirdFont = workbook.createFont();
        thirdStyle.setFont(thirdFont);

        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        setStyle(headerStyle);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        headerStyle.setFont(headerFont);

        // 数据单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        setStyle(cellStyle);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = 17;//至少字节数
        int[] arrColWidth = new int[headerList.size()];
        // 产生表格标题行,以及设置列宽
        String[] headers = new String[headerList.size()];
        int ii = 0;
        for (int i = 0; i < headerList.size(); i++) {

            headers[ii] = headerList.get(i);

            int bytes = headerList.get(i).getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }

        //第二行
        int[] secondWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] secondHead = new String[2];
        List<String> secondList = Arrays.asList("培训实施单位：" + companyName, "培训时间：" + studyDate);

        //第三行
        int[] thirdWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] thirdHead = new String[2];
        List<String> thirdList = Arrays.asList("培训项目：" + courseName, "培训平台：有课互联系统");


        ii = 0;
        for (int i = 0; i < 2; i++) {
            secondHead[ii] = secondList.get(i);
            int bytes = secondList.get(i).getBytes().length;
            secondWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, secondWidth[ii] * 256);
            ii++;
        }

        ii = 0;
        for (int i = 0; i < 2; i++) {
            thirdHead[ii] = thirdList.get(i);
            int bytes = thirdList.get(i).getBytes().length;
            thirdWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, thirdWidth[ii] * 256);
            ii++;
        }

        // 遍历集合数据，产生数据行
        //标题 0
        SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
        titleRow.createCell(0).setCellValue(title);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, new CellRangeAddress(0, 0, 0, 0), sheet, workbook);   //给合并过的单元格加边框
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerList.size() - 1));

        //第二行  1
        SXSSFRow secondRow = sheet.createRow(1); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress21 = new CellRangeAddress(1, 1, 0, 4);
        sheet.addMergedRegion(cellRangeAddress21);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress21, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress22 = new CellRangeAddress(1, 1, 5, 10);
        sheet.addMergedRegion(cellRangeAddress22);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress22, sheet, workbook);   //给合并过的单元格加边框

        secondRow.createCell(0).setCellValue(secondHead[0]);
        secondRow.getCell(0).setCellStyle(secondStyle);

        secondRow.createCell(5).setCellValue(secondHead[1]);
        secondRow.getCell(5).setCellStyle(secondStyle);

        //第三行  2
        SXSSFRow thirdRow = sheet.createRow(2); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress31 = new CellRangeAddress(2, 2, 0, 4);
        sheet.addMergedRegion(cellRangeAddress31);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress31, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress32 = new CellRangeAddress(2, 2, 5, 10);
        sheet.addMergedRegion(cellRangeAddress32);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress32, sheet, workbook);   //给合并过的单元格加边框
        thirdRow.createCell(0).setCellValue(thirdHead[0]);
        thirdRow.getCell(0).setCellStyle(thirdStyle);

        thirdRow.createCell(5).setCellValue(thirdHead[1]);
        thirdRow.getCell(5).setCellStyle(thirdStyle);

        //标题  3
        SXSSFRow headerRow = sheet.createRow(3); //列头 rowIndex =1
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        int seq = 1;

        //内容
        int rowIndex = 0;
        for (ClassDetailVO classDetailVO : classDetailVOS) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }
                rowIndex = 4;//数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = sheet.createRow(rowIndex);

            SXSSFCell newCell = dataRow.createCell(0);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(seq);


            newCell = dataRow.createCell(1);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getUserName());


            newCell = dataRow.createCell(2);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getIdCard());

            newCell = dataRow.createCell(3);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getGender());

            newCell = dataRow.createCell(4);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getAccountName());

            newCell = dataRow.createCell(5);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getTelephone());

            newCell = dataRow.createCell(6);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getSignCounts());

            newCell = dataRow.createCell(7);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getClassProcess());

            newCell = dataRow.createCell(8);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getTrainingLengthStr());

            newCell = dataRow.createCell(9);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getAskCounts());

            newCell = dataRow.createCell(10);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getScore());

            newCell = dataRow.createCell(11);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classDetailVO.getResult());

            rowIndex++;
            seq++;
        }

        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void signListExcel(String companyName, String studyDate, String courseName, String title, List<String> headerList, List<ClassSignVO> classSignVOS, String datePattern, int colWidth, OutputStream out) {

        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        setStyle(titleStyle);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);

        //第二行
        CellStyle secondStyle = workbook.createCellStyle();
        secondStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        secondStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        secondStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        secondStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font secondFont = workbook.createFont();
        secondStyle.setFont(secondFont);

        //第三行
        CellStyle thirdStyle = workbook.createCellStyle();
        thirdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        thirdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        thirdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        thirdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font thirdFont = workbook.createFont();
        thirdStyle.setFont(thirdFont);

        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        setStyle(headerStyle);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        headerStyle.setFont(headerFont);

        // 数据单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        setStyle(cellStyle);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = 17;//至少字节数
        int[] arrColWidth = new int[headerList.size()];
        // 产生表格标题行,以及设置列宽
        String[] headers = new String[headerList.size()];
        int ii = 0;
        for (int i = 0; i < headerList.size(); i++) {

            headers[ii] = headerList.get(i);

            int bytes = headerList.get(i).getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }

        //第二行
        int[] secondWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] secondHead = new String[2];
        List<String> secondList = Arrays.asList("培训实施单位：" + companyName, "培训时间：" + studyDate);

        //第三行
        int[] thirdWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] thirdHead = new String[2];
        List<String> thirdList = Arrays.asList("培训项目：" + courseName, "培训平台：有课互联系统");


        ii = 0;
        for (int i = 0; i < 2; i++) {
            secondHead[ii] = secondList.get(i);
            int bytes = secondList.get(i).getBytes().length;
            secondWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, secondWidth[ii] * 256);
            ii++;
        }

        ii = 0;
        for (int i = 0; i < 2; i++) {
            thirdHead[ii] = thirdList.get(i);
            int bytes = thirdList.get(i).getBytes().length;
            thirdWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, thirdWidth[ii] * 256);
            ii++;
        }

        // 遍历集合数据，产生数据行
        //标题 0
        SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
        titleRow.createCell(0).setCellValue(title);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, new CellRangeAddress(0, 0, 0, 0), sheet, workbook);   //给合并过的单元格加边框
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerList.size() - 1));

        //第二行  1
        SXSSFRow secondRow = sheet.createRow(1); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress21 = new CellRangeAddress(1, 1, 0, 3);
        sheet.addMergedRegion(cellRangeAddress21);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress21, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress22 = new CellRangeAddress(1, 1, 4, 8);
        sheet.addMergedRegion(cellRangeAddress22);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress22, sheet, workbook);   //给合并过的单元格加边框

        secondRow.createCell(0).setCellValue(secondHead[0]);
        secondRow.getCell(0).setCellStyle(secondStyle);

        secondRow.createCell(4).setCellValue(secondHead[1]);
        secondRow.getCell(4).setCellStyle(secondStyle);

        //第三行  2
        SXSSFRow thirdRow = sheet.createRow(2); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress31 = new CellRangeAddress(2, 2, 0, 3);
        sheet.addMergedRegion(cellRangeAddress31);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress31, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress32 = new CellRangeAddress(2, 2, 4, 8);
        sheet.addMergedRegion(cellRangeAddress32);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress32, sheet, workbook);   //给合并过的单元格加边框
        thirdRow.createCell(0).setCellValue(thirdHead[0]);
        thirdRow.getCell(0).setCellStyle(thirdStyle);

        thirdRow.createCell(4).setCellValue(thirdHead[1]);
        thirdRow.getCell(4).setCellStyle(thirdStyle);

        //标题  3
        SXSSFRow headerRow = sheet.createRow(3); //列头 rowIndex =1
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        int seq = 1;

        //内容
        int rowIndex = 0;
        for (ClassSignVO classSignVO : classSignVOS) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }
                rowIndex = 4;//数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = sheet.createRow(rowIndex);

            SXSSFCell newCell = dataRow.createCell(0);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(seq);


            newCell = dataRow.createCell(1);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getUserName());


            newCell = dataRow.createCell(2);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getIdCard());


            newCell = dataRow.createCell(3);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getTelephone());

            newCell = dataRow.createCell(4);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getTrainingLengthStr());

            newCell = dataRow.createCell(5);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getClassProcess());

            newCell = dataRow.createCell(6);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getPercent());

            newCell = dataRow.createCell(7);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getSignCounts());

            newCell = dataRow.createCell(8);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(classSignVO.getSignInDateList());

            rowIndex++;
            seq++;

        }

        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void testListExcel(String companyName, String studyDate, String courseName, String title, List<String> headerList, List<ExerciseTestVO> exerciseTestVOS, String datePattern, int colWidth, OutputStream out) {

        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        setStyle(titleStyle);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);

        //第二行
        CellStyle secondStyle = workbook.createCellStyle();
        secondStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        secondStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        secondStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        secondStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font secondFont = workbook.createFont();
        secondStyle.setFont(secondFont);

        //第三行
        CellStyle thirdStyle = workbook.createCellStyle();
        thirdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        thirdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        thirdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        thirdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font thirdFont = workbook.createFont();
        thirdStyle.setFont(thirdFont);

        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        setStyle(headerStyle);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        headerStyle.setFont(headerFont);

        // 数据单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        setStyle(cellStyle);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = 17;//至少字节数
        int[] arrColWidth = new int[headerList.size()];
        // 产生表格标题行,以及设置列宽
        String[] headers = new String[headerList.size()];
        int ii = 0;
        for (int i = 0; i < headerList.size(); i++) {

            headers[ii] = headerList.get(i);

            int bytes = headerList.get(i).getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }

        //第二行
        int[] secondWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] secondHead = new String[2];
        List<String> secondList = Arrays.asList("培训实施单位：" + companyName, "培训时间：" + studyDate);

        //第三行
        int[] thirdWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] thirdHead = new String[2];
        List<String> thirdList = Arrays.asList("培训项目：" + courseName, "培训平台：有课互联系统");


        ii = 0;
        for (int i = 0; i < 2; i++) {
            secondHead[ii] = secondList.get(i);
            int bytes = secondList.get(i).getBytes().length;
            secondWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, secondWidth[ii] * 256);
            ii++;
        }

        ii = 0;
        for (int i = 0; i < 2; i++) {
            thirdHead[ii] = thirdList.get(i);
            int bytes = thirdList.get(i).getBytes().length;
            thirdWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, thirdWidth[ii] * 256);
            ii++;
        }

        // 遍历集合数据，产生数据行
        //标题 0
        SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
        titleRow.createCell(0).setCellValue(title);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, new CellRangeAddress(0, 0, 0, 0), sheet, workbook);   //给合并过的单元格加边框
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerList.size() - 1));

        //第二行  1
        SXSSFRow secondRow = sheet.createRow(1); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress21 = new CellRangeAddress(1, 1, 0, 3);
        sheet.addMergedRegion(cellRangeAddress21);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress21, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress22 = new CellRangeAddress(1, 1, 4, 6);
        sheet.addMergedRegion(cellRangeAddress22);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress22, sheet, workbook);   //给合并过的单元格加边框

        secondRow.createCell(0).setCellValue(secondHead[0]);
        secondRow.getCell(0).setCellStyle(secondStyle);

        secondRow.createCell(4).setCellValue(secondHead[1]);
        secondRow.getCell(4).setCellStyle(secondStyle);

        //第三行  2
        SXSSFRow thirdRow = sheet.createRow(2); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress31 = new CellRangeAddress(2, 2, 0, 3);
        sheet.addMergedRegion(cellRangeAddress31);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress31, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress32 = new CellRangeAddress(2, 2, 4, 6);
        sheet.addMergedRegion(cellRangeAddress32);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress32, sheet, workbook);   //给合并过的单元格加边框
        thirdRow.createCell(0).setCellValue(thirdHead[0]);
        thirdRow.getCell(0).setCellStyle(thirdStyle);

        thirdRow.createCell(4).setCellValue(thirdHead[1]);
        thirdRow.getCell(4).setCellStyle(thirdStyle);

        //标题  3
        SXSSFRow headerRow = sheet.createRow(3); //列头 rowIndex =1
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        int seq = 1;

        //内容
        int rowIndex = 0;
        for (ExerciseTestVO exerciseTestVO : exerciseTestVOS) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }
                rowIndex = 4;//数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = sheet.createRow(rowIndex);

            SXSSFCell newCell = dataRow.createCell(0);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(seq);


            newCell = dataRow.createCell(1);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getUserName());


            newCell = dataRow.createCell(2);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getIdCard());


            newCell = dataRow.createCell(3);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getTelephone());

            newCell = dataRow.createCell(4);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getScore());

            //次数
            newCell = dataRow.createCell(5);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getCount());


            newCell = dataRow.createCell(6);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(exerciseTestVO.getScore() >= 60 ? "合格" : "不合格");

            rowIndex++;
            seq++;

        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void askListExcel(String companyName, String studyDate, String courseName, String title, List<String> headerList, List<AnswerRecordVO> answerRecordVOS, String datePattern, int colWidth, OutputStream out) {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        setStyle(titleStyle);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);

        //第二行
        CellStyle secondStyle = workbook.createCellStyle();
        secondStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        secondStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        secondStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        secondStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font secondFont = workbook.createFont();
        secondStyle.setFont(secondFont);

        //第三行
        CellStyle thirdStyle = workbook.createCellStyle();
        thirdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        thirdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        thirdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        thirdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        secondStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        Font thirdFont = workbook.createFont();
        thirdStyle.setFont(thirdFont);

        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
        setStyle(headerStyle);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
        headerStyle.setFont(headerFont);

        // 数据单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        setStyle(cellStyle);
        Font cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = 17;//至少字节数
        int[] arrColWidth = new int[headerList.size()];
        // 产生表格标题行,以及设置列宽
        String[] headers = new String[headerList.size()];
        int ii = 0;
        for (int i = 0; i < headerList.size(); i++) {

            headers[ii] = headerList.get(i);

            int bytes = headerList.get(i).getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }

        //第二行
        int[] secondWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] secondHead = new String[2];
        List<String> secondList = Arrays.asList("培训实施单位：" + companyName, "培训时间：" + studyDate);

        //第三行
        int[] thirdWidth = new int[2];
        // 产生表格标题行,以及设置列宽
        String[] thirdHead = new String[2];
        List<String> thirdList = Arrays.asList("培训项目：" + courseName, "培训平台：有课互联系统");


        ii = 0;
        for (int i = 0; i < 2; i++) {
            secondHead[ii] = secondList.get(i);
            int bytes = secondList.get(i).getBytes().length;
            secondWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, secondWidth[ii] * 256);
            ii++;
        }

        ii = 0;
        for (int i = 0; i < 2; i++) {
            thirdHead[ii] = thirdList.get(i);
            int bytes = thirdList.get(i).getBytes().length;
            thirdWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, thirdWidth[ii] * 256);
            ii++;
        }

        // 遍历集合数据，产生数据行
        //标题 0
        SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
        titleRow.createCell(0).setCellValue(title);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, new CellRangeAddress(0, 0, 0, 0), sheet, workbook);   //给合并过的单元格加边框
        titleRow.getCell(0).setCellStyle(titleStyle);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerList.size() - 1));

        //第二行  1
        SXSSFRow secondRow = sheet.createRow(1); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress21 = new CellRangeAddress(1, 1, 0, 2);
        sheet.addMergedRegion(cellRangeAddress21);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress21, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress22 = new CellRangeAddress(1, 1, 3, 5);
        sheet.addMergedRegion(cellRangeAddress22);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress22, sheet, workbook);   //给合并过的单元格加边框

        secondRow.createCell(0).setCellValue(secondHead[0]);
        secondRow.getCell(0).setCellStyle(secondStyle);

        secondRow.createCell(3).setCellValue(secondHead[1]);
        secondRow.getCell(3).setCellStyle(secondStyle);

        //第三行  2
        SXSSFRow thirdRow = sheet.createRow(2); //第二行 rowIndex =1
        CellRangeAddress cellRangeAddress31 = new CellRangeAddress(2, 2, 0, 2);
        sheet.addMergedRegion(cellRangeAddress31);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress31, sheet, workbook);   //给合并过的单元格加边框

        CellRangeAddress cellRangeAddress32 = new CellRangeAddress(2, 2, 3, 5);
        sheet.addMergedRegion(cellRangeAddress32);
        setBorderStyle(HSSFCellStyle.BORDER_THIN, cellRangeAddress32, sheet, workbook);   //给合并过的单元格加边框
        thirdRow.createCell(0).setCellValue(thirdHead[0]);
        thirdRow.getCell(0).setCellStyle(thirdStyle);

        thirdRow.createCell(3).setCellValue(thirdHead[1]);
        thirdRow.getCell(3).setCellStyle(thirdStyle);

        //标题  3
        SXSSFRow headerRow = sheet.createRow(3); //列头 rowIndex =1
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
            headerRow.getCell(i).setCellStyle(headerStyle);
        }

        int seq = 1;

        //内容
        int rowIndex = 0;
        for (AnswerRecordVO answerRecordVO : answerRecordVOS) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }
                rowIndex = 4;//数据内容从 rowIndex=2开始
            }

            SXSSFRow dataRow = sheet.createRow(rowIndex);

            SXSSFCell newCell = dataRow.createCell(0);
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(seq);


            newCell = dataRow.createCell(1);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(answerRecordVO.getTitle());


            newCell = dataRow.createCell(2);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(answerRecordVO.getAnswer());


            newCell = dataRow.createCell(3);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(answerRecordVO.getCreateDate());

            newCell = dataRow.createCell(4);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(answerRecordVO.getUserName());

            //次数
            newCell = dataRow.createCell(5);
            newCell.setCellStyle(cellStyle);
            newCell.setCellValue(answerRecordVO.getUpdateDate());

            rowIndex++;
            seq++;

        }
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 压缩单个excel文件的输出流 到zip输出流,注意zipOutputStream未关闭，需要交由调用者关闭之
     *
     * @param zipOutputStream   zip文件的输出流
     * @param excelOutputStream excel文件的输出流
     * @param excelFilename     文件名可以带目录，例如 TestDir/test1.xlsx
     */
    public static void compressFileToZipStream(ZipOutputStream zipOutputStream,
                                               ByteArrayOutputStream excelOutputStream, String excelFilename) {
        byte[] buf = new byte[1024];
        try {
            // Compress the files
            byte[] content = excelOutputStream.toByteArray();
            ByteArrayInputStream is = new ByteArrayInputStream(content);
            BufferedInputStream bis = new BufferedInputStream(is);
            // Add ZIP entry to output stream.
            zipOutputStream.putNextEntry(new ZipEntry(excelFilename));
            // Transfer bytes from the file to the ZIP file
            int len;
            while ((len = bis.read(buf)) > 0) {
                zipOutputStream.write(buf, 0, len);
            }
            // Complete the entry
            //excelOutputStream.close();//关闭excel输出流
            //zipOutputStream.closeEntry();
            bis.close();
            is.close();
            // Complete the ZIP file
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //也可用以下方法
    public static void setBorderStyle(int border, CellRangeAddress region, SXSSFSheet sheet, SXSSFWorkbook wb) {
        CellStyle cs = wb.createCellStyle(); // 样式对象
        cs.setBorderBottom((short) border);
        cs.setBorderTop((short) border);
        cs.setBorderLeft((short) border);
        cs.setBorderRight((short) border);

        setRegionStyle(cs, region, sheet);
    }

    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, SXSSFSheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            SXSSFRow row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                SXSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(cs);
            }
        }
    }

    private static void setStyle(CellStyle cellStyle) {
        // 水平居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
        // 边框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        // 边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        // 边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // 边框
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    }

}