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