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!

score 3 · Accepted Answer · edited May 05 '15 at 08:33

3

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/

edited May 05 '15 at 08:33

NorthCat

9,643
16
47
50

answered Jan 07 '13 at 02:10

Shawn Zhang

1,680
1
12
21

But I am using openpyxl already. Any idea how this can be easily done using openpyxl? – DanielY Jan 07 '13 at 02:25
@Daniel the docs in website is quite clear (http://packages.python.org/openpyxl/tutorial.html#accessing-one-cell), 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 ? – Shawn Zhang Jan 07 '13 at 02:29
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! – DanielY Jan 07 '13 at 03:09
I'm away from my pc .will let you know after 24 hours – Shawn Zhang Jan 07 '13 at 11:52
@Daniel as I go through docs of xlrd and openpyxl , not found formatting topics . this link http://stackoverflow.com/questions/7991209/identifying-excel-sheet-cell-color-code-using-xlrd-package ,bring me some info on formatting, but no further result useful for your requirement, sorry for that . – Shawn Zhang Jan 08 '13 at 06:25
Thanks! I personally tried it out too. xlrd+xlwt seems to be preserving the formatting much better than openpyxl. – DanielY Jan 10 '13 at 02:49
@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 – Shawn Zhang Jan 10 '13 at 03:05
@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. – rose May 03 '17 at 07:08

score 0 · Answer 2 · answered May 05 '15 at 08:45

For "xls" files it's possible to use the xlutils package. It's currently not possible to copy objects between workbooks in openpyxl due to the structure of the Excel format: there are lots of dependencies all over the place that need to be managed. It is, therefore, the responsibility of client code to copy everything required manually. If time permits we might try and port some of the xlutils functionality to openpyxl.

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

2 Answers2