Cannot open the excel file after openpyxl usage

Question

I am writing to an excel file in 2 sheets - sheet1 and geodata.

test_df = DataFrame({'col1':[1,2,3,4], 'col2':['a','b','c','d']})
test2_df = DataFrame({'lat':[89.0, 34.89, 23.56],'lng':[45.6, 67.9, 89.0]}, index = ['Paris','Spain','Barcelona'])

writer = pd.ExcelWriter('./test_book.xlsx', engine = 'xlsxwriter') 
test_df.to_excel(writer, 'sheet1')
test2_df.to_excel(writer, 'geodata')
writer.save()
writer.close() 

Later writing to the same file an another new sheet sheet3 and some additional data to the geodata. When I run the below code block and try to open the excel sheet from the folder, I get an error saying it cannot be opened.

test3_df = DataFrame({'col1':[3,5,6], 'col2':['x','y','k']})
test2_new_df =  DataFrame({'lat':[44.6,38.9],'lng':[35.7,89.3]},index=['US','UK'])

from openpyxl import load_workbook

writer = pd.ExcelWriter('./test_book.xlsx', engine = 'openpyxl')
writer.book = load_workbook('./test_book.xlsx')
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)

test3_df.to_excel(writer,'Sheet3')
test2_new_df.to_excel(writer, 'geodata',header = False, startrow =len(test2_df)+1)
writer.close()

When I run this block of code a second time, then I am able to open the file. But I have my new values related to index 'US' & 'UK' appended twice in geodata. I tried multiple ways but cannot open the file after running this code first time.

Answer 1

You should not import libraries in the middle of your code. Always import at the top of your code. I have copied your code and made one small change of the xlsxwriter being the engine, and I am having no issues with the resulting file.

import openpyxl
from openpyxl import *
from openpyxl.styles import NamedStyle
from openpyxl import load_workbook
import pandas as pd

test_df = pd.DataFrame({'col1':[1,2,3,4], 'col2':['a','b','c','d']})
test2_df = pd.DataFrame({'lat':[89.0, 34.89, 23.56],'lng':[45.6, 67.9, 89.0]}, index = ['Paris','Spain','Barcelona'])

writer = pd.ExcelWriter('./test_book.xlsx', engine = 'openpyxl') #I changed this to openpyxl, from xlsxwriter
test_df.to_excel(writer, 'sheet1')
test2_df.to_excel(writer, 'geodata')
writer.save()
writer.close() 

test3_df = pd.DataFrame({'col1':[3,5,6], 'col2':['x','y','k']})
test2_new_df = pd.DataFrame({'lat':[44.6,38.9],'lng':[35.7,89.3]},index=['US','UK'])

writer = pd.ExcelWriter('./test_book.xlsx', engine = 'openpyxl')
writer.book = load_workbook('./test_book.xlsx')
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)

test3_df.to_excel(writer,'Sheet3')
test2_new_df.to_excel(writer, 'geodata',header = False, startrow =len(test2_df)+1)
writer.close()

Output:

Sheet1:

Geodata:

Sheet3: