[已解决]《极客Python之效率革命》，openpyxl模块问题

shenshuai

大佬们，我现在这里有两个excel文件：Bundles.xslx,  Inventory.xslx
我现在想把两个文件合并到一起，并且保留两个excel的所有数据，就像这样

我之前用了openpyxl模块，但是不管怎么复制，其中一个excel文件都会覆盖掉另外一个,以下是代码

1. import openpyxl
   
2. wb = openpyxl.load_workbook('/Users/shenshuaichen/Desktop/Bundles.xlsx')
   
3. wb1 = openpyxl.load_workbook('/Users/shenshuaichen/Desktop/Inventory (1).xlsx')
   
4. 
   
5. ws = wb['KitComponentResults']
   
6. ws1 = wb1['DailyInventoryReportResults']
   
7. 
   
8. for i, row in enumerate(ws.iter_rows()):
   
9.     for j, cell in enumerate(row):
   
10.         ws1.cell(row = i + 1, column=j+1, value = cell.value)
    
11. wb1.save('/Users/shenshuaichen/Desktop/Inventory.xlsx')
    

复制代码

最佳答案

月排行榜 / 总排行榜

XiaoPaiShen

2019-10-26 23:58:43

假设两个原始的Excel文件中，都是只有一个表单‘Sheet’

1. import openpyxl
   
2. 
   
3. def create_sheet(sheet_name, workbook):
   
4.     # 创建表单
   
5.     if sheet_name in workbook.sheetnames:
   
6.         del workbook[sheet_name]
   
7. 
   
8.     ws_sheet = workbook.create_sheet(title = sheet_name)
   
9.     return ws_sheet
   
10. 
    
11. def write_content(ws_source, ws_target):
    
12.     content = []
    
13.     for row in ws_source.iter_rows():
    
14.         row_con = [row[i].value for i in range(len(row))]
    
15.         content.append(row_con)
    
16. 
    
17.     for row in content:
    
18.         ws_target.append(row)                  
    
19. 
    
20. def main(file_name):
    
21.     bundles_file_name = 'Bundles.xlsx'
    
22.     inventory_file_name = 'Inventory.xlsx'
    
23. 
    
24.     wb_bun = openpyxl.load_workbook(bundles_file_name)
    
25.     ws_bun = wb_bun['Sheet']
    
26. 
    
27.     wb_inv = openpyxl.load_workbook(inventory_file_name)
    
28.     ws_inv = wb_inv['Sheet']
    
29. 
    
30.     wb_target = openpyxl.Workbook()
    
31.     ws_bundles = create_sheet('Bundles', wb_target)
    
32.     ws_inventory = create_sheet('Inventory', wb_target)
    
33. 
    
34.     write_content(ws_bun, ws_bundles)
    
35.     write_content(ws_inv, ws_inventory)
    
36. 
    
37.     wb_target.save(file_name)
    
38. 
    
39. 
    
40. if __name__ == '__main__':
    
41.     file_name = 'mergedfile.xlsx'
    
42.     main(file_name)

复制代码

跳转到最佳答案楼层

XiaoPaiShen

假设两个原始的Excel文件中，都是只有一个表单‘Sheet’

1. import openpyxl
   
2. 
   
3. def create_sheet(sheet_name, workbook):
   
4.     # 创建表单
   
5.     if sheet_name in workbook.sheetnames:
   
6.         del workbook[sheet_name]
   
7. 
   
8.     ws_sheet = workbook.create_sheet(title = sheet_name)
   
9.     return ws_sheet
   
10. 
    
11. def write_content(ws_source, ws_target):
    
12.     content = []
    
13.     for row in ws_source.iter_rows():
    
14.         row_con = [row[i].value for i in range(len(row))]
    
15.         content.append(row_con)
    
16. 
    
17.     for row in content:
    
18.         ws_target.append(row)                  
    
19. 
    
20. def main(file_name):
    
21.     bundles_file_name = 'Bundles.xlsx'
    
22.     inventory_file_name = 'Inventory.xlsx'
    
23. 
    
24.     wb_bun = openpyxl.load_workbook(bundles_file_name)
    
25.     ws_bun = wb_bun['Sheet']
    
26. 
    
27.     wb_inv = openpyxl.load_workbook(inventory_file_name)
    
28.     ws_inv = wb_inv['Sheet']
    
29. 
    
30.     wb_target = openpyxl.Workbook()
    
31.     ws_bundles = create_sheet('Bundles', wb_target)
    
32.     ws_inventory = create_sheet('Inventory', wb_target)
    
33. 
    
34.     write_content(ws_bun, ws_bundles)
    
35.     write_content(ws_inv, ws_inventory)
    
36. 
    
37.     wb_target.save(file_name)
    
38. 
    
39. 
    
40. if __name__ == '__main__':
    
41.     file_name = 'mergedfile.xlsx'
    
42.     main(file_name)

复制代码

jay_jiang

应该将两个文件写到一个新建的工作簿里

shenshuai

jay_jiang 发表于 2019-10-27 00:58
应该将两个文件写到一个新建的工作簿里

应该怎么写呀？

xsb198526

厉害，我要是也会就好了

zltzlt

https://www.cnblogs.com/zlhuan/p/8011304.html

python万

python万

shenshuai

谢谢大佬们