package com.paasit.pai.core.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.ReadListener;
import com.paasit.pai.core.bean.SystemMessage;
import com.paasit.pai.core.exception.BizLogicException;
import lombok.extern.slf4j.Slf4j;
import net.logstash.logback.encoder.org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* 描述: [com.paasit.pai.core.util][Excel处理工具类]
*
* @version: 0_1
* @author: 朱安泰
* @date: 2022-07-18 16:59
*
* 基于EasyExcel的工具类,在使用时需要先将excel实体类中的字段使用@ExcelProperty来指定表头
**/
@Slf4j
public class EasyExcelAlibabaUtil {
private static final String TAG = "EasyExcelAlibabaUtil";
/**
* 将excel文件转化成javaList集合，
* 需要在传入的class的字段中加入EasyExcel的专用注解来指定表名
* @param excelFile 前端传入的文件
* @param clazz 一个Class对象
* @param <T>
* @return 可以直接转成传入的Class的List集合
*/
public static <T> List<T> excelToList(MultipartFile excelFile, Class<T> clazz) {
List<T> objects = null;
try {
InputStream inputStream = excelFile.getInputStream();
objects = EasyExcel.read(inputStream).head(clazz).sheet().doReadSync();
} catch (Exception e) {
log.error("excelToList-exception", e);
} finally {
return objects;
}
}
/**
* 获取excel表头
* @param excelFile
* @return
*/
public static List<String> getColumnNameList(MultipartFile excelFile) throws IOException {
List<String> columnNameList = new ArrayList<>();
LinkedHashMap<Integer,String> columnNames = (LinkedHashMap<Integer, String>) EasyExcel.read(excelFile.getInputStream()).sheet().headRowNumber(0).doReadSync().get(0);
for (Map.Entry<Integer, String> entry : columnNames.entrySet()) {
Integer key = entry.getKey();
columnNameList.add(columnNames.get(key));
}
log.debug("{}读取到的表头:{}",TAG,columnNameList);
return columnNameList;
}
/**
* 检查指定列是否存在
* @param excelFile
* @param columnNames
* @return
*/
public static void checkExcelColumn(MultipartFile excelFile,List<String> columnNames,StringBuffer errorMsg) throws IOException {
log.debug("{}开始检查表头内容是否正确,指定表头{}",TAG,columnNames);
List<String> columnNameList = getColumnNameList(excelFile);
for (String columnName : columnNames) {
if (!columnNameList.contains(columnName)){
errorMsg.append(String.format("<br>在文件中没有找到[%s]列,请检查!",columnName));
}
}
}
/**
* 校验字符串是否仅为字母或数字
* @param str
* @return
*/
public static boolean isLetterDigit(String str) { String regex = "^[a-z0-9A-Z]+$"; return str.matches(regex);}
/**
* 校验excel中是否存在重复数据
* @param list 校验对象需重写 hashCode与equals方法
* @return
*/
public static void isContainsDuplicate(List list,StringBuffer errorMsg){
for (int i = 0; i < list.size(); i++) {
Object ti = list.get(i);
for (int j = i+1; j < list.size(); j++) {
Object tj = list.get(j);
if(ti.equals(tj)){
errorMsg.append(String.format("<br>第[%s]行与第[%s]行数据内容相同,请检查!",i+1,j+1));
}
}
}
}
/**
* 读取excel，这个相当于一个高性能模式。
* 比上面的方法多了一个readListener接口， 需要传一个实现这个接口的实现类
* 其中invoke方法是每读取一行都会调用， 所以可以读取一行处理一行， 性能比较高。
* @param excelFile
* @param clazz
* @param readListener
*/
public static void readExcelHighPerformance(MultipartFile excelFile, Class<T> clazz, ReadListener readListener) {
try {
InputStream inputStream = excelFile.getInputStream();
EasyExcel.read(inputStream, clazz, readListener).sheet().doRead();
} catch (Exception e) {
log.error("excelToList-readExcelHighPerformance", e);
}
}
/**
* 下载excel
* @param response web项目中response对象
* @param clazz Class对象， 需要读取这个Class中的字段注解的名称， 用于生成表头
* @param list java的List集合，需要与传入的Class匹配
* @param excelName 需要生成的excel名称
*/
public static void download(HttpServletResponse response, Class<?> clazz, List<?> list, String excelName) {
try {
ServletOutputStream outputStream = response.getOutputStream();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
ByteArrayOutputStream baos = new ByteArrayOutputStream();
EasyExcel.write(baos, clazz).sheet("sheet1").doWrite(list);
response.setHeader("Content-Length", String.valueOf(baos.size()));
outputStream.write(baos.toByteArray());
response.flushBuffer();
} catch(Exception e){
log.error("excelToList-download", e);
}
}
/**
* 文件类型检验
* @param file
* @throws BizLogicException
*/
public static void checkFile(MultipartFile file) throws BizLogicException {
// 判断文件是否存在
if (null == file) {
throw new BizLogicException(new SystemMessage("FileNotExist","文件不存在"));
}
// 获得文件名
String fileName = file.getOriginalFilename();
// 判断文件是否是excel文件
if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
throw new BizLogicException(new SystemMessage("FileTypeError","文件类型错误"));
}
}
/**
* 属性规则校验 (需要在对象上使用 Validator 注解)
* @param list
* @return
*/
public static void checkObjectParam(List list,StringBuffer errorMsg) {
for (int i = 0; i < list.size(); i++) {
String s = ValidatorUtils.validateEntity(list.get(i));
if (StringUtils.isNotBlank(s)){
errorMsg.append(String.format("<br>第[%s]行,%s",i+1,s));
}
}
}
}
package com.paasit.pai.core.util;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.util.Iterator;
import java.util.Set;
/**
* 描述: [com.paasit.pai.core.util][对象属性校验工具类]
*
* @version: 0_1
* @author: 朱安泰
* @date: 2022-07-19 13:19
**/
public class ValidatorUtils {
private ValidatorUtils() { }
private static Validator validator;
static {
validator = Validation.buildDefaultValidatorFactory().getValidator();
}
/**
* 校验对象
* @param object 待校验对象
* @param groups 待校验的组
*/
public static String validateEntity(Object object, Class<?>... groups) {
StringBuilder msg = new StringBuilder();
Set<ConstraintViolation<Object>> constraintViolations = validator.validate(object, groups);
if (!constraintViolations.isEmpty()) {
Iterator<ConstraintViolation<Object>> iterator = constraintViolations.iterator();
while (iterator.hasNext()) {
ConstraintViolation<Object> constraint = iterator.next();
msg.append(constraint.getMessage());
}
}
return msg.toString();
}
}
