EasyExcel实现List＜Map＞多表头excel导出

导出excel，作为在项目中经常使用的，这里做一个小的记录。

首先贴出导出后的excel图片

 下面是Controller 层中的请求代码：

这里没啥好说的就是创建数据，设名称。

 @Request.Post("/list")
    public void list(Params params) {
        HttpServletResponse response = DataContext.getResponse();
        List<Map<String,Object>> dataList = new ArrayList<>();
        Map<String,Object> map = new HashMap<>();
        map.put("name","张三");
        map.put("className","语文");
        map.put("grade","87");
        dataList.add(map);
        Map<String,Object> map1 = new HashMap<>();
        map1.put("name","李四");
        map1.put("className","数学");
        map1.put("grade","90");
        dataList.add(map1);
        Map<String,Object> map2 = new HashMap<>();
        map2.put("name","王五");
        map2.put("className","英语");
        map2.put("grade","69");
        dataList.add(map2);
        try {
            ExcelUtil.exportExcelGrade(response,dataList, GradeModel.class,"成绩汇总表","成绩汇总表");
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

在上面的代码中可以看到我们的数据有三列，所以我们需要创建一个GradeModel的实体，

在这个实体中注解：@ExcelProperty：import com.alibaba.excel.annotation.ExcelProperty;

 value：是它的表头的值，index：则代表它所在的位置。

由导出的excel中的图，我们可以看到，成绩表 作为第一表头横跨了四格。

其他表头则正常显示，同理，所以有其他的合并表头也可以这么使用。

@Data
public class GradeModel {
    @ExcelProperty(value = {"成绩表","序号"} ,index = 0)
    private String serialnumber;
    @ExcelProperty(value = {"成绩表","名称"} ,index = 1)
    private String name;

    @ExcelProperty(value = {"成绩表","课程"} ,index = 2)
    private String className;

    @ExcelProperty(value = {"成绩表","成绩"} ,index = 3)
    private String grade;
}

下面是 ExcelUtil 工具类中代码：

import com.alibaba.excel.EasyExcel;
import cn.hutool.core.bean.BeanUtil;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ExcelUtil {
    /**
     * excel导出
     * @param response http响应对象
     * @param dataList 导出的list内容数据
     * @param clazz 导出的类型
     * @param fileNamePre 文件名（不含后缀）
     * @param sheetName sheet名
     * @throws IOException IO异常
     */

    public static void exportExcelGrade(HttpServletResponse response, List<Map<String,Object>> dataList, Class clazz, String fileNamePre, String sheetName) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //设置文件名的编码格式,防止文件名乱码
        String fileName = fileNamePre;
        response.setHeader("Content-Disposition","attachment;filename=" + fileName + ".xlsx");
        /* 序号 */
        int serialnumber = 1;
        /* 存放数据 */
        List<GradeModel> excelList = new ArrayList<>();
        for (Map<String, Object> map : dataList) {
            GradeModel exporterModel = BeanUtil.toBean(map, GradeModel.class);
            exporterModel.setSerialnumber(String.valueOf(serialnumber++));
            excelList.add(exporterModel);
        }

        // 调用工具类
        EasyExcel.write(response.getOutputStream(),clazz)
                 //excel中的格式
                .registerWriteHandler(CellStyleUtil.getHorizontalCellStyleStrategy())
                 //设置了单元格长度    
                .registerWriteHandler(new ExcelEnterpriseHandler())
                .sheet(sheetName)
                .doWrite(excelList);
    }
}

CellStyleUtil 作为设置excel中的格式，代码如下。

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.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

public class CellStyleUtil {

    public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为白色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //自动换行
//        headWriteCellStyle.setWrapped(true);
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //自动换行
        contentWriteCellStyle.setWrapped(true);
        //文字
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)12);
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

单元格设置大小

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.List;


public class ExcelEnterpriseHandler extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        Sheet sheet = writeSheetHolder.getSheet();
        sheet.setColumnWidth(cell.getColumnIndex(), 3300);
    }

}

---------------------------------------------------------------------------------------------------------------------------------

在这里顺便记录一下，用postman调用本地可以直接下载excel得方式

第一种，直接发送请求，可以看到返回的body中得流，再点击右侧保存到文件即可。

 第二种，点击send旁边得下拉三角直接点击 发送并保存也可以。