2021-01-22

import com.alibaba.fastjson.JSONObject;
import com.csvreader.CsvWriter;
import com.wind.apparatus.common.PathConstant;
import com.wind.apparatus.utils.ExcelUtils;
import com.wind.apparatus.utils.HttpGetUtils;
import org.openqa.selenium.WebDriver;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class test3 {
    public static void main(String[] args) {
        String PATH = "";

        List<Map<String, String>> mapList = ExcelUtils.readExcelContent(PATH + "中文名.xlsx", 0);
        for (Map<String, String> mapItem : mapList) {
            String company = mapItem.get("f1");
            try {
                String companyName = getCompanyName(company);
                mapItem.put("f2", companyName);
                Thread.sleep(2500);
            } catch (Exception e) {
                e.printStackTrace();
                getCompanyName(company);
            }
        }
        ExcelUtils.createExcel(mapList, "getDiseaseExcelHead", "1", "企查查.xlsx");
    }

    public static String getCompanyName(String company) {
        String s1 = HttpGetUtils.encode(company);
        boolean isOk = true;
        String result = "";
        while (isOk) {
            try {
                result = HttpGetUtils.get("https://sp0.tianyancha.com/search/suggestV2.json?key=" + s1);
                Thread.sleep(1000);
                isOk = false;
            } catch (Exception e) {
                isOk = true;
            }
        }
        JSONObject json_test = JSONObject.parseObject(result);
        List data = (List) json_test.get("data");
        if (data.size() > 0 && (!data.isEmpty())) {
            Map<String, String> companyData = (Map<String, String>) data.get(0);
            String companyName = companyData.get("comName");
            System.out.println("companyName:" + companyName);
            return company;
        }
        return "";
    }
}

 

import com.csvreader.CsvWriter;
import com.wind.apparatus.common.PathConstant;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Method;
import java.nio.charset.Charset;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by hank on 2021/1/12.
 */
@Slf4j
public class ExcelUtils {

