packagecom.cme.core;importjava.io.File;importjava.io.FileInputStream;importjava.io.FileNotFoundException;importjava.io.IOException;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importjava.util.Map.Entry;importjava.util.UUID;importorg.apache.commons.lang.StringUtils;importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellType;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importcom.cme.core.entity.CountryMapping;importcom.cme.core.entity.Currency;importcom.cme.core.entity.EnterpriseAddressInfo;importcom.cme.core.entity.IndustryInfo;importcom.cme.utils.JdbcUtils;/*** Hello world!
**/
public classApp {//产业行业分类
private static Map> infos = new HashMap>();//企业类型
private static Map enterpriseTypes = new HashMap();//城市
private static List cms = new ArrayList();//币种
private static Map currencys = new HashMap();static{
infos=getIndustryInfos();
enterpriseTypes=getEnterpriseTypes();
cms=getCountryMappings();
currencys=getCurrencys();
}public static voidmain(String[] args) {long beginTime =System.currentTimeMillis();
exec("F:/企业/企业：常山亮/21-02.xlsx");//文件地址*********************************************************************************************
long endTime =System.currentTimeMillis();
System.out.println(endTime-beginTime);
}/*** 递归读取excel文件
*
*@parampathName*/
public static voidexec(String pathName) {
File file= newFile(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文件
*
*@paramfile*/
public static voidreadExcel(File file) {try{
Workbook workBook= null;if (file.getName().endsWith(".xls")) {
workBook= new HSSFWorkbook(newFileInputStream(file));
}else if (file.getName().endsWith(".xlsx")) {
workBook= new XSSFWorkbook(newFileInputStream(file));
}int numberOfSheets =workBook.getNumberOfSheets();//一次读取3000条数据
int pageSize = 3000;//依次导入每个sheet里面的数据
for (int i = 0; i < 1; 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 = 58;//Excel列数****************************************************************************************************************************//int celNum = header.getPhysicalNumberOfCells();
List> datas = null;
List data = null;for (int j = 1; j <= totalPage; j++) {
datas= new ArrayList>();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();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();
}
}/*** 导入数据
*
*@paramdatas*/
public static void importData(List>datas) {
String column= "id, enterprise_name, enterprise_code, enterprise_import_code, enterprise_nationality_code, industry_code, divide, enterprise_type, user_level,"
+ " enterprise_introduce_text, enterprise_introduce_image, enterprise_registration,address_registration, registered_capital,currency, technical_capacity,"
+ " enterprise_logo, business_photo, achievements_and_honors, enterprise_licence, qualification_level, ebbody, address, business_scope, agree_ally,"
+ " create_time, create_user, last_update_time, last_update_user, abroad, imported, status, deleted, credit_rating, service_area, service_domain,"
+ " qualification_level_img, labels, isshow, isrecommend, sort_no";
StringBuffer sb= new StringBuffer("insert into pf_enterprise_info(").append(column).append(") values (?");
String[] columns= column.split(",");for (int i = 2; i <= columns.length - 8; i++) {if (i == 11) {sb.append(",null");continue;}//enterprise_introduce_image
if (i == 20) {sb.append(",null");continue;}//enterprise_licence
if (i == 22) {sb.append(",null");continue;}//ebbody
if (i == 23) {sb.append(",null");continue;}//address
if (i == 25) {sb.append(",1");continue;}//agree_ally
if (i == 26) {sb.append(",null");continue;}//create_time
if (i == 27) {sb.append(",null");continue;}//create_user
if (i == 28) {sb.append(",null");continue;}//last_update_time
if (i == 29) {sb.append(",null");continue;}//last_update_user
if (i == 30) {sb.append(",0");continue;}//abroad
if (i == 31) {sb.append(",1");continue;}//imported
if (i == 32) {sb.append(",0");continue;}//status
if (i == 33) {sb.append(",0");continue;}//deleted
sb.append(",?");
}
sb.append(",null");//credit_rating
sb.append(",null");//service_area
sb.append(",null");//service_domain
sb.append(",null");//qualification_level_img
sb.append(",1,1,1,0);");
String column2= "id, enterprise_id, enterprise_code, enterprise_office, address_office, contact_person, phone_num, contact_num, fax,"
+ " email, facebook, linkedin, twitter, microBlog, website, wechat, qq, zip_code, ismaster, isdel";
StringBuffer sb2= new StringBuffer("insert into pf_enterprise_contact_info(").append(column2).append(") values (?");;
String[] columns2= column2.split(",");for (int i = 2; i <= columns2.length - 2; i++) {
sb2.append(",?");
}
sb2.append(",0,0);");//ismaster isdel
String sql=sb.toString();
String sql2=sb2.toString();
System.out.println(sql);
System.out.println(sql2);
Connection conn= null;
PreparedStatement ps= null;
PreparedStatement ps2= null;try{//2.建立连接
conn =JdbcUtils.getConnection();
conn.setAutoCommit(false);for (int i = 0; i < datas.size(); i++) {//3.创建语句
ps=conn.prepareStatement(sql);
List data =datas.get(i);
String id= UUID.randomUUID().toString().replace("-", "");
ps.setObject(1, id);
Object obj= null;for (int j = 0; j < data.size(); j++) {
obj=data.get(j);if (j == 4) {//enterprise_name
ps.setObject(2, obj);continue;
}if (j == 0) {//enterprise_code
ps.setObject(3, obj);/*****************************
ps.setObject(23, obj);
****************************/
continue;
}if (j == 5) {//enterprise_import_code
ps.setObject(4, obj);continue;
}if (j == 12) {//enterprise_nationality_code
ps.setObject(5, obj);continue;
}if (j == 3) {//industry_code
ps.setObject(6, obj);continue;
}if (j == 9) {//divide
ps.setObject(7, obj);continue;
}if (j == 8) {//enterprise_type
ps.setObject(8, obj);continue;
}if (j == 23) {//user_level
ps.setObject(9, obj);continue;
}if (j == 24) {//enterprise_introduce_text
ps.setObject(10, obj);continue;
}if (j == 19) {//enterprise_registration
ps.setObject(11, obj);continue;
}if (j == 20) {//address_registration
ps.setObject(12, obj);continue;
}if (j == 13) {//registered_capital
ps.setObject(13, obj);continue;
}if (j == 15) {//currency
ps.setObject(14, obj);continue;
}if (j == 21) {//technical_capacity
ps.setObject(15, obj);continue;
}if (j == 6) {//enterprise_logo
ps.setObject(16, obj);continue;
}if (j == 25) {//business_photo
ps.setObject(17, obj);continue;
}if (j == 27) {//achievements_and_honors
ps.setObject(18, obj);continue;
}if (j == 26) {//qualification_level
ps.setObject(19, obj);continue;
}if (j == 10) {//business_scope
ps.setObject(20, obj);continue;
}/********************************************pf_enterprise_contact_info********************************************/
/********************************************************************************
if (j == 39) {//enterprise_office
ps.setObject(24, obj);
continue;
}
if (j == 40) {//address_office
ps.setObject(25, obj);
continue;
}
if (j == 29) {//contact_person
ps.setObject(26, obj);
continue;
}
if (j == 31) {//phone_num
ps.setObject(27, obj);
continue;
}
if (j == 30) {//contact_num
ps.setObject(28, obj);
continue;
}
if (j == 33) {//fax
ps.setObject(29, obj);
continue;
}
if (j == 32) {//email
ps.setObject(30, obj);
continue;
}
if (j == 41) {//facebook
ps.setObject(31, obj);
continue;
}
if (j == 42) {//linkedin
ps.setObject(32, obj);
continue;
}
if (j == 43) {//twitter
ps.setObject(33, obj);
continue;
}
if (j == 45) {//microBlog
ps.setObject(34, obj);
continue;
}
if (j == 34) {//website
ps.setObject(35, obj);
continue;
}
if (j == 46) {//wechat
ps.setObject(36, obj);
continue;
}
if (j == 47) {//qq
ps.setObject(37, obj);
continue;
}
if (j == 35) {//zip_code
ps.setObject(38, obj);
continue;
}
********************************************************************************/}//4.执行语句
ps.execute();
ps.close();
obj= null;
ps2=conn.prepareStatement(sql2);
ps2.setObject(1, UUID.randomUUID().toString().replace("-", ""));
ps2.setObject(2, id);for (int j = 0; j < data.size(); j++) {
obj=data.get(j);if (j == 0) {//enterprise_code
ps2.setObject(3, obj);continue;
}if (j == 38) {//enterprise_office
ps2.setObject(4, obj);continue;
}if (j == 39) {//address_office
ps2.setObject(5, obj);continue;
}if (j == 28) {//contact_person
ps2.setObject(6, obj);continue;
}if (j == 30) {//phone_num
ps2.setObject(7, obj);continue;
}if (j == 29) {//contact_num
ps2.setObject(8, obj);continue;
}if (j == 32) {//fax
ps2.setObject(9, obj);continue;
}if (j == 31) {//email
ps2.setObject(10, obj);continue;
}if (j == 40) {//facebook
ps2.setObject(11, obj);continue;
}if (j == 41) {//linkedin
ps2.setObject(12, obj);continue;
}if (j == 42) {//twitter
ps2.setObject(13, obj);continue;
}if (j == 44) {//microBlog
ps2.setObject(14, obj);continue;
}if (j == 33) {//website
ps2.setObject(15, obj);continue;
}if (j == 45) {//wechat
ps2.setObject(16, obj);continue;
}if (j == 46) {//qq
ps2.setObject(17, obj);continue;
}if (j == 34) {//zip_code
ps2.setObject(18, obj);continue;
}
}
ps2.execute();
ps2.close();
}
conn.commit();
}catch(Exception e) {
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(null, ps, conn);
JdbcUtils.free(null, ps2, conn);
}
}/*** 根据经营范围获取企业行业产业分类
*
*@parambusinessScope
*@return
*/
public staticIndustryInfo 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;
}/*** 根据企业类型获取企业类型编码
*
*@paramenterpriseTypeName
*@return
*/
public staticString getEnterpriseTypeCode(String enterpriseTypeName) {if(StringUtils.isBlank(enterpriseTypeName)) {return null;
}for (Entryentry : enterpriseTypes.entrySet()) {
String typeName=entry.getValue();if(enterpriseTypeName.startsWith(typeName)) {returnentry.getKey();
}for (int i = typeName.length() - 1; i > 0; i--) {if (enterpriseTypeName.startsWith(typeName.substring(0, i))) {returnentry.getKey();
}
}if(enterpriseTypeName.contains(typeName)) {returnentry.getKey();
}
}return null;
}public staticCurrency getCurrency(String registeredCapital) {if(StringUtils.isBlank(registeredCapital)) {return null;
}boolean flag = false;
Currency c= newCurrency();for (Entryentry : 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;
}
}returnc;
}/*** 根据地址获取省-市-县-街道地址
*
*@paramaddress
*@paramprovNameT
*@paramcityNameT
*@paramcountryNameT
*@return
*/
public staticEnterpriseAddressInfo 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= newEnterpriseAddressInfo();
eai.setProvId(provId);
eai.setProvName(provName);
eai.setCityId(cityId);
eai.setCityName(cityName);
eai.setCountryId(countryId);
eai.setCountryName(countryName);
eai.setAddress(address.trim());returneai;
}public staticString getShortProvName(String provName) {if(StringUtils.isBlank(provName)) {return null;
}if (provName.length() == 2) {returnprovName;
}return provName.replace("省", "").replace("市", "");
}public staticString getShortCityName(String cityName) {if(StringUtils.isBlank(cityName)) {return null;
}if (cityName.length() == 2) {returncityName;
}return cityName.replace("市", "").replace("区", "");
}public staticString getShortCountryName(String countryName) {if(StringUtils.isBlank(countryName)) {return null;
}if (countryName.length() == 2) {returncountryName;
}return countryName.replace("县", "").replace("区", "");
}/*** 获取所有企业类型
*
*@return
*/
public static MapgetEnterpriseTypes() {
Map result = new HashMap();
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);
}returnresult;
}public static MapgetCurrencys() {
Map result = new HashMap();
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);
}returnresult;
}/*** 获取地区(省-市-县)
*
*@return
*/
public static ListgetCountryMappings() {
List result = new ArrayList();
String sql= "select *from base_area";
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= newCountryMapping();
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);
}returnresult;
}/*** 获取行业分类
*
*@return
*/
public static Map>getIndustryInfos() {
Map> result = new HashMap>();
String sql= "select * from sys_industry_info";
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= newIndustryInfo();
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 infos = new ArrayList();
infos.add(info);
result.put(key, infos);
}
}
}
}
}catch(Exception e) {
JdbcUtils.rollback(conn);
e.printStackTrace();
}finally{
JdbcUtils.free(rs, ps, conn);
}returnresult;
}public static int getTotalPage(int totalCount, intpageSize) {return (totalCount + pageSize - 1) /pageSize;
}public static int getFirstResult(int pageNo, intpageSize) {return (pageNo - 1) *pageSize;
}
}
475
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
