[Py-2] Python读写excel文件及csv文件

推荐用openpyxl处理xlsx文件：

sheet1 = workbook1['班级全学科成绩简表']

workbook2 = openpyxl.load_workbook('模板.xlsx')
sheet2 = workbook2['成绩单模板1']

def find_index(name):
    for i in range(3,sheet1.max_row+1):
        # sheet1和sheet2根据姓名做关联，返回索引行数
        if sheet1['C'+str(i)].value == name:
            return i
    
for row_index in range(4,sheet2.max_row+1):
    row = str(row_index)
    name = sheet2['C'+row].value
    # 这两位同学成绩为空，无需赋值
    if name in ['王浩轩','熊浩宇']:
        continue
    index = str(find_index(name))
    
    # 语文分数及年级排名
    sheet2['D'+row] = sheet1['D'+index].value
    sheet2['L'+row] = sheet1['E'+index].value
    
    # 数学分数及年级排名
    sheet2['E'+row] = sheet1['G'+index].value
    sheet2['M'+row] = sheet1['H'+index].value
    
    # 英语分数及年级排名
    sheet2['F'+row] = sheet1['J'+index].value
    sheet2['N'+row] = sheet1['K'+index].value
    
    # 生物分数及年级排名
    sheet2['G'+row] = sheet1['M'+index].value
    sheet2['O'+row] = sheet1['N'+index].value
    
    # 地理分数及年级排名
    sheet2['H'+row] = sheet1['V'+index].value
    sheet2['P'+row] = sheet1['W'+index].value
    
    # 历史分数及年级排名
    sheet2['I'+row] = sheet1['S'+index].value
    sheet2['Q'+row] = sheet1['T'+index].value
    
    # 政治分数及年级排名
    sheet2['J'+row] = sheet1['P'+index].value
    sheet2['R'+row] = sheet1['Q'+index].value
    
    # 总分年级排名
    sheet2['K'+row] = sheet1['Z'+index].value
    
    print(name + '已录入')

workbook2.save('模板已完成.xlsx')

也可以用xlrd和xlwt，能支持xls文件：

import xlwt

book = xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet = book.add_sheet('会计一班',cell_overwrite_ok=True)  #新建sheet

col = ('姓名','成绩')
for i in range(len(col)):
        sheet.write(0,i,col[i])  #在0行i列写入表头
        
with open('1.txt','r',encoding='utf-8') as file:
    lines = file.readlines()
    for i in range(len(lines)):
        word = lines[i].split()  #split()以空格分割成列表
        #str.split( char,n )的用法：对str用char分割n次返回数组。如www.baidu.com的split('.',1)[0]的结果为www。
        for j in range(len(col)):
            sheet.write(i+1,j,word[j])  #在i+1行j列写入数据
            #sheet.write(i+1,j,word[j]+'\n'+'111') 单元格内换行写111，但要双击单元格才能看到效果

book.save('C:\\Users\\Jenkin_Zhu\\Desktop\\output.xls')

输入：

输出：

---

补充：CSV是逗号分隔文件，在excel中打开效果同表格。写它要用“姓名,武力,智力”的字符串：

# 写csv，从列表转换要用','.join；把csv读成列表要用split(',')
str1 = "姓名,武力,智力"
list2 = ["曹仁",'90','70']
with open('new.csv','w',encoding='UTF-8') as f:
    f.write(str1 + '\n')
    f.write(','.join(list2))

或者用csv.writer写入双层嵌套序列：

import csv
data = [['三国武将数值表'],['姓名','武力','智力']]
data.append(['曹操',86,96])
data.append(['刘备',84,68])    # 支持写入['刘备','',68]，这样中间的单元格是空的
data.append(['孙权',85,70])
with open('三国.csv', 'w', newline='') as file:
    writer = csv.writer(file)
    writer.writerows(data)