文件上传

1、导入

@Resource
private RedisClient redisClient;//redis使用缓存

/**
* 导入
*
* @param file
* @param request
* @param response
* @return
*/
@ResponseBody
@RequestMapping("/import")
public Map<String, Object> import(@RequestParam(value = "uploadFile") MultipartFile file, HttpServletRequest request,
HttpServletResponse response) {
// 7、定义返回结果
Map<String, Object> resultMap = new HashMap<>();
//统筹区
String aaa027 = HnybContextHelper.getAaa027();
if (StringUtils.isEmpty(aaa027)) {
resultMap.put("exception", "统筹区获取失败！");
return resultMap;
}
SimpleDateFormat n = new SimpleDateFormat("yyyy");//设置日期格式
Map bae162map = new HashMap();
bae162map.put("0","未交费");
bae162map.put("1","已缴费");
bae162map.put("2","已退费");
Integer successNum = 0; // 校验通过
Integer erroeNum = 0; // 校验不通过
// 3、完整数据校验
List<Map<String, Object>> cacheList = Lists.newArrayList();//记录错误信息
List<DzzxscbdjDto> dzzxscbdjDtoList = Lists.newArrayList();
List<DzzxscbdjDto> succList = Lists.newArrayList();//校验通过信息

String message = "";
try {
// 1、读取导入文件
InputStream inputStream = file.getInputStream();
String fileName = file.getOriginalFilename();
String szyy = jmggywCommonQueryService.queryAa29(Aa29Enum.mztcdd.getCode(), aaa027);
List<Map<String, Object>> mapList = new ArrayList<>();

mapList = ReadExcel.readExcel(inputStream, fileName, new DzzxscbdjDto().getClass(), 2, 31);
String errorColumns = "1"; // 校验错误的字段用于判断哪个字段校验不通过
if (mapList.get(0).get("message") == null) {
Iterator<Map<String, Object>> mapIter = mapList.iterator();
while (mapIter.hasNext()) {
Map<String, Object> dzzxsplcbdj = mapIter.next();
/**
* 过滤出所有字段为空的数据(个别原因造成导入时读取了一些空数据列，筛选条件该行所有的数据列都为空或空字符串)
*/
boolean isNull = true;
for (Map.Entry<String, Object> e : dzzxsplcbdj.entrySet()) {
if (!e.getKey().toString().toLowerCase().equals("yzstatus")
&& !e.getKey().toString().toLowerCase().equals("errorstrs")
&& !e.getKey().toString().toLowerCase().equals("errorcolumns") && null != e.getValue()
&& e.getValue().toString().trim().length() > 0) {
isNull = false;
}
}
if (isNull) {
mapIter.remove();
continue;
}

/**
* 1、校验导入居民证件号码是否重复
*/
String aac147_1 = "";
if (null != dzzxsplcbdj.get("aac147")) {
aac147_1 = dzzxsplcbdj.get("aac147").toString().trim();
}

int num = 0;
// 校验是否存在居民证件号码编号
for (Map<String, Object> map : mapList) {
String aac147_2 = "";
if (null != map.get("aac147")) {
aac147_2 = map.get("aac147").toString().trim();
if (aac147_1.equals(aac147_2)) {
errorColumns += ";aka120";
num++;
}
}
}
if (num > 1) {
// 将重复的数据写入错误信息列表
dzzxsplcbdj.put("errorColumns", errorColumns);
dzzxsplcbdj.put("yzStatus", "不通过");
dzzxsplcbdj.put("errorStrs", "公民身份号码重复！");
}

if (null != dzzxsplcbdj.get("yzStatus")) {
String yzStatus = dzzxsplcbdj.get("yzStatus").toString();

/***
* 2、将初步校验不通的数据写入错误信息列表里
*/
if ("不通过".equals(yzStatus)) { // 初步校验不通过
erroeNum++;
cacheList.add(dzzxsplcbdj);
} else { // 初步校验通过
/**
* 3、公民身份号码正则校验
*/
if (!CommonUtils.isIDNumber(dzzxsplcbdj.get("aac147").toString())) {
erroeNum++;
dzzxsplcbdj.put("yzStatus", "不通过");
dzzxsplcbdj.put("errorStrs", "公民身份号码填写有误!");
cacheList.add(dzzxsplcbdj);
continue;
}

/**
* 判断是否校验通过
*/
if ("不通过".equals(dzzxsplcbdj.get("yzStatus") + "")) {
erroeNum++;
String errorStrs = dzzxsplcbdj.get("errorStrs")+"";
if(errorStrs.contains(",请核实!")){
dzzxsplcbdj.put("errorStrs", errorStrs);
}else{
dzzxsplcbdj.put("errorStrs", errorStrs + "信息填写有误!");
}
cacheList.add(dzzxsplcbdj);
}
}
}
}
// 4、将导入数据中校验通过的，转化为YpjcmlglDto
dzzxscbdjDtoList = FileUtils.listMap4ListBean(mapList, new DzzxscbdjDto().getClass());
for(int i = 0 ; i < dzzxscbdjDtoList.size(); i++){
if(null==dzzxscbdjDtoList.get(i).getErrorStrs()){
succList.add(dzzxscbdjDtoList.get(i));
}
}
successNum = mapList.size() - erroeNum;
message = "本次导入 " + mapList.size() + " 条数据, 其中：校验通过 " + successNum + " 条, 不通过 " + erroeNum
+ " 条.";
System.out.println("message = " + message);
} else {
message = mapList.get(0).get("message").toString();
}
//错误信息存放到redis中
redisClient.setexObject("DZZXS_" + HnybContextHelper.getUserId(), 1800, cacheList);
resultMap.put("message", message);
resultMap.put("errorList", cacheList);
resultMap.put("succList", succList);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
resultMap.put("exception", e.getMessage());
resultMap.put("errorList", cacheList);
resultMap.put("succList", succList);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
resultMap.put("exception", e.getMessage());
resultMap.put("errorList", cacheList);
resultMap.put("succList", succList);
}
return resultMap;
}

