将近来所做的报表导入导出功能整理一下。这里参考了一些网上的做法，也有同事们的付出，感谢感谢！
一：首先，pom.xml 文件导入poi依赖：
<!-- 报表导入POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>二：excell的导出工具类：
package ***.util;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExportExcelUtil<T> {
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String fileName, String[] headers, Collection<T> dataset,
OutputStream out) {
exportExcel(workbook, sheetNum, fileName, headers, dataset, out,
"yyyy-MM-dd");
}
/**
* excel 07 导出excel调用方法
*/
public void exportExcel07(XSSFWorkbook workbook, int sheetNum,
String fileName, String[] headers, Collection<T> dataset,
OutputStream out) {
exportExcel07(workbook, sheetNum, fileName, headers, dataset, out,
"yyyy-MM-dd");
}
/**
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
private void exportExcel07(XSSFWorkbook workbook, int sheetNum,
String title, String[] headers, Collection<T> dataset,
OutputStream out, String pattern) {
try {
// 生成一个表格
XSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 25);
// 生成一个样式
XSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成一个字体
XSSFFont font = workbook.createFont();
font.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 产生表格标题行
XSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
XSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
XSSFRichTextString text = new XSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
XSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class<? extends Object> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (null == value) {
textValue = null;
} else {
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else {
textValue = value.toString();
}
}
if (textValue != null) {
cell.setCellValue(textValue);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("deprecation")
public void exportExcel(HSSFWorkbook workbook, int sheetNum, String title,
String[] headers, Collection<T> dataset, OutputStream out,
String pattern) {
try {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 25);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
field.setAccessible(true);
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class<? extends Object> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName,
new Class[]{});
Object value = getMethod.invoke(t, new Object[]{});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (null == value) {
textValue = null;
} else {
if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else {
textValue = value.toString();
}
}
if (textValue != null) {
cell.setCellValue(textValue);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
三：excell的导入工具类：
package ***.util;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
/**
* excel读取工具类
*
* @author daochuwenziyao
* @see [相关类/方法]
* @since [产品/模块版本]
*/
public class ImportExeclUtil {
/**
* 总行数
*/
private static int totalRows = 0;
/**
* 总列数
*/
private static int totalCells = 0;
/**
* 错误信息
*/
private static String errorInfo;
/**
* 无参构造方法
*/
public ImportExeclUtil() {
}
public static int getTotalRows() {
return totalRows;
}
public static int getTotalCells() {
return totalCells;
}
public static String getErrorInfo() {
return errorInfo;
}
/**
* 根据流读取Excel文件
*
* @param inputStream
* @param isExcel2003
* @return
* @see [类、类#方法、类#成员]
*/
public List<List<String>> read(InputStream inputStream, boolean isExcel2003)
throws IOException {
List<List<String>> dataLst = null;
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(inputStream);
} else {
wb = new XSSFWorkbook(inputStream);
}
dataLst = readDate(wb);
return dataLst;
}
/**
* 读取数据
*
* @param wb
* @return
* @see [类、类#方法、类#成员]
*/
private List<List<String>> readDate(Workbook wb) {
List<List<String>> dataLst = new ArrayList<List<String>>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (int c = 0; c < getTotalCells(); c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
// 数字
case NUMERIC:
cellValue = cell.getNumericCellValue() + "";
break;
case STRING:
// 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
// Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
// 公式
case FORMULA:
cellValue = cell.getCellFormula() + "";
break;
// 空值
case BLANK:
cellValue = "";
break;
// 故障
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowLst.add(cellValue);
}
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
return dataLst;
}
/**
* 按指定坐标读取实体数据 <按顺序放入带有注解的实体成员变量中>
*
* @param wb 工作簿
* @param t 实体
* @param in 输入流
* @param integers 指定需要解析的坐标
* @return T 相应实体
* @throws IOException
* @throws Exception
* @see [类、类#方法、类#成员]
*/
@SuppressWarnings("unused")
public static <T> T readDateT(Workbook wb, T t, InputStream in,
Integer[]... integers) throws IOException, Exception {
// 获取该工作表中的第一个工作表
Sheet sheet = wb.getSheetAt(0);
// 成员变量的值
Object entityMemberValue = "";
// 所有成员变量
Field[] fields = t.getClass().getDeclaredFields();
// 列开始下标
int startCell = 0;
/** 循环出需要的成员 */
for (int f = 0; f < fields.length; f++) {
fields[f].setAccessible(true);
String fieldName = fields[f].getName();
boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class);
// 有注解
if (fieldHasAnno) {
IsNeeded annotation = fields[f].getAnnotation(IsNeeded.class);
boolean isNeeded = annotation.isNeeded();
// Excel需要赋值的列
if (isNeeded) {
// 获取行和列
int x = integers[startCell][0] - 1;
int y = integers[startCell][1] - 1;
Row row = sheet.getRow(x);
Cell cell = row.getCell(y);
if (row == null) {
continue;
}
// Excel中解析的值
String cellValue = getCellValue(cell);
// 需要赋给成员变量的值
entityMemberValue = getEntityMemberValue(entityMemberValue,
fields, f, cellValue);
// 赋值
PropertyUtils.setProperty(t, fieldName, entityMemberValue);
// 列的下标加1
startCell++;
}
}
}
return t;
}
/**
* 读取列表数据 <按顺序放入带有注解的实体成员变量中>
*
* @param wb 工作簿
* @param t 实体
* @param beginLine 开始行数
* @param totalcut 结束行数减去相应行数
* @return List<T> 实体列表
* @throws Exception
* @see [类、类#方法、类#成员]
*/
@SuppressWarnings("unchecked")
public static <T> List<T> readDateListT(Workbook wb, T t, int beginLine,
int totalcut) throws Exception {
List<T> listt = new ArrayList<T>();
/** 得到第一个shell */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
/** 循环Excel的行 */
for (int r = beginLine - 1; r < totalRows - totalcut; r++) {
Object newInstance = t.getClass().newInstance();
Row row = sheet.getRow(r);
if (row == null) {
continue;
}
// 成员变量的值
Object entityMemberValue = "";
// 所有成员变量
Field[] fields = t.getClass().getDeclaredFields();
// 列开始下标
int startCell = 0;
for (int f = 0; f < fields.length; f++) {
fields[f].setAccessible(true);
String fieldName = fields[f].getName();
boolean fieldHasAnno = fields[f].isAnnotationPresent(IsNeeded.class);
// 有注解
if (fieldHasAnno) {
IsNeeded annotation = fields[f]
.getAnnotation(IsNeeded.class);
boolean isNeeded = annotation.isNeeded();
// Excel需要赋值的列
if (isNeeded) {
Cell cell = row.getCell(startCell);
if(null!=cell){
cell.setCellType(CellType.STRING);
}
String cellValue = getCellValue(cell);
entityMemberValue = getEntityMemberValue(
entityMemberValue, fields, f, cellValue);
// 赋值
PropertyUtils.setProperty(newInstance, fieldName,
entityMemberValue);
// 列的下标加1
startCell++;
}
}
}
listt.add((T) newInstance);
}
return listt;
}
/**
* 根据Excel表格中的数据判断类型得到值
*
* @param cell
* @return
* @see [类、类#方法、类#成员]
*/
private static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case NUMERIC:
if (org.apache.poi.ss.usermodel.DateUtil
.isCellDateFormatted(cell)) {
Date theDate = cell.getDateCellValue();
SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
cellValue = dff.format(theDate);
} else {
// DecimalFormat df = new DecimalFormat("0.000000");
cellValue = cell + "";
}
break;
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA:
cellValue = cell.getCellFormula() + "";
break;
case BLANK:
cellValue = "";
break;
case ERROR:
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
return cellValue;
}
/**
* 根据实体成员变量的类型得到成员变量的值
*
* @param realValue
* @param fields
* @param f
* @param cellValue
* @return
* @see [类、类#方法、类#成员]
*/
private static Object getEntityMemberValue(Object realValue,
Field[] fields, int f, String cellValue) {
String type = fields[f].getType().getName();
switch (type) {
case "char":
case "java.lang.Character":
case "java.lang.String":
realValue = cellValue;
break;
case "java.util.Date":
realValue = StringUtils.isBlank(cellValue) ? null : ExcelVersionUtil.InnerDateUtil.strToDate(cellValue, ExcelVersionUtil.InnerDateUtil.YYYY_MM_DD);
break;
case "java.lang.Integer":
cellValue = new DecimalFormat("0").format(Double
.parseDouble(cellValue));
realValue = StringUtils.isBlank(cellValue) ? null : Integer
.valueOf(cellValue);
break;
case "int":
case "float":
case "double":
realValue = StringUtils.isBlank(cellValue) ? null : new Double(
cellValue);
break;
case "java.lang.Double":
realValue = StringUtils.isBlank(cellValue) ? null : new Double(cellValue);
break;
case "java.lang.Long":
realValue = StringUtils.isBlank(cellValue)?null:Long.valueOf(cellValue);
break;
case "java.lang.Float":
case "java.lang.Short":
case "java.math.BigDecimal":
break;
default:
break;
}
return realValue;
}
/**
* 根据路径或文件名选择Excel版本
*
* @param filePathOrName
* @param in
* @return
* @throws IOException
* @see [类、类#方法、类#成员]
*/
public static Workbook chooseWorkbook(String filePathOrName, InputStream in)
throws IOException {
/** 根据版本选择创建Workbook的方式 */
Workbook wb = null;
boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName);
if (isExcel2003) {
wb = new HSSFWorkbook(in);
} else {
wb = new XSSFWorkbook(in);
}
return wb;
}
static class ExcelVersionUtil {
/**
* 是否是2003的excel，返回true是2003
*
* @param filePath
* @return
* @see [类、类#方法、类#成员]
*/
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 是否是2007的excel，返回true是2007
*
* @param filePath
* @return
* @see [类、类#方法、类#成员]
*/
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
public static class InnerDateUtil {
// ======================日期格式化常量=====================//
public static final String YYYY_MM_DDHHMMSS = "yyyy-MM-dd HH:mm:ss";
public static final String YYYY_MM_DD = "yyyy-MM-dd";
public static final String YYYY_MM = "yyyy-MM";
public static final String YYYY = "yyyy";
public static final String YYYYMMDDHHMMSS = "yyyyMMddHHmmss";
public static final String YYYYMMDD = "yyyyMMdd";
public static final String YYYYMM = "yyyyMM";
public static final String YYYYMMDDHHMMSS_1 = "yyyy/MM/dd HH:mm:ss";
public static final String YYYY_MM_DD_1 = "yyyy/MM/dd";
public static final String YYYY_MM_1 = "yyyy/MM";
/**
* 自定义取值，Date类型转为String类型
*
* @param date 日期
* @param pattern 格式化常量
* @return
* @see [类、类#方法、类#成员]
*/
public static String dateToStr(Date date, String pattern) {
SimpleDateFormat format = null;
if (null == date) {
return null;
}
format = new SimpleDateFormat(pattern, Locale.getDefault());
return format.format(date);
}
/**
* 将字符串转换成Date类型的时间
* <hr>
*
* @param s 日期类型的字符串<br>
* datePattern :YYYY_MM_DD<br>
* @return java.util.Date
*/
public static Date strToDate(String s, String pattern) {
if (s == null) {
return null;
}
Date date = null;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
try {
date = sdf.parse(s);
} catch (ParseException e) {
e.printStackTrace();
}
return date;
}
}
}
}四：注解
package ***.util;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
*
* 是否需要从解析excel赋值
*
* @author daochuwenziyao
* @see [相关类/方法]
* @since [产品/模块版本]
*/
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = { ElementType.FIELD })
public @interface IsNeeded {
/**
* 是否需要从解析excel赋值
*
* @return true:需要 false:不需要
* @see [类、类#方法、类#成员]
*/
boolean isNeeded() default true;
}五：导出实例：
这里只是将controller层写出，流程就清晰了：
/**
* 下载白名单模板信息
*
* @param response
* @throws IOException
*/
@ApiOperation(value = "下载白名单模板信息")
@Permission(level = ResourceLevel.ORGANIZATION)
@GetMapping("/{exportType}/downloadFile")
public void excelExport(HttpServletResponse response, @PathVariable("exportType") String exportType) throws IOException {
OutputStream outputStream = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
String fileName = orderRepository.setFieName(exportType);
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
// String[] headers={"商户ID","商户名称", "设备名称", "设备编码"};
String[] headers = orderRepository.setHeaders(exportType);
// List orderExcelVo = orderRepository.exportOrder(orderRequestDto, exportType);//要导出的数据
//要导出的数据(模板为空数据)
List model = Lists.newArrayList();
HSSFWorkbook workbook = new HSSFWorkbook();
ExportExcelUtil exportExcel = new ExportExcelUtil();
//就是工作簿的内容
Map<String, List> returnMap = new HashMap();
returnMap.put("model", model);
int i = 0;
for (Map.Entry<String, List> entry : returnMap.entrySet()) {
exportExcel.exportExcel(workbook, i, entry.getKey(), headers, entry.getValue(), outputStream);
i++;
}
try {
workbook.write(outputStream);
} catch (Exception e) {
log.error("模板导出异常", e.getMessage());
e.printStackTrace();
} finally {
outputStream.flush();
outputStream.close();
}
}六：导入实例：
----controller层的方法：
@ApiOperation(value = "导入模板信息")
@Permission(level = ResourceLevel.ORGANIZATION)
@PostMapping("/{importType}/uploadFile")
public Object uploadFile(@RequestParam("fileName") MultipartFile multipartFile, @PathVariable("importType") String importType) {
ResponseEntity<?> result = null;
String originalFilename = multipartFile.getOriginalFilename();
try {
if (!whitelistPosRepository.checkExcel("excel", originalFilename)) {
throw new RuntimeException("The uploaded file format is incorrect");
}
result = whitelistPosRepository.importWhiteNames(multipartFile.getInputStream(), originalFilename, importType);
} catch (Exception e) {
log.error("系统导入数据异常,异常信息为：{}", ExceptionUtil.getMessage(e));
return new BaseException(ERROR_SAME_EXITS,e.getMessage());
}
return result;
}/**
* 检查导入文件类型
*
* @param type
* @param originalFilename
* @return
*/
@Override
public boolean checkExcel(String type, String originalFilename) {
boolean isRight = false;
if ("excel".equals(type)) {
String[] fileType = {"xls", "xlsx"};
//获取文件后缀名称
int length = originalFilename.length();
String s = originalFilename.substring(originalFilename.lastIndexOf(".") + 1,length);
for (int i = 0; i < fileType.length; i++) {
if (fileType[i].equals(s.toLowerCase())) {
isRight = true;
}
}
}
return isRight;
}--service层的方法
/**
* 批量导入白名单
*
* @param inputStream
* @param originalFilename
* @param importType
* @return
*/
@Override
public ResponseEntity<?> importWhiteNames(InputStream inputStream, String originalFilename, String importType) throws Exception {
Workbook wb = ImportExeclUtil.chooseWorkbook(originalFilename, inputStream);
WhitelistPos whitelistPos = new WhitelistPos();
WhitelistServer whitelistServer = new WhitelistServer();
List<WhitelistPos> poslist;
List<WhitelistServer> serverlist;
//1.把excle文件中的数据存入list
switch (importType) {
case WalletConstant.ImportType.WHITE_POS:
poslist = ImportExeclUtil.readDateListT(wb, whitelistPos, 2, 0);
if (poslist.size() == 0) {
throw new RuntimeException("上传文件没数据,上传失败!");
}
String checkPosResult = checkPosIsNull(poslist);
if (StringUtils.isNotBlank(checkPosResult)) {
throw new RuntimeException(checkPosResult);
} else {
List<WhitelistPos> whitelistPosList = batchInsertSelective(poslist);
return Results.success(whitelistPosList);
}
case WalletConstant.ImportType.WHITE_SERVER:
serverlist = ImportExeclUtil.readDateListT(wb, whitelistServer, 2, 0);
if (serverlist.size() == 0) {
throw new RuntimeException("上传文件没数据!");
}
String checkServerResult = checkServerIsNull(serverlist);
if (StringUtils.isNotBlank(checkServerResult)) {
throw new RuntimeException(checkServerResult);
} else {
List<WhitelistServer> whitelistServers =
whitelistServerRepository.batchInsertSelective(serverlist);
return Results.success(whitelistServers);
}
default:
break;
}
return Results.success("Failed to import file");
}
检验方法：
/**
* 非空检验
*
* @param readDateListT
* @return
*/
private String checkPosIsNull(List<WhitelistPos> readDateListT) {
Map<String, List<WhitelistPos>> collect = readDateListT.stream().collect(Collectors.groupingBy(WhitelistPos::getMac));
for (Map.Entry<String,List<WhitelistPos>> entry:collect.entrySet()) {
if(entry.getValue().size()>1){
return "设备编码"+entry.getValue().get(0).getMac()+"重复";
}
WhitelistPos newWhitelistPos = new WhitelistPos();
newWhitelistPos.setMac(entry.getValue().get(0).getMac().toUpperCase());
int whitelistPos1 = selectCount(newWhitelistPos);
if( whitelistPos1>0){
throw new BaseException(ERROR_SAME_EXITS,"设备编码"+entry.getValue().get(0).getMac()+"已存在");
}
}
for (int i = 0; i < readDateListT.size(); i++) {
WhitelistPos whitelistPos = readDateListT.get(i);
if (StringUtils.isBlank(whitelistPos.getCustomerNumber())) {
return "第" + (i + 2) + "行的商户ID不能为空";
} else if (StringUtils.isBlank(whitelistPos.getCustomerName())) {
return "第" + (i + 2) + "行的商户名称不能为空";
} else if (StringUtils.isBlank(whitelistPos.getMacName())) {
return "第" + (i + 2) + "行的设备名称不能为空";
} else if (StringUtils.isBlank(whitelistPos.getMac())) {
return "第" + (i + 2) + "行的设备编码不能为空";
}
}
return null;
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
