python excel 读写，定义名称

# coding=utf-8
import re
from openpyxl import load_workbook
from openpyxl import Workbook    # 保存为ｅｘｃｅｌ表格
from openpyxl.workbook.defined_name import DefinedName
# from openpyxl.utils import absolute_coordinate, quote_sheetname


class Excel():
    def __init__(self):
        self.wb = Workbook()  # 第一次创建新 excel文件时用
        # self.ws = self.wb.active
        # self.wb = load_workbook('C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\_excel_data_new.xlsx')    # 已有excel文件，往里添加表时用

    def save_excel(self):
        self.wb.save('C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\_excel_data_new.xlsx')

    def read_excel(self, filepath, name):
        file_name = filepath + name + '.xlsx'
        wb = load_workbook(file_name)
        # ws = wb.active    # # 等同于  ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表
        sheet_names = wb.sheetnames  # ----- ['uv_付费', 'uv_定费用', 'Sheet']
        print('-----', sheet_names)
        for k in range(len(sheet_names)):
            ws = wb[sheet_names[k]]  # index为0为第一张表
            name = sheet_names[k]
            print(name , '---', k)
            # 创建新表写入数据
            self.ws = self.wb.create_sheet("%s" % name, k)  # 插入到最开始的位置

            # 读取原表数据
            l_num = ws.max_column  # 最大列
            h_num = ws.max_row  # 最大行
            for i in range(1, h_num + 1):  # h_num + 1
                line_data = []
                for j in range(1, l_num + 1):
                    lie_title = ws.cell(row=i, column=j)  # 每列标题  <Cell '固定费用'.A1>
                    # print(lie_title)
                    a_value = lie_title.value  # 此单元格里边的值
                    coord = lie_title.coordinate  # 此单元格坐标  A1  B1
                    # print(aa, type(aa))  # class= str
                    # print(coord, type(coord))  # class= str
                    line_data.append(a_value)
                print(line_data)
                # 写入新表中
                self.ws.append(line_data)
        # 写入新表中
        self.save_excel()
        # 调用 添加定义名称
        self.add_name()

    def add_name(self):    #####  添加定义名称
        xl_file = 'C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\_excel_data_new.xlsx'
        wb = load_workbook(xl_file)
        # ws = wb.active    # # 等同于  ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表，默认0读第一个表
        sheet_names = wb.sheetnames
        # print('-----', sheet_name)

        for i in range(len(sheet_names)):
            wb = load_workbook(xl_file)
            # sheet_names = wb.sheetnames
            w_name = sheet_names[i]
            ws = wb[w_name]  #
            l_num = ws.max_column  # 最大列
            h_num = ws.max_row  # 最大行
            # print(ws.max_row)
            # print(ws.max_column)
            a_list = []
            all_lie_list = []
            # wb = Workbook()
            for j in range(1, l_num + 1):  # 列
                lie_list = []
                # for i in range(1, h_num + 1):  # 行
                title = ws.cell(row=1, column=j)  # 每列标题
                name = title.value
                # print(j)
                a = ws.cell(row=1, column=j)
                coord = a.coordinate  # 此单元格坐标  A1  B1
                # print(coord)
                l = re.findall(r'^[A-Z]*', coord)[0]
                h = re.findall(r'[0-9]*$', coord)[0]
                lie_list.append([coord, name])
                all_lie_list.append([l, name])  # 添加  列名（A，B），  每一列第一行的值
            print(all_lie_list)  # [['A', 'SKU'], ['B', 'XL'], ['C', 'freeUV'], ['D', 'changeo'], ['E', 'changeAV'], ['F', 'callchangeo'], ['G', 'callchangeAV'], ['H', 'silencechangeo'], ['I', 'silencechangeAV'], ['J', 'buyingprice']]
            for lie in all_lie_list:
                bb = '%s!$' % w_name + lie[0] + '$1:$' + lie[0] + '$' + str(h_num)
                dname = DefinedName(name='%s' % lie[1])
                dname.value = bb
                wb.defined_names.append(dname)

            wb.save('C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\change_excel\\%s_01.xlsx' % w_name)    # 'C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\change_excel\\%s_01.xlsx' % w_name
            wb.close()


if __name__ == '__main__':
    import os
    filepath = "C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\read_excel\\"
    pathDir = os.listdir(filepath)
    excel = Excel()
    for alldir in pathDir:
        name = alldir.replace('.xlsx', '')
        print(name)
        # a = input('输入excel表格原名 ：')
        # name = 'zidongduqu'
        # f_name = "C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_originally\\_excel_数据"
        # xl_savefile = "C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_new\\_excel_数据" + ".xlsx"  # 新名  excel_add_name
        # excel.read_excel(filepath, name)
        excel.add_name()