导入excel

@Controller
@RequestMapping("/excel")
public class ImportExcelController{
    @Autowired(required=true)
    private ImportService importService;

    //导入excel
    @RequestMapping(value = "/import", method=RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request,HttpServletResponse response){
        Map<String, Object> map = new HashMap<String, Object>();
        String result = importService.readExcelFile(file);
        map.put("message", result);
        return map;
    }

}

package controller;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.web.multipart.MultipartFile;

/**
 *
 * @author hewangtong
 *
 */
public class ReadExcel {
    // 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    // 构造方法
    public ReadExcel() {
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @return
     */
    public List<Map<String, Object>> getExcelInfo(MultipartFile mFile) {
        String fileName = mFile.getOriginalFilename();// 获取文件名
//        List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            return createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb
     * @return
     */
    private List<Map<String, Object>> readExcelValue(Workbook wb) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            // 循环Excel的列
            Map<String, Object> map = new HashMap<String, Object>();
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
                    if (c == 0) {
                        // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String name = String.valueOf(cell.getNumericCellValue());
                            map.put("name", name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));// 名称
                        } else {
                            map.put("name", cell.getStringCellValue());// 名称
                        }
                    } else if (c == 1) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String sex = String.valueOf(cell.getNumericCellValue());
                            map.put("sex",sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1));// 性别
                        } else {
                            map.put("sex",cell.getStringCellValue());// 性别
                        }
                    } else if (c == 2) {
                        if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                            String age = String.valueOf(cell.getNumericCellValue());
                            map.put("age", age.substring(0, age.length() - 2 > 0 ? age.length() - 2 : 1));// 年龄
                        } else {
                            map.put("age", cell.getStringCellValue());// 年龄
                        }
                    }
                }
            }
            // 添加到list
            userList.add(map);
        }
        return userList;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\.(?i)(xlsx)$");
    }

}

package service.serviceImpl;

import controller.ReadExcel;
import dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import service.ImportService;

import java.util.List;
import java.util.Map;

@Service
public class ImportServiceImpl implements ImportService {
    @Autowired(required = true)
    private UserDao userDao;
    @Override
    public String readExcelFile(MultipartFile file) {
        String result = "";
        //创建处理EXCEL的类
        ReadExcel readExcel = new ReadExcel();
        //解析excel，获取上传的事件单
        List<Map<String, Object>> userList = readExcel.getExcelInfo(file);
        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
        for(Map<String, Object> user:userList){
            int ret = userDao.insertUser(user.get("name").toString(), user.get("sex").toString(), Integer.parseInt(user.get("age").toString()));
            if(ret == 0){
                result = "插入数据库失败";
            }
        }
        if(userList != null && !userList.isEmpty()){
            result = "上传成功";
        }else{
            result = "上传失败";
        }
        return result;
    }

}

package service;

import org.springframework.web.multipart.MultipartFile;

public interface ImportService {

    /**
     * 读取excel中的数据,生成list
     */
    String readExcelFile(MultipartFile file);

}

package dao.daoImpl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import dao.UserDao;

@Component
public class UserDaoImpl  implements UserDao {
    @Autowired(required = true)
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insertUser(String name, String sex, int age) {
        String sql = "insert into user(name,sex,age) values('"+ name +"','"+ sex +"',"+age+")";
        int ret = 0;
        try {
            ret = jdbcTemplate.update(sql);
        } catch (DataAccessException e) {
            e.printStackTrace();
        }
        return ret;
    }

}

package dao;

public interface UserDao {
    int insertUser(String name, String sex, int age);
}

下面为最新版，你们看的人参考前面的代码
controller层

//导入excel
@RequestMapping(value = "/import01", method=RequestMethod.POST)
public Result importExcel(@RequestParam(value="file",required = false) MultipartFile file){
    return CommonServiceUtils.callLocalService(file, SERVICE_NAME, "readExcelFile");
}

services层

