Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
com.github.springbootPlus.excel.parsing.ExcelImport Maven / Gradle / Ivy
package com.github.springbootPlus.excel.parsing;
import com.github.springbootPlus.excel.ExcelDefinitionReader;
import com.github.springbootPlus.excel.config.ExcelDefinition;
import com.github.springbootPlus.excel.config.FieldValue;
import com.github.springbootPlus.excel.exception.ExcelException;
import com.github.springbootPlus.excel.result.ExcelImportResult;
import com.github.springbootPlus.excel.util.ExcelUtil;
import com.github.springbootPlus.excel.util.ReflectUtils;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* Excel导入实现类
*
* @author lisuo
*/
public class ExcelImport extends AbstractExcelResolver {
/**
* Logger for this class
*/
private static final Logger logger = LoggerFactory.getLogger(ExcelImport.class);
public ExcelImport(ExcelDefinitionReader definitionReader) {
super(definitionReader);
}
/**
* 读取Excel信息
*
* @param id 注册的ID
* @param excelStream Excel文件流
* @param sheetIndex Sheet索引位置
* @param multivalidate 是否逐条校验，默认单行出错立即抛出ExcelException，为true时为批量校验,可通过ExcelImportResult.hasErrors,和getErrors获取具体错误信息
* @return
* @throws Exception
*/
public ExcelImportResult readExcel(String id, InputStream excelStream, Integer sheetIndex, boolean multivalidate) throws Exception {
//从注册信息中获取Bean信息
ExcelDefinition excelDefinition = definitionReader.getRegistry().get(id);
if (excelDefinition == null) {
throw new ExcelException("没有找到 [" + id + "] 的配置信息");
}
try {
return doReadExcel(excelDefinition, excelStream, sheetIndex, multivalidate);
} finally {
ExcelContextHolder.removeContext();
}
}
protected ExcelImportResult doReadExcel(ExcelDefinition excelDefinition, InputStream excelStream, Integer sheetIndex, boolean multivalidate) throws Exception {
Workbook workbook = WorkbookFactory.create(excelStream);
ExcelImportResult result = new ExcelImportResult();
int titleIndex = excelDefinition.getTitleIndex();
result.setTitleIndex(titleIndex);
//读取sheet,sheetIndex参数优先级大于ExcelDefinition配置sheetIndex
Sheet sheet = workbook.getSheetAt(sheetIndex == null ? excelDefinition.getSheetIndex() : sheetIndex);
//标题之前的数据处理
List> header = readHeader(excelDefinition, sheet);
result.setHeader(header);
//获取标题
List titles = readTitle(excelDefinition, sheet, titleIndex);
result.setTitle(titles);
//模板是否被篡改过
checkTitle(result.getErrors(), excelDefinition, titles, titleIndex);
//获取Bean
List listBean = readRows(result.getErrors(), excelDefinition, titles, sheet, titleIndex, multivalidate);
result.setListBean(listBean);
//行数据唯一性判断
String uniqueFields = excelDefinition.getUniqueFields();
String uniqueDesc = excelDefinition.getUniqueDesc();
if (StringUtils.isNotBlank(uniqueFields) && result.hasData()) {
String[] uniqueFieldList = StringUtils.split(uniqueFields, ",");
ExcelUtil.uniqueCheck(uniqueFieldList, uniqueDesc, result, titleIndex + 1);
}
return result;
}
/**
* 解析标题之前的内容,如果ExcelDefinition中titleIndex 不是0
*
* @param excelDefinition
* @param sheet
* @return
*/
protected List> readHeader(ExcelDefinition excelDefinition, Sheet sheet) {
List> header = null;
int titleIndex = excelDefinition.getTitleIndex();
if (titleIndex != 0) {
header = new ArrayList>(titleIndex);
for (int i = 0; i < titleIndex; i++) {
Row row = sheet.getRow(i);
short cellNum = row.getLastCellNum();
List item = new ArrayList(cellNum);
for (int j = 0; j < cellNum; j++) {
Cell cell = row.getCell(j);
Object value = getCellValue(cell);
item.add(value);
}
header.add(item);
}
}
return header;
}
/**
* 读取多行
*
* @param excelDefinition
* @param titles
* @param sheet
* @param titleIndex
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
protected List readRows(List errors, ExcelDefinition excelDefinition, List titles, Sheet sheet, int titleIndex, boolean multivalidate) throws Exception {
int rowNum = sheet.getLastRowNum();
//读取数据的总共次数
int totalNum = rowNum - titleIndex;
int startRow = 1;
List listBean = new ArrayList(totalNum);
for (int i = titleIndex + 1; i <= rowNum; i++) {
Row row = sheet.getRow(i);
Object bean = readRow(errors, excelDefinition, row, titles, startRow + i, multivalidate);
listBean.add((T) bean);
}
return listBean;
}
/**
* 读取1行
*
* @param excelDefinition
* @param row
* @param titles
* @param rowNum 第几行
* @return
* @throws Exception
*/
protected Object readRow(List errors, ExcelDefinition excelDefinition, Row row, List titles, int rowNum, boolean multivalidate) throws Exception {
//创建注册时配置的bean类型
Object bean = ReflectUtils.newInstance(excelDefinition.getClazz());
boolean rowValid = true;
// 1.
for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
String title = fieldValue.getTitle().trim();
for (int j = 0; j < titles.size(); j++) {
if (title.equals(titles.get(j))) {
try {
Cell cell = row.getCell(j);
//获取Excel原生value值
Object value = getCellValue(cell);
//校验
validate(fieldValue, value, rowNum);
//非空非空格
if (StringUtils.isNotBlank(ObjectUtils.toString(value))) {
if (value instanceof String) {
//去除前后空格
value = value.toString().trim();
}
value = super.convert(bean, value, fieldValue, Type.IMPORT, rowNum);
ReflectUtils.setProperty(bean, fieldValue.getName(), value);
}
break;
} catch (ExcelException e) {
rowValid = false;
//应用multivalidate
if (multivalidate) {
errors.add(new ExcelError(rowNum, e.getMessage()));
continue;
} else {
throw e;
}
}
}
}
}
// 2. 设置默认值
Map context = ExcelContextHolder.getContext();
for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
//
String defaultValue = fieldValue.getDefaultValue();
// String convName = fieldValue.getCellValueConverterName();
Object val = ReflectUtils.getProperty(bean, fieldValue.getName());
if (StringUtils.isBlank(ObjectUtils.toString(val)) && defaultValue != null) {
Object value = defaultValue;
if (defaultValue.startsWith("${") && defaultValue.endsWith("}")) {
String key = defaultValue.substring(2, defaultValue.length() - 1);
value = (context == null) ? null : context.get(key);
}
if (defaultValue.equals("newDate()")) {
value = new Date();
}
ReflectUtils.setProperty(bean, fieldValue.getName(), value);
}
}
// 3. ROWFILTER自定义数据校验
String rowFilter = excelDefinition.getRowFilter();
if (StringUtils.isNotBlank(rowFilter) && rowValid)
checkRow(rowFilter, bean, RowFilter.Type.IMPORT, rowNum, errors);
return bean;
}
protected List readTitle(ExcelDefinition excelDefinition, Sheet sheet, int titleIndex) {
// 获取Excel标题数据
Row hssfRowTitle = sheet.getRow(titleIndex);
int cellNum = hssfRowTitle.getLastCellNum();
List titles = new ArrayList(cellNum);
// 获取标题数据
for (int i = 0; i < cellNum; i++) {
Cell cell = hssfRowTitle.getCell(i);
Object value = getCellValue(cell);
if (value == null) {
logger.warn("id 为:[" + excelDefinition.getId() + "] " + (i + 1) + "列的标题不能为[ null ]");
value = "";
}
titles.add(value.toString().trim());
}
return titles;
}
/**
* 检查导入模板是否被修改过,一些必填字段列必须出现在导入模板中
*
* @param errors
* @param excelDefinition
* @param titles
* @param titleIndex
*/
void checkTitle(List errors, ExcelDefinition excelDefinition, List titles, int titleIndex) {
for (FieldValue fieldValue : excelDefinition.getFieldValues()) {
if (!fieldValue.isNull()) {
String title = fieldValue.getTitle().trim();
boolean hasTitle = false;
for (int j = 0; j < titles.size(); j++) {
if (title.equals(titles.get(j))) {
hasTitle = true;
break;
}
}
if (!hasTitle)
errors.add(new ExcelError(titleIndex + 1, "列[" + title + "]不存在,请使用正确的导入模板"));
}
}
}
/**
* 数据有效性校验
*
* @param fieldValue
* @param value
* @param rowNum
*/
private void validate(FieldValue fieldValue, Object value, int rowNum) {
if (value == null || StringUtils.isBlank(value.toString())) {
//空校验
if (!fieldValue.isNull()) {
String err = getErrorMsg(fieldValue, "不能为空", rowNum);
throw new ExcelException(err);
}
} else {
//正则校验
String regex = fieldValue.getRegex();
if (StringUtils.isNotBlank(regex)) {
String val = value.toString().trim();
if (!val.matches(regex)) {
String errMsg = fieldValue.getRegexErrMsg() == null ? "格式错误" : fieldValue.getRegexErrMsg();
String err = getErrorMsg(fieldValue, errMsg, rowNum);
throw new ExcelException(err);
}
}
}
}
}
