服务器之家

服务器之家 > 正文

springboot中EasyPoi实现自动新增序号的方法

时间:2022-01-19 11:02     来源/作者:御风寻

方式一:

新增一个名为序号的列,设置导出格式为:isAddIndex

不要在ExportParams 中设置addIndex 为true 这个没啥用

?
1
2
3
4
5
List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
//自动新增首列序号
ExcelExportEntity xh = new ExcelExportEntity("序号", "xh");
xh.setFormat("isAddIndex");
colList.add(xh);

方式二:

第一步:重新定义一个ExcelExportUtil(easypoi 中的ExcelExportUtil是被final 修饰的)

如下图所示: SubExcelExportService是我们继承easypoi 的ExcelExportService 并重写了insertDataToSheet()方法。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.List;
 
import javax.servlet.http.HttpServletResponse;
 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
 
public class ExcelExportUtil {
 
    public static Workbook exportExcel(ExportParams entity, List<ExcelExportEntity> entityList, Collection<?> dataSet) {
        Workbook workbook =getWorkbook(entity.getType(),dataSet.size());;
        new SubExcelExportService().createSheetForMap(workbook, entity, entityList, dataSet);
        return workbook;
    }
 
    public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook =getWorkbook(entity.getType(), dataSet.size());
        new SubExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
        return workbook;
    }
 
    private static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else if (size < 100000) {
            return new XSSFWorkbook();
        } else {
            return new SXSSFWorkbook();
        }
    }
 
    public static void exportExcel(HttpServletResponse response, Workbook workbook) throws Exception {
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        String fileName = System.currentTimeMillis() + ".xlsx";
        response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setCharacterEncoding("UTF-8");
        OutputStream output = response.getOutputStream();
        workbook.write(output);
        workbook.close();
        output.close();
    }
}

第二步:继承ExcelExportService 并重写insertDataToSheet()方法

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
 
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
 
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import cn.afterturn.easypoi.exception.excel.ExcelExportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelExportEnum;
import cn.afterturn.easypoi.util.PoiExcelGraphDataUtil;
 
public class SubExcelExportService extends ExcelExportService {
    private static intMAX_NUM= 60000;
 
    @Override
    protected void insertDataToSheet(Workbook workbook, ExportParams entity, List<ExcelExportEntity> entityList,
                                     Collection<?> dataSet, Sheet sheet) {
        try {
            dataHandler = entity.getDataHandler();
            if (dataHandler != null && dataHandler.getNeedHandlerFields() != null) {
                needHandlerList = Arrays.asList(dataHandler.getNeedHandlerFields());
            }
            dictHandler = entity.getDictHandler();
            i18nHandler = entity.getI18nHandler();
            // 创建表格样式
            setExcelExportStyler(
                    (IExcelExportStyler) entity.getStyle().getConstructor(Workbook.class).newInstance(workbook));
            Drawing patriarch = PoiExcelGraphDataUtil.getDrawingPatriarch(sheet);
            List<ExcelExportEntity> excelParams = new ArrayList<ExcelExportEntity>();
            if (entity.isAddIndex()) {
                excelParams.add(indexExcelEntity(entity));
            }
            excelParams.addAll(entityList);
            int index = entity.isCreateHeadRows() ? createHeaderAndTitle(entity, sheet, workbook, excelParams) : 0;
            int titleHeight = index;
            setCellWith(excelParams, sheet);
            setColumnHidden(excelParams, sheet);
            short rowHeight = entity.getHeight() != 0 ? entity.getHeight() : getRowHeight(excelParams);
            setCurrentIndex(1);
            Iterator<?> its = dataSet.iterator();
            List<Object> tempList = new ArrayList<Object>();
            while (its.hasNext()) {
                Object t = its.next();
                index += createCells(patriarch, index, t, excelParams, sheet, workbook, rowHeight, 0)[0];
                tempList.add(t);
                if (index >=MAX_NUM) {
                    break;
                }
            }
            if (entity.getFreezeCol() != 0) {
                sheet.createFreezePane(entity.getFreezeCol(), 0, entity.getFreezeCol(), 0);
            }
 
            mergeCells(sheet, excelParams, titleHeight);
 
            its = dataSet.iterator();
            for (int i = 0, le = tempList.size(); i < le; i++) {
                its.next();
                its.remove();
            }
            if (LOGGER.isDebugEnabled()) {
LOGGER.debug("List data more than max ,data size is {}", dataSet.size());
            }
            // 发现还有剩余list 继续循环创建Sheet
            if (dataSet.size() > 0) {
                createSheetForMap(workbook, entity, entityList, dataSet);
            } else {
                // 创建合计信息
                addStatisticsRow(getExcelExportStyler().getStyles(true, null), sheet);
            }
 
        } catch (Exception e) {
LOGGER.error(e.getMessage(), e);
            throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, e.getCause());
        }
    }

第三步:设置导出文件的时候自动新增序号

?
1
2
3
ExportParams params = new ExportParams();
 
params.setAddIndex(true);

注意事项:ExcelExportUtil需要使用我们自己定义的(不要使用easyPoi的),ExcelExportUtil.exportExcel()中的 ExcelExportService 一定要使用我们自己定义的SubExcelExportService详细参考上图中的示例。

到此这篇关于springboot中EasyPoi实现自动新增序号的方法的文章就介绍到这了,更多相关springboot中EasyPoi自动新增序号内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!

原文链接:https://blog.csdn.net/qq_40249955/article/details/120491911

相关文章

热门资讯

蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整
蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整 2021-08-24
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
2021年耽改剧名单 2021要播出的59部耽改剧列表
2021年耽改剧名单 2021要播出的59部耽改剧列表 2021-03-05
返回顶部