Read and Write data to excel sheet using python

Home / Uncategorized / Read and Write data to excel sheet using python

Question:
I need to Read excel file as shown below, the excel sheet will have the following columns (IP address, System Name, System value, phone number, credentials) these are the headers. And they have the following entries.
IP address|System Name |System value |phone number|credentials|
100.3.32.2|win32 |e2 |1234567890 |admin |
100.3.32.1|linux |e3 |1234567891 |User |

I need to read the excel sheet including header and respective values under the header, and i should also write new data under respective header, and it should not overwrite if any new header is added between the already existing header(if a new column is added)

new value and column added using python.
IP address|Name|System Name|System value|phone number|credentials|
100.3.32.2| |win32 |e2 |1234567890 |admin |
100.3.32.1| |linux |e3 |1234567891 |User |
100.2.2.3.| |win64 |e4 |9929123445 |admin2 |
100.2.2.3.| |win64 |e4 |9929123445 |admin2 |

This is the python code that i have written. I am not able to write the data into excel sheet based on the header name, the data is getting written based on the column number, if a new column is added in between other columns, the value is written to that column, suppose if Name is added between IP address and System Name, System Name values are added into Name, which should not happen it should write data into corresponding header i.e Column. Can you please provide me input as to how to go about this.
for ip,cred,sn,st,phn in List:w_sheet.write(row,0,ip)
w_sheet.write(row,2, cred )
w_sheet.write(row,3, sn)
w_sheet.write(row,5,st)
w_sheet.write(row,4,phn)
row += 1

here 0,2,3,5,4 are the column number, data is written to excel sheet based on the column number. from xlrd import open_workbook from xlutils.copy import copy
book = open_workbook(r’TestData_New_modified.xls’)
sheet = book.sheet_by_index(1)
wb= copy(book)
w_sheet = wb.get_sheet(1)
dict_list=[]
keys = [sheet.cell(0, col_index).value for col_index in range(sheet.ncols)]
for i in keys:print i
#w_sheet.write(sheet.nrows,i,List[i])
d={}
for row_index in range(1, sheet.nrows):d = {keys[col_index]: sheet.cell(row_index, col_index).value
for col_index in xrange(sheet.ncols)}
dict_list.append(d)
for le in range(0,len(dict_list)):data=dict_list[le][i]
print data
List = (
[‘100.98.23.22’, ‘admin2’, ‘SN1′,’e1′,’9900’],
[‘100.33.44.5’, ‘admin3’, ‘SN3′,’e3′,’8888’],
[‘100.44.55.66’, ‘admin4′,’SN4′,’e4′,’9999’])
row= sheet.nrows
for ip,cred,sn,st,phn in List:w_sheet.write(row,0,ip)
w_sheet.write(row,2, cred )
w_sheet.write(row,3, sn)
w_sheet.write(row,5,st)
w_sheet.write(row,4,phn)
row += 1
#To write data into excel sheet
wb.save(‘TestData_New_modified.xls’)


Answer:

Read more

Leave a Reply

Your email address will not be published. Required fields are marked *