com.gitee.beiding.template_excel.Extractor Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of template-excel Show documentation
Show all versions of template-excel Show documentation
使用模板快速提取excel文件中的数据为数据实体,或者使用模板将数据实体渲染成excel
package com.gitee.beiding.template_excel;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.IOException;
import java.io.InputStream;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
//提取器
public class Extractor {
static {
ZipSecureFile.setMinInflateRatio(-1d);
}
//--------------------------------Sheet页提取--------------------------------
public static ExtractResult extract(XSSFSheet template, XSSFSheet data) {
return extract(template, data, Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(XSSFSheet template, XSSFSheet data, Map> entityMapping) {
return extract(template, data, entityMapping, ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(XSSFSheet template, XSSFSheet data, ColNumberMatchingMode colModel) {
return extract(template, data, Collections.emptyMap(), colModel);
}
/**
* 按照给定的提取模式进行提取
*
* @param template 模板sheet页
* @param data 数据sheet页
* @param entityMapping 映射实体
* @param colModel 提取的列合并模式
* @return 提取的结果
*/
public static ExtractResult extract(XSSFSheet template, XSSFSheet data, Map> entityMapping, ColNumberMatchingMode colModel) {
DoubleActingIndex doubleActingIndex = new DoubleActingIndex();
Map> colValueMap = new HashMap<>();
TemplateSheetHolder templateSheetHolder = new TemplateSheetHolder(template, colValueMap, colModel);
DataSheetHolder dataSheetHolder = new DataSheetHolder(data, templateSheetHolder);
doubleActingIndex.setLeft(templateSheetHolder);
doubleActingIndex.setRight(dataSheetHolder);
while (doubleActingIndex.next()) {
}
ValueHandler valueHandler = new ValueHandler();
valueHandler.setEntityMapping(entityMapping);
Map handle = valueHandler.handle(colValueMap);
//js回收
Js.recycle();
return new ExtractResult(handle);
}
//--------------------------------输入流提取--------------------------------
public static ExtractResult extract(Map templateMap, InputStream data) throws IOException {
return extract(templateMap, PoiUtils.read(data), Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(Map templateMap, InputStream data, Map> entityMapping) throws IOException {
return extract(templateMap, PoiUtils.read(data), entityMapping, ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(Map templateMap, InputStream data, ColNumberMatchingMode colModel) throws IOException {
return extract(templateMap, PoiUtils.read(data), Collections.emptyMap(), colModel);
}
/**
* 读取多个sheet页中的数据
*
* @param templateMap 模板map
* @param data 数据文件
* @param entityMapping 映射实体
* @param colModel 匹配模式
* @return 提取的结果
* @throws IOException 读写时可能发生io异常
*/
public static ExtractResult extract(Map templateMap, InputStream data, Map> entityMapping, ColNumberMatchingMode colModel) throws IOException {
return extract(templateMap, PoiUtils.read(data), entityMapping, colModel);
}
//----------------------------------------Excel全提-----------------------------------------------
public static ExtractResult extract(Map templateMap, XSSFWorkbook data) {
return extract(templateMap, data, Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(Map templateMap, XSSFWorkbook data, ColNumberMatchingMode colModel) {
return extract(templateMap, data, Collections.emptyMap(), colModel);
}
public static ExtractResult extract(Map templateMap, XSSFWorkbook data, Map> entityMapping) {
return extract(templateMap, data, entityMapping, ColNumberMatchingMode.EQUALS);
}
/**
* 使用模板Map对data进行提取
*
* @param templateMap 模板Map
* @param data 数据
* @param entityMapping 映射实体
* @param colModel 匹配模式
* @return 提取结果
*/
public static ExtractResult extract(Map templateMap, XSSFWorkbook data, Map> entityMapping, ColNumberMatchingMode colModel) {
Map map = new HashMap<>();
for (int i = 0; i < data.getNumberOfSheets(); i++) {
XSSFSheet d = data.getSheetAt(i);
XSSFSheet t = templateMap.get(d.getSheetName());
if (t == null) {
continue;
}
map.putAll(extract(t, d, entityMapping, colModel).getData());
}
return new ExtractResult(map);
}
//-----------------------------------使用一个Sheet页提取数据-----------------------------------
public static ExtractResult extract(XSSFSheet template, InputStream data) throws IOException {
return extract(template, data, Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(XSSFSheet template, InputStream data, ColNumberMatchingMode colModel) throws IOException {
return extract(template, data, Collections.emptyMap(), colModel);
}
public static ExtractResult extract(XSSFSheet template, InputStream data, Map> entityMapping) throws IOException {
return extract(template, data, entityMapping, ColNumberMatchingMode.EQUALS);
}
/**
* 从输入流中读取和template同名的sheet页
*
* @param template 模板
* @param data 数据
* @param entityMapping 实体映射
* @param colModel 匹配模式
* @return 提取的结果
* @throws IOException 读写时可能发生io异常
*/
public static ExtractResult extract(XSSFSheet template, InputStream data, Map> entityMapping, ColNumberMatchingMode colModel) throws IOException {
XSSFWorkbook read = PoiUtils.read(data);
return extract(template, read, entityMapping, colModel);
}
/**
* 使用一个模板sheet提取一个sheet页中的数据相同的sheet页
*
* @param template 模板
* @param data 数据
* @param entityMapping 实体映射
* @param colModel 匹配模式
* @return 提取的结果
*/
public static ExtractResult extract(XSSFSheet template, XSSFWorkbook data, Map> entityMapping, ColNumberMatchingMode colModel) {
Map map = new HashMap<>();
XSSFSheet d = data.getSheet(template.getSheetName());
if (d != null) {
map.putAll(extract(template, d, entityMapping, colModel).getData());
}
return new ExtractResult(map);
}
//------------------------使用输入流作为提取模板进行提取-----------------------------------------
public static ExtractResult extract(InputStream template, InputStream data) throws IOException {
return extract(template, data, Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(InputStream template, InputStream data, ColNumberMatchingMode colModel) throws IOException {
return extract(template, data, Collections.emptyMap(), colModel);
}
public static ExtractResult extract(InputStream template, InputStream data, Map> entityMapping) throws IOException {
return extract(template, data, entityMapping, ColNumberMatchingMode.EQUALS);
}
/**
* 从输入流中读取模板和数据Excel和template相同的sheet页
*
* @param template 模板
* @param data 数据
* @param entityMapping 实体映射
* @param colModel 匹配模式
* @return 提取的结果
* @throws IOException 读写时可能发生io异常
*/
public static ExtractResult extract(InputStream template, InputStream data, Map> entityMapping, ColNumberMatchingMode colModel) throws IOException {
XSSFWorkbook tb = PoiUtils.read(template);
XSSFWorkbook db = PoiUtils.read(data);
return extract(tb, db, entityMapping, colModel);
}
//---------------------------使用Excel作为另一个Excel的模板进行提取-----------------------
public static ExtractResult extract(XSSFWorkbook template, XSSFWorkbook data) {
return extract(template, data, Collections.emptyMap(), ColNumberMatchingMode.EQUALS);
}
public static ExtractResult extract(XSSFWorkbook template, XSSFWorkbook data, ColNumberMatchingMode colModel) {
return extract(template, data, Collections.emptyMap(), colModel);
}
public static ExtractResult extract(XSSFWorkbook template, XSSFWorkbook data, Map> entityMapping) {
return extract(template, data, entityMapping, ColNumberMatchingMode.EQUALS);
}
/**
* 从数据Excel中读取和模板Excel中同名的sheet页中的数据
*
* @param template 模板
* @param data 数据
* @param entityMapping 实体映射
* @param colModel 匹配模式
* @return 提取的结果
*/
public static ExtractResult extract(XSSFWorkbook template, XSSFWorkbook data, Map> entityMapping, ColNumberMatchingMode colModel) {
Map sheetMap = new HashMap<>();
for (int i = 0; i < template.getNumberOfSheets(); i++) {
XSSFSheet d = template.getSheetAt(i);
sheetMap.put(d.getSheetName(), d);
}
return extract(sheetMap, data, entityMapping, colModel);
}
private static Map> handleMerge(List cellRangeAddresses) {
Map> map = new HashMap<>();
if (cellRangeAddresses.size() > 0) {
for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
Map absent = map.computeIfAbsent(cellRangeAddress.getFirstRow(), k -> new HashMap<>());
absent.computeIfAbsent(cellRangeAddress.getFirstColumn(), k -> Merge.get(cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() + 1, cellRangeAddress.getLastColumn() - cellRangeAddress.getFirstColumn() + 1));
}
}
return map;
}
private static class TemplateSheetHolder implements DoubleActingIndex.Handle {
XSSFSheet template;
//当前指针
int current;
//指针最大
int max;
Map> margeMap;
Map> colValueMap;
private ColNumberMatchingMode colModel;
TemplateSheetHolder(XSSFSheet template, Map> colValueMap, ColNumberMatchingMode colModel) {
this.colModel = colModel;
this.colValueMap = colValueMap;
this.template = template;
this.current = template.getFirstRowNum();
this.max = template.getLastRowNum();
this.margeMap = handleMerge(template.getMergedRegions());
//初始化的时候跳转一次
next();
}
TemplateRow templateRow;
Map empty = Collections.emptyMap();
Map getMerge(int row) {
Map mergeMap = margeMap.get(row);
if (mergeMap == null) {
mergeMap = empty;
}
return mergeMap;
}
@Override
public boolean next() {
while (true) {
if (current > max) {
return false;
}
templateRow = TemplateRow.compile(template.getRow(current), getMerge(current), colValueMap, colModel);
current++;
if (templateRow != null) {//空白行不能作为模板行,没有任何意义
return true;
}
}
}
@Override
public boolean shouldChange() {//总是跳转到另一行中
return true;
}
@Override
public void afterChange() {
}
@Override
public void afterChangeTo() {
}
TemplateRow getTemplateRow() {
return templateRow;
}
}
private static class DataSheetHolder implements DoubleActingIndex.Handle {
XSSFSheet data;
//当前指针
int current;
//指针最大
int max;
Map> margeMap;
XSSFRow row;
Map empty = Collections.emptyMap();
Map getMerge(int row) {
Map mergeMap = margeMap.get(row);
if (mergeMap == null) {
mergeMap = empty;
}
return mergeMap;
}
//可用于获取模板行数据
TemplateSheetHolder templateSheetHolder;
DataSheetHolder(XSSFSheet data, TemplateSheetHolder templateSheetHolder) {
this.templateSheetHolder = templateSheetHolder;
this.data = data;
this.current = data.getFirstRowNum();
this.max = data.getLastRowNum();
margeMap = handleMerge(data.getMergedRegions());
}
@Override
public boolean next() {
while (true) {
if (current > max) {
return false;
}
row = data.getRow(current);
merge = getMerge(current);
current++;
if (row != null) {
return true;
}
}
}
Map merge;
@Override
public boolean shouldChange() {
//TODO 处理跳转逻辑
TemplateRow templateRow = templateSheetHolder.getTemplateRow();
boolean b = templateRow.extract(row, merge);
//表明当前行已经不符合模板行
if (!b) {
//指针后移一下,在变更模板行后重新判断当前行中的数据是否符合模板行
current--;
//改变模板行
return true;
}
return false;
}
@Override
public void afterChange() {
}
@Override
public void afterChangeTo() {
}
}
}