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_免费', '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.wb = Workbook()
self.ws = self.wb.create_sheet("%s" % name, 0) # 插入到最开始的位置
# 读取原表数据
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.wb.save('C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\%s.xlsx' % name)
# 写入新表中
# 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()
###############################################################################################################################################################################################################################################
# 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
import os
class Excel():
def __init__(self, filepath, xl_file):
self.filepath = filepath
self.xl_file = xl_file
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_付费', 'u '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.wb = Workbook()
self.ws = self.wb.create_sheet("%s" % name, 0) # 插入到最开始的位置
# 读取原表数据
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.wb.save('C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\%s.xlsx' % name)
# 写入新表中
# self.save_excel()
# 调用 添加定义名称
# self.add_name()
def add_name(self, xl_file_path, name): ##### 添加定义名称
# xl_file = 'C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\_excel_data_new.xlsx'
xl_file = xl_file_path + name + '.xlsx'
print(xl_file)
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(1): # 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()
def select_excel(self):
# filepath = "C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\read_excel\\"
# filepath = self.filepath
filepath = self.xl_file
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\\_excel_数据"
# xl_savefile = "C:\\Users\\Administrator\\Desktop\\spider_excel\\excel_new\\_excel_数据" + ".xlsx" # 新名 excel_add_name
######## 一个excel文件里多个表 单独 拆分开
# self.read_excel(filepath, name)
######## 单独拆开的表添加定义名称
self.add_name(filepath, name)
if __name__ == '__main__':
# import os
# filepath = "C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\read_excel\\"
# pathDir = os.listdir(filepath)
filepath = "C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\read_excel\\"
xl_file_path = 'C:\\Users\\Administrator\\Desktop\\all_spider_excel_05\\new_excel\\'
excel = Excel(filepath, xl_file_path)
excel.select_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()
浙公网安备 33010602011771号