python操作xlsx文件(openpyxl)

背景

在处理一些作业时，经常会碰到统计未交人数、分数等需求，虽然我们在数据库中有了对应的数据，
但是数据库只是面向开发者的，仍然需要将其转换为专业的可阅读格式-excel表格。

目前常用的操作xlsx表格的是openpyxl, 官网地址

编程模型

读取

from openpyxl import Workbook
from openpyxl.utils import get_column_letter
 
 ## 在内存中创建一个workbook对象，而且会至少创建一个 worksheet
 wb = Workbook()
 
 #获取当前活跃的worksheet,默认就是第一个worksheet
 ws = wb.active
 
 #设置单元格的值，A1等于6(测试可知openpyxl的行和列编号从1开始计算)，B1等于7
 ws.cell(row=1, column=1).value = 6
 ws.cell("B1").value = 7
 
 #从第2行开始，写入9行10列数据，值为对应的列序号A、B、C、D...
 for row in range(2,11):
  for col in range (1,11):
   ws.cell(row=row, column=col).value = get_column_letter(col)
 
 #可以使用append插入一行数据
 ws.append(["我","你","她"])
 
 #保存
 wb.save(filename="/Users/budong/Desktop/a.xlsx")

写入

from openpyxl import load_workbook

#打开一个workbook
wb = load_workbook(filename="/Users/budong/Desktop/a.xlsx")

#获取当前活跃的worksheet,默认就是第一个worksheet
#ws = wb.active

#当然也可以使用下面的方法

#获取所有表格(worksheet)的名字
sheets = wb.get_sheet_names()
#第一个表格的名称
sheet_first = sheets[0]
#获取特定的worksheet
ws = wb.get_sheet_by_name(sheet_first)

#获取表格所有行和列，两者都是可迭代的
rows = ws.rows
columns = ws.columns

#迭代所有的行
for row in rows:
line = [col.value for col in row]
print(line)

#通过坐标读取值
print(ws.cell('A1').value) ## A表示列,1表示行
print(ws.cell(row=1, column=1).value)

处理包含学生学号和学生姓名的表格

在该例子中，xlsx表格中只有两列，第一列是学生学号，第二列是学生姓名，
使用get_stu可以获取xlsx文件中的学号与姓名供使用，使用write_xlsx
可以向xlsx文件中写入数据，其中data为二维列表。

from openpyxl import Workbook, load_workbook

src_name = 'students.xlsx'
def get_stu(file_name, type='code'):
    wb = load_workbook(file_name)
    ws = wb.active 

    stu_code = []
    rows = ws.rows 
    for row in rows:
        if type=='code':
        ## stu code
            col = row[1]
        elif type=='name':
            col = row[2]
        else:
            col = row[1]

        stu_code.append(str(col.value))

    return stu_code 
def write_xlsx(file_name, data):
    wb = Workbook() 
    ws = wb.active
    for row in range(1,len(data) + 1):
        for column in range(1, len(data[row-1]) + 1):
            ws.cell(row=row, column=column).value = data[row-1][column-1]
    wb.save(filename=file_name)

参考：
1. 脚本之家