在具有相同名称openpyxl的多个工作表中创建命名单元格 [英] Create named cells in multiple sheets with same name openpyxl
问题描述
我正在创建具有多个工作表的excel文件.在每张工作表中,我都需要具有相同定义名称的行和列组.我们可以手动创建它,但是可以通过程序来实现.
I am creating excel file with multiple sheets. In every sheet i need same define name with group of rows and columns.we can create it manually but through program how to achieve this.
下面是代码:
wb = openpyxl.load_workbook(file.xlsx)
shee1 = wb['sheet1']
shee2 = wb['sheet1']
wb.create_named_range('sales', shee1 , '$B$11:$B$35')
wb.create_named_range('sales', shee2 , '$B$11:$B$35')
以下是我得到的错误
文件 "C:\ Users \ 728355 \ AppData \ Local \ Programs \ Python \ Python36 \ lib \ site-packages \ openpyxl \ workbook \ workbook.py", 第319行,位于create_named_range中 self.defined_names.append(defn)文件"C:\ Users \ 728355 \ AppData \ Local \ Programs \ Python \ Python36 \ lib \ site-packages \ openpyxl \ workbook \ defined_name.py", 附录中的第201行 引发ValueError("具有相同名称和范围的DefinedName已存在""))ValueError:具有相同名称和范围的DefinedName 范围已经存在
File "C:\Users\728355\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\workbook\workbook.py", line 319, in create_named_range self.defined_names.append(defn) File "C:\Users\728355\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\workbook\defined_name.py", line 201, in append raise ValueError("""DefinedName with the same name and scope already exists""") ValueError: DefinedName with the same name and scope already exists
推荐答案
我认为命名范围是工作簿的全局范围,而不是每个工作表的局部范围.您收到的错误消息提示:
I think the named ranges are global to the workbook, not local to each sheet. Which is hinted at by the error message you're getting:
具有相同名称和范围的DefinedName已经存在
DefinedName with the same name and scope already exists
所以您必须给这些不同的名字
So you'd have to give those different names
wb = openpyxl.load_workbook(file.xlsx)
sheet1 = wb['sheet1']
sheet2 = wb['sheet2']
wb.create_named_range('sales1', sheet1 , '$B$11:$B$35')
wb.create_named_range('sales2', sheet2 , '$B$11:$B$35')
或更干的解决方案:
wb = openpyxl.load_workbook(file.xlsx)
sheets = []
for sheet_no in range(1,3):
sheets[sheet_no] = wb[f'sheet{sheet_no}'] # note pre python 3.6 you should change f'sheet{sheet_no}' to 'sheet{}'.format(sheet_no)
wb.create_named_range(f'sales{sheet_no}', sheets[sheet_no], '$B$11:$B$35')
这篇关于在具有相同名称openpyxl的多个工作表中创建命名单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!