TestImportTchService public class TestImportTchService { public static void main(String[] args) { TestConfig.init(); File file = new File("C:/Users/dufuzhong/Desktop/crm_import_temp.xls"); // ImportTchService.me.developing = true; ImportXls
public class TestImportTchService {
public static void main(String[] args) {
TestConfig.init();
File file = new File("C:/Users/dufuzhong/Desktop/crm_import_temp.xls");
// ImportTchService.me.developing = true;
ImportXls importXls = ImportTchService.me.getImportXls("1", 1);
Kv kv = importXls.save(file, "crm_opportunity");
StringBuilder msgs = importXls.getMsgs();
System.out.println(kv.toJson());
System.out.println(msgs.toString());
System.exit(0);
}
}
ImportXls
/**
* Copyright (c) 2017 MomaThink (Beijing) Technology Inc. All rights reserved.
* Company:摩码创想（北京）科技有限公司
*
* @author dufuzhong
* @date 2017年8月8日 下午10:19:54
*/
package com.momathink.common.tools.util;
import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import com.jfinal.kit.Kv;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Record;
import com.momathink.common.kit.Db;
/**
*
* @author dufuzhong
* @date 2017年8月9日 上午10:14:01
*
* 如果需要异步处理:
* new Thread(){ ImportXls的处理 }.start();
*
*/
public abstract class ImportXls {
protected HashMap
> checkContains = new HashMap<>();
protected StringBuilder msgs = new StringBuilder();
/**
* K 必须和xls列头一样 , V 必须和数据库一样
* 例子: return new Kv().set("姓名","name") ...
* @param index sheet的页数
* */
protected abstract Kv getTitleAndColumn(int index);
/**
* 检查 record 的字段 是否符合数据库字段写入要求(字段已处理两端空格和空的情况都=null)
*/
protected Record setRecord(Record record, String column, Object value, int rowIndex, int rowIndexCount, int cellnum) {
return record.set(column, value);
}
/**
* 检查 record 是否符合数据库写入要求(字段已处理两端空格和空的情况都=null)
* @param rowIndexCount : rowIndex + 1
* @return 允许写入:返回true 不允许写入:返回false
*/
protected boolean isCheckRecord(Record record, int rowIndex, int rowIndexCount) {
return true;
}
/**
* 检查 record 是否 需要 停止读取
* @param rowIndexCount : rowIndex + 1
* @return 停止读取:返回true 不停止读取:返回false
*/
private boolean isCheckBreak(Record record, int rowIndex, int rowIndexCount) {
return false;
}
/**
* 单表导入
* @param file: XLS 文件
* @param tableName: 表名
* @return Kv: isOK=成功数 , isNo=失败数 , length=执行总数 , errcode=异常状态 , errmsg=异常信息
*/
public Kv save(File file, String tableName) {
Kv ret = new Kv().set("isOK", 0).set("isNo", 0).set("length", 0).set("errcode", 0);
try {
HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
int index = 0;//预留多sheet导入
HSSFSheet sheet = hwb.getSheetAt(index);
Kv columns = new Kv();
List
recordList = new ArrayList
(); Kv recordRowIndex = new Kv();//记录record角标的 int rowIndex = getColumns(sheet, columns, index) + 1; boolean doValve = true; long isOK = 0; long isNo = 0; do { HSSFRow row = sheet.getRow(rowIndex); if(row != null){ int rowIndexCount = rowIndex + 1; Record record = new Record(); for (int cellnum = 0; cellnum < columns.size(); cellnum++) { String column = columns.getStr(cellnum); HSSFCell cell = row.getCell(cellnum); if( cell != null ) { String value = parseExcel(cell); if( StrKit.notBlank(value) ){ setRecord(record, column, value, rowIndex, rowIndexCount, cellnum); } } } if(isCheckRecord(record, rowIndex, rowIndexCount )){ recordList.add(record); recordRowIndex.set(record, rowIndex); }else{ isNo ++; } if(isCheckBreak(record, rowIndex, rowIndexCount )){ break; } rowIndex ++; continue;//下一轮遍历 } doValve = false; } while (doValve); int[] batchSave = batchSave(tableName, recordList); for (int i = 0; i < batchSave.length; i++) { if(batchSave[i] == 1){ isOK ++; }else{ isNo ++; appendMsg(recordRowIndex.getInt(recordList.get(i)) + 1 , "全部", "数据写入失败,请检查格式"); } } ret.set("isOK", isOK).set("isNo", isNo).set("length", isOK + isNo); } catch (Exception e) { e.printStackTrace(); ret.set("errcode", 500).set("errmsg", e.toString()); } return ret; } /** * 批量写入数据库 * */ protected int[] batchSave(String tableName, List
recordList) { return Db.batchSave(tableName, recordList, 200); } /** * 从XLS表格中取值, 转换为字符串 * */ protected String parseExcel(Cell cell) { String result = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型 if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式 SimpleDateFormat sdf = null; if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { sdf = new SimpleDateFormat("HH:mm"); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd"); } Date date = cell.getDateCellValue(); result = sdf.format(date); } else if (cell.getCellStyle().getDataFormat() == 58) { // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = cell.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value); result = sdf.format(date); } else { double value = cell.getNumericCellValue(); CellStyle style = cell.getCellStyle(); DecimalFormat format = new DecimalFormat(); String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); } result = format.format(value); } break; case HSSFCell.CELL_TYPE_STRING:// String类型 result = cell.getRichStringCellValue().toString().trim(); break; case HSSFCell.CELL_TYPE_BLANK: result = ""; default: result = null; break; } return result; } /** * 获取XLS与数据库对相应关系键值, */ protected int getColumns(HSSFSheet sheet, Kv columns, int index){ int rowIndex = 0; HSSFRow titleRow = sheet.getRow(rowIndex); // K 必须和数据库一样, V 必须和xls列头一样 Kv t = getTitleAndColumn(index); int cellnum = 0; boolean doValve = true; do { HSSFCell cell = titleRow.getCell(cellnum); if(cell != null){ cell.setCellType(HSSFCell.CELL_TYPE_STRING); String key = cell.getStringCellValue(); if(key != null){ // 和XLS 对应 columns.set(cellnum, t.getStr( key.trim() )); cellnum = cellnum + 1; continue;//下一轮遍历 } } doValve = false; } while (doValve); return rowIndex; } /** * 检查值XLS中 数值的唯一性, 如果是唯一的:把值存入并返回false * */ protected boolean isCheckContainsOrAdd(String key, String val){ if(val == null || key == null){ return false;} if(isCheckContains(key, val)){ return true; } addCheckContains(key, val); return false; } /** * 检查值XLS中 数值的唯一性 * */ protected boolean isCheckContains(String key, String val){ if(val == null || key == null){ return false;} HashSet
hashSet = checkContains.get(key); if(hashSet == null){ return false; } return hashSet.contains(val); } /** * 把值存入唯一性检查集合 * */ protected ImportXls addCheckContains(String key, String val){ if(val == null || key == null){ return this;} HashSet
hashSet = checkContains.get(key); if(hashSet == null){ hashSet = new HashSet<>(); checkContains.put(key, hashSet); } hashSet.add(val); return this; } /** * 写日志 * */ protected StringBuilder appendMsg(Object rowIndexCount, String title, String msg ) { return msgs.append("第").append(rowIndexCount).append("行的[").append(title).append("]列 ").append(msg).append("
"); } /**获取日志 * */ public StringBuilder getMsgs(){ return msgs; } /** * 验证时间字符串格式 是否正确 , 如果不正确返回 默认值 */ protected static String getCheckDateTimeStr(Object timeStr, String defaultTimeStr) { return isCheckDateTimeStr((String)timeStr) ? (String)timeStr : defaultTimeStr; } /** * 验证时间字符串格式 是否正确 */ protected static boolean isCheckDateTimeStr(String timeStr) { return timeStr.matches( "((19|20)[0-9]{2})-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01]) ([01]?[0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]"); } /** * 验证时间字符串格式 是否正确 , 如果不正确返回 默认值 */ protected static String getCheckDateStr(Object timeStr, String defaultTimeStr) { return isCheckDateStr((String)timeStr) ? (String)timeStr : defaultTimeStr; } /** * 验证时间字符串格式 是否正确 */ protected static boolean isCheckDateStr(String timeStr) { return timeStr.matches( "((19|20)[0-9]{2})-(0?[1-9]|1[012])-(0?[1-9]|[12][0-9]|3[01])"); } }
ImportTchService 使用例子, 需要做校验的时候,才重写, 不做校验的话, 就重写getTitleAndColumn就可以了
public class ImportTchService {
public static final ImportTchService me = new ImportTchService();
public boolean developing = false;
/**
* 组装导入业务 结构
* */
public ImportXls getImportXls(final Object campusid, final Object userids) {
return new ImportXls(){
@Override
protected int[] batchSave(String tableName, List
recordList) {
if(developing){
MomaLog.getLog(ImportTchService.class).info("-batchSave-" + recordList.toString());
int[] ret = {};
return ret;
}
return super.batchSave(tableName, recordList);
}
@Override
protected Kv getTitleAndColumn(int index) {
return new Kv()
.set("姓名", "contacter")
.set("性别", "sex")
.set("联系电话", "phonenumber")
.set("电子邮箱", "email")
.set("QQ", "qq")
.set("微信", "weixin")
.set("出生日期", "birthDate")
.set("短信通知", "phonenMessage")
.set("与学生关系", "relation")
.set("授课类型", "classtype")
.set("转介绍人名字", "recommendusername")
.set("转介绍人电话", "recommendphone")
.set("下次回访时间", "nextvisit")
.set("线索状态", "isconver")
.set("客户类型", "customer_rating")
.set("以前的学校", "SCHOOL")
.set("以前的年级", "GRADE_NAME")
.set("所属活动名称", "activityid")
.set("所属渠道", "mediatorid")
.set("其他备注", "note");
}
@Override
protected boolean isCheckRecord(Record record, int rowIndex, int rowIndexCount) {
//如果姓名为空了, 就不保存 /---------------------------------------
String contacter = record.getStr("contacter");
if (contacter == null){
appendMsg(rowIndexCount, "姓名", "不能为空");
return false;
}
if (isCheckMy("contacter", contacter)){
appendMsg(rowIndexCount, "姓名", "系统已存在");
return false;
}
//"联系电话,电子邮箱 ,QQ,微信", "不能为空必填一项", 就不保存 /---------------
String phonenumber = record.getStr("phonenumber");
String email = record.getStr("email");
String qq = record.getStr("qq");
String weixin = record.getStr("weixin");
if( phonenumber!=null || email!=null || qq!=null || weixin!=null ){
if (isCheckMy("phonenumber", phonenumber)){
appendMsg(rowIndexCount, "联系电话", "系统已存在");
return false;
}
if (isCheckMy("email", email)){
appendMsg(rowIndexCount, "电子邮箱", "系统已存在");
return false;
}
if (isCheckMy("qq", qq)){
appendMsg(rowIndexCount, "QQ", "系统已存在");
return false;
}
if (isCheckMy("weixin", weixin)){
appendMsg(rowIndexCount, "微信", "系统已存在");
return false;
}
}else{
appendMsg(rowIndexCount, "联系电话,电子邮箱 ,QQ,微信", "不能为空必填一项");
return false;
}
//固定数据-------------------------------------------------------
record.set("campusid", campusid);
record.set("createtime", new Date());
record.set("status", "1");
return true;
}
private boolean isCheckMy(String key, String val) {
return isCheckContainsOrAdd(key, val) || Opportunity.dao.queryOpportunityCount(key, val) > 0 ;
}
@Override
protected Record setRecord(Record record, String column, Object value, int rowIndex, int rowIndexCount, int cellnum) {
switch (column) {
case "sex"://性别
value = "男".equals(value) ? 1 : 0;
break;
case "phonenMessage"://短信通知
value = "接收".equals(value) ? 1 : 0;
break;
case "relation"://与学生关系 1本人2母亲3父亲4其他
value =
"本人".equals(value) ? 1 :
"母亲".equals(value) ? 2 :
"父亲".equals(value) ? 3 :
4;
break;
case "classtype"://授课类型
value = "一对一".equals(value) ? 1 : 0;
break;
case "customer_rating"://客户类型
//0：未知客户 1：潜在客户 2：目标客户 3：发展中客户 4：交易客户 5：后续介绍客户 6：非客户
value =
"潜在客户".equals(value) ? 1 :
"目标客户".equals(value) ? 2 :
"发展中客户".equals(value) ? 3 :
"交易客户".equals(value) ? 4 :
"后续介绍客户".equals(value) ? 5 :
"非客户".equals(value) ? 6 :
0;
break;
case "isconver"://线索状态
//0、未跟进；1、已成单；2、跟进中；3、考虑中；4、无意向；5、已放弃 6：有意向
value =
"已成单".equals(value) ? 1 :
"跟进中".equals(value) ? 2 :
"考虑中".equals(value) ? 3 :
"无意向".equals(value) ? 4 :
"已放弃".equals(value) ? 5 :
"有意向".equals(value) ? 6 :
0;
break;
case "birthDate"://出生日期
value = getCheckDateStr( value , null);//针对日期处理
break;
case "nextvisit"://下次回访时间
value = getCheckDateTimeStr( value , null);//针对日期处理
break;
case "activityid"://所属活动名称
value = Activity.dao.queryActivityByNameGetId(value , userids);
break;
case "mediatorid"://所属渠道
value = Mediator.dao.queryByNameGetId((String)value, "0");
break;
default:
break;
}
// 重要代码
return super.setRecord(record, column, value, rowIndex, cellnum , cellnum);
}
};
}
}