使用Python语言读写Excel数据demo

最新推荐文章于 2021-12-12 18:55:06 发布

爱做梦真是太好了

最新推荐文章于 2021-12-12 18:55:06 发布

阅读量767

点赞数

CC 4.0 BY-SA版权

分类专栏： Python编程语言

本文链接：https://blog.youkuaiyun.com/github_37412255/article/details/86412830

Python编程语言专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一种使用Python openpyxl库从大型Excel文件中筛选并分割数据的方法。通过读取一个包含特定ID的文本文件，该脚本能够从一个包含13665条记录的Excel文件中筛选出符合ID条件的数据，并将其保存到两个新的Excel文件中：一个包含匹配ID的数据，另一个包含未匹配ID的数据。此过程涉及读取源Excel文件的多个列，将数据与文本文件中的ID进行比较，然后将结果写入新文件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import openpyxl
from openpyxl import load_workbook
                                                                                   
f = open(file='data/10886数据集/最原始数据/电子病历对应病历号from10886.txt', encoding='utf-8')   
list_f = f.readlines()                                                                                 
ll = []                                                                                                
for item in list_f:                                                                                    
    item = item.replace('\n', '')                                                                      
    ll.append(item)                                                                                    
                                                                                                       
wb = load_workbook("data/10886数据集/最原始数据/首次病程记录_抽取_原始数据_13665.xlsx")          
sheet1 = wb.get_sheet_by_name("Sheet1")                                                                

# 读excel                                                                                                   
column_A = sheet1["A"]                                                                                 
column_B = sheet1["B"]                                                                                 
column_C = sheet1["C"]                                                                                 
column_D = sheet1["D"]                                                                                 
column_E = sheet1["E"]                                                                                 
column_F = sheet1["F"]                                                                                 
column_G = sheet1["G"]                                                                                 
column_H = sheet1["H"]                                                                                 
                                                                                                       
filepath1 = 'data/10886数据集/最原始数据/从13665中抽取出的10886条数据.xlsx'                            
filepath2 = 'data/10886数据集/最原始数据/从13665中抽取出的除10886条数据以外的数据.xlsx'                
                                                                                                       
wb1 = openpyxl.Workbook()                                                                              
ws1 = wb1.create_sheet('Sheet1')                                                                       
                                                                                                       
wb2 = openpyxl.Workbook()                                                                              
ws2 = wb2.create_sheet('Sheet1')                                                                       
                                                                                                       
index_ws1 = 1                                                                                          
index_ws2 = 1
# 写excel                                                                                          
for i in range(0, 13666):                                                                              
    print("i :" + str(i) + "...")                                                                      
    if i == 0:                                                                                         
        ws1.cell(row=index_ws1, column=1, value=sheet1["A"][i].value).value                            
        ws1.cell(row=index_ws1, column=2, value=sheet1["B"][i].value).value                            
        ws1.cell(row=index_ws1, column=3, value=sheet1["C"][i].value).value                            
        ws1.cell(row=index_ws1, column=4, value=sheet1["D"][i].value).value                            
        ws1.cell(row=index_ws1, column=5, value=sheet1["E"][i].value).value                            
        ws1.cell(row=index_ws1, column=6, value=sheet1["F"][i].value).value                            
        ws1.cell(row=index_ws1, column=7, value=sheet1["G"][i].value).value                            
        ws1.cell(row=index_ws1, column=8, value=sheet1["H"][i].value).value                            
                                                                                                       
        ws2.cell(row=index_ws2, column=1, value=sheet1["A"][i].value).value                            
        ws2.cell(row=index_ws2, column=2, value=sheet1["B"][i].value).value                            
        ws2.cell(row=index_ws2, column=3, value=sheet1["C"][i].value).value                            
        ws2.cell(row=index_ws2, column=4, value=sheet1["D"][i].value).value                            
        ws2.cell(row=index_ws2, column=5, value=sheet1["E"][i].value).value                            
        ws2.cell(row=index_ws2, column=6, value=sheet1["F"][i].value).value                            
        ws2.cell(row=index_ws2, column=7, value=sheet1["G"][i].value).value                            
        ws2.cell(row=index_ws2, column=8, value=sheet1["H"][i].value).value                            
                                                                                                       
        index_ws1 += 1                                                                                 
        index_ws2 += 1                                                                                 
    else:                                                                                              
        if str(sheet1["A"][i].value) in ll:                                                            
            ws1.cell(row=index_ws1, column=1, value=sheet1["A"][i].value).value                        
            ws1.cell(row=index_ws1, column=2, value=sheet1["B"][i].value).value                        
            ws1.cell(row=index_ws1, column=3, value=sheet1["C"][i].value).value                        
            ws1.cell(row=index_ws1, column=4, value=sheet1["D"][i].value).value                        
            ws1.cell(row=index_ws1, column=5, value=sheet1["E"][i].value).value                        
            ws1.cell(row=index_ws1, column=6, value=sheet1["F"][i].value).value                        
            ws1.cell(row=index_ws1, column=7, value=sheet1["G"][i].value).value                        
            ws1.cell(row=index_ws1, column=8, value=sheet1["H"][i].value).value                        
                                                                                                       
            index_ws1 += 1                                                                             
        elif str(sheet1["A"][i].value) not in ll:                                                      
            ws2.cell(row=index_ws2, column=1, value=sheet1["A"][i].value).value                        
            ws2.cell(row=index_ws2, column=2, value=sheet1["B"][i].value).value                        
            ws2.cell(row=index_ws2, column=3, value=sheet1["C"][i].value).value                        
            ws2.cell(row=index_ws2, column=4, value=sheet1["D"][i].value).value                        
            ws2.cell(row=index_ws2, column=5, value=sheet1["E"][i].value).value                        
            ws2.cell(row=index_ws2, column=6, value=sheet1["F"][i].value).value                        
            ws2.cell(row=index_ws2, column=7, value=sheet1["G"][i].value).value                        
            ws2.cell(row=index_ws2, column=8, value=sheet1["H"][i].value).value                        
                                                                                                       
            index_ws2 += 1                                                                             
                                                                                                       
wb1.save(filepath1)                                                                                    
wb2.save(filepath2)