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

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

目录方式一：方式二：

方式一：

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

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

List colList = new ArrayList();

//自动新增首列序号

ExcelExportEntity xh = new ExcelExportEntity("序号", "xh");

xh.setFormat("isAddIndex");

colList.add(xh);

方式二：

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

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

import java.io.OutputStream;

import java-.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 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()方法

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 entityList,

Collection> dataSet, Sheet sheet) {

try {

dataHandler = entity.getDataHandler();

if (dataHandler != null &&amphttp://; 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 excelParams = new ArrayList();

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

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

}

}

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

ExportParams params = new ExportParams();

params.setAddIndex(true);

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。