package com.cme.core;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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 com.cme.core.entity.CountryMapping;
import com.cme.core.entity.Currency;
import com.cme.core.entity.EnterpriseAddressInfo;
import com.cme.core.entity.IndustryInfo;
import com.cme.utils.JdbcUtils;
/**
* Hello world!
*
*/
public class AppOld
{
//產業行業分類
private static Map<String,List<IndustryInfo>> infos = new HashMap<String, List<IndustryInfo>>();
//企業類型
private static Map<String,String> enterpriseTypes = new HashMap<String, String>();
//城市
private static List<CountryMapping> cms = new ArrayList<CountryMapping>();
//幣種
private static Map<String,String> currencys = new HashMap<String, String>();
static{
infos = getIndustryInfos();
enterpriseTypes = getEnterpriseTypes();
cms = getCountryMappings();
currencys = getCurrencys();
}
public static void main( String[] args )
{
long beginTime = System.currentTimeMillis();
exec(args[0]);
long endTime = System.currentTimeMillis();
System.out.println(endTime - beginTime);
}
/**
* 递归读取excel文件
* @param pathName
*/
public static void exec(String pathName){
File file = new File(pathName);
if(file.isDirectory()){
File[] childs = file.listFiles();
for(File child : childs){
exec(child.getPath());
}
}else if(file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx")){
//读取并导入excel文件
readExcel(file);
}else{
System.out.println("不需要导入的文件:" + file.getName());
}
}
/**
* 读取并导入excel文件
* @param file
*/
public static void readExcel(File file){
try {
Workbook workBook = null;
if(file.getName().endsWith(".xls")){
workBook = new HSSFWorkbook(new FileInputStream(file));
}else if(file.getName().endsWith(".xlsx")){
workBook = new XSSFWorkbook(new FileInputStream(file));
}
int numberOfSheets = workBook.getNumberOfSheets();
//一次读取3000条数据
int pageSize = 3000;
//依次导入每个sheet里面的数据
for(int i =0 ;i< numberOfSheets; i++){
Sheet sheet = workBook.getSheetAt(i);
//算出总记录数
int totalCount = sheet.getLastRowNum();
//算出总页数
int totalPage = getTotalPage(totalCount, pageSize);
Row header = sheet.getRow(1);
if(header != null){
int celNum = 23;
//int celNum = header.getPhysicalNumberOfCells();
List<List<Object>> datas = null;
List<Object> data = null;
for(int j = 1 ;j <= totalPage; j++){
datas = new ArrayList<List<Object>>();
int firstResult = j==1 ? 1:getFirstResult(j, pageSize)+1 ;
int lastResult = pageSize * j > totalCount ? totalCount : pageSize * j;
for(int k = firstResult ; k <= lastResult; k++){
Row row = sheet.getRow(k);
if(row != null){
data = new ArrayList<Object>();
for(int t = 0 ; t < celNum ; t++){
Cell cell = row.getCell(t);
if(cell == null){
data.add(null);
}else{
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
if(StringUtils.isNotBlank(value))
value = value.trim();
data.add(cell.getStringCellValue());
}
}
data.add(file.getPath());
datas.add(data);
}
}
//导入数据
System.out.println("filePath=" + file.getPath());
importData(datas);
}
}
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导入数据
* @param datas
*/
public static void importData(List<List<Object>> datas){
Connection conn = null;
PreparedStatement ps = null;
String column = "enterpriseName,contactPerson,contactNum,phoneNum,fax,email,zipCode,provinceName,cityName,countyName,addressNameT,businessScope,enterpriseTypeText,registeredCapitalT,website,businessState,setupTime,businessPeriod,enterpriseRegistration,divide,businessIntroduction,qqNum,weChat,filePath,industry,industryCode,enterpriseType,provinceCode,cityCode,countyCode,address,registerProvinceCode,registerProvinceName,registerCityCode,registerCityName,registerCountyCode,registerCountyName,enterpriseGJ,enterpriseNationalityCode,currency,currencyCode,registeredCapital";
StringBuffer sb = new StringBuffer("insert into cme_enterprise_imp(").append(column).append(") values (");
String[] columns = column.split(",");
for(int i = 0; i < columns.length ; i++){
if(i != 0){
sb.append(",");
}
sb.append("?");
}
sb.append(")");
String sql = sb.toString();
System.out.println(sql);
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false);
for(int i = 0 ; i < datas.size() ; i++){
// 3.创建语句
ps = conn.prepareStatement(sql);
List<Object> data = datas.get(i);
//經營範圍
String businessSope = null;
//企業類型名稱
String enterpriseTypeName = null;
int j = 0;
int provIndex = 7;
int cityIndex = 8;
int countryIndex = 9;
int registeredCapital = 13;
int registerAddress = 18;
String provNameT = null;
String cityNameT = null;
String countryNameT = null;
EnterpriseAddressInfo eai = null;
EnterpriseAddressInfo registerEai = null;
Currency currency = null;
int tmpIndex =1;
for(; j < data.size() ; j ++){
Object o = data.get(j);
ps.setObject(tmpIndex, o);
if(j == provIndex && o != null && StringUtils.isNotBlank(o.toString())){
provNameT = o.toString();
}
if(j == cityIndex && o != null && StringUtils.isNotBlank(o.toString())){
cityNameT = o.toString();
}
if(j == countryIndex && o != null && StringUtils.isNotBlank(o.toString())){
countryNameT = o.toString();
}
//处理地址
if(j == 10 && o != null){
eai = getEnterpriseCountry(o.toString(),provNameT,cityNameT,countryNameT);
}
//处理注册地
if(j == registerAddress && o != null){
registerEai = getEnterpriseCountry(o.toString(),null,null,null);
}
//處理注冊資金
if(j == registeredCapital && o!= null){
currency = getCurrency(o.toString());
}
if(j == 11 && o != null){
businessSope = o.toString();
}
if(j == 12 && o != null){
enterpriseTypeName = o.toString();
}
tmpIndex++;
}
//根據經營範圍獲取產業行業
IndustryInfo info = getEnterpriseIndustry(businessSope);
String industryName = info != null ? info.getName() : null;
String industryCode = info != null ? info.getCode() : null;
ps.setObject(tmpIndex++, industryName);
ps.setObject(tmpIndex++, industryCode);
ps.setObject(tmpIndex++, getEnterpriseTypeCode(enterpriseTypeName));
//处理地址
if(eai != null && StringUtils.isNotBlank(eai.getProvId())){
ps.setObject(provIndex+1, eai.getProvName());
ps.setObject(tmpIndex++, eai.getProvId());
}else{
ps.setObject(tmpIndex++, null);
}
if(eai != null && StringUtils.isNotBlank(eai.getCityId())){
ps.setObject(cityIndex+1, eai.getCityName());
ps.setObject(tmpIndex++, eai.getCityId());
}else{
ps.setObject(tmpIndex++, null);
}
if(eai != null && StringUtils.isNotBlank(eai.getCountryId())){
ps.setObject(countryIndex+1, eai.getCountryName());
ps.setObject(tmpIndex++, eai.getCountryId());
}else{
ps.setObject(tmpIndex++, null);
}
if(eai != null && StringUtils.isNotBlank(eai.getAddress())){
ps.setObject(tmpIndex++, eai.getAddress());
}else{
ps.setObject(tmpIndex++, null);
}
//处理注册地
if(registerEai !=null && StringUtils.isNotBlank(registerEai.getProvId())){
ps.setObject(tmpIndex++, registerEai.getProvId());
ps.setObject(tmpIndex++, registerEai.getProvName());
}else{
ps.setObject(tmpIndex++, null);
ps.setObject(tmpIndex++, null);
}
if(registerEai !=null && StringUtils.isNotBlank(registerEai.getCityId())){
ps.setObject(tmpIndex++, registerEai.getCityId());
ps.setObject(tmpIndex++, registerEai.getCityName());
}else{
ps.setObject(tmpIndex++, null);
ps.setObject(tmpIndex++, null);
}
if(registerEai !=null && StringUtils.isNotBlank(registerEai.getCountryId())){
ps.setObject(tmpIndex++, registerEai.getCountryId());
ps.setObject(tmpIndex++, registerEai.getCountryName());
}else{
ps.setObject(tmpIndex++, null);
ps.setObject(tmpIndex++, null);
}
ps.setObject(tmpIndex++, "中国");
ps.setObject(tmpIndex++, "RE01-001");
if(currency != null){
ps.setObject(tmpIndex++, currency.getCode());
ps.setObject(tmpIndex++, currency.getName());
ps.setObject(tmpIndex++, currency.getRegisteredCapital());
}else{
ps.setObject(tmpIndex++, null);
ps.setObject(tmpIndex++, null);
ps.setObject(tmpIndex++, null);
}
// 4.执行语句
ps.execute();
}
conn.commit();
}catch(Exception e){
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(null, ps, conn);
}
}
/**
* 根據經營範圍獲取企業行業產業分類
* @param businessScope
* @return
*/
public static IndustryInfo getEnterpriseIndustry(String businessScope){
if(StringUtils.isBlank(businessScope)){
return null;
}
for(String key : infos.keySet()){
if(businessScope.contains(key)){
return infos.get(key).get(0);
}
}
return null;
}
/**
* 根据企业类型获取企业类型编码
* @param enterpriseTypeName
* @return
*/
public static String getEnterpriseTypeCode(String enterpriseTypeName){
if(StringUtils.isBlank(enterpriseTypeName)){
return null;
}
for(Entry<String, String> entry : enterpriseTypes.entrySet()){
String typeName = entry.getValue();
if(enterpriseTypeName.startsWith(typeName)){
return entry.getKey();
}
for(int i = typeName.length()-1; i >0; i--){
if(enterpriseTypeName.startsWith(typeName.substring(0,i))){
return entry.getKey();
}
}
if(enterpriseTypeName.contains(typeName)){
return entry.getKey();
}
}
return null;
}
public static Currency getCurrency(String registeredCapital){
if(StringUtils.isBlank(registeredCapital)){
return null;
}
boolean flag = false;
Currency c = new Currency();
for(Entry<String,String> entry : currencys.entrySet()){
String name = entry.getValue();
if(registeredCapital.contains(name)){
registeredCapital = registeredCapital.replace(name, "");
flag = true;
}
if(!flag){
for(int i = name.length()-1; i >0; i--){
String tmp = name.substring(0,i);
if(registeredCapital.contains(tmp)){
registeredCapital = registeredCapital.replace(tmp, "");
flag = true;
break;
}
}
}
if(flag){
c.setCode(entry.getKey());
c.setName(name);
c.setRegisteredCapital(registeredCapital.trim());
break;
}
}
return c;
}
/**
* 根据地址获取省-市-县-街道地址
* @param address
* @param provNameT
* @param cityNameT
* @param countryNameT
* @return
*/
public static EnterpriseAddressInfo getEnterpriseCountry(String address,String provNameT,String cityNameT,String countryNameT){
String provName = null;
String provId = null;
String countryName = null;
String countryId = null;
String cityName = null;
String cityId = null;
boolean hasProv = false;
boolean hasCity = false;
boolean hasCountry = false;
if(StringUtils.isBlank(address)){
return null;
}
for(CountryMapping cm : cms){
if(StringUtils.isNotBlank(provNameT) &&(cm.getProvName().contains(provNameT) || provNameT.contains(cm.getProvName()))){
hasProv = true;
}
address = address.trim();
//获取省
if(!hasProv && address.startsWith(getShortProvName(cm.getProvName()))){
hasProv = true;
}
if(hasProv){
provId = cm.getProvId();
provName = cm.getProvName();
address = address.replaceAll(getShortProvName(cm.getProvName()).concat("省"), "");
address = address.replaceAll(getShortProvName(cm.getProvName()).concat("市"), "");
address = address.replaceAll(cm.getProvName(), "");
break;
}
}
for(CountryMapping cm : cms){
if(StringUtils.isNotBlank(cityNameT) && (cm.getCityName().contains(cityNameT) || cityNameT.contains(cm.getCityName()))){
hasCity = true;
}
address = address.trim();
if(!hasCity && address.startsWith(getShortCityName(cm.getCityName()))){
hasCity = true;
}
if(hasCity){
address = address.replaceAll(getShortCityName(cm.getCityName()).concat("市"), "");
address = address.replaceAll(getShortCityName(cm.getCityName()).concat("区"), "");
address = address.replaceAll(cm.getCityName(), "");
cityId = cm.getCityId();
cityName = cm.getCityName();
if(!hasProv){
provId = cm.getProvId();
provName = cm.getProvName();
hasProv = true;
}
break;
}
}
for(CountryMapping cm : cms){
if(StringUtils.isNotBlank(countryNameT) &&StringUtils.isNotBlank(cm.getCountryName()) && (cm.getCountryName().contains(countryNameT) || countryNameT.contains(cm.getCountryName()))){
hasCountry = true;
}
//获取县
address = address.trim();
if(!hasCountry && StringUtils.isNotBlank(cm.getCountryName()) && address.startsWith(getShortCountryName(cm.getCountryName()))){
hasCountry = true;
}
if(hasCountry){
address = address.replaceAll(getShortCountryName(cm.getCountryName()).concat("县"), "");
address = address.replaceAll(getShortCountryName(cm.getCountryName()).concat("区"), "");
address = address.replaceAll(cm.getCountryName(), "");
countryId = cm.getCountryId();
countryName = cm.getCountryName();
if(!hasProv){
provId = cm.getProvId();
provName = cm.getProvName();
hasProv = true;
}
if(!hasCity){
cityId = cm.getCityId();
cityName = cm.getCityName();
hasCity = true;
}
break;
}
}
EnterpriseAddressInfo eai = new EnterpriseAddressInfo();
eai.setProvId(provId);
eai.setProvName(provName);
eai.setCityId(cityId);
eai.setCityName(cityName);
eai.setCountryId(countryId);
eai.setCountryName(countryName);
eai.setAddress(address.trim());
return eai;
}
public static String getShortProvName(String provName){
if(StringUtils.isBlank(provName)){
return null;
}
if(provName.length() == 2){
return provName;
}
return provName.replace("省", "").replace("市", "");
}
public static String getShortCityName(String cityName){
if(StringUtils.isBlank(cityName)){
return null;
}
if(cityName.length() == 2){
return cityName;
}
return cityName.replace("市", "").replace("区", "");
}
public static String getShortCountryName(String countryName){
if(StringUtils.isBlank(countryName)){
return null;
}
if(countryName.length() == 2){
return countryName;
}
return countryName.replace("县", "").replace("区", "");
}
/**
* 获取所有企业类型
* @return
*/
public static Map<String,String> getEnterpriseTypes(){
Map<String,String> result = new HashMap<String, String>();
String sql = "select *from cme_treedictionary_info where pid = 'FE01'";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String code = rs.getString("dkey");
String name = rs.getString("dvalue");
result.put(code, name);
}
}catch(Exception e){
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(rs, ps, conn);
}
return result;
}
public static Map<String,String> getCurrencys(){
Map<String,String> result = new HashMap<String, String>();
String sql = "select *from cme_treedictionary_info where pid = 'CT01'";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String code = rs.getString("dkey");
String name = rs.getString("dvalue");
result.put(code, name);
}
}catch(Exception e){
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(rs, ps, conn);
}
return result;
}
/**
* 获取地区（省-市-县）
* @return
*/
public static List<CountryMapping> getCountryMappings(){
List<CountryMapping> result = new ArrayList<CountryMapping>();
String sql = "select *from t_country_mapping";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String provId = rs.getString("provId");
String provName = rs.getString("provName");
String cityId = rs.getString("cityId");
String cityName = rs.getString("cityName");
String countryId = rs.getString("countryId");
String countryName = rs.getString("countryName");
CountryMapping cm = new CountryMapping();
cm.setProvId(provId);
cm.setProvName(provName);
cm.setCityId(cityId);
cm.setCityName(cityName);
cm.setCountryId(countryId);
cm.setCountryName(countryName);
result.add(cm);
}
}catch(Exception e){
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(rs, ps, conn);
}
return result;
}
/**
* 获取行业分类
* @return
*/
public static Map<String, List<IndustryInfo>> getIndustryInfos(){
Map<String,List<IndustryInfo>> result = new HashMap<String, List<IndustryInfo>>();
String sql = "select *from cme_industry_import";
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 2.建立连接
conn = JdbcUtils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
String code = rs.getString("code");
String name = rs.getString("name");
String desc = rs.getString("description");
IndustryInfo info = new IndustryInfo();
info.setCode(code);
info.setName(name);
if(StringUtils.isNotBlank(desc)){
String[] keys = desc.split(",");
for(String key : keys){
if(result.get(key) != null){
result.get(key).add(info);
}else{
List<IndustryInfo> infos = new ArrayList<IndustryInfo>();
infos.add(info);
result.put(key, infos);
}
}
}
}
}catch(Exception e){
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(rs, ps, conn);
}
return result;
}
public static int getTotalPage(int totalCount,int pageSize){
return (totalCount + pageSize - 1)/pageSize;
}
public static int getFirstResult(int pageNo,int pageSize){
return (pageNo -1) * pageSize;
}
}
浙公网安备 33010602011771号