从excel导入数据

从excel导入数据

直接上代码，前端js代码自己写（就是选择excel文件上传）

  @RequestMapping(value = "/uploadInterview")
    @ResponseBody
    public JSONObject uploadInterview(MultipartFile file, String custtype, HttpServletRequest request) throws Exception {
        MoocMUserPojo moocMUserPojoNow = (MoocMUserPojo) request.getSession().getAttribute("user");
        logger.debug(file.getOriginalFilename() + "  " + custtype);
        JSONObject object = new JSONObject();
        object.put("status", -1);
        try {
            //判断文件是否为空
            if (file == null) {
                object.put("status", -1);
                return object;
            }
            String name = file.getOriginalFilename();
            long size = file.getSize();
            if (name == null || ExcelUtil.EMPTY.equals(name) && size == 0) {
                object.put("status", -2);//文件内容异常
                return object;
            }
            //读取Excel数据到List中
            List<ArrayList<String>> list = new ExcelRead().readExcel1(file);
            List<TblMoocInterview> rightStus = new ArrayList<TblMoocInterview>();
            List<StuCheckLogPojo> stuCheckLogBeans = new ArrayList<StuCheckLogPojo>();
            //list中存的就是excel中的数据，可以根据excel中每一列的值转换成你所需要的值（从0开始），如：
            TblMoocInterview tblMoocInterview = null;
            List<TblMoocInterview> listMoocUser = new ArrayList<TblMoocInterview>();

            for (ArrayList<String> arr : list) {
                tblMoocInterview = new TblMoocInterview();
                tblMoocInterview.setPhone(arr.get(0));                      //每一行的第一个单元格--username
                tblMoocInterview.setInterview_method(arr.get(2));             //访谈方式
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
                if(("").equals(arr.get(3))){
                    Date dateTime = new Date();
                    logger.debug(arr.get(3) + "---------------------------"+dateTime);
                    String dt =df.format(dateTime);
                    logger.debug(dt + "---------------------------"+dateTime);
                    tblMoocInterview.setCreate_time(dt+"");
                }else{
                    logger.debug(arr.get(3) + "--------------6666666666666666666-------------");
                    tblMoocInterview.setCreate_time(arr.get(3));        //访谈时间
                }
                tblMoocInterview.setContent(arr.get(4));                      //访谈内容
                tblMoocInterview.setInterviewer_id(Integer.valueOf(moocMUserPojoNow.getUserid()));   //推荐人
                listMoocUser.add(tblMoocInterview);
            }
            int total = 0;
            for (int i = 0; i < listMoocUser.size(); i++) {
                //每一个客户对象
                TblMoocInterview userPojo = listMoocUser.get(i);
                boolean flag = true;
                String str = "";

                /***********手机**************/
                if (null == userPojo.getPhone()) {
                    str += "联系电话不能为空,";
                    flag = false;
                } else if (!Pattern.matches("^(1[3|4|5|7|8][0-9])\\d{8}$", userPojo.getPhone())) {
                    str += "联系电话不正确（11位手机号）,";
                    flag = false;
                }
                List<MoocUserPojo> li =moocUserService.selectByPhone(userPojo.getPhone());
                if(li.size()==0){
                    str +="用户不存在";
                    flag = false;
                }
                if(!DateUtils.isDate(userPojo.getCreate_time())){
                    str +="时间格式不对";
                    flag = false;
                }else if(("").equals(userPojo.getInterview_method())){
                    str +="访谈方式未填";
                    flag = false;
                }else if(("").equals(userPojo.getContent())){
                    str +="访谈内容未填";
                    flag = false;
                }

                if (flag) {
                    rightStus.add(userPojo);
                    total += moocUserService.addInterview(Integer.valueOf(li.get(0).getUserid()),userPojo.getInterviewer_id() ,userPojo.getContent(),userPojo.getInterview_method(),userPojo.getCreate_time());

                } else {
                    StuCheckLogPojo checkLogPojo = new StuCheckLogPojo();
                    //设置账号、提示信息
                    checkLogPojo.setAccount(userPojo.getPhone());
                    checkLogPojo.setMsg(str);
                    stuCheckLogBeans.add(checkLogPojo);
                }

            }
            StuCheckRequestPojo checkRequestPojo = new StuCheckRequestPojo();
            checkRequestPojo.setRcount(rightStus.size() + "");
            checkRequestPojo.setEcount(stuCheckLogBeans.size() + "");
            checkRequestPojo.setErrorBean(stuCheckLogBeans);
            object.put("status", checkRequestPojo);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return object;
    }

读取excel文件数据方法

根据需要选择一种方法即可，也可自行更改方法

package com.cy.common.utils;

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

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

/**
 * 读取Excel
 */
public class ExcelRead {
    public int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    /**
     * read the Excel .xlsx,.xls
     * @param file jsp中的上传文件
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {
        if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
            return null;
        }else{
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            if(!ExcelUtil.EMPTY.equals(postfix)){
                if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
                    return readXls(file);
                }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
                    return readXlsx(file);
                }else{
                    return null;
                }
            }
        }
        return null;
    }
    public List<ArrayList<String>> readExcel1(MultipartFile file) throws IOException {
        if(file==null||ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())){
            return null;
        }else{
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            if(!ExcelUtil.EMPTY.equals(postfix)){
                if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
                    return readXls1(file);
                }else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)){
                    return readXlsx1(file);
                }else{
                    return null;
                }
            }
        }
        return null;
    }
    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     * @throws IOException
     */
    @SuppressWarnings("deprecation")
    public List<ArrayList<String>> readXlsx(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if(xssfSheet == null){
                    continue;
                }
                totalRows = xssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
                        String str = "";
                        rowList = new ArrayList<String>();
                        totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始
//                        for(int c=0;c<=totalCells+1;c++){
                        for(int c=0;c<=6;c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }else {
                                str+=xssfRow.getCell(c);
                            }
                            rowList.add(ExcelUtil.getXValue(cell).trim());
                        }
                        if(str.length()<=0){
                            continue;
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     * @throws IOException
     */
    @SuppressWarnings("deprecation")
    public List<ArrayList<String>> readXlsx1(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if(xssfSheet == null){
                    continue;
                }
                totalRows = xssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for(int rowNum = 2;rowNum <= totalRows-1;rowNum++){
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
                        String str = "";
                        rowList = new ArrayList<String>();
                        totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for(int c=0;c<=4;c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }else {
                                str+=xssfRow.getCell(c);
                            }
                            rowList.add(ExcelUtil.getXValue(cell).trim());
                        }
                        if(str.trim().length()<=0){
                            continue;
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }

    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     */
    public List<ArrayList<String>> readXls(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null){
                    continue;
                }
                totalRows = hssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow!=null){
                        String str = "";
                        rowList = new ArrayList<String>();
                        totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
//                        for(short c=0;c<=totalCells+1;c++){
                        for(short c=0;c<=6;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }else {
                                str+=hssfRow.getCell(c);
                            }
                            rowList.add(ExcelUtil.getHValue(cell).trim());
                        }
                        if(str.length()<=0){
                            continue;
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     */
    public List<ArrayList<String>> readXls1(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            //读取sheet(页)
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null){
                    continue;
                }
                totalRows = hssfSheet.getLastRowNum();
                //读取Row,从第二行开始
                for(int rowNum = 2;rowNum <= totalRows-1;rowNum++){
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow!=null){
                        String str = "";
                        rowList = new ArrayList<String>();
                        totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for(short c=0;c<=4;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }else {
                                str+=hssfRow.getCell(c);
                            }
                            rowList.add(ExcelUtil.getHValue(cell).trim());
                        }
                        if(str.trim().length()<=0){
                            continue;
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}