2、ReadExcel

import com.wondersgroup.hnsi.common.utils.FileRowProperty;
import com.wondersgroup.hnsi.common.utils.FileRowPropertyBuilder;
import com.wondersgroup.wdls.core.exception.BusinessException;
import com.wondersgroup.wdls.core.suport.file.BaseFileRow;
import com.wondersgroup.wdls.core.util.ClassUtils;
import com.wondersgroup.wdls.core.util.DateUtils;
import com.wondersgroup.wdls.core.util.NumberUtils;
import com.wondersgroup.wdls.core.util.StringUtils;
import com.wondersgroup.wdls.data.dictionary.DicData;
import com.wondersgroup.wdls.data.dictionary.DicUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Time;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ReadExcel {

private static final int VERSION2003 = 2003;

private static final int VERSION2007 = 2007;

public static List<Map<String, Object>> readExcel(InputStream inputStream, String fileName,
Class<? extends BaseFileRow> beanType,
int startRow, int modelCol) throws BusinessException {

// 解析接受解析结果bean的属性
FileRowProperty fileRowProperty;
try {
fileRowProperty = FileRowPropertyBuilder.buildProperty(beanType);
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException("导入文件类定义有误");
}

int version;
String[] fileNameA = StringUtils.splitPreserveAllTokens(fileName, ".");
String fileEx;
if (fileNameA.length < 2) {
throw new BusinessException("暂只支持xls和xlsx格式");
} else {
fileEx = fileNameA[fileNameA.length - 1].toLowerCase();
if ("xls".equals(fileEx)) {
version = VERSION2003;
} else if ("xlsx".equals(fileEx)) {
version = VERSION2007;
} else {
throw new BusinessException("暂只支持xls和xlsx格式");
}
}

Workbook wb;
Sheet sheet;
Row row;
Cell cell;
List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();

String[] columns = fileRowProperty.getColumns();
String[] columnNames = fileRowProperty.getColumnNames();
Boolean[] notNulls = fileRowProperty.getNotNulls();
Boolean[] isSelects = fileRowProperty.getIsSelects();
Class[] dataTypes = fileRowProperty.getDataTypes();
String[] regs = fileRowProperty.getRegs();
String[] regMsgs = fileRowProperty.getRegMsgs();
Boolean[] isSumResults = fileRowProperty.getIsSumResults();
Boolean[] isSums = fileRowProperty.getIsSums();
Boolean[] isJgs = fileRowProperty.getIsJgs();

try {
if (version == VERSION2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
} catch (IOException ex) {
ex.printStackTrace();
try {
inputStream.close();
} catch (Exception ignore) {

}
throw new BusinessException("文件读取失败：IO错误");
}

try {
String errors = "";
String yzStatus = "通过"; // 默认通过
sheet = wb.getSheetAt(0);
int countRow = sheet.getLastRowNum();
if(countRow>10000){
inputStream.close();
throw new BusinessException("导入模板文件存在大量的空白行,请删除后重新导入！");
}
int maxCol = fileRowProperty.getSize() - 1;
//清空所有空行数据
if(countRow>0){
for(int i=startRow-1; i <= countRow; i++){
row = sheet.getRow(i);
if (isRowEmpty(row, maxCol)) {
if(i==countRow){
countRow=countRow-1;
break;
}else{
sheet.shiftRows(i+1, countRow, -1);
countRow=countRow-1;
i--;
continue;
}

}
}
}

// 调用校验模板方法
checkModel(sheet, countRow, columnNames, modelCol);

// 读列表数据
for (int i = startRow-1; i <= countRow; i++) { // 循环行
BaseFileRow colbean = ClassUtils.getInstance(beanType);
if (StringUtils.isNotBlank(columns[0])) {
PropertyUtils.setSimpleProperty(colbean, columns[0], (long) (i + 1));
}
maxCol = fileRowProperty.getSize() - 1;

Map<String, Object> rowMap = new HashMap<>();

row = sheet.getRow(i);

BigDecimal sumResult = BigDecimal.ZERO;// 导入的合计值
BigDecimal result = BigDecimal.ZERO;// 根据isSum标志求和
String sumResultColName = "";// 导入的合计值的列名
String resultColName = "";// isSum标志求和的列名
// 判断是否是空行
if (isRowEmpty(row, maxCol)) {
sheet.shiftRows(i+1, countRow, -1);
countRow=countRow-1;
i--;
continue;
} else {
for (int j = 0; j <= maxCol; j++) { // 循环列
cell = row.getCell(j);

// 判断是列是否必输项
Boolean notNull = notNulls[j + 1];
String name = columns[j + 1];
String columnName = columnNames[j + 1];
String regMsg = regMsgs[j + 1];
Class ptype = dataTypes[j + 1];
String reg = regs[j + 1];

Boolean isSumResult = isSumResults[j + 1];
Boolean isSum = isSums[j + 1];
Boolean isJg = isJgs[j + 1];

if (cell == null) {
rowMap.put(name, null);
if (notNull) {
String error = "列【" + columnName + "】,不能为空。";
errors += error;
yzStatus = "不通过";
}
continue;
}

String text = "";
if (!ptype.isAssignableFrom(Time.class)) {
cell.setCellType(CellType.STRING);
text = cell.getStringCellValue();
}

if (text == null || "".equals(text)) {
if (notNull) {
String error = "列【" + columnName + "】,不能为空。";
errors += error;
yzStatus = "不通过";
}
rowMap.put(name, text);
continue;
} else {
text = text.trim();
rowMap.put(name, text);

if (ptype.isAssignableFrom(Integer.class) || ptype.isAssignableFrom(Long.class)
|| ptype.isAssignableFrom(Short.class)) {
if (!isZhengShu(text)) {
errors += "列【" + columnName + "】,只能为整数。";
yzStatus = "不通过";
}
} else if (ptype.isAssignableFrom(Double.class)
|| ptype.isAssignableFrom(Float.class)
|| ptype.isAssignableFrom(BigDecimal.class)) {
if (!isXiaoShu(text)) {
errors += "列【" + columnName + "】,只能为小数。";
yzStatus = "不通过";
}
} else if (ptype.equals(Time.class)) {
try {
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, Time.valueOf(DateUtils.formatTime(date)));
} catch (Exception e) {
// TODO: handle exception
errors += "列【" + columnName + "】,时间格式错误。";
yzStatus = "不通过";
}

} else if (ptype.equals(Date.class)) {
try {
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, date);
} catch (Exception e) {
// TODO: handle exception
errors += "列【" + columnName + "】,日期格式错误。";
yzStatus = "不通过";
}
} else {

PropertyUtils.setSimpleProperty(colbean, name, text);
}
}
if (isSelects[j + 1]) {
String str = isDic(name, text);
if (null == str) {
errors += "列【" + columnName + "】,选项【" + text + "】不存在。";
yzStatus = "不通过";
}
}
if (isSumResults[j + 1]) {
sumResultColName = columnName;
if (isZhengShu(text) || isXiaoShu(text)) {
sumResult = new BigDecimal(text);
}
}
if (isSums[j + 1]) {
if (isZhengShu(text) || isXiaoShu(text)) {
result = NumberUtils.sum(result, new BigDecimal(text));
}
resultColName = resultColName + "+" + columnName;
}
if (reg != null && !"".equals(reg) && text != null && !"".equals(text)
&& !text.matches(reg)) {
if (regMsg != null && !"".equals(regMsg)) {
errors += "列【" + columnName + "】," + regMsg + "错误}。";
} else {
errors += "列【" + columnName + "】,格式错误{正则:" + reg + "}。";
}
yzStatus = "不通过";
}
}
}
if (NumberUtils.subtract(sumResult, result).compareTo(BigDecimal.ZERO) != 0) {
errors += sumResultColName + "不等于其他金额之和" + "。";
yzStatus = "不通过";
}
rowMap.put("yzStatus", yzStatus);
rowMap.put("errorStrs", errors);
resultList.add(rowMap);
errors = "";
yzStatus = "通过";
}
return resultList;
} catch (Exception ex) {
//BusinessException exs = (BusinessException) ex;
//ex.getCause().
// ex.printStackTrace();
throw new BusinessException("文件读取失败：" + ex.getMessage());
} finally {
try {
inputStream.close();
} catch (Exception ignore) {

}
}
}

