I wrote some simple examples about handling excel with python in article Read Excel Table With Python.
Now let’s deal with a more complex task, to combine two different tables based on a common column.
![](https://www.weiy.city/wp-content/uploads/2019/08/1.png)
![](https://www.weiy.city/wp-content/uploads/2019/08/2-300x234.png)
Sheet1
Above two tables have the common column, 编号,the number of rows in sheet0 is more than 200, so it’s disgusting to copy and paste tables and create a new sheet manually. We need a better solution to get things done.
Python is a powerful tool to handle trivial stuff in daily life, we can use it to complete this task, generate the following result sheet.
![](https://www.weiy.city/wp-content/uploads/2019/08/3.png)
The python library openpyxl is very convenient to read/write xlsx/xlsm files, we will use it on this occasion, more implement details are in the following code snippet.
#coding=UTF-8
import openpyxl
fileName = "/Users/weiyang/Desktop/table1.xlsx"
resultSheetName = 'Sheet2'
wb = openpyxl.load_workbook( fileName )
sheet0 = wb.get_sheet_by_name( 'Sheet0' )
sheet1 = wb.get_sheet_by_name( 'Sheet1' )
if resultSheetName in wb.get_sheet_names():
wb.remove_sheet( wb.get_sheet_by_name( resultSheetName ) )
sheet2 = wb.create_sheet( resultSheetName )
print openpyxl.__version__
print "size of sheet0 is:", sheet0.max_row, sheet0.max_column
print "size of sheet0 is:", sheet1.max_row, sheet1.max_column
# read info in sheet0
families = {}
for row in range( 8, sheet0.max_row+1 ):
if sheet0.cell( row, 1 ).value != "" and sheet0.cell( row, 1).value != None :
key = sheet0.cell( row, 1 ).value
family = []
person = []
for col in range(2, 7):
person.append( sheet0.cell( row, col ).value )
family.append( person )
row = row + 1
while sheet0.cell( row, 1 ).value == None and sheet0.cell( row, 2 ).value != None:
person = []
for col in range(2, 6):
person.append( sheet0.cell( row, col ).value )
family.append( person )
row = row + 1
row = row - 1
families[ key ] = family
# read info in sheet1
numberInfo = []
for row in range( 2, sheet1.max_row+1 ):
info = []
for col in range( 1, 4 ):
info.append( sheet1.cell( row, col ).value )
numberInfo.append( info )
#numberInfo[ sheet1.cell( row, 3 ).value ] = info
# write keys in sheet2
Keys = []
for col in range(1, 4):
Keys.append( sheet1.cell( 1, col ).value )
for col in range(1, 7):
Keys.append( sheet0.cell( 7, col ).value )
for col in range(1, Keys.__len__()):
sheet2.cell( 1, col ).value = Keys[col]
# write values in sheet2
sheet2Row = 2
for row in range( 0, numberInfo.__len__() ):
info = numberInfo[row]
key = info[2]
family = families[key]
#print family
for col in range( 1, 4 ):
sheet2.cell( sheet2Row, col ).value = info[col-1]
for i in range( 0, family.__len__() ):
person = family[i]
rowInSheet2 = sheet2[sheet2Row]
for index in range(0, person.__len__()):
rowInSheet2[3+index].value = person[index]
sheet2Row = sheet2Row+1
wb.save( fileName )