文件:excel_defind_names.py
# coding=utf-8
import re
from openpyxl import load_workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.utils import absolute_coordinate, quote_sheetname
from excel_pizhu import pizhu
def f(xl_savefile, a):
aa = pizhu(a)
xl_file = aa + ".xlsx"
print(xl_file)
wb = load_workbook(xl_file)
ws = wb.active
l_num = ws.max_column # 最大列
h_num = ws.max_row # 最大行
print(ws.max_row)
print(ws.max_column)
a_list = []
all_lie_list = []
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 = 'Sheet1!$' + lie[0] + '$1:$' + lie[0] + '$' + str(h_num)
dname = DefinedName(name='%s' % lie[1])
dname.value = bb
wb.defined_names.append(dname)
wb.save(xl_savefile)
wb.close()
if __name__ == '__main__':
import os
filepath = "C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_originally\\"
pathDir = os.listdir(filepath)
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\\" + name
# xl_savefile = "C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_add_name\\" + name + "_01.xlsx" # 新名
# f(xl_savefile, f_name)
#########################################################################################
文件:excel_pizhu.py
# coding=utf-8
import re
from openpyxl import Workbook # 写入
from openpyxl.comments import Comment # 批注
# from openpyxl.workbook.defined_name import DefinedName # 定义名称
from openpyxl import load_workbook # 读取
def pizhu(xl_file):
wb1 = Workbook()
ws1 = wb1.active
# 读取execl
# 默认可读写,若有需要可以指定write_only和read_only为True
# filename = 'zidongduqu'
wb = load_workbook('%s.xlsx' % xl_file)
# 获取工作表--Sheet
# 获得所有sheet的名称
# print(wb.get_sheet_names())
# 根据sheet名字获得sheet
# a_sheet = wb.get_sheet_by_name('Sheet')
# 获得sheet名
# print(a_sheet.title)
# 获得当前正在显示的sheet, 也可以用wb.get_active_sheet()
sheet = wb.active
l_num = sheet.max_column # 最大列
h_num = sheet.max_row # 最大行
# print(sheet.max_row)
# print(sheet.max_column)
all_data = []
one_rows_data = []
for i in range(1, h_num + 1): # h_num + 1
for j in range(1, l_num + 1):
lie_title = sheet.cell(row=i, column=j) # 每列标题
print(lie_title)
aa_value = lie_title.value # 此单元格里边的值 写入新表格
coord = lie_title.coordinate # 此单元格坐标 A1 B1
# print(aa, type(aa)) # class= str
# print(coord, type(coord)) # class= str
# 添加批注
# p = sheet.cell(row=1, column=j)
# pz = p.value # 第一行的值 字段
# comment = Comment('%s' % pz, 'wyang')
# ws1['%s' % coord].comment = comment # 批注
# 写入新表格
# one_rows_data.append(aa)
# ws1['%s' % coord] = aa
ws1['%s' % coord].value = aa_value
# print(one_rows_data)
file_name = xl_file + '___临时表'
# ws1.append(one_rows_data)
wb1.save('%s.xlsx' % file_name)
return file_name
if __name__ == '__main__':
a = 'C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_originally\\api_daily_item_index_d'
pizhu(a)