How to plot in new sheet without deleting the existing sheet in Python?-CodePudding

I am ploting excel charts in Python. As you can see in the following code, after I completing the first sheet "Sheet 1", I am trying to create a new sheet and plot in it. However, everytime I create 'Sheet 2', 'Sheet 1' is gone. How can I plot in a new sheet without deleting the old one?

import xlsxwriter
############# Create Sheet 1 and plot in it #############
file_source = 'file.xlsx'
workbook = xlsxwriter.Workbook(file_source) 
worksheet = workbook.add_worksheet('Sheet 1')
bold = workbook.add_format({'bold': 1}) 
headings = ['Animal Count','Human Count']
data = [[1,4,7],[6,3,8]]
worksheet.write_row('A1', headings, bold)

worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1]) 
chart1 = workbook.add_chart({'type': 'line'})
for i in range(1):
    chart1.add_series({ 
        'name':       ['Sheet 1', 0, i],
        'categories': ['Sheet 1', 1, 0, 1, 0], 
        'values':     ['Sheet 1', 1, i, 1, i], })
worksheet.insert_chart('J2', chart1, {'x_scale': 2, 'y_scale': 1.5})
workbook.close()


############# Create another Sheet 2 and plot in it #############
workbook = xlsxwriter.Workbook(file_source) 
worksheet = workbook.add_worksheet('Sheet 2')
bold = workbook.add_format({'bold': 1}) 
headings = ['Animal Count','Human Count']
data = [[1,4,7],[6,3,8]]
worksheet.write_row('A1', headings, bold)

worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1]) 
chart1 = workbook.add_chart({'type': 'line'})
for i in range(1):
    chart1.add_series({ 
        'name':       ['Sheet 2', 0, i],
        'categories': ['Sheet 2', 1, 0, 1, 0], 
        'values':     ['Sheet 2', 1, i, 1, i], })
worksheet.insert_chart('J2', chart1, {'x_scale': 2, 'y_scale': 1.5})
workbook.close()

CodePudding user response：

I think the issue is when you close one workbook object and then create another with same name.

#### bunch of other lines of code ####
worksheet.insert_chart('J2', chart1, {'x_scale': 2, 'y_scale': 1.5})
workbook.close() # <<<< you close the workbook here! get rid of this line


############# Create another Sheet 2 and plot in it #############
# VV Then you create another workbook here, overwriting the first one VV
workbook = xlsxwriter.Workbook(file_source) # <<<< get rid of this line!
# vvvvYou want to keep this line if you want create another sheet in the same work book.
worksheet = workbook.add_worksheet('Sheet 2')

CodePudding user response：

Possible duplication
When you call

workbook = xlsxwriter.Workbook(file_source)

You overwrite the file you already created. Close it only when you wrote everything there

import xlsxwriter
############# Create Sheet 1 and plot in it #############
file_source = 'file.xlsx'
workbook = xlsxwriter.Workbook(file_source) 
worksheet = workbook.add_worksheet('Sheet 1')
bold = workbook.add_format({'bold': 1}) 
headings = ['Animal Count','Human Count']
data = [[1,4,7],[6,3,8]]
worksheet.write_row('A1', headings, bold)

worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1]) 
chart1 = workbook.add_chart({'type': 'line'})
for i in range(1):
    chart1.add_series({ 
        'name':       ['Sheet 1', 0, i],
        'categories': ['Sheet 1', 1, 0, 1, 0], 
        'values':     ['Sheet 1', 1, i, 1, i], })
worksheet.insert_chart('J2', chart1, {'x_scale': 2, 'y_scale': 1.5})

############# Create another Sheet 2 and plot in it #############
worksheet = workbook.add_worksheet('Sheet 2')
bold = workbook.add_format({'bold': 1}) 
headings = ['Animal Count','Human Count']
data = [[1,4,7],[6,3,8]]
worksheet.write_row('A1', headings, bold)

worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1]) 
chart1 = workbook.add_chart({'type': 'line'})
for i in range(1):
    chart1.add_series({ 
        'name':       ['Sheet 2', 0, i],
        'categories': ['Sheet 2', 1, 0, 1, 0], 
        'values':     ['Sheet 2', 1, i, 1, i], })
worksheet.insert_chart('J2', chart1, {'x_scale': 2, 'y_scale': 1.5})
workbook.close()