easyexcel表格导出

最新推荐文章于 2025-07-01 14:39:06 发布

miaoao611

最新推荐文章于 2025-07-01 14:39:06 发布

阅读量462

点赞数

CC 4.0 BY-SA版权

文章标签： excel java servlet

本文链接：https://blog.csdn.net/miaoao611/article/details/130508596

该代码实现了一个ExcelHelper工具类，利用阿里巴巴的EasyExcel库进行Excel文件的导出。方法包括使用模板导出数据，支持自定义合并策略和样式策略，以及读取Excel数据的功能。此外，还展示了如何创建自定义的样式策略类CustomerCellStyleStrategy，用于设置表格的头部和内容样式。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

表格导出工具类

package com.walmart.vendor.app.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.walmart.alohaframework.core.exception.BaseRuntimeException;
import com.walmart.vendor.app.constants.enums.ErrorInfoEnum;
import com.walmart.vendor.app.datasource.vendor_neg_support.model.dto.CategoryItemDetailInfoDTO;
import com.walmart.vendor.app.datasource.vendor_neg_support.model.vo.CategoryItemQueryInfo;
import java.io.BufferedOutputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.util.IOUtils;
import org.springframework.core.io.ClassPathResource;

public class ExcelHelper {
    private ExcelWriter excelWriter = null;
    private WriteSheet writeSheet = null;
    /**
     * @param response: 输出流对象
     * @param template: 导出使用的模板文件地址
     * @param dataList: 填充到导出文件的数据
     * @Title: exportExcel
     * @Description: 使用导出模板文件导出Excel
     * @author: esther
     * @throws:
     * @return: null
     */
    public <T> void exportExcel(HttpServletResponse response, AbstractMergeStrategy mergeStrategy, String template, String fileName, List<T> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            dataList = new ArrayList<>();
        }

        BufferedOutputStream bos = null;
        InputStream templateInput = null;
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            templateInput = new ClassPathResource(template).getInputStream();
            bos = new BufferedOutputStream(response.getOutputStream());
            ExcelWriterBuilder excelWriterBuilder = null;
            if(null != mergeStrategy){
                excelWriterBuilder = EasyExcel.write(bos).registerWriteHandler(mergeStrategy).withTemplate(templateInput);
            }else{
                excelWriterBuilder = EasyExcel.write(bos).withTemplate(templateInput);
            }
            ExcelWriter excelWriter = excelWriterBuilder.build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(dataList, writeSheet);
            excelWriter.finish();
            bos.flush();
        } catch (Exception ex) {
            throw new BaseRuntimeException(ErrorInfoEnum.FAIL_CUSTOM_MSG+"|"+"导出excel异常", ex);
        } finally {
            IOUtils.closeQuietly(templateInput);
        }
    }

    public <T> void exportDetailExcelAppendly(
            HttpServletResponse response,
            AbstractCellStyleStrategy mergeStrategy,
            String template,
            String fileName,
            Map<String,List<T>> map) {

        BufferedOutputStream bos = null;
        InputStream templateInput = null;
        try {
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));

            templateInput = new ClassPathResource(template).getInputStream();
            bos = new BufferedOutputStream(response.getOutputStream());
            ExcelWriterBuilder excelWriterBuilder = null;
            if(null != mergeStrategy){
                excelWriterBuilder = EasyExcel.write(bos).registerWriteHandler(mergeStrategy).withTemplate(templateInput);
            }else{
                excelWriterBuilder = EasyExcel.write(bos).withTemplate(templateInput);
            }
            ExcelWriter excelWriter = excelWriterBuilder.build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            int count=0;
            for(Entry<String,List<T>> entry: map.entrySet()){
                List<T> list=entry.getValue();
                if(CollectionUtils.isEmpty(list)){
                    continue;
                }
                count+=list.size();
                excelWriter.fill(list, writeSheet);
            }
            if(count==0){
                CategoryItemDetailInfoDTO dto=new CategoryItemDetailInfoDTO();
                excelWriter.fill(Arrays.asList(dto),writeSheet);
            }
            excelWriter.finish();
            bos.flush();
        } catch (Exception ex) {
            throw new BaseRuntimeException(ErrorInfoEnum.FAIL_CUSTOM_MSG+"|"+"导出excel异常", ex);
        } finally {
            IOUtils.closeQuietly(templateInput);
        }
    }
    /**
     * @param fs:           导入文件二进制流
     * @param readListener: 读取时候行数据监听处理
     * @param clazz:        返回的class对象
     * @Title: readExcel
     * @Description: 读取导入的Excel数据
     * @author: esther
     * @throws:
     * @return: null
     */
    public <T> List<T> readExcel(InputStream fs, AnalysisEventListener<T> readListener, Class<T> clazz) {
        return EasyExcel
                .read(fs)
                .registerReadListener(readListener)
                .head(clazz)
                .sheet()
                .headRowNumber(1)
                .autoTrim(true)
                .doReadSync();
    }


}