/**
* 判断空行，如果是空行返回true，否则返回false
*/
public static boolean isRowEmpty(Row row, int maxCol) {
if(null==row){
return true;
}else{
for (int i = 0; i <= maxCol; i++) {
Cell cell = row.getCell(i);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
}
return true;
}

/**
* 判断是否整数
* @param text
* @return boolean
* @Description 描述
* @author WangJianyu
* @version 版本
* @lastAuthor WangJianyu
* @lastDate 2018年5月5日
* @Rewrite 修改记录
*/
public static boolean isZhengShu(String text) {
Pattern pattern = Pattern.compile("^(0|[1-9][0-9]*|-[1-9][0-9]*)$");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}

/**
* 判断是否是小数
* @param text
* @return boolean
* @Description 描述
* @author WangJianyu
* @version 版本
* @lastAuthor WangJianyu
* @lastDate 2018年5月5日
* @Rewrite 修改记录
*/
public static boolean isXiaoShu(String text) {
Pattern pattern = Pattern.compile("^(\\-|\\+)?\\d+(\\.\\d+)?$*");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}

/**
* 校验是否字典项
* @param name : 列名
* @param text : 列对应的值
* @return boolean
* @Description 描述
* @author WangJianyu
* @version 版本
* @lastAuthor WangJianyu
* @lastDate 2018年5月8日
* @Rewrite 修改记录
*/
public static String isDic(String name, String text) {
List <DicData> dicList = DicUtils.getDicList(name.toUpperCase());
if (null == dicList) {
return null;
}
for (int i = 0; i < dicList.size(); i++) {
DicData dic = dicList.get(i);
String dicText = dic.getText();
String value = dic.getValue();
if (text.equals(dicText)) {
return value;
}
}
return null;
}

/**
* 校验模板是否正确
* @Description 描述
* @author dingyanyan
* @version 版本
* @param sheet: 工作簿
* @param countRow: 总行数
* @param columnNames: 列数组
* @lastDate 2018年5月31日
* @Rewrite 修改记录
*/
public static void checkModel(Sheet sheet, int countRow, String[] columnNames, int modelCol) {
Row hRowHead = sheet.getRow(0);// 根据sheet获取行对象,表头由第一行开始,
// 第一行内容为空,则返回文件内容不正确
if (countRow == 0) {
throw new BusinessException("表中没有数据，请添加数据！");
}
if (null==hRowHead) {
throw new BusinessException("第一行不能为空！");
}
// hSheet.
int column = hRowHead.getLastCellNum();// 得到多少列
if (column != modelCol) {
throw new BusinessException("字段列与模版不一致！");
}
Cell celld = null;
for (short columnIndex = 0; columnIndex < modelCol; columnIndex++) {
celld = hRowHead.getCell(columnIndex);
if (celld.toString() == null && celld.toString() == "") {
// ++columnIndex;
throw new BusinessException("表头第" + (columnIndex + 1) + "列不能为空！");
}
String value = (celld.getStringCellValue() + " ").trim();
if(StringUtils.isNotEmpty(columnNames[columnIndex + 1])){
if (!columnNames[columnIndex + 1].equals(value)) {
throw new BusinessException("表头第" + (columnIndex + 1) + "列应为" + columnNames[columnIndex + 1]
+ "。");
}
}else{
throw new BusinessException("表头第" + (columnIndex + 1) + "列不应为空。");
}
}
}

}

