最近写了个excel导入并多线程持久化到数据库的功能，捣鼓了一天才弄好，先记录下来防止自己忘了。
（1）先controller类中方法。
@AccessLog
@ApiOperation(value = "导入excel", httpMethod = "POST", notes = "导入excel")
@RequestMapping(value = "/importExcel",method = RequestMethod.POST)
@ApiImplicitParams({
@ApiImplicitParam(name="postionId",value="岗位ID",dataType="long", paramType = "query"),
@ApiImplicitParam(name="typeId",value="类型ID(1：岗位 2：人员)",dataType="int", paramType = "query"),
@ApiImplicitParam(name="agencyId",value="部门ID",dataType="long", paramType = "query")
})
public ResponseResult importExcel(@RequestParam(value="file") MultipartFile file,
Long postionId, Integer typeId, Long agencyId) {
SelAgencyAndPostionVO selAgencyAndPostionVO = new SelAgencyAndPostionVO(agencyId,postionId,typeId);
if (null == selAgencyAndPostionVO) {
return new ResponseResult(ExceptionCode.PARAM_IS_NULL);
}
//类型标识（1：岗位 2：人员）
typeId = selAgencyAndPostionVO.getTypeId();
if (null == typeId) {
log.info("1", "typeId is null");
return new ResponseResult(ExceptionCode.PARAM_IS_NULL);
}
//获取上传的文件名称；
String name = file.getOriginalFilename();
//判断是否为excel类型文件
if(!name.endsWith(".xls") && !name.endsWith(".xlsx")){
log.info("导入的文件不是excel类型");
return new ResponseResult<>("导入的文件不是excel类型");
}
try {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.
getRequestAttributes()).getRequest();
//上传至绝对路径
String path = request.getSession().getServletContext().getRealPath(File.separator);
String uploadDir = path+"upload"+File.separator;
log.info(this.getClass().getName()+"临时保存图片路径saveImgUrl："+uploadDir);
File f = new File(uploadDir);
//如果不存在该路径就创建
if (!f.exists()) {
f.mkdir();
}
//获取文件名
String uuid= new Date().getTime()+"_"+UUID.randomUUID().toString().
replace("-","").substring(0,6);
//文件保存绝对路径
String newName = uploadDir+ uuid + "_"+name;
//上传文件位置
File dir = new File(uploadDir);
if (!dir.exists()) {
dir.mkdirs();
}
File imgFile = new File(newName);
//存入临时内存
FileUtils.writeByteArrayToFile(imgFile, file.getBytes());
//获取excel中的数据信息
List<Map<String, Object>> maps = ImportExcelFileUtil.getDataFromExcel(newName,typeId == 1 ? new ElPositionDTO() :
typeId == 2 ? new ElUserInfoDTO(): null);
//删除临时保存的图片
if(imgFile.exists() && imgFile.isFile()) {
imgFile.delete();
}
if (CollectionUtils.isEmpty(maps)) {
log.error("ElAttachmentController的importExcel方法获取导入的excel数据为空");
return new ResponseResult<>(ExceptionCode.METHOD_FAILURE);
}
//获取的是成功插入的次数
int row = elAgencyPositionUserService.importBatchData(maps,selAgencyAndPostionVO);
String result = "";
if ((maps.size() - row) == 0 ) {
result = "全部导入成功"+row+"条";
} else if ((maps.size() - row) > 0) {
result ="导入成功"+row+"条，导入失败" + (maps.size() - row) + "条（错误或重复）";
}
return new ResponseResult(result);
}catch(BusinessException e){
log.error("ElAttachmentController的importExcel方法error"+e.getMessage(),e);
return new ResponseResult<>(e);
}catch (Exception e) {
log.error("ElAttachmentController的importExcel异常"+e.getMessage(), e);
return new ResponseResult(ExceptionCode.INTERFACE_USE_FAILURE);
}
}
（2）InportExcelFileUtil类处理excel文件的信息。此excel方法是通用的方法
package com.zhengtoon.enforcelaw.utils;
import com.zhengtoon.enforcelaw.dto.ElUserInfoDTO;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
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;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Author 601278
* Date 2018/6/11、9:18
* Version 1.0
**/
public class ImportExcelFileUtil {
private static final Logger log = LoggerFactory.getLogger(ImportExcelFileUtil.class);
private final static String excel2003L =".xls"; //2003- 版本的excel
private final static String excel2007U =".xlsx"; //2007+ 版本的excel
/**
* 拼装单个obj 通用
*
* @param obj
* @param row
* @return
* @throws Exception
*/
private static Map<String, Object> dataObj(Object obj, Row row) throws Exception {
Class<?> rowClazz= obj.getClass();
Field[] fields = FieldUtils.getAllFields(rowClazz);
if (fields == null || fields.length < 1) {
return null;
}
//容器
Map<String, Object> map = new HashMap<String, Object>();
//注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
for (int j = 0; j < fields.length; j++) {
map.put(fields[j].getName(), getVal(row.getCell(j)));
}
return map;
}
/**
* 处理val
*
* @param cell
* @return
*/
public static String getVal(Cell cell) {
Object value = null;
DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字
SimpleDateFormat sdf = new SimpleDateFormat("yyy-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("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){
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.toString();
}
/**
* * 读取出filePath中的所有数据信息
*
* @param filePath excel文件的绝对路径
* @param obj
* @return
*/
public static List<Map<String, Object>> getDataFromExcel(String filePath, Object obj){
if (null == obj) {
return null;
}
List<Map<String, Object>> ret = null;
FileInputStream fis =null;
Workbook wookbook = null;
int lineNum = 0;
Sheet sheet = null;
try{
//获取一个绝对地址的流
fis = new FileInputStream(filePath);
wookbook = getWorkbook(fis,filePath);
//得到一个工作表
sheet = wookbook.getSheetAt(0);
//获得表头
Row rowHead = sheet.getRow(0);
//列数
int rows = rowHead.getPhysicalNumberOfCells();
//行数
lineNum = sheet.getLastRowNum();
if(0 == lineNum){
log.info("ImportExcelFileUtil中的getDataFromExcel方法导入的Excel内没有数据！");
}
ret = getData(sheet, lineNum, rows, obj);
} catch (Exception e){
e.printStackTrace();
}
return ret;
}
* @param obj
* @return
*/
public static List<Map<String, Object>> getData(Sheet sheet, int lineNum, int rowNum, Object obj){
List<Map<String, Object>> ret = null;
try {
//容器
ret = new ArrayList<Map<String, Object>>();
//获得所有数据
for(int i = 1; i <= lineNum; i++){
//获得第i行对象
Row row = sheet.getRow(i);
if(row!=null){
//装载obj
ret.add(dataObj(obj,row));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return ret;
}
/**
* 描述：根据文件后缀，自适应上传文件的版本
*
* @param inStr,fileName
* @return
* @throws Exception
*/
public static 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;
}
public static void main(String[] args) throws Exception{
ElUserInfoDTO dto = new ElUserInfoDTO();
List<Map<String, Object>> dataFromExcel = getDataFromExcel("D:\\img\\测试4.xls", dto);
for (int i = 0; i < dataFromExcel.size(); i++) {
for (Map.Entry<String, Object> entry : dataFromExcel.get(i).entrySet()) {
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
}
}
System.out.println(dataFromExcel);
}
}
（3）创建多线程，并计算线程数，此实现的线程是Call,为了可以返回成功的结果
public int importBatchData(List<Map<String, Object>> list,SelAgencyAndPostionVO selAgencyAndPostionVO) {
//部门主键ID
Long agencyId = selAgencyAndPostionVO.getAgencyId();
//类型ID（1：岗位 2：人员 ）
Integer typeId = selAgencyAndPostionVO.getTypeId();
//岗位主键ID
Long postionId = selAgencyAndPostionVO.getPostionId();
int row = 0;
try {
if (typeId == 1) {
row = savePositionInfoList(list,agencyId);
} else if (typeId == 2) {
/* ElUserInfo elUserInfo = new ElUserInfo();
elUserInfo.setAgencyId(agencyId);
elUserInfo.setPositionId(postionId);*/
//在导入之前，把用户全部放进缓存，防止重复导入
List<ElUserInfo> usersByUserNameAndTel = userInfoMapper.getUsersByUserNameAndTel(null);
for (ElUserInfo key : usersByUserNameAndTel) {
redisCacheService.setRedisCacheByKey(key.getUserName(),key.getUserName(),3L,TimeUnit.HOURS);
}
row = saveUserInfoList(list,agencyId,postionId);
}
} catch (Exception e) {
e.printStackTrace();
}
return row;
public int saveUserInfoList(List<Map<String, Object>> list, Long agencyId, Long postionId) {
Integer row = 1;
Integer successCount = 0;
int count = 50;// 一个线程处理50条数据
int listSize = list.size();// 数据集合大小
int runThreadSize = (listSize / count) + 1; // 开启的线程数
List<Map<String, Object>> newlist = null;// 存放每个线程的执行数据
ExecutorService executor = Executors.newFixedThreadPool(runThreadSize);// 创建一个线程池，数量和开启线程的数量一样
// 创建两个个计数器
CountDownLatch begin = new CountDownLatch(1);
CountDownLatch end = new CountDownLatch(runThreadSize);
// 循环创建线程
for (int i = 0; i < runThreadSize; i++) {
if ((i + 1) == runThreadSize) {
int startIndex;
startIndex = (i * count);
int endIndex = list.size();
newlist = list.subList(startIndex, endIndex);
} else {
int startIndex = (i * count);
int endIndex = (i + 1) * count;
newlist = list.subList(startIndex, endIndex);
}
//线程类，处理数据持久化
UserInfoThread userInfoThread = new UserInfoThread(newlist,begin,end,agencyId,postionId);
//executor.execute(userInfoThread);
Future<Integer> submit = executor.submit(userInfoThread);
try {
//提交成功的次数
row = submit.get();
successCount += row;
} catch (InterruptedException e1) {
log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e1.getMessage(),e1);
} catch (ExecutionException e2) {
log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e2.getMessage(),e2);
}
}
try{
begin.countDown();
end.await();
//执行完关闭线程池
executor.shutdown();
}catch (Exception e) {
log.error("ElAgencyPositionUserServiceImpl的saveUserInfoList方法error"+e.getMessage(),e);
}
return successCount;
}
（4）UserInfoThread具体实现业务
/**
* 封装获取的对象信息
*
* @param sheet
* @param lineNum
* @param rowNum
public class UserInfoThread implements Callable<Integer> {
//private static final String appId = DisconfDataGetter.getByFile("systemConfig.properties").get("enforcelaw.appId").toString();
private static final Logger log = LoggerFactory.getLogger(PostionThread.class);
private List<Map<String, Object>> list;
private CountDownLatch begin;
private CountDownLatch end;
private Long agencyId;
private Long postionId;
private UserInfoMapper userInfoMapper;
private OrgEmployeeService orgEmployeeService;
private RedisCacheService redisCacheService;
private PositionInfoMapper positionInfoMapper;
//创建个构造函数初始化 list,和其他用到的参数
public UserInfoThread(List<Map<String, Object>> list,CountDownLatch begin,CountDownLatch end, Long agencyId, Long postionId){
this.list = list;
this.begin = begin;
this.end = end;
this.agencyId = agencyId;
this.postionId = postionId;
userInfoMapper = (UserInfoMapper)SpringUtil.getBean("userInfoMapper");
orgEmployeeService = (OrgEmployeeService)SpringUtil.getBean(OrgEmployeeService.class);
redisCacheService = (RedisCacheService)SpringUtil.getBean(RedisCacheService.class);
positionInfoMapper = (PositionInfoMapper)SpringUtil.getBean("positionInfoMapper");
}
@Override
public Integer call(){
int row = 0;
try {
List<ElUserInfo> userList = new ArrayList<ElUserInfo>();
if (CollectionUtils.isNotEmpty(list)) {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
String userSex = map.get("userSex").toString().trim();
String userName = map.get("userName").toString().trim();
String userTel = map.get("userTel").toString().trim();
String key = userName + userTel;
String redisCacheByKey = redisCacheService.getRedisCacheByKey(key);
log.info(redisCacheByKey);
if (!StringUtils.isEmpty(redisCacheByKey)) {
redisCacheService.deleteRedisCacheByKey(key);
//continue;
}
if ("男".equals(userSex)) {
userSex = "0";
} else if ("女".equals(userSex)){
userSex = "1";
}
ElUserInfo user = new ElUserInfo();
user.setUserName(userName);
user.setUserTel(userTel);
user.setPassWord(MD5(map.get("passWord").toString().trim()));
user.setUserSex(userSex);
user.setPositionId(postionId);
user.setAgencyId(agencyId);
user.setCreateDate(new Date());
user.setUpdateDate(new Date());
user.setDelMark(0);
user.setRoleId(0L);
user.setEmployeeId(0L);
user.setOrgId(0L);
userList.add(user);
}
if (CollectionUtils.isNotEmpty(userList)) {
//先持久化本地
row = userInfoMapper.createBatchUserInfoList(userList);
if (row > 0) {
//持久化成功后同步组织平台
String add = orgEmployeeService.addOrganRoleUserToPlatform(userList, "add");
if (!"-1".equals(add)) {
//同步成功后,修改OrgId和EmployeeId
for (ElUserInfo user : userList) {
user.setOrgId(Long.parseLong(add));
user.setEmployeeId(1L);
}
//以用户手机号码为唯一标示，批量修改OrgId和EmployeeId
userInfoMapper.updateBatchOrgId(userList);
}
log.info(this.getClass().getName()+"的UserInfoThread"+add.toString());
}
}
}
//....
//执行完让线程直接进入等待
// begin.await();
} catch (Exception e) {
log.error("elPositionInfoServiceImpl的UserInfoThread方法error"+e.getMessage(),e);
}finally{
//这里要主要了，当一个线程执行完了计数要减一不要这个线程会被一直挂起
//，end.countDown()，这个方法就是直接把计数器减一的
end.countDown();
}
return row;
}
}
@Component
public class SpringUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if(SpringUtil.applicationContext == null) {
SpringUtil.applicationContext = applicationContext;
}
}
//获取applicationContext
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
//通过name获取 Bean.
public static Object getBean(String name){
return getApplicationContext().getBean(name);
}
//通过class获取Bean.
public static <T> T getBean(Class<T> clazz){
return getApplicationContext().getBean(clazz);
}
//通过name,以及Clazz返回指定的Bean
public static <T> T getBean(String name,Class<T> clazz){
return getApplicationContext().getBean(name, clazz);
}
}