一.批量导入的错误

出现org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.binding.BindingException: Parameter '__frch_item_0' not found. Available parameters are [param1, list]

1.检查xml层的字段和数据库，实体类的字段是否一样。

2.数据库的<foreach collection="list" item="item" index="index" separator=",">中如果mapper是List则collection=list，如果是数组collection=array

3.mapper接口用Param注解参数则collection为param的值

二.批量导入的xml写法

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false" keyProperty="Id">
  insert into gm_buyerProduct
  (buyerId, productName,
  materialQuality, specifications, totalNumber,
  createTime, updateTime, operator,
  publisher, delFlag, productStatus,
 )
  values
  <foreach collection="list" item="item" index="index" separator=",">
    (#{item.buyerid}, #{item.productname},
    #{item.materialquality}, #{item.specifications}, #{item.totalnumber},
    #{item.createtime}, #{item.updatetime}, #{item.operator},
    #{item.publisher}, #{item.delflag}, #{item.productstatus},
  )
  </foreach>
</insert>

三.Excel表格导入的工具类
package cn.taocai.Hbmui.util;

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

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;
/**
 * 读取Excel
 * @author lp
 *
 */
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;
    }
  
    @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){
                        rowList = new ArrayList<String>();
                        totalCells = xssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for(int c=0;c<=totalCells+1;c++){
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }
                            rowList.add(ExcelUtil.getXValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;

    }
    /**
     * read the Excel 2003-2007 .xls
     * @param file
     * @param
     * @param
     * @return
     * @throws IOException
     */
    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){
                        rowList = new ArrayList<String>();
                        totalCells = hssfRow.getLastCellNum();
                        //读取列，从第一列开始
                        for(short c=0;c<=totalCells+1;c++){
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                continue;
                            }
                            rowList.add(ExcelUtil.getHValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            e.printStackTrace();
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

}
四.批量导入的controller层
@RequestMapping(value = "/buyOffer.json",method = RequestMethod.POST)
@ResponseBody
public Result uploadSellerOffer(@RequestParam(value = "file") MultipartFile file){
    GmUser gmUser = (GmUser)getSession().getAttribute("loginUser");
    if (file == null){
        return  Result.fail();
    }
    String name = file.getOriginalFilename();
    long size = file.getSize();
    if (name == null || ExcelUtil.EMPTY.equals(name) && size == 0){
        return Result.fail();
    }
    try {
        List<ArrayList<String>>  list = new ExcelRead().readExcel(file);

        GmBuyerProduct pro =new GmBuyerProduct();
        List<GmBuyerProduct> listPro = new ArrayList<>();
        for (ArrayList<String> arr :list){
            pro.setCreatetime(MyDateUtil.currentTime());//创建时间
            pro.setUpdatetime(MyDateUtil.currentTime());//修改时间
            pro.setDelflag("1");//删除标志
            pro.setProductname(arr.get(0));//品名
            pro.setSpecifications(arr.get(2));//规格
            pro.setMaterialquality(arr.get(1));//材质