3、FileUtils

import com.wondersgroup.wdls.core.config.ConfigHolder;
import com.wondersgroup.wdls.core.context.ContextHolder;
import com.wondersgroup.wdls.core.context.UserContext;
import com.wondersgroup.wdls.core.exception.BusinessException;
import com.wondersgroup.wdls.core.suport.file.BaseFileRow;
import com.wondersgroup.wdls.core.suport.file.ExportModel;
import com.wondersgroup.wdls.core.util.ClassUtils;
import com.wondersgroup.wdls.core.util.DateUtils;
import com.wondersgroup.wdls.core.util.NumberUtils;
import com.wondersgroup.wdls.core.util.StringUtils;
import com.wondersgroup.wdls.data.dictionary.DicData;
import com.wondersgroup.wdls.data.dictionary.DicUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.Time;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.util.JxlsHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class FileUtils {
private static Logger logger = LoggerFactory.getLogger(FileUtils.class);
private static final int VERSION2003 = 2003;
private static final int VERSION2007 = 2007;
private static final String EXCEL_NAME = "name";
private static final String EXCEL_VERSION = "version";

public FileUtils() {
}

public static List<Map<String, Object>> readExcel(InputStream inputStream, String fileName, Class<? extends BaseFileRow> beanType, int startRow) {
FileRowProperty fileRowProperty;
try {
fileRowProperty = FileRowPropertyBuilder.build(beanType);
} catch (Exception var49) {
var49.printStackTrace();
throw new RuntimeException("导入文件类定义有误");
}

String[] fileNameA = StringUtils.splitPreserveAllTokens(fileName, ".");
if (fileNameA.length < 2) {
throw new RuntimeException("暂只支持xls和xlsx格式");
} else {
String fileEx = fileNameA[fileNameA.length - 1].toLowerCase();
short version;
if ("xls".equals(fileEx)) {
version = 2003;
} else {
if (!"xlsx".equals(fileEx)) {
throw new RuntimeException("暂只支持xls和xlsx格式");
}

version = 2007;
}

List<Map<String, Object>> resultList = new ArrayList();
String[] columns = fileRowProperty.getColumns();
String[] columnNames = fileRowProperty.getColumnNames();
Boolean[] notNulls = fileRowProperty.getNotNulls();
Boolean[] isSelects = fileRowProperty.getIsSelects();
Class[] types = fileRowProperty.getTypes();

Object wb;
try {
if (version == 2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
} catch (IOException var48) {
var48.printStackTrace();

try {
inputStream.close();
} catch (Exception var45) {
}

throw new RuntimeException("文件读取失败：IO错误");
}

if (!validateVersion((Workbook)wb)) {
Map statusMap = new HashMap();
statusMap.put("message", "模板校验不通过,模板已经过期，请下载最新模板。");
resultList.add(statusMap);
return resultList;
} else {
try {
Sheet sheet = ((Workbook)wb).getSheetAt(0);
int countRow = sheet.getLastRowNum();

for(int i = startRow - 1; i <= countRow; ++i) {
BaseFileRow colbean = (BaseFileRow)ClassUtils.getInstance(beanType);
if (StringUtils.isNotBlank(columns[0])) {
PropertyUtils.setSimpleProperty(colbean, columns[0], (long)(i + 1));
}

int maxCol = fileRowProperty.getSize() - 1;
Row row = sheet.getRow(i);
Map<String, Object> rowMap = new HashMap();
String errors = "";
String errorColumns = "1";
String yzStatus = "通过";

for(int j = 0; j <= maxCol; ++j) {
Cell cell = row.getCell(j);
Boolean notNull = notNulls[j + 1];
String name = columns[j + 1];
String columnName = columnNames[j + 1];
Class ptype = types[j + 1];
String text;
if (cell == null) {
rowMap.put(name, (Object)null);
if (notNull) {
errorColumns = errorColumns + ";" + name.toUpperCase();
text = "列【" + columnName + "】,为必输项不能为空。";
errors = errors + text;
yzStatus = "不通过";
}
} else {
text = "";
if (!ptype.isAssignableFrom(Time.class)) {
cell.setCellType(CellType.STRING);
text = cell.getStringCellValue();
}

String str;
if (text != null && !"".equals(text)) {
text = text.trim();
rowMap.put(name, text);
if (!ptype.isAssignableFrom(Integer.class) && !ptype.isAssignableFrom(Long.class) && !ptype.isAssignableFrom(Short.class)) {
if (!ptype.isAssignableFrom(Double.class) && !ptype.isAssignableFrom(Float.class) && !ptype.isAssignableFrom(BigDecimal.class)) {
Date date;
if (ptype.equals(Time.class)) {
try {
date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, Time.valueOf(DateUtils.formatTime(date)));
} catch (Exception var47) {
errorColumns = errorColumns + ";" + name.toUpperCase();
errors = errors + "列【" + columnName + "】,时间格式错误。";
yzStatus = "不通过";
}
} else if (ptype.equals(Date.class)) {
try {
date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, date);
} catch (Exception var46) {
errorColumns = errorColumns + ";" + name.toUpperCase();
errors = errors + "列【" + columnName + "】,日期格式错误。";
yzStatus = "不通过";
}
} else {
PropertyUtils.setSimpleProperty(colbean, name, text);
}
} else if (!isXiaoShu(text)) {
errorColumns = errorColumns + ";" + name.toUpperCase();
errors = errors + "列【" + columnName + "】,只能为小数。";
yzStatus = "不通过";
}
} else if (!isZhengShu(text)) {
errorColumns = errorColumns + ";" + name.toUpperCase();
errors = errors + "列【" + columnName + "】,只能为整数。";
yzStatus = "不通过";
}

if (isSelects[j + 1]) {
str = isDic(name, text);
if (null == str) {
errorColumns = errorColumns + ";" + name.toUpperCase();
errors = errors + "列【" + columnName + "】,选项【" + text + "】不存在。";
yzStatus = "不通过";
}
}
} else {
if (notNull) {
errorColumns = errorColumns + ";" + name.toUpperCase();
str = "列【" + columnName + "】,为必输项不能为空。";
errors = errors + str;
yzStatus = "不通过";
}

rowMap.put(name, text);
}
}
}

rowMap.put("yzStatus", yzStatus);
rowMap.put("errorStrs", errors);
rowMap.put("errorColumns", errorColumns);
resultList.add(rowMap);
}

ArrayList var53 = resultList;
return var53;
} catch (Exception var50) {
var50.printStackTrace();
throw new RuntimeException("文件读取失败：" + var50.getMessage());
} finally {
try {
inputStream.close();
} catch (Exception var44) {
}

}
}
}
}

