java实现的Excel批量导入

　　导入Maven

1　　　　 <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>2.3.1</version>
        </dependency>

　　本人的Excle表

　　

　　

 

　　定义一个实体类用于接收解析Excle里的数据

package ********.entity;

import java.io.Serializable;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
 * 批量导入的实体类
 * @author 
 *
 */
@Data
@NoArgsConstructor　　　　
@AllArgsConstructor
public class TblFixChange implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    /**手机号**/
    private String preferentialPhone;
    /**车牌号**/
    private String preferentialCarNumber;
    
    /**-- 优惠分组的id --**/
    private String groupingId;

    /**-- 创建时间 --**/
    private String createTime;

    /**-- 修改时间 --**/
    private String modifyTime;


}

 

 

　　Controller

    //导入
    @RequestMapping(value = "/im/activity/upload", method = RequestMethod.POST)
    public Map<String, Object> importExcel(@RequestParam("file") MultipartFile file) throws IOException, Exception{
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            map = groupConfigService.importExcel(file);
            
        } catch (Exception e) {
            map.put("status",-1);
            map.put("data", "导入异常");
        }
        return map;
        
    }

　　serviceImpl

/**
     * 批量导入（批量添加）
     */

　　　　　　private static final String XLS = "xls";
　　　　　　private static final String XLSK = "xlsx";　　//定义全局的常量值

    @SuppressWarnings("resource")
    public Map<String, Object> importExcel(MultipartFile file) throws Exception {
        List<TblFixChange> list = new ArrayList<TblFixChange>();
        Map<String, Object> rsultMap = new HashMap<String, Object>();
      
        
        Workbook workbook = null;
        String fileName = myFile.getOriginalFilename();
        if(fileName.endsWith(XLS)) {
            //2003
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
            } catch (Exception e) {
                e.printStackTrace( );
            }
            
        }else if(fileName.endsWith(XLSK)) {
            try {
                //2007
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (Exception e) {
                e.printStackTrace( );
            }
        }else {
            throw new Exception("文件不是Excel文件");
        }
        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();//指定行数。一共多少+
        if(rows==0) {
            throw new Exception("请填写行数");
        }
        
        for (int i = 1; i < rows+1; i++) {
            //读取左上端单元格
            Row row = sheet.getRow(i);
            //行不为空
            if(row != null) {
                //读取cell
                TblFixChange tblFixChange = new TblFixChange();
                //手机号
                String phone = getCellValue(row.getCell(0));
                tblFixChange.setPreferentialPhone(phone);
                //车牌号
                String catNumber = getCellValue(row.getCell(1));
                tblFixChange.setPreferentialCarNumber(catNumber);
                //组的id
                String groupId = getCellValue(row.getCell(2));
             
              　 tblFixChange.setGroupingId(groupId);

　　　　　　　　　　SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
　　　　　　　　　　String time = sdf.format(new Date());
　　　　　　　　　　tblFixChange.setCreateTime(time);
　　　　　　　　　　tblFixChange.setModifyTime(time);

                list.add(tblFixChange);　　//把实数据放入集合里
            }
        }
        try {
            groupConfigDao.addBatchMembers(list);　　//批量添加 (执行sql语句批量增加)
            rsultMap.put("status", 1);
            rsultMap.put("data", "导入数据成功");
        } catch (Exception e) {
            rsultMap.put("status", -1);
            rsultMap.put("data", "导入数据异常");
        }
        
        return rsultMap;
    }

//获取Cell内容
    private String getCellValue(Cell cell) {
        String value = "";
        if(cell != null) {
            //以下是判断数据的类型
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC://数字
                value = cell.getNumericCellValue() + "";
                if(HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if(date != null) {
                        value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                    }else {
                        value = "";
                    }
                }else {
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case HSSFCell.CELL_TYPE_STRING: //字符串
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN: //boolean
                value = cell.getBooleanCellValue() + "";
                break;
            case HSSFCell.CELL_TYPE_FORMULA: //公式
                value = cell.getCellFormula() + "";
                break;
            case HSSFCell.CELL_TYPE_BLANK: //空值
                value = "";
                break;
            case HSSFCell.CELL_TYPE_ERROR: //故障
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
            }
        }
        return value.trim();
    }
    

 

sql语句的批量增加（本人的id是自增长的）

<insert id="addBatchMembers"  parameterType="java.util.List">
    
        INSERT INTO activity_prefere (
        <trim suffix="" suffixOverrides=",">
             preferential_car_number ,
             grouping_id ,
             create_time ,
             modify_time ,
             preferential_phone 
        </trim>
        ) VALUES 
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.preferentialCarNumber} ,
                #{item.groupingId} ,
                #{item.createTime} ,
                #{item.modifyTime} ,
                #{item.preferentialPhone} 
            )
           </foreach>
    </insert>