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);
}
}
}
junit解析Excel并导入数据库
最新推荐文章于 2023-10-26 18:05:40 发布