public static <T> List<T> listMap2ListBean(List<Map<String, Object>> excelList, Class<? extends BaseFileRow> beanType) {
FileRowProperty fileRowProperty;
try {
fileRowProperty = FileRowPropertyBuilder.build(beanType);
} catch (Exception var19) {
var19.printStackTrace();
throw new RuntimeException("导入文件类定义有误");
}

List<T> resultList = new ArrayList();
String[] columns = fileRowProperty.getColumns();
Boolean[] isColumns = fileRowProperty.getIsColumns();
Class[] types = fileRowProperty.getTypes();
Boolean[] isSelects = fileRowProperty.getIsSelects();

try {
int countRow = excelList.size();

for(int i = 0; i < countRow; ++i) {
BaseFileRow colbean = (BaseFileRow)ClassUtils.getInstance(beanType);
if (StringUtils.isNotBlank(columns[0])) {
PropertyUtils.setSimpleProperty(colbean, columns[0], (long)(i + 1));
}

Map<String, Object> rowMap = (Map)excelList.get(i);
if (null == rowMap.get("yzStatus") || !rowMap.get("yzStatus").toString().trim().equals("不通过")) {
int maxCol = fileRowProperty.getSize() - 1;

for(int j = 0; j <= maxCol; ++j) {
Boolean isColumn = isColumns[j + 1];
String name = columns[j + 1];
Class ptype = types[j + 1];
if (isColumn) {
String text = "";
if (null != rowMap.get(name) && rowMap.get(name).toString().trim().length() != 0) {
text = rowMap.get(name).toString().trim();
if (ptype.isAssignableFrom(Integer.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Integer.valueOf(text));
} else if (ptype.isAssignableFrom(Long.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Long.valueOf(text));
} else if (ptype.isAssignableFrom(Double.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Double.valueOf(text));
} else if (ptype.isAssignableFrom(Float.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Float.valueOf(text));
} else if (ptype.equals(Time.class)) {
PropertyUtils.setSimpleProperty(colbean, name, (Time)rowMap.get(name));
} else if (ptype.equals(Date.class)) {
PropertyUtils.setSimpleProperty(colbean, name, (Date)rowMap.get(name));
} else if (ptype.isAssignableFrom(Short.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Short.valueOf(text));
} else if (ptype.isAssignableFrom(BigDecimal.class)) {
PropertyUtils.setSimpleProperty(colbean, name, new BigDecimal(text));
} else {
PropertyUtils.setSimpleProperty(colbean, name, text);
}
} else {
PropertyUtils.setSimpleProperty(colbean, name, (Object)null);
}

if (isSelects[j + 1]) {
String str = isDic(name, text);
PropertyUtils.setSimpleProperty(colbean, name, str);
}
}
}

resultList.add(colbean);
}
}

return resultList;
} catch (Exception var20) {
var20.printStackTrace();
throw new RuntimeException("文件读取失败：" + var20.getMessage());
}
}

public static InputStream exportExcel(List<Map<String, Object>> dataList, ExportModel exportModel) {
try {
UserContext userContext = ContextHolder.getUserContext();
String templatePath = ConfigHolder.getExcelTemplatePath();
if (StringUtils.isBlank(templatePath)) {
templatePath = "/excel/";
}

URL baseUrl = FileUtils.class.getClassLoader().getResource(templatePath);
if (baseUrl == null) {
throw new BusinessException("无效的模板目录");
} else {
String fromTemplateName = StringUtils.join(new String[]{baseUrl.getPath(), exportModel.getTemplate(), ".", exportModel.getFileType()});
String toTemplateName = StringUtils.join(new String[]{baseUrl.getPath(), exportModel.getTemplate() + "_" + userContext.getUserId() + "_TEM", ".", exportModel.getFileType()});
Map beans = new HashMap();
Map report = new HashMap();
List rows = new ArrayList();
rows.addAll(dataList);
report.put("rows", rows);
beans.put("report", report);
InputStream io = new FileInputStream(toTemplateName);
return io;
}
} catch (Exception var11) {
var11.printStackTrace();
logger.error(var11.getMessage(), "导入文件出错");
return null;
}
}

