How to copy a row of Excel sheet to another sheet using Python

Question

I want to compare the value of a given column at each row against another value, and if the values are equal, I want to copy the whole row to another spreadsheet.

How can I do this using Python?

THANKS!

Solution

pls refer to python excel library xlrd(for excel reading)/xlwt(for excel writing) http://www.python-excel.org/

for example(reading)(from this):

import xlrd

fname = "sample.xls"
bk = xlrd.open_workbook(fname)
shxrange = range(bk.nsheets)
try:
    sh = bk.sheet_by_name("Sheet1")
except:
    print "no sheet in %s named Sheet1" % fname
    return None
nrows = sh.nrows
ncols = sh.ncols
print "nrows %d, ncols %d" % (nrows,ncols)

cell_value = sh.cell_value(1,1)
print cell_value

row_list = []
for i in range(1,nrows):
    row_data = sh.row_values(i)
    row_list.append(row_data)

if you are handling with Excel 2007 , then use openpyxl : http://packages.python.org/openpyxl/

Comments

[DanielY]

But I am using openpyxl already. Any idea how this can be easily done using openpyxl?

[Shawn Zhang]

@Daniel the docs in website is quite clear (packages.python.org/openpyxl/tutorial.html#accessing-one-ce‌​ll), read one row form workbook1 and save into a listA, then read one row from workbook2 and save into a listB, then compare each element of them. or can you be more specific issue that you can't figure out ?

[DanielY]

Thanks for the xlrd code. I tried openpyxl but the format is not well-preserved (e.g., '123.456000' is read as 123.456 instead). Do you think xlrd could preserve the format better? Thanks!

[Shawn Zhang]

I'm away from my pc .will let you know after 24 hours

[Shawn Zhang]

@Daniel as I go through docs of xlrd and openpyxl , not found formatting topics . this link stackoverflow.com/questions/7991209/… ,bring me some info on formatting, but no further result useful for your requirement, sorry for that .

[DanielY]

Thanks! I personally tried it out too. xlrd+xlwt seems to be preserving the formatting much better than openpyxl.

[Shawn Zhang]

@Daniel it is really hassle to do this in Python, did you consider manipulate spreadsheet via Python Win Com. I didn't dive it much ,but believe that COM will save some troubles

[rose]

@Daniel : But xlrd and xlwt won't be able to handle data in .xlsx .I have smae requirement ,But want to process on .xlsx file only. If any pointers Please let me know.