junit解析Excel并导入数据库

package com.mg.background.friend.service;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.sql.SQLException;import java.text.DecimalFormat;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.poifs.filesystem.POIFSFileSystem;import org.junit.BeforeClass;import org.junit.Test;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import com.mg.background.friend.dao.IUserInfoDAO;import com.mg.background.friend.entity.UserInfo;public class ReadExcelTest {    private static ApplicationContext ctx;    private static IUserInfoDAO userInfoDAO;    @BeforeClass    public static void setUpBeforeClass() throws Exception {        try {            //加载配置文件,使用spring动态注入依赖            ctx = new ClassPathXmlApplicationContext("classpath*:spring-web-*.xml");            //利用spring得到userInfoDAO实例            userInfoDAO = ctx.getBean(IUserInfoDAO.class);        } catch (Exception e) {            e.printStackTrace();        }    }    @Test    public void test() throws SQLException, IOException, ClassNotFoundException {        File file = new File("C:/Users/Administrator/Desktop/boyss.xls");          POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(file));         // hssfWorkbook为excel文件        HSSFWorkbook hssfWorkbook =  new HSSFWorkbook(poifsFileSystem);         // HSSFSheet 是excel文件中sheet 第一页的索引为0        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);         //得到最后一行的索引        int rowEnd = hssfSheet.getLastRowNum();        next:        for(int i=1;i<=rowEnd;i++)          {              //HSSFRow 为行对象            HSSFRow row = hssfSheet.getRow(i);              if(null == row){                continue;            }              int cellStart = row.getFirstCellNum();              int cellEnd = row.getLastCellNum();              UserInfo userInfo = new UserInfo();            for(int k=cellStart;k<=cellEnd;k++)              {                   //HSSFCell为列对象                HSSFCell cell = row.getCell(k);                  if(null==cell){                    continue;                                   }                //如果当前列的数据类型为string且为空就略过当前列直接读取下一列                if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING && " ".equals(cell.getStringCellValue())){                    continue;                 }                switch (k)                  {                      case 1:                        userInfo.setName(cell.getStringCellValue());                        break;                     case 2:                        userInfo.setGender("B");                        break;                    case 3:                        double height = 0;                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){                            height = cell.getNumericCellValue();                                             }else{                            if(null == cell.getStringCellValue() ||"".equals(cell.getStringCellValue())                            ||cell.getStringCellValue().isEmpty()){                                break;                            }else{                                height = Double.valueOf(cell.getStringCellValue());                            }                        }                        userInfo.setHeight(height);                        break;                    case 4:                                           double weight = 0;                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){                             weight = cell.getNumericCellValue();                                             }else{                            if(null == cell.getStringCellValue() ||"".equals(cell.getStringCellValue())                            ||cell.getStringCellValue().isEmpty()){                                break;                            }else{                                weight = Double.valueOf(cell.getStringCellValue());                            }                        }                        userInfo.setWeight(weight);                        break;                    case 5:                        if("大专".equals(cell.getStringCellValue())){                            userInfo.setEducation(3);                        }else if("本科".equals(cell.getStringCellValue())){                            userInfo.setEducation(4);                        }else if("硕士".equals(cell.getStringCellValue())){                            userInfo.setEducation(5);                        }else if("博士".equals(cell.getStringCellValue())){                            userInfo.setEducation(6);                        }                        break;                    case 6:                        if(cell.getStringCellValue().getBytes().length > 50){                            break;                        }                        userInfo.setCompany(cell.getStringCellValue());                        break;                    case 7:                        userInfo.setOccupation(cell.getStringCellValue());                        break;                    case 8:                        if(cell.getStringCellValue().getBytes().length > 200){                            break;                        }                        userInfo.setHobby(cell.getStringCellValue());                        break;                    case 9:                        if(cell.getStringCellValue().getBytes().length > 200){                            break;                        }                        userInfo.setIntroduction(cell.getStringCellValue());                        break;                    case 10:                        userInfo.setOComment(cell.getStringCellValue());                        break;                    case 11:                                String mobile ="";                        if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){                            mobile = cell.getStringCellValue();                        }else{                            double d = cell.getNumericCellValue();                            DecimalFormat df = new DecimalFormat("#");                            mobile = df.format(d);                        }                        UserInfo user = new UserInfo();                        user.setMobile(mobile);                        List<UserInfo> list = userInfoDAO.findList(user);                        if(list.size() != 0){                            continue next;                        }                        userInfo.setMobile(mobile);                        break;                    case 12:                        String weixinno = "";                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){                            double cellValue = cell.getNumericCellValue();                            DecimalFormat format = new DecimalFormat("#");                            weixinno = format.format(cellValue);                        }else{                            weixinno = cell.getStringCellValue();                        }                        userInfo.setWeixinNo(weixinno);                        break;                }              }            //这边设置数据库中限制了不能为空的列            userInfo.setBalance(0d);            userInfo.setFrozenBalance(0d);            userInfo.setReceivePushDate(0);            userInfoDAO.insert(userInfo);            }    }}