public static void exportExcel(List<Map<String, Object>> dataList, ExportModel exportModel, String tempFileName) {
try {
String templatePath = ConfigHolder.getExcelTemplatePath();
if (StringUtils.isBlank(templatePath)) {
templatePath = "/excel/";
}

URL baseUrl = FileUtils.class.getClassLoader().getResource(templatePath);
if (baseUrl == null) {
throw new BusinessException("无效的模板目录");
}

String basePath = baseUrl.getPath().replaceAll("%20", " ");
String fromTemplateName = StringUtils.join(new String[]{basePath, exportModel.getTemplate(), ".", exportModel.getFileType()});
String toTemplateName = StringUtils.join(new String[]{basePath, tempFileName, ".", exportModel.getFileType()});
Map<String, Object> beans = new HashMap();
beans.put("row", dataList);
Context context = new Context();
if (beans != null) {
Iterator var10 = beans.keySet().iterator();

while(var10.hasNext()) {
String key = (String)var10.next();
context.putVar(key, beans.get(key));
}
}

InputStream is = new FileInputStream(fromTemplateName);
OutputStream os = new FileOutputStream(toTemplateName);
JxlsHelper jxlsHelper = JxlsHelper.getInstance();
Transformer transformer = jxlsHelper.createTransformer(is, os);
JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
Map<String, Object> funcs = new HashMap();
evaluator.getJexlEngine().setFunctions(funcs);
jxlsHelper.processTemplate(context, transformer);
} catch (Exception var16) {
var16.printStackTrace();
logger.error(var16.getMessage(), "导入文件出错");
}

}

