Copying excel rows via python
Question: I basically have one excel file with has the following entries in a particular
sheet

    
    
    row[0][0]=hello
    
    row[1][0]=bye
    
    row[2][0]=hi
    

I want to copy these three rows into the number of rows present in the
original sheet, so that the modified sheet has the following.

    
    
    row[0][0]=hello
    
    row[1][0]=bye
    
    row[2][0]=hi
    
    row[3][0]=hello
    
    row[4][0]=bye
    
    row[5][0]=hi
    
    row[6][0]=hello
    
    row[7][0]=bye
    
    row[8][0]=hi
    

My code is the below.

    
    
    from xlutils.copy import copy
    from xlrd import open_workbook
    import xlwt
    
    book=open_workbook("/Users/tusharbakaya/Desktop/test.xlsx")
    book1=copy(book)
    sheet=book.sheet_by_name('Sheet1')
    sheet1=book1.get_sheet(0)
    totalrows=sheet.nrows
    print totalrows
    for j in range(0,totalrows):
        for i in range(0,totalrows):
            row=sheet.cell_value(i,0)
            sheet1.write(j+totalrows,0,row)
            i+=1
        j+=totalrows
    book1.save("/Users/tusharbakaya/Desktop/test1.xls") 
    

However, I get the following output

    
    
    row[0][0]=hello
    
    row[1][0]=bye
    
    row[2][0]=hi
    
    row[3][0]=hello
    
    row[4][0]=hello
    
    row[5][0]=hello
    
    row[6][0]=hello
    

Not sure why this is happening.


Answer: The issue is that even if you change the loop variable `j` inside the loop,
that change would be overwritten with the next value from `range()` function.

An example to show this -

    
    
    >>> for i in range(10):
    ...     print(i)
    ...     i = 1000
    ...
    0
    1
    2
    3
    4
    5
    6
    7
    8
    9
    

But your code is depending on this to happen, instead you should try to set to
i+j*totalrows (everytime) and start j from `1`. And the logic inside the inner
loop is also a bit wrong, you should depend on `i` variable as well to set the
values in new workbook.

So the code would become -

    
    
    from xlutils.copy import copy
    from xlrd import open_workbook
    import xlwt
    
    book=open_workbook("/Users/tusharbakaya/Desktop/test.xlsx")
    book1=copy(book)
    sheet=book.sheet_by_name('Sheet1')
    sheet1=book1.get_sheet(0)
    totalrows=sheet.nrows
    print totalrows
    for j in range(0,totalrows):
        for i in range(0,totalrows):
            row=sheet.cell_value(i,0)
            sheet1.write(i+(j*totalrows),0,row)
    book1.save("/Users/tusharbakaya/Desktop/test1.xls") 
    

or you can use a `while` loop instead of `for` loop and

Example for while loop -

    
    
    from xlutils.copy import copy
    from xlrd import open_workbook
    import xlwt
    
    book=open_workbook("/Users/tusharbakaya/Desktop/test.xlsx")
    book1=copy(book)
    sheet=book.sheet_by_name('Sheet1')
    sheet1=book1.get_sheet(0)
    totalrows=sheet.nrows
    print totalrows
    j,k = 0, 0
    while k < totalrows
        for i in range(0,totalrows):
            row=sheet.cell_value(i,0)
            sheet1.write(i+j,0,row)
        j+=totalrows
        k += 1
    book1.save("/Users/tusharbakaya/Desktop/test1.xls")
    