//excel的导入
    public EiInfo readExcelFile(EiInfo inInfo) {
        try{
            MultipartFile file = (MultipartFile)inInfo.get(CommonConstants.PARAM_KEY);
            String result = "";
            //创建处理EXCEL的类
            ReadExcelUtils readExcel = new ReadExcelUtils();
            //解析excel，获取上传的事件单
            Class className = null;
            try {
                className = Class.forName("com.baosight.bddfms.de.wb.domain.DevopsWb01");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
            List<Map<String, Object>> userList = readExcel.getExcelInfo(file,className);
            //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
            for(Map<String, Object> user:userList){
                //int ret = userDao.insertUser(user.get("name").toString(), user.get("sex").toString(), Integer.parseInt(user.get("age").toString()));
                DevopsWb01 devopsWb01 = new DevopsWb01();
                String id = user.get("id").toString();
                if(!StringUtils.isNotEmpty(id)){
                    inInfo.setStatus(EiConstant.STATUS_FAILURE);
                    inInfo.setMsg("id不能为空");
                    return inInfo;
                }
//                devopsWb01.setRequestNo(user.get("requestNo").toString());
//                devopsWb01.setCardNo(user.get("cardNo").toString());
//                devopsWb01.setCardName("cardName");
//                devopsWb01.setTaskTime(user.get("taskTime").toString());
//                devopsWb01.setTaskEndTime(user.get("taskEndTime").toString());
//                devopsWb01.setStatus(user.get("status").toString());
//                devopsWb01.setMaintainStatus(user.get("maintainStatus").toString());
//                devopsWb01.setChargeMan(user.get("chargeMan").toString());
//                devopsWb01.setExecFirstMan(user.get("execFirstMan").toString());
//                devopsWb01.setExecSecondMan(user.get("execSecondMan").toString());
//                devopsWb01.setExecWorker(user.get("execWorker").toString());
//                devopsWb01.setExecDate(user.get("execDate").toString());
//                devopsWb01.setChargeMan(user.get("checkMan").toString());
//                devopsWb01.setCheckDate(user.get("checkDate").toString());
//                devopsWb01.setDelayReason(user.get("delayReason").toString());
//                devopsWb01.setDelayOperatorr(user.get("delayOperatorr").toString());
//                devopsWb01.setTerminateReason(user.get("terminateReason").toString());
//                devopsWb01.setTerminateOperatorr(user.get("terminateOperatorr").toString());
//                devopsWb01.setRemark(user.get("remark").toString());
//                devopsWb01.setCreateTime(user.get("createTime").toString());
//                devopsWb01.setUpdateTime(user.get("updateTime").toString());
//                devopsWb01.setCreatePerson(user.get("createPerson").toString());
                DevopsWb01 devo = (DevopsWb01)PopulateUtils.mapToEntity(user, devopsWb01);
                devo.setCreateTime(RecUtils.getCurTime());
                devo.setCreatePerson(RecUtils.getCurUserId());
                dao.insert(DevopsWbConstants.DEWB01_INSERT, devo);
            }
            inInfo.addBlock(EiConstant.resultBlock).getRows().clear();
            inInfo.getBlock(EiConstant.resultBlock).addRow("上传成功");
        } catch (PlatException ex) {
            inInfo.setStatus(EiConstant.STATUS_FAILURE);
            inInfo.setMsg(ex.getMessage());
        }
        return inInfo;
    }

工具包

package com.baosight.bddfms.common.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author hewangtong
 *
 */
public class ReadExcelUtils {
    // 总行数
    private int totalRows = 0;
    // 总条数
    private int totalCells = 0;
    // 错误信息接收器
    private String errorMsg;

    // 构造方法
    public ReadExcelUtils() {
    }

    // 获取总行数
    public int getTotalRows() {
        return totalRows;
    }

    // 获取总列数
    public int getTotalCells() {
        return totalCells;
    }

    // 获取错误信息
    public String getErrorInfo() {
        return errorMsg;
    }

    /**
     * 读EXCEL文件，获取信息集合
     *
     * @return
     */
    public List<Map<String, Object>> getExcelInfo(MultipartFile mFile,Class className) {
        String fileName = mFile.getOriginalFilename();// 获取文件名
        try {
            if (!validateExcel(fileName)) {// 验证文件名是否合格
                return null;
            }
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            return createExcel(mFile.getInputStream(), isExcel2003,className);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据excel里面的内容读取客户信息
     *
     * @param is      输入流
     * @param isExcel2003   excel是2003还是2007版本
     * @return
     * @throws IOException
     */
    public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003,Class className) {
        try {
            Workbook wb = null;
            if (isExcel2003) {// 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(is);
            }
            return readExcelValue(wb,className);// 读取Excel里面客户的信息
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取Excel里面客户的信息
     *
     * @param wb
     * @return
     */
    private List<Map<String, Object>> readExcelValue(Workbook wb,Class className) {
        // 得到第一个shell
        Sheet sheet = wb.getSheetAt(0);
        // 得到Excel的行数
        this.totalRows = sheet.getPhysicalNumberOfRows();
        // 得到Excel的列数(前提是有行数)
        if (totalRows > 1 && sheet.getRow(0) != null) {
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }
        List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
        // 循环Excel行数
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            // 循环Excel的列
            Map<String, Object> map = new HashMap<String, Object>();
            for (int c = 0; c < this.totalCells; c++) {
                Cell cell = row.getCell(c);
                if (null != cell) {
//                    Class className = null;
//                    try {
//                        className = Class.forName("com.baosight.bddfms.de.wb.domain.DevopsWb01");
//                    } catch (ClassNotFoundException e) {
//                        e.printStackTrace();
//                    }
                    //获取该类的所有属性
                    Field[] field = className.getDeclaredFields();
                    String[] cellName = new String[field.length];
                    for (int i = 0; i < field.length; i++) {
                        cellName[i]=field[i].getName();
                    }
                    //String[] cellName = {"id","requestNo","cardNo","cardName","taskTime","taskEndTime","status","maintainStatus","chargeMan","execFirstMan","execSecondMan","execWorker","execDate","checkMan","checkDate","delayReason","delayOperatorr","terminateReason","terminateOperatorr","remark","createTime","updateTime","createPerson"};
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        String id = String.valueOf(cell.getNumericCellValue());
                        map.put(cellName[c], id.substring(0, id.length() - 2 > 0 ? id.length() - 2 : 1));// 名称
                    } else {
                        map.put(cellName[c], cell.getStringCellValue());
                    }
                }
            }
            // 添加到list
            userList.add(map);
        }
        return userList;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = "文件名不是excel格式";
            return false;
        }
        return true;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\.(?i)(xls)$");
    }

    // @描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\.(?i)(xlsx)$");
    }

}