How to get Python script to write to existing sheet

Question

I am writing a Python script and stuck on one of the early steps. I am opening an existing sheet and want to add two columns so I have used this:

#import the writer
import xlwt
#import the reader
import xlrd
#open the sussex results spreadsheet
book = xlrd.open_workbook('sussex.xlsx')
#open the first sheet
first_sheet = book.sheet_by_index(0)
#print the values in the second column of the first sheet
print first_sheet.col_values(1)
#in cell 0,0 (first cell of the first row) write "NIF"
sheet1.write(0, 6, "NIF")
#in cell 0,0 (first cell of the first row) write "Points scored"
sheet1.write(0, 6, "Points scored")

On line 12 I get an error:

name 'sheet1' is not defined

How do I define sheet 1 within the sheet that I have already opened?

kosist · Answer 1 · 2018-11-15T11:33:26.023

1

I guess you need to have something like sheet1 = book.sheet_by_index(0); because now sheet1 is not defined. Also, document is opened using xlrd which is reader, and you need to write there values - so document should be opened also using xlwt.

edited Nov 15 '18 at 11:33

answered Nov 15 '18 at 11:27

kosist

2,868
2
17
30

Thanks - I think that may have worked but I now get ''Sheet' object has no attribute 'write' – J4G Nov 15 '18 at 11:30
Please, check the update answer - you open document using `xlrd`, so that's why `sheet` object does not have `write` method. – kosist Nov 15 '18 at 11:34
that makes sense - how would i open it using xlwt too? – J4G Nov 15 '18 at 12:07
Check this example - https://stackoverflow.com/a/40022335/6917446, it is described there... – kosist Nov 15 '18 at 12:27
so i need to read from sussex.xlsx and write to a new worksheet? – J4G Nov 15 '18 at 12:30
No, just read the example carefully: `wb_in = xlrd.open_workbook(r"input.xls")` - open document; `sheet_name = wb_in.sheet_names()[0]` - get first sheet`s name from the document; `ws_in = wb_in.sheet_by_name(sheet_name)` - select sheet by name; `wb_out = xlwt.Workbook()` - init xlwt object, to be able to write data; `ws_out = wb_out.add_sheet(sheet_name)` - initialize 1st sheet from the previously opened document, for writing. – kosist Nov 15 '18 at 12:37
i am officially lost, i added this from lines 8 - 14: 'wb_in = xlrd.open_workbook('sussex.xlsx') #get first sheet's name from the document sheet_name = wb_in.sheet_names()[0] #select sheet by name ws_in = wb_in.sheet_by_name(sheet_name) #init xlwt object, to be able to write data wb_out = xlwt.Workbook #initialise first sheet from the previously opened document, for writing ws_out = wb_out.add_sheet(sheet_name)' Error: 'TypeError: unbound method add_sheet() must be called with Workbook instance as first argument (got unicode instance instead)' – J4G Nov 15 '18 at 13:19

score 0 · Accepted Answer · edited Dec 09 '18 at 09:33

sheet1 is never declared. Try changing it to

#import the writer
import xlwt
#import the reader
import xlrd
#open the sussex results spreadsheet
book = xlrd.open_workbook('sussex.xlsx')
#open the first sheet
first_sheet = book.sheet_by_index(0)
#print the values in the second column of the first sheet
print first_sheet.col_values(1)
#in cell 0,0 (first cell of the first row) write "NIF"
first_sheet.write(0, 6, "NIF")
#in cell 0,0 (first cell of the first row) write "Points scored"
first_sheet.write(0, 6, "Points scored")

edit: You could also use Pandas to read and write to Excel:

import pandas as pd
import numpy as np
#open the sussex results spreadsheet, first sheet is used automatically
df = pd.read_excel('sussex.xlsx')

#print the values in the second column of the first sheet
print(df.iloc[:,1])

#Create column 'NIF'
df['NIF'] = np.nan #I don't know what you want to do with this column, so I filled it with NaN's
#in cell 0,7 (first cell of the first row) write "Points scored"
df['Points scored'] = np.nan #I don't know what you want to do with this column, so I filled it with NaN's
<.... Do whatever calculations you want with NIF and Points scored ...> 
# Write output
df.to_excel('sussex.xlsx')

thanks @Niels, that seems to have worked but I have now encountered an issue where it can't write - how would i open the workbook to write to it? — J4G, Nov 15 '18 at 12:06
`Sheet object has no attribute 'write'` is what I get when i try to write in the sheet. — Toma Tomov, Oct 06 '19 at 13:22

How to get Python script to write to existing sheet

2 Answers2

Linked