python-excel数据导入导出

xlrd 读取数据
xlwt 写入数据，要求列数不能超过256
xlsxwriter 写入数据，支持列数超过256列，但不支持带格式文件
openpyxl 性能不稳定

读

# 导入库
import xlrd

# 调用库中的函数，读取工作薄
xlsx = xlrd.open_workbook('excel位置')

# 读取工作薄中的某工作表
table = xlsx.sheet_by_index(0)  # 根据位置
# table = xlsx.sheet_by_name('工作表名') # 根据工作表名称
# 工作表较多时，for循环读取工作表名
for i in xlsx.sheet_names():
    print(i)

# 读取某单元格数据
print(table.cell_value(11,2)) # 实际对应单元格12行3列的值
print(table.cell(11,2).value)
print(table.row(11)[2].value)

写

import xlwt

# 新建工作薄
new_workbook = xlwt.Workbook()

# 创建工作表
worksheet = new_workbook.add_sheet('test') # 缺省默认为sheet1

# 工作表中写入数据，工作表.wirte(行，列，'值')
worksheet.write(0, 0, 'test1')

# 保存工作薄
new_workbook.save(r'D:\Users\ywango\Desktop\new_test.csv')
#new_workbook.save(r'D:\Users\ywango\Desktop\new_test.xlsx) # 无法打开工作薄

往带格式文档中写

# xlutils，excel模版格式复制到新建工作薄
from xlutils.copy import copy 
import xlrd
import xlwt

# 导入模版
# 保留格式信息，模版一定要存03版本格式，utils对新版支持较差
tem_excel = xlrd.open_workbook(r'D:\Users\ywango\Desktop\模版.xls', formatting_info=True) 
#tem_excel = xlrd.open_workbook(r'D:\Users\ywango\Desktop\模版.xlsx', formatting_info=True) # 会报错

# 对应工作表
tem_sheet = tem_excel.sheet_by_index(0)

# 新建工作薄
new_excel = copy(tem_excel)

new_sheet = new_excel.get_sheet(0)

# 对工作表新建样式
# 初始化样式
style = xlwt.XFStyle()

# 设置字体
font = xlwt.Font()
font.name = 'Microsoft YaHei UI'
font.bold = True
font.height = 11*20 # python中zlwt要对excel字体大小乘20

# 将字体添加到样式中
style.font = font

# 设置边框
borders = xlwt.Borders()
borders.top = xlwt.Borders.THIN  # 细线
borders.bottom = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN

# 添加到整体样式中
style.borders = borders

# 设置对齐方式
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 水平居中对齐
alignment.vert = xlwt.Alignment.VERT_CENTER # 垂直居中对齐

style.alignment = alignment

# 带上格式写入数据
new_sheet.write(2,1,12,style)
new_sheet.write(3,1,8,style)
new_sheet.write(4,1,10,style)

# 保存工作薄
new_excel.save(r'D:\Users\ywango\Desktop\填写.xls')

xlwt/xlsxwriter/openpyxl 对比

import xlwt
workbook = xlwt.Workbook()
sheet0 = workbook.add_sheet('sheet0')
for i in range(0,300):  # 超出xlwt支持的列，报错
    sheet0.write(0,i,i)
workbook.save(r'D:\Users\ywango\Desktop\test0.xls')

import xlsxwriter as xw
workbook = xw.Workbook(r'D:\Users\ywango\Desktop\test0.xlsx')
sheet0 = workbook.add_worksheet('sheet0')
for i in range(0,300):
    sheet0.write(0,i,i)
workbook.close()

import openpyxl
workbook = openpyxl.load_workbook(r'D:\Users\ywango\Desktop\模版.xlsx')
sheet0 = workbook['Sheet1']
sheet0['B3']='13'
sheet0['B4']='8'
sheet0['B5']='25'
workbook.save(r'D:\Users\ywango\Desktop\测试数据.xlsx')

通过读写汇总学生成绩

import xlrd
import xlwt

xlsx = xlrd.open_workbook(r'D:\Users\ywango\Desktop\test_data.xlsx')
sheet = xlsx.sheet_by_name('学生成绩')
all_data = []                           # 创建空列表
num_set = set()                    # 创建空集合
for row_i in range(1,sheet.nrows):
    num = sheet.cell_value(row_i,0)
    name = sheet.cell_value(row_i,1)
    grade = sheet.cell_value(row_i,3)
    
    student = {
        'num':num,
        'name':name,
        'grade':grade
    }
    all_data.append(student)
    num_set.add(num)
    
# print(all_data)
# print(num_set)

# 计算总分
sum_list = []
for num in num_set:
    name = ' '
    sum = 0
    for student in all_data:
        if num == student['num']:
            sum += student['grade']
            name = student['name']
    sum_stu = {
        'num' : num,
        'name' : name,
        'sum' : sum
    }
    sum_list.append(sum_stu)
# print(sum_list)

# 写入新的excel
new_workbook = xlwt.Workbook()
worksheet = new_workbook.add_sheet('2班')
worksheet.write(0,0,'学号')
worksheet.write(0,1,'姓名')
worksheet.write(0,2,'总分')

for row in range(0,len(sum_list)):
    worksheet.write(row+1,0,sum_list[row]['num'])
    worksheet.write(row+1,1,sum_list[row]['name'])
    worksheet.write(row+1,2,sum_list[row]['sum'])
    
new_workbook.save(r'D:\Users\ywango\Desktop\学生总成绩.xls')