# -*- 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*ncount=0x1=all_content1[i]y1=all_content2[i]j=0while 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+=1j+=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'%countf.write(text)'''