public static List<Map<String, Object>> readExcel(InputStream inputStream, String fileName, Class<? extends BaseFileRow> beanType, int startRow, int modelCol) throws BusinessException {
FileRowProperty fileRowProperty;
try {
fileRowProperty = FileRowPropertyBuilder.buildProperty(beanType);
} catch (Exception var63) {
var63.printStackTrace();
throw new BusinessException("导入文件类定义有误");
}

String[] fileNameA = StringUtils.splitPreserveAllTokens(fileName, ".");
if (fileNameA.length < 2) {
throw new BusinessException("暂只支持xls和xlsx格式");
} else {
String fileEx = fileNameA[fileNameA.length - 1].toLowerCase();
short version;
if ("xls".equals(fileEx)) {
version = 2003;
} else {
if (!"xlsx".equals(fileEx)) {
throw new BusinessException("暂只支持xls和xlsx格式");
}

version = 2007;
}

List<Map<String, Object>> resultList = new ArrayList();
String[] columns = fileRowProperty.getColumns();
String[] columnNames = fileRowProperty.getColumnNames();
Boolean[] notNulls = fileRowProperty.getNotNulls();
Boolean[] isSelects = fileRowProperty.getIsSelects();
Class[] dataTypes = fileRowProperty.getDataTypes();
String[] regs = fileRowProperty.getRegs();
String[] regMsgs = fileRowProperty.getRegMsgs();
Boolean[] isSumResults = fileRowProperty.getIsSumResults();
Boolean[] isSums = fileRowProperty.getIsSums();
Boolean[] isJgs = fileRowProperty.getIsJgs();

Object wb;
try {
if (version == 2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
} catch (IOException var62) {
var62.printStackTrace();

try {
inputStream.close();
} catch (Exception var58) {
}

throw new BusinessException("文件读取失败：IO错误");
}

try {
String errors = "";
String yzStatus = "通过";
Sheet sheet = ((Workbook)wb).getSheetAt(0);
int countRow = sheet.getLastRowNum();
int maxCol = fileRowProperty.getSize() - 1;
Row row;
int i;
if (countRow > 0) {
for(i = startRow - 1; i <= countRow; ++i) {
row = sheet.getRow(i);
if (isRowEmpty(row, maxCol)) {
if (i == countRow) {
--countRow;
break;
}

sheet.shiftRows(i + 1, countRow, -1);
--countRow;
--i;
}
}
}

checkModel(sheet, countRow, columnNames, modelCol);

for(i = startRow - 1; i <= countRow; ++i) {
BaseFileRow colbean = (BaseFileRow)ClassUtils.getInstance(beanType);
if (StringUtils.isNotBlank(columns[0])) {
PropertyUtils.setSimpleProperty(colbean, columns[0], (long)(i + 1));
}

maxCol = fileRowProperty.getSize() - 1;
Map<String, Object> rowMap = new HashMap();
row = sheet.getRow(i);
BigDecimal sumResult = BigDecimal.ZERO;
BigDecimal result = BigDecimal.ZERO;
String sumResultColName = "";
String resultColName = "";
if (isRowEmpty(row, maxCol)) {
sheet.shiftRows(i + 1, countRow, -1);
--countRow;
--i;
} else {
for(int j = 0; j <= maxCol; ++j) {
Cell cell = row.getCell(j);
Boolean notNull = notNulls[j + 1];
String name = columns[j + 1];
String columnName = columnNames[j + 1];
String regMsg = regMsgs[j + 1];
Class ptype = dataTypes[j + 1];
String reg = regs[j + 1];
Boolean var10000 = isSumResults[j + 1];
var10000 = isSums[j + 1];
var10000 = isJgs[j + 1];
String text;
if (cell == null) {
rowMap.put(name, (Object)null);
if (notNull) {
text = "列【" + columnName + "】,不能为空。";
errors = errors + text;
yzStatus = "不通过";
}
} else {
text = "";
if (!ptype.isAssignableFrom(Time.class)) {
cell.setCellType(CellType.STRING);
text = cell.getStringCellValue();
}

String str;
if (text != null && !"".equals(text)) {
text = text.trim();
rowMap.put(name, text);
if (!ptype.isAssignableFrom(Integer.class) && !ptype.isAssignableFrom(Long.class) && !ptype.isAssignableFrom(Short.class)) {
if (!ptype.isAssignableFrom(Double.class) && !ptype.isAssignableFrom(Float.class) && !ptype.isAssignableFrom(BigDecimal.class)) {
Date date;
if (ptype.equals(Time.class)) {
try {
date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, Time.valueOf(DateUtils.formatTime(date)));
} catch (Exception var61) {
errors = errors + "列【" + columnName + "】,时间格式错误。";
yzStatus = "不通过";
}
} else if (ptype.equals(Date.class)) {
try {
date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
rowMap.put(name, date);
} catch (Exception var60) {
errors = errors + "列【" + columnName + "】,日期格式错误。";
yzStatus = "不通过";
}
} else {
PropertyUtils.setSimpleProperty(colbean, name, text);
}
} else if (!isXiaoShu(text)) {
errors = errors + "列【" + columnName + "】,只能为小数。";
yzStatus = "不通过";
}
} else if (!isZhengShu(text)) {
errors = errors + "列【" + columnName + "】,只能为整数。";
yzStatus = "不通过";
}

if (isSelects[j + 1]) {
str = isDic(name, text);
if (null == str) {
errors = errors + "列【" + columnName + "】,选项【" + text + "】不存在。";
yzStatus = "不通过";
}
}

if (isSumResults[j + 1]) {
sumResultColName = columnName;
if (isZhengShu(text) || isXiaoShu(text)) {
sumResult = new BigDecimal(text);
}
}

if (isSums[j + 1]) {
if (isZhengShu(text) || isXiaoShu(text)) {
result = NumberUtils.sum(new BigDecimal[]{result, new BigDecimal(text)});
}

resultColName = resultColName + "+" + columnName;
}

if (reg != null && !"".equals(reg) && text != null && !"".equals(text) && !text.matches(reg)) {
if (regMsg != null && !"".equals(regMsg)) {
errors = errors + "列【" + columnName + "】," + regMsg + "错误}。";
} else {
errors = errors + "列【" + columnName + "】,格式错误{正则:" + reg + "}。";
}

yzStatus = "不通过";
}
} else {
if (notNull) {
str = "列【" + columnName + "】,不能为空。";
errors = errors + str;
yzStatus = "不通过";
}

rowMap.put(name, text);
}
}
}

if (NumberUtils.subtract(sumResult, new BigDecimal[]{result}).compareTo(BigDecimal.ZERO) != 0) {
errors = errors + sumResultColName + "不等于其他金额之和。";
yzStatus = "不通过";
}

rowMap.put("yzStatus", yzStatus);
rowMap.put("errorStrs", errors);
resultList.add(rowMap);
errors = "";
yzStatus = "通过";
}
}

ArrayList var66 = resultList;
return var66;
} catch (Exception var64) {
throw new BusinessException("文件读取失败：" + var64.getMessage());
} finally {
try {
inputStream.close();
} catch (Exception var59) {
}

}
}
}

public static <T> List<T> listMap4ListBean(List<Map<String, Object>> excelList, Class<? extends BaseFileRow> beanType) throws BusinessException {
FileRowProperty fileRowProperty;
try {
fileRowProperty = FileRowPropertyBuilder.buildProperty(beanType);
} catch (Exception var18) {
var18.printStackTrace();
throw new BusinessException("导入文件类定义有误");
}

List<T> resultList = new ArrayList();
String[] columns = fileRowProperty.getColumns();
Class[] types = fileRowProperty.getTypes();
Boolean[] isSelects = fileRowProperty.getIsSelects();

try {
int countRow = excelList.size();

for(int i = 0; i < countRow; ++i) {
BaseFileRow colbean = (BaseFileRow)ClassUtils.getInstance(beanType);
if (StringUtils.isNotBlank(columns[0])) {
PropertyUtils.setSimpleProperty(colbean, columns[0], (long)(i + 1));
}

Map<String, Object> rowMap = (Map)excelList.get(i);
Boolean jyjgFlag = true;
if (null != rowMap.get("yzStatus") && rowMap.get("yzStatus").toString().trim().equals("不通过")) {
jyjgFlag = false;
}

int maxCol = fileRowProperty.getSize() - 1;

for(int j = 0; j <= maxCol; ++j) {
String name = columns[j + 1];
Class ptype = types[j + 1];
if (!jyjgFlag) {
PropertyUtils.setSimpleProperty(colbean, "errorStrs", rowMap.get("errorStrs").toString());
}

String text = "";
if (null != rowMap.get(name) && rowMap.get(name).toString().trim().length() != 0) {
text = rowMap.get(name).toString().trim();
if (ptype.isAssignableFrom(Integer.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Integer.valueOf(text));
} else if (ptype.isAssignableFrom(Long.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Long.valueOf(text));
} else if (ptype.isAssignableFrom(Double.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Double.valueOf(text));
} else if (ptype.isAssignableFrom(Float.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Float.valueOf(text));
} else if (ptype.equals(Time.class)) {
PropertyUtils.setSimpleProperty(colbean, name, rowMap.get(name));
} else if (ptype.equals(Date.class)) {
PropertyUtils.setSimpleProperty(colbean, name, rowMap.get(name));
} else if (ptype.isAssignableFrom(Short.class)) {
PropertyUtils.setSimpleProperty(colbean, name, Short.valueOf(text));
} else if (ptype.isAssignableFrom(BigDecimal.class)) {
PropertyUtils.setSimpleProperty(colbean, name, new BigDecimal(text));
} else {
PropertyUtils.setSimpleProperty(colbean, name, text);
}
} else {
PropertyUtils.setSimpleProperty(colbean, name, (Object)null);
}

if (isSelects[j + 1]) {
String str = isDic(name, text);
if (null != str && jyjgFlag) {
PropertyUtils.setSimpleProperty(colbean, name, str);
} else {
PropertyUtils.setSimpleProperty(colbean, name, text);
}
}
}

resultList.add(colbean);
}

return resultList;
} catch (Exception var19) {
var19.printStackTrace();
throw new BusinessException("文件读取失败：" + var19.getMessage());
}
}

