I want edit one cell in a Excel-XLSX file with OpenPyXL in Python and then save this changed file into a xlsx with other name. But i get the following error: Traceback (most recent call last):
File "E:\ExcelPythonTest\workspace\ExcelTest\ExcelTest.py", line 14, in <module>
book.save(path2)
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\workbook\workbook.py", line 294, in save
save_workbook(self, filename)
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 270, in save_workbook
writer.save(filename)
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 251, in save
self.write_data()
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\excel.py", line 94, in write_data
archive.writestr(ARC_WORKBOOK, write_workbook(self.workbook))
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\writer\workbook.py", line 121, in write_workbook
defined_names.append(name)
File "C:\Python27\lib\site-packages\openpyxl-2.4.0b1-py2.7.egg\openpyxl\workbook\defined_name.py", line 191, in append
raise ValueError("""DefinedName with the same name and scope already exists""")
ValueError: DefinedName with the same name and scope already exists
My Code:
import os
import openpyxl
# Change this to suit
path = r'E:\ExcelPythonTest\example.xlsx'
path2 = r'E:\ExcelPythonTest\examle_UHAHAHAHAHHA.xlsx'
book = openpyxl.load_workbook( path )
sheet = book.get_sheet_by_name('Dictionary')
print sheet['G8'].value
sheet['G8'] = 3.18
print sheet['G8'].value
book.save(path2)
Can anyone help me?
P.S: i have tried to edit the same .xlsx with code in C and c-library libxl . So i think the xlsx file doen't have problem.
I have tried the following code without any editing of xlsx:
import os
import openpyxl
# Change this to suit
path = r'E:\ExcelPythonTest\Dt_RECORD_SHPS_RM_ROStruct.xlsx'
path2 = r'E:\ExcelPythonTest\Dt_RECORD_SHPS_RM_ROStruct_UHAHAHAHAHHA.xlsx'
book = openpyxl.load_workbook( path )
book.save(path2)
But the same error was occurred.
I battled with the same problem at work until figuring out the solution. You only need to add one line to your existing code. I don't exactly know what the definedName objects are good for but the resulting file is not broken (unlike without the line) and doesn't seem to have any visual differences caused by clearing the definedNames list.
import os
import openpyxl
# Change this to suit
path = r'E:\ExcelPythonTest\example.xlsx'
path2 = r'E:\ExcelPythonTest\examle_UHAHAHAHAHHA.xlsx'
book = openpyxl.load_workbook( path )
sheet = book.get_sheet_by_name('Dictionary')
print sheet['G8'].value
sheet['G8'] = 3.18
print sheet['G8'].value
# This fixes it
book.defined_names.definedName = []
book.save(path2)