python"># -*- coding: utf-8 -*-
"""
Created on Wed Jul  7 14:57:44 2021
@author: Administrator
"""
import xlrd
import math
import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt

#将excel的两列转化为二个列表
worksheet = xlrd.open_workbook(u'C:/Users/Administrator/Desktop/data1.xlsx')
sheet_names= worksheet.sheet_names()
#print(sheet_names)
for sheet_name in sheet_names:
    sheet = worksheet.sheet_by_name(sheet_name)
    rows = sheet.nrows # 获取行数
    cols = sheet.ncols # 获取列数，尽管没用到
    all_content1 = []
    for i in range(1,rows) : #excel第一行是列名，从第二列开始取数
        cell1 = sheet.cell_value(i, 0) # 取第一列列数据
        try:
            all_content1.append(cell1)
        except ValueError as e:
            pass
for sheet_name in sheet_names:
    sheet = worksheet.sheet_by_name(sheet_name)
    rows = sheet.nrows # 获取行数
    cols = sheet.ncols # 获取列数，尽管没用到
    all_content2 = []
    for i in range(1,rows) :
        cell2 = sheet.cell_value(i, 1) # 取第二列列数据
        try:
            all_content2.append(cell2)
        except ValueError as e:
            pass    
length1=len(all_content1) #计算数据列表的长度
data=zip(all_content1,all_content2) #使用zip函数将两个列表打包成一个元组


worksheet1 = xlrd.open_workbook(u'C:/Users/Administrator/Desktop/data2.xlsx')
sheet_names1= worksheet1.sheet_names()

for sheet_name1 in sheet_names1:
    sheet1 = worksheet1.sheet_by_name(sheet_name1)
    rows1 = sheet1.nrows # 获取行数
    cols1 = sheet1.ncols # 获取列数
    all_content3 = []
    for i in range(1,rows1) : #excel第一行是列名，从第二列开始取数
        cell3 = sheet1.cell_value(i, 0) # 取第一列列数据
        try:
            all_content3.append(cell3)
        except ValueError as e:
            pass
for sheet_name2 in sheet_names1:
    sheet2 = worksheet1.sheet_by_name(sheet_name2)
    rows2 = sheet2.nrows # 获取行数
    cols2 = sheet2.ncols # 获取列数，尽管没用到
    all_content4 = []
    for i in range(1,rows2) :
        cell4 = sheet2.cell_value(i, 1) # 取第二列列数据
        try:
            all_content4.append(cell4)
        except ValueError as e:
            pass   
data1=zip(all_content3,all_content4)
'''for each in data1:
    print(each)'''
length2=len(all_content3)#计算事故列表的长度

for i in range(length1):
    t=0.00017966296 #20m对应的经纬度
    n=50 #决定搜索半径为20n米，即方圆1000米内
    D=t*n
    count=0
    x1=all_content1[i]
    y1=all_content2[i]
    j=0
    while j<length2:
        x2=all_content3[j]
        y2=all_content4[j]
        d=math.sqrt(math.pow((x2-x1),2)+math.pow((y2-y1),2)) 
        if d<D:
            count+=1
        j+=1
    #print(i,count)
    print('表1中编号为%d的点方圆%d米内共有%d个表2中的点'%(i+1,20*n,count))
    '''with open('C:/Users/Administrator/Desktop/点数.txt','a',encoding='utf-8') as f:
          text='%d\n'%count
          f.write(text)'''