How to transfer data from one worksheet into another using python in the same workbook?

Question

I want to transfer my data which is in 'Sheet1' into worksheet 'Sheet2'in the same workbook, using python.I have written the script below, but facing an 'IndexError: list index out of range'. I know this is not the best way to go about it. I will appreciate if anyone can guide me with a more efficient way to go about it. I am sharing the Snapshot of excel file below

Can I directly enter 'Sheet1' cell value ---> 'Sheet2' cell value rather than doing 'Sheet1' cell value ---> List---->'Sheet2' cell value?

 import openpyxl  
 wb = openpyxl.load_workbook('C:\Users\laban\Desktop\Par-emails1.xlsx') 
 type(wb)
 wb.get_sheet_names() 
 sheet = wb.get_sheet_by_name('Sheet1') 
 type(sheet)  
 anotherSheet = wb.active
 sheet1 = wb.get_sheet_by_name('Sheet2')
 type(sheet1)
 par=[sheet.cell(row= col, column=1).value for col in range(1, 2450)]
 email_no=[sheet.cell(row= col, column=2).value for col in range(1, 2450)]
 Domain=[sheet.cell(row= col, column=3).value for col in range(1, 2450)]
 email=[sheet.cell(row= col, column=4).value for col in range(1, 2450)]

 for x in range(0,2450):
      if email_no[x]<9:
          sheet1.cell(row= x+1, column=1).value=par[x]
          sheet1.cell(row= x+1, column=2).value=email_no[x]
          sheet1.cell(row= x+1, column=3).value=Domain[x]
          sheet1.cell(row= x+1, column=4).value=email[x]


 wb.save('C:\Users\laban\Desktop\Par-emails1.xlsx')

Answer 1

You can use:

wb = openpyxl.load_workbook('C:/Users/laban/Desktop/Par-emails1.xlsx')
sheet1 = wb.get_sheet_by_name('Sheet1')
sheet2 = wb.get_sheet_by_name('Sheet2')

for i,row in enumerate(sheet1.iter_rows()):
  for j,col in enumerate(row):
    sheet2.cell(row=i+1,column=j+1).value = col.value

Apparently in 2.4 you can do this with one command: Copy whole worksheet with openpyxl

Answer 2

Obviously this is very simplified version from what I am currently using. Here is a code snippet to copy data from sheet1 to sheet2 without any formatting. You dont need to specify max rows , max columns as you can get it using sheet.max_row and sheet.max_columns methods.

from openpyxl.cell import Cell    

max_row = sheet1.max_row       #Get max row of first sheet
max_col = sheet1.max_column    #Get max column of first sheet

for row_num in range(1,max_row + 1):    #Iterate through rows
    for col_num in range(1, max_col + 1):   #Iterate through columns
        _cell1 = sheet1.cell(row=row_num, column=col_num)    
        _cell2 = sheet2.cell(row=row_num, column=col_num)    
        _cell2.value = _cell1.value

Added extra variables for understanding. You can compact at your end.

Answer 3

bernie probably has the best answer here (Copy whole worksheet) but your index error might be coming from:

par=[sheet.cell(row= col, column=1).value for col in range(1, 2450)]

being 1 cell shorter than:

for x in range(0,2450):

Answer 4

def excel_op():

   filename='D://Python//excelread.xlsx'
        sheet_name='Sheet1'
        book = xlrd.open_workbook(str(filename))

        sheet = book.sheet_by_name(sheet_name)
        workbook = xlsxwriter.Workbook('excelwrite.xlsx')
        worksheet = workbook.add_worksheet()

        row_count = int(sheet.nrows)
        col_count = int(sheet.ncols)
        for row in range(0, int(row_count)):
            for col in range(0, int(col_count)):
                worksheet.write(row,col,str(sheet.cell(row, col).value))
        workbook.close()
        del book

Answer 5

 shutil.copy("Source.xlsx", "target.xlsx")