    // 定义一个CSV路径
    static String csvFilePath = "";
    static CsvWriter csvWriter= new CsvWriter(csvFilePath, ',', Charset.forName("UTF-8"));
    /**
     * 读取excel文件内容,文件需要含有表头，如果没有表头请以f1,f2,f3......填充
     *
     * @param filePath 文件路径
     * @param sheetAt  表格
     * @return List<Map < String, String>>
     */
    public static List<Map<String, String>> readExcelContent(String filePath, Integer sheetAt) {
        List<Map<String, String>> mapList = new ArrayList<>();
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            //延迟解析比率
            ZipSecureFile.setMinInflateRatio(-1.0d);
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = workbook.getSheetAt(sheetAt);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i <= rows; i++) {
                //此行不为空行
                if (sheet.getRow(i) != null) {
                    Map<String, String> map = new HashMap<>();
                    int columns = sheet.getRow(i).getPhysicalNumberOfCells();
                    for (int j = 0; j < columns; j++) {
                        map.put("f" + (j + 1), getString(sheet.getRow(i).getCell(j)).trim());
                    }
                    mapList.add(map);
                }
            }
        } catch (Exception e) {
            log.error("service:ExcelUtils,method:readExcelContent,message:读取文件出现异常");
        }
        return mapList;
    }

    /**
     * 把单元格的内容转为字符串
     *
     * @param xssfCell 单元格
     * @return String
     */
    public static String getString(XSSFCell xssfCell) {
        if (xssfCell == null) {
            return "";
        }
        if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {
            DecimalFormat df = new DecimalFormat("0");
            return df.format(xssfCell.getNumericCellValue());
        } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 创建含有表头的excel
     *
     * @param workbook  表格
     * @param sheetName 表格名称
     * @param headList  表头字符串列表
     * @return XSSFWorkbook
     */
    public static XSSFWorkbook getWorkbook(XSSFWorkbook workbook, String sheetName, List<String> headList) {
        XSSFSheet sheet = workbook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        for (int i = 0; i < headList.size(); i++) {
            XSSFCell cell = titleRow.createCell(i);
            cell.setCellValue(headList.get(i));
        }
        return workbook;
    }

    /**
     * 将需要输出的内容填充到excel并保存到本地
     *
     * @param workbook     表格
     * @param sheetName    表格名称
     * @param path         输出到本地的路径
     * @param excelMapList 需要输出的内容
     */
    public static void writeExcelContent(XSSFWorkbook workbook, String sheetName, String path, List<Map<String, String>> excelMapList) {
        // 延迟解析比率
        ZipSecureFile.setMinInflateRatio(-1.0d);
        XSSFSheet sheet = workbook.getSheet(sheetName);
        for (int i = 1; i <= excelMapList.size(); i++) {
            XSSFRow row = sheet.createRow(i);
            Map<String, String> map = excelMapList.get(i - 1);
            for (int j = 0; j < map.size(); j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(map.get("f" + (j + 1)));
            }
        }
        //用输出流写到excel
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(path);
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            log.error("service:ExcelUtils,method:writeExcelContent,message:写入excel发生异常");
        }
    }

    /**
     * 将excelMapList内容生成excel
     *
     * @param excelMapList        待输出的内容
     * @param excelHeadMethodName 获取excel表头的方法名称
     * @param sheetName           sheet名称
     * @param excelName           excel名称
     */
    public static void createExcel(List<Map<String, String>> excelMapList, String excelHeadMethodName, String sheetName, String excelName) {
        try {
            //通过反射获取excel表头
            Class clazz = Class.forName("com.wind.apparatus.utils.ExcelHead");
            ExcelHead excelHead = (ExcelHead) clazz.newInstance();
            Method method = clazz.getDeclaredMethod(excelHeadMethodName);
            List<String> headList = (List<String>) method.invoke(excelHead);
            //创建含有表头的excel
            XSSFWorkbook workbook = new XSSFWorkbook();
            workbook = ExcelUtils.getWorkbook(workbook, sheetName, headList);
            //将excelMapList内容写入Excel
            String excelPath = PathConstant.basePath + excelName;
            ExcelUtils.writeExcelContent(workbook, sheetName, excelPath, excelMapList);
        } catch (Exception e) {
            log.error("service:ExcelUtils,method:createExcel,message:将excelMapList内容生成excel出现异常");
        }
    }

    /**
     * 将excelMapList内容生成excel,多sheet情况
     *
     * @param workbook            表格
     * @param excelMapList        待输出的内容
     * @param excelHeadMethodName 获取excel表头的方法名称
     * @param sheetName           sheet名称
     * @param excelName           excel名称
     */
    public static void createExcel(List<Map<String, String>> excelMapList, XSSFWorkbook workbook, String excelHeadMethodName, String sheetName, String excelName) {
        try {
            //通过反射获取excel表头
            Class clazz = Class.forName("com.wind.medicineSpider.utils.ExcelHead");
            ExcelHead excelHead = (ExcelHead) clazz.newInstance();
            Method method = clazz.getDeclaredMethod(excelHeadMethodName);
            List<String> headList = (List<String>) method.invoke(excelHead);
            //创建含有表头的excel
            workbook = ExcelUtils.getWorkbook(workbook, sheetName, headList);
            //将excelMapList内容写入Excel
            String excelPath = PathConstant.basePath + excelName;
            ExcelUtils.writeExcelContent(workbook, sheetName, excelPath, excelMapList);
        } catch (Exception e) {
            log.error("service:ExcelUtils,method:createExcel,message:将excelMapList内容生成excel出现异常");
        }
    }


    public static CsvWriter createCSV() {
        try {
            // 写表头
            String[] csvHeaders = {"f1", "f2"};
            csvWriter.writeRecord(csvHeaders);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return csvWriter;
    }

    public static void writeCSV(CsvWriter csvWriter, String[] csvContent) {
        // 定义一个CSV路径
        try {
            // 写内容
            csvWriter.writeRecord(csvContent);
            csvWriter.close();
            System.out.println("--------CSV文件已经写入--------");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

 

 

import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLContext;
import javax.net.ssl.TrustManager;
import javax.net.ssl.X509TrustManager;
import java.io.*;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.security.KeyManagementException;
import java.security.NoSuchAlgorithmException;
import java.security.cert.CertificateException;
import java.security.cert.X509Certificate;
import java.util.Map;

public class HttpGetUtils {
    protected static final int SOCKET_TIMEOUT = 10000; // 10S
    protected static final String GET = "GET";

    public static String get(String host) {
        try {
            // 设置SSLContext
            SSLContext sslcontext = SSLContext.getInstance("TLS");
            sslcontext.init(null, new TrustManager[]{myX509TrustManager}, null);

            String sendUrl = host;

            // System.out.println("URL:" + sendUrl);

            URL uri = new URL(sendUrl); // 创建URL对象
            HttpURLConnection conn = (HttpURLConnection) uri.openConnection();
            if (conn instanceof HttpsURLConnection) {
                ((HttpsURLConnection) conn).setSSLSocketFactory(sslcontext.getSocketFactory());
            }

            conn.setConnectTimeout(SOCKET_TIMEOUT); // 设置相应超时
            conn.setRequestMethod(GET);
            conn.setRequestProperty("User-Agent","Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4021.2 Safari/537.36");
            int statusCode = conn.getResponseCode();
            if (statusCode != HttpURLConnection.HTTP_OK) {
                System.out.println("Http错误码：" + statusCode);
            }

            // 读取服务器的数据
            InputStream is = conn.getInputStream();
            BufferedReader br = new BufferedReader(new InputStreamReader(is));
            StringBuilder builder = new StringBuilder();
            String line = null;
            while ((line = br.readLine()) != null) {
                builder.append(line);
            }

            String text = builder.toString();

            close(br); // 关闭数据流
            close(is); // 关闭数据流
            conn.disconnect(); // 断开连接

            return text;
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (KeyManagementException e) {
            e.printStackTrace();
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
        }

        return null;
    }

    public static String getUrlWithQueryString(String url, Map<String, String> params) {
        if (params == null) {
            return url;
        }

        StringBuilder builder = new StringBuilder(url);
        if (url.contains("?")) {
            builder.append("&");
        } else {
            builder.append("?");
        }

        int i = 0;
        for (String key : params.keySet()) {
            String value = params.get(key);
            if (value == null) { // 过滤空的key
                continue;
            }

            if (i != 0) {
                builder.append('&');
            }

            builder.append(key);
            builder.append('=');
            builder.append(encode(value));

            i++;
        }

        return builder.toString();
    }

    protected static void close(Closeable closeable) {
        if (closeable != null) {
            try {
                closeable.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 对输入的字符串进行URL编码, 即转换为%20这种形式
     *
     * @param input 原文
     * @return URL编码. 如果编码失败, 则返回原文
     */
    public static String encode(String input) {
        if (input == null) {
            return "";
        }

        try {
            return URLEncoder.encode(input, "utf-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

        return input;
    }

    private static TrustManager myX509TrustManager = new X509TrustManager() {

        @Override
        public X509Certificate[] getAcceptedIssuers() {
            return null;
        }

        @Override
        public void checkServerTrusted(X509Certificate[] chain, String authType) throws CertificateException {
        }

        @Override
        public void checkClientTrusted(X509Certificate[] chain, String authType) throws CertificateException {
        }
    };
}