import numpy
import matplotlib
from matplotlib import pyplot as plt
from matplotlib.pyplot import MultipleLocator
import openpyxl
from openpyxl import workbook
from openpyxl.styles import Font, colors, Alignment
import os
import operator
#=============================提取台站excel文件中的数据到新的excel中==========================
work_directory = "F:\王子铭文件\土耳其地震\土耳其地震记录\excel type record/U excel/"
file_name = os.listdir(work_directory)
station_code = []
latitude = []
longitude = []
v_30 = []
site_classification = []
epicenter = []
pga = []
for i in range(0,len(file_name)):
path = work_directory + file_name[i]
wb_2 = openpyxl.load_workbook(path)
sheet_name = wb_2.sheetnames
sheet = wb_2[sheet_name[1]]
a = sheet.cell(row=13, column=1).value.lstrip("STATION_CODE: ") + "号"
b = float(sheet.cell(row=15, column=1).value.lstrip("STATION_LATITUDE_DEGREE: "))
c = float(sheet.cell(row=16, column=1).value.lstrip("STATION_LONGITUDE_DEGREE: "))
d = sheet.cell(row=20, column=1).value.lstrip("VS30_M/S: ")
e = sheet.cell(row=21, column=1).value.lstrip("SITE_CLASSIFICATION_EC8:")
f = float(sheet.cell(row=23, column=1).value.lstrip("EPICENTRAL_DISTANCE_KM: "))
g = float(sheet.cell(row=38, column=1).value.lstrip("PGA_CM/S^2: "))
station_code.append(a)
latitude.append(b)
longitude.append(c)
v_30.append(d)
site_classification.append(e)
epicenter.append(f)
pga.append(g)
print(file_name[i]+":",station_code[i],latitude[i],longitude[i],v_30[i],site_classification[i],epicenter[i],pga[i])
# =============================创建一个新的excel文件,将表头和数据写入其中===========================
path_new = "F:\王子铭文件\土耳其地震\土耳其地震记录/sic.xlsx"
wb_1 = openpyxl.load_workbook(path_new)
wb_names = wb_1.sheetnames
sheet_1 = wb_1[wb_names[0]]
biao_tou = ["台站编号", "经度", "纬度", "30m剪切波速(m/s)","场地类别(turkey)", "震中距(km)", "PGA(gal)"]
for i in range(1, len(biao_tou) + 1):
sheet_1.cell(row=1, column=i).value = biao_tou[i - 1]
for h in range(2,len(station_code)+2):
sheet_1.cell(row=h,column=1).value = station_code[h-2]
sheet_1.cell(row=h,column=2).value =latitude[h-2]
sheet_1.cell(row=h, column=3).value = longitude[h-2]
sheet_1.cell(row=h, column=4).value = v_30[h-2]
sheet_1.cell(row=h, column=5).value = site_classification[h-2]
sheet_1.cell(row=h, column=6).value = epicenter[h-2]
sheet_1.cell(row=h, column=7).value = pga[h-2]
wb_1.save("F:\王子铭文件\土耳其地震\土耳其地震记录/sic.xlsx")