导出策略示例

package com.test.common.excel;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
 
import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;
 
/**
 * @Description: 自定义样式策略
 */
public class CustomerCellStyleStrategy extends AbstractCellStyleStrategy {
 
    private WriteCellStyle headWriteCellStyle;
    private List<WriteCellStyle> contentWriteCellStyleList;
 
    private CellStyle headCellStyle;
    private List<CellStyle> contentCellStyleList;
 
    public CustomerCellStyleStrategy() {
        this.headWriteCellStyle = buildHeadWriteCellStyle();
        this.contentWriteCellStyleList = Collections.singletonList(buildContentWriteCellStyle());
    }
 
    private static WriteCellStyle buildContentWriteCellStyle() {
        //表内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        //内容字体
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("微软雅黑");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return contentWriteCellStyle;
    }
 
    private static WriteCellStyle buildHeadWriteCellStyle() {
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //表头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("黑体");
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setWriteFont(headWriteFont);
        return headWriteCellStyle;
    }
 
    /**
     * 编程方式自定义表格样式
     *
     * @return
     */
    public static List<WriteHandler> getCustomWriteHandlerList() {
        List<WriteHandler> handlerList = new ArrayList<>();
        WriteHandler writeHandler = new HorizontalCellStyleStrategy(buildHeadWriteCellStyle(), buildContentWriteCellStyle());
        handlerList.add(writeHandler);
        return handlerList;
    }
 
    /**
     * 编程方式自动表格宽度
     *
     * @param headList 表头宽度集合
     * @return
     */
    public static Map<Integer, Integer> getLongestColumnWidthMap(List<List<String>> headList) {
        Map<Integer, Integer> columnWidth = new HashMap<>();
        for (int i = 0; i < headList.size(); i++) {
            List<String> list = headList.get(i);
            //取最后一行表头
            String title = list.get(list.size() - 1);
            //参见 com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy.setColumnWidth
            columnWidth.put(i, title.getBytes().length * 250);
        }
        return columnWidth;
    }
 
    /**
     * 编程方式自动表格宽度
     *
     * @param clazz 导出实体类
     * @return
     */
    public static Map<Integer, Integer> getLongestColumnWidthMap(Class clazz) {
        Field[] declaredFields = clazz.getDeclaredFields();
        List<ExcelProperty> propertyList = new ArrayList<>();
        for (Field declaredField : declaredFields) {
            ExcelProperty excelProperty = declaredField.getAnnotation(ExcelProperty.class);
            if (excelProperty != null) {
                propertyList.add(excelProperty);
            }
        }
        List<ExcelProperty> headList = propertyList.stream()
                .sorted(Comparator.comparing(ExcelProperty::order))
                .collect(Collectors.toList());
        Map<Integer, Integer> columnWidth = new HashMap<>();
        for (int i = 0; i < headList.size(); i++) {
            ExcelProperty excelProperty = headList.get(i);
            String[] value = excelProperty.value();
            //取最后一行表头
            String title = value[value.length - 1];
            //参见 com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy.setColumnWidth
            columnWidth.put(i, title.getBytes().length * 250);
        }
        return columnWidth;
    }
 
    public CustomerCellStyleStrategy(WriteCellStyle headWriteCellStyle,
                                     List<WriteCellStyle> contentWriteCellStyleList) {
        this.headWriteCellStyle = headWriteCellStyle;
        this.contentWriteCellStyleList = contentWriteCellStyleList;
    }
 
    public CustomerCellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) {
        this.headWriteCellStyle = headWriteCellStyle;
        contentWriteCellStyleList = new ArrayList<WriteCellStyle>();
        contentWriteCellStyleList.add(contentWriteCellStyle);
    }
 
    @Override
    protected void initCellStyle(Workbook workbook) {
        if (headWriteCellStyle != null) {
            headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
        }
        if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) {
            contentCellStyleList = new ArrayList<CellStyle>();
            for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) {
                contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle));
            }
        }
    }
 
    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (headCellStyle == null) {
            return;
        }
        cell.setCellStyle(headCellStyle);
    }
 
    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        if (contentCellStyleList == null || contentCellStyleList.isEmpty()) {
            return;
        }
        cell.setCellStyle(contentCellStyleList.get(relativeRowIndex % contentCellStyleList.size()));
    }
 
}