改造EasyPoi导出excel不能设置序号的功能

最新推荐文章于 2024-04-25 11:27:44 发布

码蚁1203

最新推荐文章于 2024-04-25 11:27:44 发布

阅读量3.1k

点赞数 1

文章标签： excel poi

本文链接：https://blog.csdn.net/dingfengbo1203/article/details/106007053

版权

当我们直接调用cn.afterturn.easypoi.excel.ExcelExportUtil的exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet)时，如果调用了cn.afterturn.easypoi.excel.entity.ExportParams.setAddIndex(true)时，系统会报空指针异常。

Caused by: java.lang.NullPointerException
	at cn.afterturn.easypoi.excel.export.base.ExportCommonService.getCellValue(ExportCommonService.java:194)
	at cn.afterturn.easypoi.excel.export.base.BaseExportService.createCells(BaseExportService.java:102)
	at cn.afterturn.easypoi.excel.export.ExcelExportService.insertDataToSheet(ExcelExportService.java:240)
	at cn.afterturn.easypoi.excel.export.ExcelExportService.createSheetForMap(ExcelExportService.java:206)
	at cn.afterturn.easypoi.excel.export.ExcelExportService.createSheet(ExcelExportService.java:176)

查看源代码发现是在cn.afterturn.easypoi.excel.export.ExcelExportService类的第228行的insertDataToSheet方法改变了序号的ExcelExportEntity的排序序号，使得序号放到了最后一列。然而序号的ExcelExportEntity的method和methods属性是都没有被赋值的，所以当执行到cn.afterturn.easypoi.excel.export.base.ExportCommonService的194行会报NullPointerException。
既然发现了问题，就要解决问题。
解决步骤一：
查看原代码发现cn.afterturn.easypoi.excel.ExcelExportUtil被final修饰了，不能被继承，所以我们重新定义一个工具类来模仿。

package com.mayi1203.myproject.util;

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 com.mayi1203.myproject.service.SubExcelExportService;

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();
	}
}

发现cn.afterturn.easypoi.excel.export.ExcelExportService没有被final修饰，所以我们定义一个类来继承它，然后重写insertDataToSheet方法，改变的地方就是注释掉排序的代码，具体代码如下：

package com.mayi1203.myproject.service;

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 int MAX_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);
//			sortAllParams(excelParams);
			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());
		}
	}
}

然后看一下我写的例子：
添加依赖

        <!-- Excel = EasyPoi -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.0.0</version>
            <exclusions>
                <exclusion>
                    <artifactId>commons-lang3</artifactId>
                    <groupId>org.apache.commons</groupId>
                </exclusion>
            </exclusions>
        </dependency>

定义一个实体类，添加相应的注解：

package com.mayi1203.myproject.entity;

import java.io.Serializable;
import java.math.BigDecimal;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

/**
 * 学生类
 * @author mayi1203
 * @date 2020年5月8日
 */
@Data
public class Student implements Serializable {
    private static final long serialVersionUID = 2131321500629905052L;
    
    @Excel(name = "学生姓名")
    private String studentName;

    @Excel(name = "学生年龄")
    private Integer age;
    
    @Excel(name = "语文成绩")
    private BigDecimal chineseScore;
    
    @Excel(name = "数学成绩")
    private BigDecimal mathScore;
    
}

首先看不带入序号的例子：

package com.mayi1203.myproject.controller;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.mayi1203.myproject.entity.Student;
import com.mayi1203.myproject.util.ExcelExportUtil;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

@RestController
@RequestMapping("v1/test")
public class TestController {

	@GetMapping("exportExcel")
	public void testExcelExport(HttpServletResponse response) throws Exception {
		// 表格信息
		ExportParams exportParams = new ExportParams("学生成绩表", "学生成绩表", ExcelType.XSSF);
		List<Student> dataSet = new ArrayList<>(3);
		// 添加导出数据
		this.addStudents(dataSet);
		// 使用easypoi的工具类生成worbook
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, dataSet);
		// 导出表格
		ExcelExportUtil.exportExcel(response, workbook);
	}
	
	private void addStudents(List<Student> dataSet) {
		Student stu1 = new Student();
		stu1.setStudentName("张三");
		stu1.setAge(11);
		stu1.setChineseScore(new BigDecimal(87));
		stu1.setMathScore(new BigDecimal(88));
		Student stu2 = new Student();
		stu2.setStudentName("王五");
		stu2.setAge(12);
		stu2.setChineseScore(new BigDecimal(66));
		stu2.setMathScore(new BigDecimal(87));
		Student stu3 = new Student();
		stu3.setStudentName("赵六");
		stu3.setAge(13);
		stu3.setChineseScore(new BigDecimal(77));
		stu3.setMathScore(new BigDecimal(98));
		dataSet.add(stu1);
		dataSet.add(stu2);
		dataSet.add(stu3);
	}
}

调用接口，查看结果如下：
在这里插入图片描述
然后看带入序号的例子：

package com.mayi1203.myproject.controller;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.mayi1203.myproject.entity.Student;
import com.mayi1203.myproject.util.ExcelExportUtil;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;

@RestController
@RequestMapping("v1/test")
public class TestController {

	@GetMapping("exportExcel")
	public void testExcelExport(HttpServletResponse response) throws Exception {
		// 表格信息
		ExportParams exportParams = new ExportParams("学生成绩表", "学生成绩表", ExcelType.XSSF);
		// 设置添加序号
		exportParams.setAddIndex(true);
		List<Student> dataSet = new ArrayList<>(3);
		// 添加导出数据
		this.addStudents(dataSet);
		// 使用easypoi的工具类生成worbook
		Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, dataSet);
		// 导出表格
		ExcelExportUtil.exportExcel(response, workbook);
	}
	
	private void addStudents(List<Student> dataSet) {
		Student stu1 = new Student();
		stu1.setStudentName("张三");
		stu1.setAge(11);
		stu1.setChineseScore(new BigDecimal(87));
		stu1.setMathScore(new BigDecimal(88));
		Student stu2 = new Student();
		stu2.setStudentName("王五");
		stu2.setAge(12);
		stu2.setChineseScore(new BigDecimal(66));
		stu2.setMathScore(new BigDecimal(87));
		Student stu3 = new Student();
		stu3.setStudentName("赵六");
		stu3.setAge(13);
		stu3.setChineseScore(new BigDecimal(77));
		stu3.setMathScore(new BigDecimal(98));
		dataSet.add(stu1);
		dataSet.add(stu2);
		dataSet.add(stu3);
	}
}

调用接口，查看结果如下：
在这里插入图片描述
喜大普奔！！！改造成功！！！

码蚁1203

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
5
评论
改造EasyPoi导出excel不能设置序号的功能

当我们直接调用cn.afterturn.easypoi.excel.ExcelExportUtil的exportExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet)时，如果调用了cn.afterturn.easypoi.excel.entity.ExportParams.setAddIndex(true)时，系统会报空指针异常。Caused by: java.lang.NullPointerExc
复制链接

扫一扫