poi 导出
项目中有两个不同框架组装成的Excel，现在要对Excel内容合并，组成多sheet的Excel。
主调用方法：
public ResponseEntity exportExcel(Long id) {
List<PcOrganization> list = partnerOrganizationService.getList(id);
List<Long> ids = list.stream().map(PcOrganization::getId).collect(Collectors.toList());
List<ExportAgentVo> sheet2 = makeSheetDeviceList(ids);
List<ExportAgentAndCustomerOrderVo> sheet3 = makeSheetCustomerList(ids);
HashMap<String, Object> map = Maps.newHashMap();
map.put(ExportEnum.DEVICEdETAIL.getSheetName(), sheet2);
map.put(ExportEnum.CUSTOMERDETAIL.getSheetName(), sheet3);
HashMap<String, Class> classMap = Maps.newHashMap();
classMap.put(ExportEnum.DEVICEdETAIL.getSheetName(), ExportAgentVo.class);
classMap.put(ExportEnum.CUSTOMERDETAIL.getSheetName(), ExportAgentAndCustomerOrderVo.class);
LocalDate now = LocalDate.now();
File datafile = null;
//File datafile2 = null;
try {
datafile = ExcelTools.createDatafile(location, now.toString());
//datafile2 = ExcelTools.createDatafile(location, "原版");
} catch (Exception e) {
throw new BusinessException(ResultCodeEnum.OPERATION_ERR.getCode(), "创建文件失败");
}
Workbook sheets = ExcelTools.exportSheet(map, classMap, datafile);
PcOrganization organization = list.get(0);
List<OrgStatisticsExcelVo> statisticsExcelVos = makeSheetOrgStatistics(list);
//主sheet
Workbook workBook = StatisticsExcelUtils.createExcelFile2(statisticsExcelVos, organization);
Iterator<Sheet> sheetIterator = sheets.sheetIterator();
int hasSheetIndex = workBook.getNumberOfSheets()-1;//主Excel的最后一个sheet的索引
while(sheetIterator.hasNext()){
Sheet srcSheet = sheetIterator.next();
HSSFSheet newSheet1 = (HSSFSheet) workBook.createSheet();
CopySheetUtil.copySheets(newSheet1, (HSSFSheet) srcSheet, true);
hasSheetIndex += 1;
workBook.setSheetName(hasSheetIndex, srcSheet.getSheetName());
}
try {
ExcelTools.writeDatafile(workBook, datafile);
} catch (Exception e) {
throw new BusinessException(ResultCodeEnum.OPERATION_ERR.getCode(), "导出失败");
}
return new ResponseEntity(ResultEnum.SUCCESS, null);
}变量sheets是由easypoi生成。变量workbook由原生生成。
附easypoi生成Excel的封装方法：
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.Assert;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
public class ExcelTools {
/**
* map 即为要导出的数据。map的key为sheet的名称，value为excel的list数据
* classMap为easypoi要导出的数据entuty，与第一个参数map要对应。map的长度要与classMap的长度一致。
* ckassMap的key与第一个参数map的key一致，都是sheet的名称，value是要解析的注解了@ExcelTagret的class对象。
* 目的是讲第一个参数map的value的list数据解析成classMap的class对象。
* dataFile为导出的excel的文件全路径名
* 功能描述：把同一个表格多个sheet测试结果重新输出，如果后续增加多个List<Map<String, Object>>对象，需要后面继续追加
* @ExcelEntiry sheet表格映射的实体对象
* @return
*/
public static Workbook exportSheet(Map map, Map classMap, File datafile){
Assert.isTrue(map.size() == classMap.size(), "导出的数据map长度要与对应的对象map长度一致");
Workbook workBook = null;
List<Map<String, Object>> sheetsList = new ArrayList<>();
Iterator<Map.Entry<String, List>> iterator = map.entrySet().iterator();
while(iterator.hasNext()){
Map.Entry<String, List> it = iterator.next();
String key = it.getKey();
List value = it.getValue();
Class t = (Class) classMap.get(key);
// 创建参数对象（用来设定excel得sheet得内容等信息）
ExportParams exportParams = new ExportParams();
//添加序号
//exportParams.setAddIndex(true);
// 设置sheet得名称
exportParams.setSheetName(key);
// 设置sheet表头名称
exportParams.setTitle(key);
// 创建sheet使用得map
Map<String, Object> exportMap = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
exportMap.put("title", exportParams);
// 模版导出对应得实体类型
exportMap.put("entity", t);
// sheet中要填充得数据
exportMap.put("data", value);
sheetsList.add(exportMap);
}
// 执行方法
workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
return workBook;
}
/**
* 设置文件路径 && 保证文件对象的正确打开
* */
public static File createDatafile(String path, String fileName) throws Exception{
String resource = path+fileName+".xls";
//URI uri = new URI(resource);
File myFile = new File(resource);//创建File对象，参数为String类型，表示目录名
//判断文件是否存在，如不存在则调用createNewFile()创建新目录，否则跳至异常处理代码
if(!myFile.exists()) {
myFile.createNewFile();
}
return myFile;
}
public static void writeDatafile(Workbook workBook, File datafile) throws Exception{
try{
FileOutputStream fos = new FileOutputStream(datafile);
workBook.write(fos);
fos.close();
}catch (Exception e){
e.printStackTrace();
}finally {
if(workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}对easypoi生成的Workbook复制其sheet到主Workbook中。工具类来源于网上copy的，实际运行时发现有bug，改动后可以正常使用：
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class CopySheetUtil {
public CopySheetUtil() {
}
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) {
copySheets(newSheet, sheet, true);
}
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet,
boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>()
: null;
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
HSSFRow srcRow = sheet.getRow(i);
HSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
CopySheetUtil.copyRow(sheet, newSheet, srcRow, destRow,
styleMap, mergedRegions);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) { //设置列宽
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
/**
* 复制并合并单元格
* @param srcSheet
* @param destSheet
* @param srcRow
*/
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet,
HSSFRow srcRow, HSSFRow destRow,
Map<Integer, HSSFCellStyle> styleMap, Set<CellRangeAddressWrapper> mergedRegions) {
try{
//Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
int deltaRows = destRow.getRowNum() - srcRow.getRowNum(); //如果copy到另一个sheet的起始行数不同
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
HSSFCell oldCell = srcRow.getCell(j); // old cell
HSSFCell newCell = destRow.getCell(j); // new cell
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion = getMergedRegion(srcSheet,
srcRow.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(
mergedRegion.getFirstRow() + deltaRows,
mergedRegion.getLastRow() + deltaRows, mergedRegion
.getFirstColumn(), mergedRegion
.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(
newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
boolean i = mergedRegions.add(wrapper);
if(i){
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中
*
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(HSSFCell oldCell, HSSFCell newCell,
Map<Integer, HSSFCellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet()
.getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook()
.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType().getCode()) {
//case CellType.STRING:
case 1:
newCell.setCellValue(oldCell.getStringCellValue());
break;
//case CellType.NUMERIC:
case 0:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
//case CellType.BLANK:
case 3:
newCell.setCellType(CellType.BLANK);
break;
//case CellType.BOOLEAN:
case 4:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
//case CellType.ERROR:
case 5:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
//case CellType.FORMULA:
case 2:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
// 获取merge对象
public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum,
short cellNum) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress merged = sheet.getMergedRegion(i);
if (merged.isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
private static boolean isNewMergedRegion(
CellRangeAddressWrapper newMergedRegion,
Set<CellRangeAddressWrapper> mergedRegions) {
boolean bool = mergedRegions.contains(newMergedRegion);
return !bool;
}
}其中为了判断合并单元格是否一样，自定义的类
CellRangeAddressWrapper：import org.apache.poi.ss.util.CellRangeAddress;
public class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper>{
public CellRangeAddress range;
public CellRangeAddressWrapper(CellRangeAddress theRange) {
this.range = theRange;
}
@Override
public int compareTo(CellRangeAddressWrapper craw) {
if (range.getFirstColumn() < craw.range.getFirstColumn()
|| range.getFirstRow() < craw.range.getFirstRow()) {
return -1;
} else if (range.getFirstColumn() == craw.range.getFirstColumn()
&& range.getFirstRow() == craw.range.getFirstRow()) {
return 0;
} else {
return 1;
}
}
}
即可将源对象的sheet拷贝至目标对象中。
需要注意的是 生成 ExcelTools 类中方法 exportSheet 中有注释掉的 生成文件加 序号 的方法。这个晚上可以找到很多。也贴一下，需要自定义 导出的方法，原生的会报错。/**
* 重写easypoi的工具类，原工具类是final类型的
*
*/
public class ExcelExportUtil {
public static int USE_SXSSF_LIMIT = 100000;
public static final String SHEET_NAME = "sheetName";
private ExcelExportUtil() {
}
/**
* 大数据量导出
*
* @param entity 表格标题属性
* @param pojoClass Excel对象Class
* @param server 查询数据的接口
* @param queryParams 查询数据的参数
*/
public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
IExcelExportServer server, Object queryParams) {
ExcelBatchExportService batchServer = new ExcelBatchExportService();
batchServer.init(entity, pojoClass);
return batchServer.exportBigExcel(server, queryParams);
}
/**
* 大数据量导出
*
* @param entity
* @param excelParams
* @param server 查询数据的接口
* @param queryParams 查询数据的参数
* @return
*/
public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,
IExcelExportServer server, Object queryParams) {
ExcelBatchExportService batchServer = new ExcelBatchExportService();
batchServer.init(entity, excelParams);
return batchServer.exportBigExcel(server, queryParams);
}
/**
* @param entity 表格标题属性
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
*/
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 < USE_SXSSF_LIMIT) {
return new XSSFWorkbook();
} else {
return new SXSSFWorkbook();
}
}
/**
* 根据Map创建对应的Excel
*
* @param entity 表格标题属性
* @param entityList Map对象列表
* @param dataSet Excel对象数据List
*/
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;
}
/**
* 根据Map创建对应的Excel(一个excel 创建多个sheet)
*
* @param list 多个Map key title 对应表格Title key entity 对应表格对应实体 key data
* Collection 数据
* @return
*/
public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
Workbook workbook = getWorkbook(type, 0);
for (Map<String, Object> map : list) {
//ExcelExportService service = new ExcelExportService();
new SubExcelExportService().createSheet(workbook, (ExportParams) map.get("title"),
(Class<?>) map.get("entity"), (Collection<?>) map.get("data"));
}
return workbook;
}
/**
* 导出文件通过模板解析,不推荐这个了,推荐全部通过模板来执行处理
*
* @param params 导出参数类
* @param pojoClass 对应实体
* @param dataSet 实体集合
* @param map 模板集合
* @return
*/
@Deprecated
public static Workbook exportExcel(TemplateExportParams params, Class<?> pojoClass,
Collection<?> dataSet, Map<String, Object> map) {
return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, pojoClass, dataSet,
map);
}
/**
* 导出文件通过模板解析只有模板,没有集合
*
* @param params 导出参数类
* @param map 模板集合
* @return
*/
public static Workbook exportExcel(TemplateExportParams params, Map<String, Object> map) {
return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, null, null, map);
}
/**
* 导出文件通过模板解析只有模板,没有集合
* 每个sheet对应一个map,导出到处,key是sheet的NUM
*
* @param params 导出参数类
* @param map 模板集合
* @return
*/
public static Workbook exportExcel(Map<Integer, Map<String, Object>> map,
TemplateExportParams params) {
return new ExcelExportOfTemplateUtil().createExcelByTemplate(params, map);
}
/**
* 导出文件通过模板解析只有模板,没有集合
* 每个sheet对应一个list,按照数量进行导出排序,key是sheet的NUM
*
* @param params 导出参数类
* @param map 模板集合
* @return
*/
public static Workbook exportExcelClone(Map<Integer, List<Map<String, Object>>> map,
TemplateExportParams params) {
return new ExcelExportOfTemplateUtil().createExcelCloneByTemplate(params, map);
}
}其中的自定义 SubExcelExportService 类：
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;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.util.*;
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());
}
}
}需要注意的是，加入依赖的poi版本。3.几的我忘了，会报错，找不到貌似叫一个CellType的错，忘了截图了。需要更换至 4 以上的版本即可。
附带序号的excel示例图
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
