基于SSM完成EXCEL表格数据导入到MySQL数据库

基于SSM完成EXCEL表格数据导入到MySQL数据库，采用poi实现

jar

poi-3.16.jar

poi-examples-3.16.jar

poi-ooxml-3.16.jar

poi-ooxml-schemas-3.16.jar

commons-fileupload-1.3.3.jar

jsp

<form method="post" enctype="multipart/form-data"action="importexcel">  
  文件： <input type="file" name="file"/>  
         <input type="submit" value="submit">  

     </form>  

controller

//导入excel 

 @RequestMapping("importexcel")
 public String admin_importmembers(HttpServletResponse response,HttpServletRequest request) throws Exception {
  MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;    
     List<List<Object>> listob = null; 
     MultipartFile file = multipartRequest.getFile("file");
    // employeeMapper.impExcel(file);
     System.out.println((POIservice==null)+"--------");
     POIservice.impExcel(file);
     System.out.println("............");
     return "redirect:/emps";
  }

service

//导入excel
 public void impExcel(MultipartFile file) throws Exception;

serviceImpl

package com.mybatis.poi;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.mybatis.Dao.EmployeeMapper;
import com.mybatis.bean.Employee;

@Service
public class EmpServiceImpl implements POIService{
 
 @Autowired
 private EmployeeMapper employeeMapper;
 
 
// @Autowired
// private POIService POIservice;

 //导入excel
 @Override
 public void impExcel(MultipartFile file) throws Exception {
  // TODO Auto-generated method stub
   System.out.println("............");
  List<Employee> impusers = new  ArrayList<>();
  if(file.isEmpty()){ 
            throw new Exception("文件不存在！"); 
        } 
  InputStream in = file.getInputStream(); 
  List<List<Object>> listob = new ImportExcelUtils().getBankListByExcel(in,file.getOriginalFilename()); 
        in.close();
       
        for (int i = 1; i < listob.size(); i++) { 
            List<Object> lo = listob.get(i);  
            Employee employee = new Employee();
            String id = String.valueOf(lo.get(0));
            employee.setId(Integer.parseInt(id));
            employee.setLastName(String.valueOf(lo.get(1)));
            employee.setGender(String.valueOf(lo.get(2)));
            employee.setEmail(String.valueOf(lo.get(3)));
            impusers.add(employee); 
          
        }
        System.out.println("impusersSize:"+impusers.size());
        System.out.println(impusers.toString());
         employeeMapper.impusers(impusers);
 }

 

}

ImportExcelUtils

package com.mybatis.poi;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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;

public class ImportExcelUtils {
 private final static String excel2003L =".xls";    //2003- 版本的excel 
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel 
     
    /**
     * 描述：获取IO流中的数据，组装成List<List<Object>>对象
     * @param in,fileName
     * @return
     * @throws IOException 
     * 47行sheet==null
     */ 
    public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{ 
        List<List<Object>> list = null; 
         
        //创建Excel工作薄 
        Workbook work = this.getWorkbook(in,fileName); 
        if(null == work){ 
            throw new Exception("创建Excel工作薄为空！"); 
        } 
        Sheet sheet = null; 
        Row row = null; 
        Cell cell = null; 
         
        list = new ArrayList<List<Object>>(); 
        //遍历Excel中所有的sheet 
        for (int i = 0; i < work.getNumberOfSheets(); i++) { 
            sheet = work.getSheetAt(i);
            if(sheet.getLastRowNum()==0){continue;} 
              System.out.println(sheet.getLastRowNum());
            //遍历当前sheet中的所有行 
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { 
                row = sheet.getRow(j); 
                if(row==null||row.getFirstCellNum()==j){continue;}
                 
                //遍历所有的列 
                List<Object> li = new ArrayList<Object>(); 
                for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) { 
                    cell = row.getCell(y); 
                    li.add(this.getCellValue(cell)); 
                } 
                list.add(li); 
            } 
        } 

        work.close(); 
        return list; 
    } 
 
    /**
     * 描述：根据文件后缀，自适应上传文件的版本 
     * @param inStr,fileName
     * @return
     * @throws Exception
     */ 
    public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{ 
        Workbook wb = null; 
        String fileType = fileName.substring(fileName.lastIndexOf(".")); 
        if(excel2003L.equals(fileType)){ 
            wb = new HSSFWorkbook(inStr);  //2003- 
        }else if(excel2007U.equals(fileType)){ 
            wb = new XSSFWorkbook(inStr);  //2007+ 
        }else{ 
            throw new Exception("解析的文件格式有误！"); 
        } 
        return wb; 
    }
   
    /**
     * 描述：对表格中数值进行格式化
     * @param cell
     * @return
     * 102行m/d/yy
     * 105行"yyyy/M/dd".equals(cell.getCellStyle().getDataFormatString())
     */ 
    public  Object getCellValue(Cell cell){ 
        Object value = null; 
        DecimalFormat df = new DecimalFormat("0");  //格式化number String字符 
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化 
        DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字 
         
        switch (cell.getCellType()) { 
        case Cell.CELL_TYPE_STRING: 
            value = cell.getRichStringCellValue().getString(); 
            break;
        case Cell.CELL_TYPE_NUMERIC: 
            if("General".equals(cell.getCellStyle().getDataFormatString())){ 
                value = df.format(cell.getNumericCellValue());  
            }else if(DateUtil.isCellDateFormatted(cell)){
             value = sdf.format(cell.getDateCellValue());
            }else{ 
                value = df2.format(cell.getNumericCellValue()); 
            } 
            break; 
        case Cell.CELL_TYPE_BOOLEAN: 
            value = cell.getBooleanCellValue(); 
            break; 
        case Cell.CELL_TYPE_BLANK: 
            value = ""; 
            break; 
        default: 
            break; 
        } 
        return value; 
    } 
}

mapper

public Integer impusers(List<Employee> impusers);

mapper.xml

 <insert id="impusers" parameterType="java.util.List">
  insert into tbl_employee(id,last_name,gender,email)
  values
  <foreach collection="list" item="item" index="index" separator=",">
   (#{item.id},#{item.lastName},#{item.gender},#{item.email})
  </foreach>
 </insert>