Java导入Excel文件最新版

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 java.util.UUID;

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 App {
    // 产业行业分类
    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("F:/企业/企业：陈祥龙/08-childcare services-企业主表.xlsx");// 文件地址*********************************************************************************************
        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 < 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<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) {
        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_info2(").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_info2(").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);
            ps = conn.prepareStatement(sql);
            ps2 = conn.prepareStatement(sql2);
            
            for (int i = 0; i < datas.size(); i++) {
                // 3.创建语句          
                
                //ps = conn.prepareStatement(sql);
                List<Object> 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);
                        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;
                    } 
                }                
                // 4.执行语句
                ps.addBatch();
                //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.addBatch();
                
                if (i % 10000 == 0){
                     ps.executeBatch();
                     ps2.executeBatch();
                     conn.commit();
                 }
            }
            ps.executeBatch();
            ps2.executeBatch();
            conn.commit();
            
            ps.close();
            ps2.close();
            
        } catch (Exception e) {
            JdbcUtils.rollback(conn);
            e.printStackTrace();
        } finally {
            JdbcUtils.free(null, ps, conn);
            JdbcUtils.free(null, ps2, 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 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 = 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 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 = 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;
    }

}