原始的文件导入

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;
    }
    
}