Java easy-excel导出表格

最新推荐文章于 2024-12-26 15:50:51 发布

原创最新推荐文章于 2024-12-26 15:50:51 发布 · 670 阅读

0 ·

CC 4.0 BY-SA版权

spring 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了如何使用Spring Boot中的ExcelUtil进行文件导出，包括设置响应头、创建ExcelWriter并处理分页导出。重点展示了ExcelExportTemplate抽象类，用于生成Excel文件，通过子类化实现查询和数据处理功能。

package com.heytap.mall.marketing.core.utils;



import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

@Slf4j
public class ExcelUtil {
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
            throws Exception{
        try{
            fileName = URLEncoder.encode(fileName,"utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //此处指定了文件类型为xls，如果是xlsx的，请自行替换修改
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e){
            throw new Exception("导出文件失败！");
        }
    }

    public static void writeExcel(HttpServletResponse response, List<?> list, String fileName,
                                  String sheetName, Class clazz) throws Exception {
        try{
            ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLS);
            Sheet sheet = new Sheet(1, 0, clazz);
            sheet.setSheetName(sheetName);
            writer.write(list, sheet);
            writer.finish();
        }catch(Exception e){
            log.error("excel文件导出失败, 失败原因：{}", e);
        }
    }
}

package com.heytap.mall.marketing.core.service.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.heytap.mall.marketing.core.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;


/**
 * 分页导出excel，需要子类实现查询count，查询集合记录
 * @author wangfenglei
 * @date 2021/4/21 10:55
 */
@Slf4j
public abstract class ExcelExportTemplate<T> {

    /**
     * 每个sheet最大导出记录行数
     */
    private static final Integer EXPORT_MAXNUM = 50000;

    /**
     * 导出excel方法
     * @param fileName 文件名称
     * @param clazz 导出对象的class
     * @param response 响应对象
     */
    public void exportExcelSheet(String fileName,Class clazz, HttpServletResponse response) {
        int userCount = getRecordCount();
        int sheetNum = 1;
        if(userCount>EXPORT_MAXNUM){
            sheetNum = (userCount % EXPORT_MAXNUM) == 0 ? (userCount / EXPORT_MAXNUM) : (userCount / EXPORT_MAXNUM)+1;
        }
        try{
            OutputStream outputStream = ExcelUtil.getOutputStream(fileName+"参与人",response);
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream,clazz).excelType(ExcelTypeEnum.XLSX);
            ExcelWriter excelWriter = excelWriterBuilder.build();
            ExcelWriterSheetBuilder excelWriterSheetBuilder;
            WriteSheet writeSheet;
            for (int i = 1;i<= sheetNum;i++){

                List<T> excelUserList = getRecordList(i,EXPORT_MAXNUM);
                excelWriterSheetBuilder = new ExcelWriterSheetBuilder(excelWriter);
                excelWriterSheetBuilder.sheetNo(i);
                excelWriterSheetBuilder.sheetName("批次"+i);
                writeSheet = excelWriterSheetBuilder.build();
                excelWriter.write(excelUserList, writeSheet);

            }
            outputStream.flush();
            excelWriter.finish();
            outputStream.close();
        }catch(Exception e){
            log.error("ExcelExportTemplate导出错误error",e.getMessage());
        }
    }

    //                Page<FissionUser> page = fissionUserService.page(new Page<>(i,EXPORT_MAXNUM),wrapper);
//                List<ExcelFissionUserVO> excelUserList = Lists.newArrayList();
//                if(CollectionUtils.isNotEmpty(page.getRecords())){
//                    ExcelFissionUserVO userVO = new ExcelFissionUserVO();
//                    for (FissionUser fissionUser:(List<FissionUser>)page.getRecords()) {
//                        BeanUtil.copyProperties(fissionUser,userVO);
//                        userVO.setActivityName(activityName);
//                        userVO.setIsSuccess(fissionUser.getIsSuccess() == 1?"成功":"失败");
//                        excelUserList.add(userVO);
//                    }
//                }

    /**
     * 子类需要实现查询记录
     * @param i
     * @param exportMaxnum
     * @return
     */
    protected abstract List<T> getRecordList(int i, Integer exportMaxnum);

    //        LambdaQueryWrapper<FissionUser> wrapper = new LambdaQueryWrapper<>();
//        wrapper.eq(FissionUser::getActivityId,activityId);
//        wrapper.eq(FissionUser::getDeleted, DeletedStatusEnum.NOT_DELETED.getCode());
//int userCount = fissionUserService.count(wrapper);

    /**
     * 子类需要实现的查询的记录总数
     * @return
     */
    protected abstract int getRecordCount();
}