EXCEL导入功能的实现。
简单直接不废话。。。。
1.jsp导出按钮及相应的ajax方法
<button class="export_btn">
<span >导入</span>
<input id="uploadIntentionFile" name="myfiles" onchange="uploadIntentionFileChange()" class="file-up" type="file" value="导入" style="">
</button>
Ajax方法：
//推荐补贴表单上传
function uploadIntentionFileChange(){
var filePath = $("#uploadIntentionFile").val();
var fileName = filePath.substring(filePath.lastIndexOf("."),filePath.length);
if(!/.(xls|xlsx)/.test(fileName)){
$("#modal_message").text("请选择正确的Excel文件");
$("#modal_1").modal("show");
$("#uploadIntentionFile").val('');
}else{
ajaxFileUpload("uploadIntentionFile");
}
};
function ajaxFileUpload(fileId){
$.ajaxFileUpload({
url:'/memberRelation/uploadIntentionFile',
secureuri:false,
type: "POST",
fileElementId: fileId,
dataType: 'json',
success: function (data){
searchResult();
var jsonData = eval("(" + data + ")");
$("#modal_message").text(jsonData.message);
$("#modal_1").modal("show");
},
error: function (data){
searchResult();
$("#modal_message").text("Excel表格上传失败");
$("#modal_1").modal("show");
}
});
}
2.Controller层：
逻辑：解析相关Excel的name以及类型，在服务器上寻找相应的服务器，如果没有文件夹则创建，
/**
* 导入推荐补贴数据
* @param myfiles
* @throws IOException
*/
@ResponseBody
@RequestMapping(method = RequestMethod.POST, value = "/uploadIntentionFile", produces = "application/json;charset=UTF-8")
public ResultMessage uploadIntentionFile(@RequestParam MultipartFile myfiles) throws IOException {
ShiroUser user=(ShiroUser) SecurityUtils.getSubject().getPrincipal();
ResultMessage resultMessage = new ResultMessage(false,"上传文件解析失败");
String fileName = RandomSeriNoUtils.genCodeByTime("")+ "." + ImageUtil.getExtention(myfiles.getOriginalFilename());
String path = "/home/omo/file/intentionExcel/"+ DateConvertUtils.format(new Date(), "yyyyMM");
File newFile=new File(path);
// 创建目录
if (!newFile.exists()) {
newFile.mkdirs();// 目录不存在的情况下，创建目录。
}
String filePath = path+"/"+fileName;
//保存excel
try {
myfiles.transferTo(new File(filePath));
} catch (IllegalStateException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
Map<String,Object> resultJson = null;
if(filePath != null){
try {
resultJson = new PoiReadExcel().readExcel(filePath);//解析excel
if( resultJson!=null && resultJson.get("jsonList")!=null ){
List<JSONObject> resultList = (List<JSONObject>)resultJson.get("jsonList");
resultMessage = omoReRelationService.dealRelationExcel(resultList,user.getId());
}
} catch (IOException e) {
e.printStackTrace();
logger.error("解析补偿简历excel失败"+e);
return new ResultMessage(false,"解析Excel出错");
}
}
return resultMessage;
}
3.解析相关的工具类方法：
package com.omo.util;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCellType;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
public class PoiReadExcel {
private Map<String, Integer> rowIndexMap;
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public Map<String, Object> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null;
} else {
String postfix = Util.getPostfix(path);
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path);
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path);
}
} else {
System.out.println(path + Common.NOT_EXCEL_FILE);
}
}
return null;
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public Map<String, Object> readXlsx(String path) throws IOException {
Map<String, Object> result = new HashMap<>();
List<JSONObject> jsonList = new ArrayList<>();
JSONObject jsonObject = null;
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
if(rowIndexMap != null && rowIndexMap.size() > 0){
jsonObject = new JSONObject();
for(Map.Entry<String, Integer> entry : rowIndexMap.entrySet()){
Integer index = entry.getValue();
String key = entry.getKey();
String cellValue = null;
if(index >= xssfRow.getLastCellNum()){
//保存数据
jsonObject = setFieldValue(key,"",jsonObject);
}else{
try {
if(keySet.contains(key)){
XSSFCell cell = xssfRow.getCell(index);
cell.setCellType(cell.CELL_TYPE_STRING);
String value = String.valueOf(cell.getStringCellValue());
if ("".equals(value) ) {
continue;
}
BigDecimal bd = new BigDecimal(value);
cellValue = bd.toString();
}else if("interviewTimeString".equals(key) || "entryTimeString".equals(key)){
cellValue = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(HSSFDateUtil.getJavaDate(xssfRow.getCell(index).getNumericCellValue()) );
}else{
if(xssfRow.getCell(index)!= null){
cellValue = getValue(xssfRow.getCell(index));
}else{
cellValue = "";
}
}
} catch (Exception e) {
e.printStackTrace();
}
//保存数据
jsonObject = setFieldValue(key,cellValue,jsonObject);
}
}
jsonList.add(jsonObject);
}else{
for (int i = 0; i < xssfRow.getPhysicalNumberOfCells(); i++) {
XSSFCell value = xssfRow.getCell(i);
if (rowIndexMap == null || rowIndexMap.size() <= 0) {
rowIndexMap = new HashMap<String, Integer>();
}
if(titleMap.containsKey(getValue(value))){
rowIndexMap.put(titleMap.get(getValue(value)), i);
}
}
}
}
}
}
result.put("jsonList", jsonList);
return result;
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public Map<String, Object> readXls(String path) throws IOException {
Map<String, Object> result = new HashMap<>();
List<JSONObject> jsonList = new ArrayList<>();
JSONObject jsonObject = null;
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
if(rowIndexMap != null && rowIndexMap.size() > 0){
jsonObject = new JSONObject();
for(Map.Entry<String, Integer> entry : rowIndexMap.entrySet()){
Integer index = entry.getValue();
String key = entry.getKey();
String cellValue = null;
if(index > hssfRow.getPhysicalNumberOfCells()){
//保存数据
jsonObject = setFieldValue(key,"",jsonObject);
}else{
if(keySet.contains(key)){
HSSFCell hssfCell = hssfRow.getCell(index);
hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
String value = String.valueOf(hssfCell.getStringCellValue());
if ("".equals(value) ) {
continue;
}
BigDecimal bd = new BigDecimal(value);
cellValue = bd.toString();
}else if("interviewTimeString".equals(key) || "entryTimeString".equals(key)){
cellValue = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(HSSFDateUtil.getJavaDate(hssfRow.getCell(index).getNumericCellValue()) );
}else{
if(hssfRow.getCell(index)!= null){
cellValue = getValue(hssfRow.getCell(index));
}else{
cellValue = "";
}
}
//保存数据
jsonObject = setFieldValue(key,cellValue,jsonObject);
}
}
jsonList.add(jsonObject);
}else{
for (int i = 0; i < hssfRow.getPhysicalNumberOfCells(); i++) {
HSSFCell value = hssfRow.getCell(i);
if (rowIndexMap == null || rowIndexMap.size() <= 0) {
rowIndexMap = new HashMap<String, Integer>();
}
if(titleMap.containsKey(getValue(value))){
rowIndexMap.put(titleMap.get(getValue(value)), i);
}
}
}
}
}
}
result.put("jsonList", jsonList);
return result;
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
* 保存对象数据值
* @param key
* @param cellValue
* @param entity
* @param object
* @return
*/
public JSONObject setFieldValue(String key, String cellValue, JSONObject jsonObject){
if (cellValue != null && !"".equals(cellValue.trim())) {
if (key != null) {
try {
if (key.indexOf("[") > 0 && key.indexOf("[") < key.indexOf(".")){
String entityName = key.substring(0, key.indexOf("["));
String otherName = key.substring(key.indexOf("]")+1, key.indexOf("."));
JSONArray finalName = (JSONArray)jsonObject.get(entityName);
if (finalName == null) {
finalName = new JSONArray();
JSONObject js = new JSONObject();
finalName.add(js);
jsonObject.accumulate(entityName, finalName);
}
JSONArray json = setJSONValue(key.substring(key.indexOf(".") + 1),cellValue,finalName,otherName);
jsonObject.remove(entityName);
jsonObject.accumulate(entityName, json);
} else if(key.contains(".")) {
String entityName = key.substring(0, key.indexOf("."));
JSONObject finalName = (JSONObject)jsonObject.get(entityName);
if (finalName == null) {
finalName = new JSONObject();
jsonObject.accumulate(entityName, finalName);
}
//递归调用保存数据
JSONObject json = setFieldValue(key.substring(key.indexOf(".") + 1),cellValue,finalName);
jsonObject.remove(entityName);
jsonObject.accumulate(entityName, json);
}else {
Object object = jsonObject.get(key);
if (object != null) {
jsonObject.remove(key);
}
jsonObject.accumulate(nameMap.get(key), cellValue);
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
return jsonObject;
}
/**
* 对数组进行处理数据
* @param key
* @param cellValue
* @param jsonArray
* @param otherName
* @return
*/
public JSONArray setJSONValue(String key, String cellValue, JSONArray jsonArray, String otherName){
try {
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jsonObject = (JSONObject)jsonArray.get(0);
jsonObject.accumulate(nameMap.get(key), cellValue);
}
} catch (Exception e) {
e.printStackTrace();
}
return jsonArray;
}
private static HashSet keySet = null;
static{
keySet = new HashSet();
keySet.add("mobile");
keySet.add("recoMobile");
keySet.add("money");
keySet.add("actualMoney");
}
private static Map<String, String> nameMap = null;
static{
nameMap = new HashMap<String, String>();
nameMap.put("name", "name");
nameMap.put("gender", "gender");
nameMap.put("cardValue", "idCardValue");
nameMap.put("mobile", "mobile");
nameMap.put("age", "age");
nameMap.put("education", "education");
nameMap.put("employmentNature", "employmentNature");
nameMap.put("intentionPlace", "intentionPlace");
nameMap.put("intentionPosition", "intentionPosition");
nameMap.put("workYear", "workYear");
nameMap.put("skillTag", "skillTag");
nameMap.put("nearWorkStartTime", "nearWorkStartTime");
nameMap.put("nearWorkEndTime", "nearWorkEndTime");
nameMap.put("nearWorkCompanyName", "nearWorkCompanyName");
nameMap.put("memberName","memberName");
nameMap.put("recoMemberName","recoMemberName");
nameMap.put("recoIdCard","recoIdCard");
nameMap.put("recoMobile","recoMobile");
nameMap.put("interviewTimeString","interviewTimeString");
nameMap.put("entryTimeString","entryTimeString");
nameMap.put("entryCorpName","entryCorpName");
nameMap.put("coopCompany","coopCompany");
nameMap.put("agentName","agentName");
nameMap.put("money","money");
nameMap.put("actualMoney","actualMoney");
nameMap.put("entryPipeline","entryPipeline");
}
private static Map<String, String> titleMap = null;
static{
titleMap = new HashMap<String, String>();
titleMap.put("姓名", "name");
titleMap.put("性别", "gender");
titleMap.put("身份证号码", "cardValue");
titleMap.put("手机号码", "mobile");
titleMap.put("身份证", "cardValue");
titleMap.put("手机号", "mobile");
titleMap.put("年龄", "age");
titleMap.put("学历", "education");
titleMap.put("用工性质", "employmentNature");
titleMap.put("期望就业城市", "intentionPlace");
titleMap.put("期望岗位", "intentionPosition");
titleMap.put("最高学历", "education");
titleMap.put("工作年限", "workYear");
titleMap.put("技能标签", "skillTag");
titleMap.put("最近工作起始年月", "nearWorkStartTime");
titleMap.put("最近工作终止年月", "nearWorkEndTime");
titleMap.put("最近工作单位全称", "nearWorkCompanyName");
titleMap.put("推荐人","memberName");
titleMap.put("被推荐人","recoMemberName");
titleMap.put("被推荐人身份证","recoIdCard");
titleMap.put("推荐人电话", "mobile");
titleMap.put("被推荐人电话","recoMobile");
titleMap.put("面试时间","interviewTimeString");
titleMap.put("报到时间","entryTimeString");
titleMap.put("面试企业","entryCorpName");
titleMap.put("合作公司","coopCompany");
titleMap.put("职业顾问","agentName");
titleMap.put("补贴费用","money");
titleMap.put("实发金额","actualMoney");
titleMap.put("入职管道","entryPipeline");
}
}
//相关的util类
package com.omo.util;
public class Util {
/**
* get postfix of the path
* @param path
* @return
*/
public static String getPostfix(String path) {
if (path == null || Common.EMPTY.equals(path.trim())) {
return Common.EMPTY;
}
if (path.contains(Common.POINT)) {
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());
}
return Common.EMPTY;
}
}
4.用到的相关的常量定义：
package com.omo.util;
public class Common {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
public static final String EMPTY = "";
public static final String POINT = ".";
public static final String LIB_PATH = "E:\\Excel\\";
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "批量报备上传" + POINT + OFFICE_EXCEL_2003_POSTFIX;
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "批量报备上传" + POINT + OFFICE_EXCEL_2010_POSTFIX;
public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
public static final String PROCESSING = "Processing...";
}
5.service层：
主要进行业务逻辑处理，包括根据手机排除一些不能导入的数据，统计导入成功的数据条数。
/**
* 存推荐补贴excel的数据
*/
public ResultMessage dealRelationExcel(List<JSONObject> resultList,Long userId){
if(userId==null){
return new ResultMessage(false,"操作人信息为空");
}
HashSet setMobileKey = new HashSet();//声明一个异常mobileKey的集合
String message = "";//异常手机号字符串
int succussCount = 0;
for(JSONObject initRelationInfo : resultList){
//初始化detail对象
OmoReRelation relationInfo = initRelationDetail(initRelationInfo,userId);
String mobile = relationInfo.getMobile();//推荐人手机号
if(mobile == null || "".equals(mobile)){
continue;
}
//验证用户是否存在调用c端接口。
ResultMessage resultMessage = ylwCountIsUseByMobile(mobile);
if(!resultMessage.isSuccess()){
setMobileKey.add(mobile);
continue;
}
OmoMember memberByMobile = omoMemberDao.getByMobile(mobile);
if(memberByMobile!=null){
relationInfo.setMemberId(memberByMobile.getId());
}
OmoMember memberByRecoMobile = omoMemberDao.getByMobile(relationInfo.getRecoMobile());
if(memberByRecoMobile!=null){
relationInfo.setRecoMemberId(memberByRecoMobile.getId());
}
relationInfo.setIsSubsidies(0);
relationInfo.setStatusNo(1);
relationInfo.setCreateTime(new Date());
relationInfo.setUpdateTime(new Date());
relationInfo.setCreateBy(userId.intValue());
omoReRelationDao.insert(relationInfo);
succussCount++;
}
if(setMobileKey.size() > 0){
message = "异常的手机号有："+setMobileKey.toString()+",请验证是否开通优蓝账户。";
}
return new ResultMessage(true,"本次共成功添加了"+succussCount+"条数据;"+message);
}
public OmoReRelation initRelationDetail(JSONObject initRelationInfo, Long userId){
OmoReRelation relationInfo = com.alibaba.fastjson.JSON.toJavaObject(
JSON.parseObject(initRelationInfo.toString()), OmoReRelation.class);
return relationInfo;
}
public OmoReRelation initRelationDetail(JSONObject initRelationInfo, Long userId){
OmoReRelation relationInfo = com.alibaba.fastjson.JSON.toJavaObject(
JSON.parseObject(initRelationInfo.toString()), OmoReRelation.class);
return relationInfo;
}
导入功能结束。。。
176
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
