基于阿里巴巴easyExcel实现复杂数据的EXCEL导出

/**
     * 对Map<String,List<Object>>结构的数据源将其里面的数据导入到excel表单
     *
     * @param data 导出数据集合
     * @param sheetName 工作表的名称
     * @return 结果
     */
    public AjaxResult exportExcel(Map<String,List<Object>> data, String sheetName)
    {
        String filename = encodingFilename(sheetName);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = getHorizontalCellStyleStrategy();
        try {
            //列宽的策略,宽度是小单位
            Integer[] columnWidthArr = new Integer[dynamicList(data).size()];
            for(int i=0;i<columnWidthArr.length;i++){
                columnWidthArr[i]=7000;
            }
            List<Integer> columnWidths = Arrays.asList(columnWidthArr);
            CustomSheetWriteHandler customSheetWriteHandler = new CustomSheetWriteHandler(columnWidths);
            EasyExcel.write(getAbsoluteFile(filename))
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .registerWriteHandler(customSheetWriteHandler)
                    // 这里放入动态头
                    .head(dynamicHead(data)).sheet(sheetName)
                    // 当然这里数据也可以用 List<List<String>> 去传入
                    .doWrite(dynamicList(data));
            return  AjaxResult.success(filename);
        }
        catch (Exception e)
        {
            log.error("导出Excel异常{}", e.getMessage());
            throw new BusinessException("导出Excel失败，请联系网站管理员！");
        }
    }

    /**
     * 动态设置EXCEL的表头  headList --> 所有表头行集合
     * @param data
     * @return
     */
    private List<List<String>> dynamicHead(Map<String,List<Object>> data) {
        List<List<String>> headList = new ArrayList<>();
        for (String key:data.entrySet())
        {
            List<String> head = new ArrayList<>();
            head.add(key);
            headList.add(head);
        }
        return headList;
    }

    /**
     * 动态设置EXCEL数据(所有行的集合)
     * @param data
     * @return
     */
    private List<List<Object>> dynamicList(Map<String,List<Object>> data){
        // 所有行的集合
        List<List<Object>> list = new ArrayList<>();
        int max =0;
        for (String key : data.entrySet())
        {
            List<Object> lineData=data.get(key);
            if(max<lineData.size()){
                max=lineData.size();
            }
        }
        for(int i=0;i<max;i++){
            //当前行的数据
            List<Object> line=new ArrayList<> ();
            for (String key : data.entrySet())
            {
                List<Object> lineData=data.get(key);
                line.add(lineData.get(i));
            }
            list.add(line);
        }
        return list;
    }
 /**
     * 获取策略,这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
     *
     * @return
     */
    private HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为白
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());

        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);

        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        return horizontalCellStyleStrategy;
    }

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.collections.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

/**
 * 自定义拦截器
 *
 * @author
 */
public class CustomSheetWriteHandler implements SheetWriteHandler {
    /**列宽集合*/
    private List<Integer> columnWidths;
    /**构造*/
    public CustomSheetWriteHandler(List<Integer> columnWidths) {
        this.columnWidths = columnWidths;
    }

    private static final Logger LOGGER = LoggerFactory.getLogger(CustomSheetWriteHandler.class);

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        LOGGER.info("第{}个Sheet写入成功。", writeSheetHolder.getSheetNo());
        LOGGER.info("普通策略设置setColumnWidth开始~");
        if(CollectionUtils.isNotEmpty(columnWidths)){
            for (int i = 0; i < columnWidths.size(); i++) {
                writeSheetHolder.getSheet().setColumnWidth(i, columnWidths.get(i));
            }
        }
        LOGGER.info("普通策略设置setColumnWidth结束~");
    }
}

	    <dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>2.1.6</version>
		</dependency>