java导入excel数据，2003/2007（xls/xlsx）通用，并且带数据重复性、唯一性、长度、非空、正则校验，并且输出提示。

 

导入excel的请求代码，直接copy的代码，改改就能用了

@RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    @ResponseBody
    public String importExcel(@RequestParam("file") MultipartFile file)
            throws Exception {
        Json result=new Json();
        RestExcelMsg restExcelMsg = new RestExcelMsg();

        try {
            if (file.isEmpty()) {
                result.put("success", false);
                result.put("reason", String.format( "导入excel为空文件"));
                return result.toString();
            }
            String type = ImportExcelUtil.checkFileType(file.getOriginalFilename());
            if ("".equals(type)) {
                result.put("success", false);
                result.put("reason", String.format( "excel格式不正确"));
                return result.toString();
            }

            String sheetId = "machine";
            List<MgMachine> excelList = null;

            excelList = (List<MgMachine>) ImportExcelUtil.parseExcel(sheetId, file.getInputStream(), type, restExcelMsg);
            String msg = restExcelMsg.getMsg();
            if(!"".equals(msg)){
                result.put("success", false);
                result.put("reason", msg);
                return result.toString();
            }else{
                MgMachine obj = null;
                MgMachine mm = null;
                String deptName = "";
                int len = excelList.size();
                for(int i = 0; i < len; i++){
                    mm = new MgMachine();
                    mm.setMachineId(excelList.get(i).getMachineId());
                    obj = machineService.getByParam(mm);
                    if(obj != null && !"".equals(obj.getMachineId())){
                        msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "编号" + "已存在\n";
                        result.put("success", false);
                        result.put("reason", msg);
                        return result.toString();
                    } else if(obj == null){
                        mm = new MgMachine();
                        mm.setIpAddress(excelList.get(i).getIpAddress());
                        obj = machineService.getByParam(mm);
                        if(obj != null && !"".equals(obj.getIpAddress())) {
                            msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + " IP地址" + "已存在\n";
                            result.put("success", false);
                            result.put("reason", msg);
                            return result.toString();
                        }
                    }
                    deptName = departmentService.getName(excelList.get(i).getDeptId());
                    if(deptName == null || "".equals(deptName)){
                        msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "编号" + "不存在\n";
                        result.put("success", false);
                        result.put("reason", msg);
                        return result.toString();
                    }else if(!excelList.get(i).getDeptName().equals(deptName)){
                        msg += "第" + Integer.valueOf(i+2).toString() + " 行 " + "名称和编号不匹配" + "\n";
                        result.put("success", false);
                        result.put("reason", msg);
                        return result.toString();
                    }
                }
            }

            List<MgMachine> list = new ArrayList<MgMachine>();
            if(!CollectionUtils.isEmpty(excelList)){
                for(int i =0; i< excelList.size(); i++){
                    MgMachine obj = excelList.get(i);
                    obj.setCreateBy(requestContext.getUser().getUsername());
                    obj.setOperatorName(requestContext.getUser().getUsername());
                    obj.setCreateOn(date);
                    obj.setId(UUID.randomUUID().toString());
                    list.add(obj);
                }
            }
            machineService.batchSave(list);
            result.put("success", true);
            result.put("reason", "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result.toString();
    }

package com.xxx.util;

import com.xxx.RestExcelMsg;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;


public class ImportExcelUtil {

    public static List parseExcel(String sheetId, InputStream input, String type, RestExcelMsg restExcelMsg) throws Exception {
        // 根据sheetId解析xml，获取Excel模板
        String msg = "";
        Element element = readXML(sheetId);
        String clazzString = element.attributeValue("class");

        // 从哪行开始解析
        int start = Integer.valueOf(element.attributeValue("startRow"));

        // 反射创建对象实例
        Class<? extends Object> clazz = Class.forName(clazzString);

        List<Element> columns = element.elements();

        Object instance = null;
        // 结果集
        List resultList = new ArrayList();
        // 要调用的方法
        Method method = null;
        // 调用Set方法要传递的参数
        Class<?>[] args = null;

        Cell cell = null;
        Workbook wb=null;
        Sheet sheet = null;
       if("xlsx".equals(type)){
           wb=new XSSFWorkbook(input);
           sheet =  wb.getSheetAt(0);

        }else{
            wb=new HSSFWorkbook(input);
           sheet =  wb.getSheetAt(0);
        }
        try
        {
            if(sheet.getLastRowNum() <= 0){

                msg += "导入excel为空文件\n";
                restExcelMsg.setMsg(msg);
                return resultList;
            }

            String cname = "";
            String name = "";
            int columnSize = columns.size();
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Map<String, Map<String, String>> mapList = new HashMap<String, Map<String, String>>();
            Map<String, String> map = new HashMap<>();

            // 获取第一个Sheet页
            for (int i = start; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if(row == null)
                {
                    continue;
                }
                instance = clazz.newInstance();

                StringBuffer buf = new StringBuffer();
                for (int j = 0; j < columnSize; j++) {
                    cname = columns.get(j).attributeValue("cname");
                    name = columns.get(j).attributeValue("name");
                    cell = row.getCell(j);
                    if (cell == null) {
                        if("N".equals(columns.get(j).attributeValue("isnull"))){
                            msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "不能为空\n";
                        }
                        continue;
                    }
                    args = new Class[1];
                    args[0] = getParameterType(columns.get(j).attributeValue("type"));
                    Object value = getCellValue(cell,args[0],dateFormat);

                    //累计字符串内容
                    buf.append(getStringValue(value));

                    //Date情况转成String,其他类型保持原样
                    args[0] = Date.class.equals(args[0]) ? String.class : args[0];


                    if(value !=null && !"".equals(value.toString().trim()) && value.toString().length() < Integer.valueOf(columns.get(j).attributeValue("length").split(",")[0]) || value.toString().length() > Integer.valueOf(columns.get(j).attributeValue("length").split(",")[1])){
                        cname = columns.get(j).attributeValue("cname");
                        msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "字符长度应在" + Integer.valueOf(columns.get(j).attributeValue("length").split(",")[0]) + "到" + Integer.valueOf(columns.get(j).attributeValue("length").split(",")[1]) + "之间";
                    }else if(!"".equals(columns.get(j).attributeValue("reg")) && Pattern.matches(columns.get(j).attributeValue("reg"), String.valueOf(value))){
                        msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "格式不正确";
                    }else{
                        if("Y".equals(columns.get(j).attributeValue("unique"))){
                            if(mapList != null && mapList.containsKey(name) && mapList.get(name).containsKey(value)){
                                msg += "第 " + Integer.valueOf(i+1).toString() + " 行 " + cname + "重复\n";
                            }else{
                                map = new HashMap<>();
                                map.put(value.toString(), "");
                                mapList.put(name, map);
                            }
                        }
                        method = clazz.getDeclaredMethod("set" + name, args);
                        method.invoke(instance, value);

                    }

                }

                //整行为空时丢弃该行数据
                if ("".equals(buf.toString().trim()))
                {
                    continue;
                }
                //加到集合中
                resultList.add(instance);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            if (wb != null) {
                wb.close();
            }
        }
        System.out.println(msg);
        restExcelMsg.setMsg(msg);

        return resultList;
    }

    public static String checkFileType(String filename) {
        if ((filename == null) || (filename.length() == 0)) {
            return "";
        }

        String type = "";
        int dot = filename.lastIndexOf('.');
        if ((dot > -1) && (dot < (filename.length() - 1))) {
            type = filename.substring(dot + 1);
        }
        if (!"xlsx".equalsIgnoreCase(type) && !"xls".equalsIgnoreCase(type)) {
            return "";
        }
        return type;
    }

    @SuppressWarnings("unchecked")
    private static Element readXML(String sheetId) throws DocumentException, FileNotFoundException {
        File file = ResourceUtils.getFile("classpath:excel/Sheet.xml");
        SAXReader reader = new SAXReader();
        Document document = reader.read(file);
        Element root = document.getRootElement();
        Iterator<Element> iterator = root.elementIterator();
        Element element = null;
        while (iterator.hasNext()) {
            element = iterator.next();
            if (sheetId.equals(element.attributeValue("id"))) {
                break;
            }
        }
        return element;
    }

    private static Object getCellValue(Cell cell,Class<? extends Object> clazz,SimpleDateFormat dateFormat) {
        Object obj = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case Cell.CELL_TYPE_ERROR:
                obj = cell.getErrorCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                //用户自定义类型检测
                if(String.class.equals(clazz))
                {
                    DecimalFormat df = new DecimalFormat("#.#########"); // 数字格式，防止长数字成为科学计数
                    obj = df.format(cell.getNumericCellValue());
                }else if(Date.class.equals(clazz))
                {
                    obj = dateFormat.format(cell.getDateCellValue());
                }else if(Double.class.equals(clazz)) {
                    obj = cell.getNumericCellValue();
                }else if(Integer.class.equals(clazz)) {
                    DecimalFormat df = new DecimalFormat("#.#########"); // 数字格式，防止长数字成为科学计数
                    String val = df.format(cell.getNumericCellValue());
                    obj = Integer.parseInt(val);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                String cellValue = cell.getStringCellValue();

                if (Double.class.equals(clazz)) {
                    Double d = Double.valueOf(cellValue);
                    obj = d;
                }else if(Integer.class.equals(clazz)) {
                    obj = Integer.parseInt(cellValue);
                }else {
                    obj=cellValue;
                }
                break;
            default:
                break;
        }

        return obj;
    }
    /**
     * 获取参数类型，默认为String
     *
     * @param parameterType
     * @return
     */
    private static Class<?> getParameterType(String parameterType) {
        switch (parameterType) {
            case "Byte":
                return Byte.class;
            case "Short":
                return Short.class;
            case "Integer":
                return Integer.class;
            case "Long":
                return Long.class;
            case "Float":
                return Float.class;
            case "Double":
                return Double.class;
            case "char":
                return char.class;
            case "String":
                return String.class;
            case "Date":
                return Date.class;
            default:
                return String.class;
        }
    }

    /**
     * 获取字符串内容
     * @param obj
     * @return
     */
    private static String getStringValue(Object obj)
    {
        if (obj != null)
        {
            return String.valueOf(obj);
        }
        return "";
    }



}

public class RestExcelMsg {


    private String msg;

    public RestExcelMsg() {
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<import>
    <!-- type类型:Byte,Short,Integer,Long,Float,Double,char,String,缺省为String -->
    <sheet id="machine" startRow="1" class="com.xxx.MgMachine">
        <column name="DeptId"  type="String" isnull="N" length="2,20" unique="N" reg="" cname="机编号"></column>
        <column name="DeptName"  type="String" isnull="N" length="2,50" unique="N" reg="" cname="名称"></column>
        <column name="MachineId"  type="String" isnull="N" length="2,20" unique="Y" reg="" cname="编号"></column>
        <column name="IpAddress"  type="String" isnull="N" length="7,15" unique="Y" reg="" cname="IP地址"></column>
        <column name="Description"  type="String" isnull="Y" length="2,200" unique="N" reg="" cname="描述"></column>
        <column name="WorkingDays" type="String" isnull="N" length="2,20" unique="N" reg="" cname="工作日"></column>
        <column name="WorkingHours" type="String" isnull="N" length="2,20" unique="N" reg="" cname="工作时间"></column>
        <column name="ServiceTime" type="String" isnull="N" length="2,20" unique="N" reg="" cname="上传时间"></column>
    </sheet>
</import>

这里需要注意的是com.xxx.MgMachine实体类中的私有属性和Sheet.xml中的column中的name属性必须保持一致性，cname就是属性的名称。

其中type表示数据类型，isnull，length，unique是否唯一，reg是正则表达式，cname就是名称，对于校验excel导入的数据格式有很好的作用，其实很多导入excel最重要的不是导入，而是数据校验，重复性，格式，长度，唯一性，是否为空这些才是最主要的，demo没有单独写，这些代码已经足够了，代码肯定有不完善的地方，大家多多包涵，希望能够帮助到各位，写成更好的代码。

excel的Sheet的路径

pom依赖

        <!--execl解析-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.13</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.13</version>
        </dependency>
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6.1</version>
        </dependency>

打赏二维码，多谢支持