java 实现Excel的导入

java 实现Excel的导入"/>

java 实现Excel的导入

文章目录

• 一、将Excel文件转为List
• 二、Controller层
• 三、文件的上传

一、将Excel文件转为List

/*** 对excel表单默认第一个索引名转换成list** @param is 输入流* @return 转换后集合*/public List<T> importExcel(InputStream is) throws Exception {return importExcel(StringUtils.EMPTY, is);}/*** 对excel表单指定表格索引名转换成list** @param sheetName 表格索引名* @param is        输入流* @return 转换后集合*/public List<T> importExcel(String sheetName, InputStream is) throws Exception {this.type = Type.IMPORT;this.wb = WorkbookFactory.create(is);List<T> list = new ArrayList<T>();Sheet sheet = null;if (StringUtils.isNotEmpty(sheetName)) {// 如果指定sheet名,则取指定sheet中的内容.sheet = wb.getSheet(sheetName);} else {// 如果传入的sheet名不存在则默认指向第1个sheet.sheet = wb.getSheetAt(0);}if (sheet == null) {throw new IOException("文件sheet不存在");}int rows = sheet.getPhysicalNumberOfRows();if (rows > 0) {// 定义一个map用于存放excel列的序号和field.Map<String, Integer> cellMap = new HashMap<String, Integer>();// 获取表头Row head = sheet.getRow(0);for (int i = 0; i < head.getPhysicalNumberOfCells(); i++) {Cell cell = head.getCell(i);if (StringUtils.isNotNull(cell)) {String value = this.getCellValue(head, i).toString();cellMap.put(value, i);} else {cellMap.put(null, i);}}// 有数据时才处理 得到类的所有field.Field[] allFields = clazz.getDeclaredFields();// 定义一个map用于存放列的序号和field.Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();for (int col = 0; col < allFields.length; col++) {Field field = allFields[col];Excel attr = field.getAnnotation(Excel.class);if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {// 设置类的私有字段属性可访问.field.setAccessible(true);Integer column = cellMap.get(attr.name());if (column != null) {fieldsMap.put(column, field);}}}for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.Row row = sheet.getRow(i);T entity = null;for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet()) {//Map.Entry是Map声明的一个内部接口，此接口为泛型，定义为Entry<K,V>。// 它表示Map中的一个实体（一个key-value对）。接口中有getKey(),getValue方法。Object val = this.getCellValue(row, entry.getKey());// 如果不存在实例则新建.entity = (entity == null ? clazz.newInstance() : entity);// 从map中得到对应列的field.Field field = fieldsMap.get(entry.getKey());// 取得类型,并根据对象类型设置值.Class<?> fieldType = field.getType();if (String.class == fieldType) {String s = Convert.toStr(val);if (StringUtils.endsWith(s, ".0")) {val = StringUtils.substringBefore(s, ".0");} else {val = Convert.toStr(val);}} else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val))) {val = Convert.toInt(val);} else if (Long.TYPE == fieldType || Long.class == fieldType) {val = Convert.toLong(val);} else if (Double.TYPE == fieldType || Double.class == fieldType) {val = Convert.toDouble(val);} else if (Float.TYPE == fieldType || Float.class == fieldType) {val = Convert.toFloat(val);} else if (BigDecimal.class == fieldType) {val = Convert.toBigDecimal(val);} else if (Date.class == fieldType) {if (val instanceof String) {val = DateUtils.parseDate(val);} else if (val instanceof Double) {val = DateUtil.getJavaDate((Double) val);}} else if (Boolean.TYPE == fieldType || Boolean.class == fieldType) {val = Convert.toBool(val, false);}if (StringUtils.isNotNull(fieldType)) {Excel attr = field.getAnnotation(Excel.class);String propertyName = field.getName();if (StringUtils.isNotEmpty(attr.targetAttr())) {propertyName = field.getName() + "." + attr.targetAttr();} else if (StringUtils.isNotEmpty(attr.readConverterExp())) {val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());} else if (StringUtils.isNotEmpty(attr.dictType())) {
//                            val = reverseDictByExp(Convert.toStr(val), attr.dictType(), attr.separator());}ReflectUtils.invokeSetter(entity, propertyName, val);}}list.add(entity);}}return list;}/*** 获取单元格值** @param row    获取的行* @param column 获取单元格列号* @return 单元格值*/public Object getCellValue(Row row, int column) {if (row == null) {return row;}Object val = "";try {Cell cell = row.getCell(column);if (StringUtils.isNotNull(cell)) {if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {val = cell.getNumericCellValue();if (DateUtil.isCellDateFormatted(cell)) {val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换} else {if ((Double) val % 1 > 0) {val = new BigDecimal(val.toString());} else {val = new DecimalFormat("0").format(val);}}} else if (cell.getCellType() == CellType.STRING) {val = cell.getStringCellValue();} else if (cell.getCellType() == CellType.BOOLEAN) {val = cell.getBooleanCellValue();} else if (cell.getCellType() == CellType.ERROR) {val = cell.getErrorCellValue();}}} catch (Exception e) {return val;}return val;}/*** 反向解析值 男=0,女=1,未知=2** @param propertyValue 参数值* @param converterExp  翻译注解* @param separator     分隔符* @return 解析后值*/public static String reverseByExp(String propertyValue, String converterExp, String separator) {StringBuilder propertyString = new StringBuilder();String[] convertSource = converterExp.split(",");for (String item : convertSource) {String[] itemArray = item.split("=");if (StringUtils.containsAny(separator, propertyValue)) {for (String value : propertyValue.split(separator)) {if (itemArray[1].equals(value)) {propertyString.append(itemArray[0] + separator);break;}}} else {if (itemArray[1].equals(propertyValue)) {return itemArray[0];}}}return StringUtils.stripEnd(propertyString.toString(), separator);}

Convert文件，用于转换值的类型

public class Convert
{/*** 转换为字符串<br>* 如果给定的值为null，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static String toStr(Object value, String defaultValue){if (null == value){return defaultValue;}if (value instanceof String){return (String) value;}return value.toString();}/*** 转换为字符串<br>* 如果给定的值为<code>null</code>，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static String toStr(Object value){return toStr(value, null);}/*** 转换为int<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static Integer toInt(Object value, Integer defaultValue){if (value == null){return defaultValue;}if (value instanceof Integer){return (Integer) value;}if (value instanceof Number){return ((Number) value).intValue();}final String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}try{return Integer.parseInt(valueStr.trim());}catch (Exception e){return defaultValue;}}/*** 转换为int<br>* 如果给定的值为<code>null</code>，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static Integer toInt(Object value){return toInt(value, null);}/*** 转换为long<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static Long toLong(Object value, Long defaultValue){if (value == null){return defaultValue;}if (value instanceof Long){return (Long) value;}if (value instanceof Number){return ((Number) value).longValue();}final String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}try{// 支持科学计数法return new BigDecimal(valueStr.trim()).longValue();}catch (Exception e){return defaultValue;}}/*** 转换为long<br>* 如果给定的值为<code>null</code>，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static Long toLong(Object value){return toLong(value, null);}/*** 转换为double<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static Double toDouble(Object value, Double defaultValue){if (value == null){return defaultValue;}if (value instanceof Double){return (Double) value;}if (value instanceof Number){return ((Number) value).doubleValue();}final String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}try{// 支持科学计数法return new BigDecimal(valueStr.trim()).doubleValue();}catch (Exception e){return defaultValue;}}/*** 转换为double<br>* 如果给定的值为空，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static Double toDouble(Object value){return toDouble(value, null);}/*** 转换为Float<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static Float toFloat(Object value, Float defaultValue){if (value == null){return defaultValue;}if (value instanceof Float){return (Float) value;}if (value instanceof Number){return ((Number) value).floatValue();}final String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}try{return Float.parseFloat(valueStr.trim());}catch (Exception e){return defaultValue;}}/*** 转换为Float<br>* 如果给定的值为空，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static Float toFloat(Object value){return toFloat(value, null);}/*** 转换为BigDecimal<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static BigDecimal toBigDecimal(Object value, BigDecimal defaultValue){if (value == null){return defaultValue;}if (value instanceof BigDecimal){return (BigDecimal) value;}if (value instanceof Long){return new BigDecimal((Long) value);}if (value instanceof Double){return new BigDecimal((Double) value);}if (value instanceof Integer){return new BigDecimal((Integer) value);}final String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}try{return new BigDecimal(valueStr);}catch (Exception e){return defaultValue;}}/*** 转换为BigDecimal<br>* 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static BigDecimal toBigDecimal(Object value){return toBigDecimal(value, null);}/*** 转换为boolean<br>* String支持的值为：true、false、yes、ok、no，1,0 如果给定的值为空，或者转换失败，返回默认值<br>* 转换失败不会报错** @param value 被转换的值* @param defaultValue 转换错误时的默认值* @return 结果*/public static Boolean toBool(Object value, Boolean defaultValue){if (value == null){return defaultValue;}if (value instanceof Boolean){return (Boolean) value;}String valueStr = toStr(value, null);if (StringUtils.isEmpty(valueStr)){return defaultValue;}valueStr = valueStr.trim().toLowerCase();switch (valueStr){case "true":return true;case "false":return false;case "yes":return true;case "ok":return true;case "no":return false;case "1":return true;case "0":return false;default:return defaultValue;}}/*** 转换为boolean<br>* 如果给定的值为空，或者转换失败，返回默认值<code>null</code><br>* 转换失败不会报错** @param value 被转换的值* @return 结果*/public static Boolean toBool(Object value){return toBool(value, null);}}

public class StringUtils extends org.apachemons.lang3.StringUtils {/*** * 判断一个对象是否非空** @param object Object* @return true：非空 false：空*/public static boolean isNotNull(Object object){return !isNull(object);}/*** * 判断一个对象是否为空** @param object Object* @return true：为空 false：非空*/public static boolean isNull(Object object){return object == null;}/*** * 判断一个字符串是否为非空串** @param str String* @return true：非空串 false：空串*/public static boolean isNotEmpty(String str){return !isEmpty(str);}/*** * 判断一个字符串是否为空串** @param str String* @return true：为空 false：非空*/public static boolean isEmpty(String str){return isNull(str) || NULLSTR.equals(str.trim());}}

import org.apachemons.lang3.StringUtils;
import org.apachemons.lang3.Validate;
import org.apache.poi.ss.usermodel.DateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import tech.niuamon.utils.DateUtils;
import tech.niuamon.utils.text.Convert;import java.lang.reflect.*;
import java.util.Date;/*** 反射工具类. 提供调用getter/setter方法, 访问私有变量, 调用私有方法, 获取泛型类型Class, 被AOP过的真实类等工具函数.* */
@SuppressWarnings("rawtypes")
public class ReflectUtils
{private static final String SETTER_PREFIX = "set";private static final String GETTER_PREFIX = "get";private static final String CGLIB_CLASS_SEPARATOR = "$$";private static Logger logger = LoggerFactory.getLogger(ReflectUtils.class);/*** 调用Setter方法, 仅匹配方法名。* 支持多级，如：对象名.对象名.方法*/public static <E> void invokeSetter(Object obj, String propertyName, E value){Object object = obj;String[] names = StringUtils.split(propertyName, ".");for (int i = 0; i < names.length; i++){if (i < names.length - 1){String getterMethodName = GETTER_PREFIX + StringUtils.capitalize(names[i]);object = invokeMethod(object, getterMethodName, new Class[] {}, new Object[] {});}else{String setterMethodName = SETTER_PREFIX + StringUtils.capitalize(names[i]);invokeMethodByName(object, setterMethodName, new Object[] { value });}}}
}

二、Controller层

 @PostMapping("/uploaExcel")public ResultJson uploadExcel(MultipartFile file) throws Exception {// 上传文件路径String filePath = NiuaConfig.getUploadPath();//System.out.println(filePath);// 上传并返回新文件名称//2022/07/27/3015c5d0-8e05-4e02-b1f6-81440f373b56.xlsString fileName = FileUploadUtils.uploadExcel(filePath, file);String pathName=filePath + File.separator + fileName;//把上传的文件转换成输入流FileInputStream fileInputStream = new FileInputStream(new File(pathName));ExcelUtil<User> util = new ExcelUtil<>(User.class);List<User>userList= util.importExcel(fileInputStream);int suc = userService.saveByExcel(userList);if(suc>0){Map<String, String> prams = new HashMap<String, String>();String url = serverConfig.getUrl() + fileName;prams.put("url", url);return ResultJson.ok(prams);}return ResultJson.failure(ResultCode.NOT_UPDATE);}

这里uploadExcel方法与图片上传的FileUploadUtils.upload方法大致一样，稍作修改：

 public static final String uploadExcel(String baseDir, MultipartFile file, String[] allowedExtension)throws FileSizeLimitExceededException, IOException, FileNameLengthLimitExceededException,InvalidExtensionException{int fileNamelength = file.getOriginalFilename().length();if (fileNamelength > FileUploadUtils.DEFAULT_FILE_NAME_LENGTH){throw new FileNameLengthLimitExceededException(FileUploadUtils.DEFAULT_FILE_NAME_LENGTH);}assertAllowed(file, allowedExtension);String fileName = extractFilename(file);File desc = getAbsoluteFile(baseDir, fileName);file.transferTo(desc);//String pathFileName = getPathFileName(baseDir, fileName);return fileName;}

这是原upload，getPathFileName会给文件名前加上资源映射路径

三、文件的上传

文件上传参考：FileUploadUtils.upload