public static void checkModel(Sheet sheet, int countRow, String[] columnNames, int modelCol) {
Row hRowHead = sheet.getRow(0);
if (countRow == 0) {
throw new BusinessException("表中没有数据，请添加数据！");
} else if (null == hRowHead) {
throw new BusinessException("第一行不能为空！");
} else {
int column = hRowHead.getLastCellNum();
if (column != modelCol) {
throw new BusinessException("字段列与模版不一致！");
} else {
Cell celld = null;

for(short columnIndex = 0; columnIndex < modelCol; ++columnIndex) {
celld = hRowHead.getCell(columnIndex);
if (celld.toString() == null && celld.toString() == "") {
throw new BusinessException("表头第" + (columnIndex + 1) + "列不能为空！");
}

String value = (celld.getStringCellValue() + " ").trim();
if (!StringUtils.isNotEmpty(columnNames[columnIndex + 1])) {
throw new BusinessException("表头第" + (columnIndex + 1) + "列不应为空。");
}

if (!columnNames[columnIndex + 1].equals(value)) {
throw new BusinessException("表头第" + (columnIndex + 1) + "列应为" + columnNames[columnIndex + 1] + "。");
}
}

}
}
}

public static boolean isRowEmpty(Row row, int maxCol) {
if (null == row) {
return true;
} else {
for(int i = 0; i <= maxCol; ++i) {
Cell cell = row.getCell(i);
if (cell != null && cell.getCellType() != 3) {
return false;
}
}

return true;
}
}

public static boolean isZhengShu(String text) {
Pattern pattern = Pattern.compile("^(0|[1-9][0-9]*|-[1-9][0-9]*)$");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}

public static boolean isXiaoShu(String text) {
Pattern pattern = Pattern.compile("^(\\-|\\+)?\\d+(\\.\\d+)?$*");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}

public static String isDic(String name, String text) {
List<DicData> dicList = DicUtils.getDicList(name.toUpperCase());
if (null == dicList) {
return null;
} else {
for(int i = 0; i < dicList.size(); ++i) {
DicData dic = (DicData)dicList.get(i);
String dicText = dic.getText();
String value = dic.getValue();
if (text.equals(dicText)) {
return value;
}
}

return null;
}
}

public static String DicFromValueToText(String name, String value) {
List<DicData> dicList = DicUtils.getDicList(name.toUpperCase());
if (null == dicList) {
return null;
} else {
for(int i = 0; i < dicList.size(); ++i) {
DicData dic = (DicData)dicList.get(i);
String dicText = dic.getText();
String divValue = dic.getValue();
if (value.equals(divValue)) {
return dicText;
}
}

return null;
}
}

public static void main(String[] args) {
URL baseUrl = FileUtils.class.getClassLoader().getResource("/hnpages/");
System.out.println(baseUrl.getPath());
}

public static boolean validateVersion(Workbook upLoadworkbook) {
Boolean status = false;

try {
Sheet sheet = upLoadworkbook.getSheet("version");
Row row = sheet.getRow(1);
String name = row.getCell(0).toString().trim();
String version = row.getCell(1).toString().trim();
String ename = row.getCell(2).toString();
Map serverExecl = getServerExcelVersion(JxlsUtils.getTemplate(ename));
String serverExeclName = serverExecl.get("name").toString().trim();
String serverExcelVsersion = serverExecl.get("version").toString().trim();
if (!name.equals(serverExeclName) && name != serverExeclName) {
logger.info("版本校验不通过,请检查模板名称");
} else if (!version.equals(serverExcelVsersion) && version != serverExcelVsersion) {
logger.info("版本校验不通过,请检查版本号");
} else {
status = true;
logger.info("版本校验通过");
}
} catch (Exception var10) {
logger.error(var10.getCause().toString());
}

return status;
}

private static Map getServerExcelVersion(File file) {
HashMap map = new HashMap();

try {
Workbook serverWordbook = new HSSFWorkbook(new FileInputStream(file));
Sheet sheet = serverWordbook.getSheet("version");
Row row = sheet.getRow(1);
String name = row.getCell(0).toString();
String version = row.getCell(1).toString();
logger.info("服务器上模板为:" + name + ", 版本号为:" + version);
map.put("name", name);
map.put("version", version);
} catch (Exception var7) {
logger.error(var7.getCause().toString());
}

return map;
}
}

文件上传

原文：https://www.cnblogs.com/xiaoyuer1229/p/13580335.html