How to define existing worksheet in Python

Hi,

I am trying to define existing worksheet but I am not able to do it.
I have copied code from below URL for reference.
Instead of adding a new worksheet I want to use existing worksheet from the workbook.

How do I edit worksheet = workbook.add_worksheet() this line.
I am trying to provide Sheet1 here but not able to do it.

Can anyone please help me this.

# import xlsxwriter module
import xlsxwriter

# Workbook() takes one, non-optional, argument
# which is the filename that we want to create.
workbook = xlsxwriter.Workbook( 'chart_bar.xlsx' )

# The workbook object is then used to add new
# worksheet via the add_worksheet() method.
worksheet = workbook.add_worksheet()

# Create a new Format object to formats cells
# in worksheets using add_format() method .

# here we create bold format object .
bold = workbook.add_format({ 'bold' : 1 })

# create a data list .
headings = [ 'Number' , 'Batch 1' , 'Batch 2' ]

data = [
[ 2 , 3 , 4 , 5 , 6 , 7 ],
[ 80 , 80 , 100 , 60 , 50 , 100 ],
[ 60 , 50 , 60 , 20 , 10 , 20 ],
]

# Write a row of data starting from 'A1'
# with bold format .
worksheet.write_row( 'A1' , headings, bold)

# Write a column of data starting from
# 'A2', 'B2', 'C2' respectively .
worksheet.write_column( 'A2' , data[ 0 ])
worksheet.write_column( 'B2' , data[ 1 ])
worksheet.write_column( 'C2' , data[ 2 ])

# Create a chart object that can be added
# to a worksheet using add_chart() method.

# here we create a bar chart object .
chart1 = workbook.add_chart({ 'type' : 'bar' })

# Add a data series to a chart
# using add_series method.

# Configure the first series.
# = Sheet1 !$A$1 is equivalent to ['Sheet1', 0, 0].
chart1.add_series({
'name' : '= Sheet1 !$B$1' ,
'categories' : '= Sheet1 !$A$2:$A$7' ,
'values' : '= Sheet1 !$B$2:$B$7' ,
})

# Configure a second series.
# Note use of alternative syntax to define ranges.
# [sheetname, first_row, first_col, last_row, last_col].
chart1.add_series({
'name' : [ 'Sheet1' , 0 , 2 ],
'categories' : [ 'Sheet1' , 1 , 0 , 6 , 0 ],
'values' : [ 'Sheet1' , 1 , 2 , 6 , 2 ],
})

# Add a chart title
chart1.set_title ({ 'name' : 'Results of data analysis' })

# Add x-axis label
chart1.set_x_axis({ 'name' : 'Test number' })

# Add y-axis label
chart1.set_y_axis({ 'name' : 'Data length (mm)' })

# Set an Excel chart style.
chart1.set_style( 11 )

# add chart to the worksheet
# the top-left corner of a chart
# is anchored to cell E2 .
worksheet.insert_chart( 'E2' , chart1)

# Finally, close the Excel file
# via the close() method.
workbook.close()

I might not fully understand the problem but the code works for me. There are warnings about the addressing columns using !$B1… syntax style, but it seems to work.
From what I understand - a workbook has no worksheets, so you add one and then use it.
See the screenshot:

1 Like

Hi @pymc , thanks lot for the help.
worksheet = workbook.get_worksheet_by_name(‘Sheet1’)
This line worked for me. Have a nice day ahead. :slight_smile: