easyExcel复杂表头的设置与样式的设置(非注解)

easyExcel复杂表头的设置与样式的设置(非注解)

表头样式一

引入jar包

  <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
            <scope>compile</scope>
  </dependency>

excel的样式设置

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;

import java.util.ArrayList;
import java.util.List;

public class HeadContentCellStyle {


    /**
     *  设置表头  和内容样式
     * @return
     */
    public static HorizontalCellStyleStrategy myHorizontalCellStyleStrategy(){
//1 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short)11);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式  多个样式则隔行换色
        List<WriteCellStyle>   listCntWritCellSty =  new ArrayList<>();

//2 内容样式策略  样式一
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short)11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //背景设置白色（这里一定要设置表格内容的背景色WPS下载下来的文件没有问题，但是office下载下来的文件表格内容会变成黑色）
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        //设置自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 头默认了 FillPatternType所以可以不指定。
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //设置背景黄色
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        //设置水平靠左
        //contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle);
        //内容风格可以定义多个。
        listCntWritCellSty.add(contentWriteCellStyle);

//2 内容样式策略  样式二
        WriteCellStyle contentWriteCellStyle2 = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。
        // 头默认了 FillPatternType所以可以不指定。
        contentWriteCellStyle2.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //背景设置白色
        contentWriteCellStyle2.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        // 背景绿色
//        contentWriteCellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        //设置垂直居中
        contentWriteCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle2);
        listCntWritCellSty.add(contentWriteCellStyle2);
        // 水平单元格风格综合策略(表头 + 内容)
        // return  new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return  new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
    }

    /**
     * 设置边框样式
     * @param contentWriteCellStyle
     */
    private static void setBorderStyle(WriteCellStyle contentWriteCellStyle){
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); //颜色
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    }

}

文件流的输出

  /**
     *
     * 
     * @Param 复杂表头文件导出
     * @return 
     */
    public void downloadFile(HttpServletResponse response, List<?> list, String fileNames, String sheetName,List<List<String>> titleList) throws IOException {
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException(3698,"当前参考单位无数据");
        }
        if (StringUtils.isEmpty(fileNames)) {
            fileNames = new Date().toString();
        }
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = "Sheet1";
        }
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(fileNames, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
//            EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).autoCloseStream(Boolean.FALSE).sheet(sheetName)
//                    .doWrite(list);
            EasyExcel.write(response.getOutputStream())
                    .head(roateHeadFields(titleList))
                    .registerWriteHandler(HeadContentCellStyle.myHorizontalCellStyleStrategy())
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(15)) // 简单的列宽策略，列宽15
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)15)) // 简单的行高策略：头行高30，内容行高15
                    .sheet("sheet").doWrite(list);
        } catch (Exception e) {
            log.error("导出失败------------"+e.getMessage());
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
    
       /**
     * 表头设置
     * @param headFields
     * @return
     */
    private List<List<String>> roateHeadFields(List<List<String>> headFields) {
        List<List<String>> result = new ArrayList<>();
        for (List<String> row : headFields) {
            for (int j = 0; j < row.size(); j++) {
                if (result.size() > j) {
                    // 往对应第j个List<String> 添加添加值
                    result.get(j).add(row.get(j));
                } else {
                    // 分割成单个List<String>
                    result.add(new ArrayList<>(Collections.singletonList(row.get(j))));
                }
            }
        }
        return result;
    }
    /**
     * 文件的头
     * @param titleList
     * @return
     */
    public List<String> makeUpTitle(List<String> titleList) {
        List<String> makeUpList = new ArrayList<>();
        String temp = "";
        for (String e : ListUtils.emptyIfNull(titleList)) {
            if (StringUtils.isNotEmpty(e)) {
                temp = e;
            }
            makeUpList.add(temp);
        }

        return makeUpList;

    }

设置文件头和数据导入

   /**
     * @description 全市单科成绩统计表
     *
     **/
    public void downloadCitySingleScore(HttpServletResponse response, String examId) throws IOException {
        //获取文件名称
        String fileNames = getFileNames(examId,"全市单科成绩统计表");
        //获取单科成绩数据
        List<CitySingleScore> scoreList = getCitySingleScores(examId);
        //建立表头
        List<List<String>> titleList = new ArrayList<>();
        List<String> titleOne = Lists.newArrayList(fileNames,"","","");
        List<String> titleTwo = Lists.newArrayList("科目","平均分","最高分","最高分人数");
        titleList.add(easyExcelUtil.makeUpTitle(titleOne));
        titleList.add(easyExcelUtil.makeUpTitle(titleTwo));
        easyExcelUtil.downloadFile(response,scoreList,fileNames,null,titleList);
    }

表头样式二

jar包和excel的样式设置不变 ，表头数据需要自己组装

  /**
     * 创建表头
     * @return
     */
    public   List<List<String>> head(String examId) {
        List<List<String>> list = new ArrayList<List<String>>();
        //表头数据
        String basicInfo=examUitl.getFileNames(examId,"全市赋分等级区间表");
        String a="等级",b="比例",c="赋分区间",d="原始分分布区间";
        //第一列，1/2/3行
        list.add( Lists.newArrayList(basicInfo,a,a ));
        //第二列，1/2/3行
        list.add( Lists.newArrayList(basicInfo,b,b));
        //第三列
        list.add( Lists.newArrayList(basicInfo,c,c));
        //动态根据月份生成
        List<String> orderSpeaces = Lists.newArrayList("政治","地理","化学","生物");
        orderSpeaces.forEach(title->{
            list.add( Lists.newArrayList(basicInfo,d ,title) );
        });

        return list;
    }

数据导入

/**
     * @description 全市赋分等级区间表
     *
     * @params [response, examId]
     * @return void
     * @author xiaoyinchuan
     * @date 2020/11/24 0024 15:08
     **/
    public void downloadGraduation(HttpServletResponse response, String examId) throws IOException {
       
        List<NaturalGradeVO> list = highScoresNewService.selectNaturalGrade(examId);
          //生成数据
        List<Graduation> resultList = JSON.parseArray(JSON.toJSONString(list), Graduation.class);
        List<List<String>> head = head(examId);
        easyExcelUtil.downloadFileNext(response,resultList,examUitl.getFileNames(examId,"全市赋分等级区间表"),null,head);
    }

导入util

   /**
     *
     * 和方式一导入的差异是.head(titleList) ，表头直接放入
     * @Param 复杂表头文件导出
     * @return
     */
    public void downloadFileNext(HttpServletResponse response, List<?> list, String fileNames, String sheetName,List<List<String>> titleList) throws IOException {
        if (CollectionUtils.isEmpty(list)) {
            throw new BusinessException(3698,"当前参考单位无数据");
        }
        if (StringUtils.isEmpty(fileNames)) {
            fileNames = new Date().toString();
        }
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = "Sheet1";
        }
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode(fileNames, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
//            EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).autoCloseStream(Boolean.FALSE).sheet(sheetName)
//                    .doWrite(list);
            EasyExcel.write(response.getOutputStream())
                    .head(titleList)  
                    .registerWriteHandler(HeadContentCellStyle.myHorizontalCellStyleStrategy())
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(15)) // 简单的列宽策略，列宽15
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)15)) // 简单的行高策略：头行高25，内容行高15
                    .sheet("sheet").doWrite(list);
        } catch (Exception e) {
            log.error("导出失败------------"+e.getMessage());
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

参考文章：https://blog.csdn.net/qq_35461948/article/details/116658131
参考文章：https://blog.csdn.net/jie873440996/article/details/106399471