Python简单处理Excel样例

# author: YuJun.Wang
# date: 2020.08.12 20:17
import openpyxl
from openpyxl.styles import PatternFill, colors
import requests
import json
import os
import time
def main():
    try:
        # 打开文件
        wb =  openpyxl.load_workbook("py.xlsx")
        # 选择活动表
        ws = wb.active
        # 单元格颜色
        bgcolor = PatternFill("solid", fgColor="FFC1C1")
        i = 1
        max_rows = ws.max_row
        GFirst = None
        GFlag = 0
        for cell in ws['G']:
            os.system('cls')
            print('正在处理第'+str(i)+'/'+str(max_rows)+'条........')
            if i%100==0:
                print('高德地图限制高并发等待中....')
                wb.save("py.xlsx")
                time.sleep(10)
            cellI = 'I' + str(i)
            cellJ = 'J' + str(i)
            cellH = 'H' + str(i)
            cellK = 'K' + str(i)  
            if cell.value == GFirst and GFlag == 1:
                cellK2 = 'K' + str(i-1)
                ws[cellK]= ws[cellK2].value
                ws[cellK].fill = bgcolor
                GFirst = cell.value
                i = i + 1
                continue
            else:
                GFlag = 0
            GFirst = cell.value
            i = i + 1
            data = None
            if '#' in cell.value:
                continue
            if cell.value is None:
                break
            if not cell.value is None:
                cellValue = cell.value[4:]
                data = search_address(cellValue)
                if data != 0:
                    ws[cellK]= data
                    ws[cellK].fill = bgcolor
                    GFlag = 1
                    continue
            if not ws[cellI].value is None:
                cellValue = ws[cellH].value[2:]+ws[cellI].value
                data = search_address(cellValue)
                if data != 0:
                    ws[cellK]= data
                    ws[cellK].fill = bgcolor
                    GFlag = 1
                    continue
            if ws[cellI].value is None and not ws[cellJ].value is None:
                cellValue = ws[cellH].value[2:] + ws[cellJ].value[1:-1]
                data = search_address(cellValue)
                if data != 0:
                    ws[cellK]= data
                    ws[cellK].fill = bgcolor
                    GFlag = 1
                    continue
    finally:
        wb.save("py.xlsx")
        print('文件处理完成！')
        print(data)

def search_address(data):
    agisurl = 'https://restapi.amap.com/v3/assistant/inputtips?keywords='+data+'&key=自己申请高德地图Key'
    r = requests.get(agisurl)
    decoded = json.loads(r.text)
    if decoded['count'] == '0' :
        return 0
    return decoded['tips'][0]['name']
if __name__ == '__main__':
    main()