excel数据提取与写入

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")