Python实现自动化办公之Excel简单操作

• • 一、用python创建一个excel
  • • 1.首先下载相关的excel库函数：
    • 2.制作一个成绩单
    • 3.运行结果
  • 二 、读出刚刚用代码生成的excel,并进行相关的操作（求成绩的平均值，及最大值）
  • • 运行结果：

一、用python创建一个excel

1.首先下载相关的excel库函数：

pip install xlwd 
pip install xlrd

如果下载慢的话：进行换源操作

pip install xlwd -i https://pypi.douban.com/simple
pip install xlrd -i https://pypi.douban.com/simple

2.制作一个成绩单

import xlwt 
import xlrd
import random
#新建excel
workbook = xlwt.Workbook()

#新建sheet表
worksheet = workbook.add_sheet("一班",cell_overwrite_ok=True)

#设置行列，写表格
name = ['','张三','李四','王五']
subject = ['','语文','数学','英语','化学']

for i in range(4):
    worksheet.write(i,0,name[i])
for j in range(5):
    worksheet.write(0,j,subject[j])

for i in range(1,4):
    for j in range(1,5):
        score = random.randint(60,100)
        worksheet.write(i,j,score)

#保存
workbook.save('成绩单.xls')

3.运行结果

二 、读出刚刚用代码生成的excel,并进行相关的操作（求成绩的平均值，及最大值）

import xlrd

#打开excel
book = xlrd.open_workbook("成绩单.xls")

#打开表单  [0] 是第一个 [1]是第二个
sheet = book.sheets()[0]

#读取行列数量
nrows = sheet.nrows
print('表格总行数',nrows)
ncols = sheet.ncols
print('表格总列数',ncols)

def average_col(nrows,ncols):
    title = ''
    for i in range(1,ncols):    #统计列   
        title = sheet.cell(0,i).value
        score_sum = 0
        cnt = 0
        #print(title)
        for j in range(1,nrows):   #统计行
            score_sum = score_sum + sheet.cell(j,i).value  
            cnt = cnt +1
        average = score_sum//cnt
        print(title + "平均分:",average)
        #return average

def average_row(nrows,ncols):
    title = ''
    for i in range(1,nrows):    #统计行   
        title = sheet.cell(i,0).value
        score_sum = 0
        cnt = 0
        #print(title)
        for j in range(1,ncols):   #统计列
            score_sum = score_sum + sheet.cell(i,j).value  
            cnt = cnt +1
        average = score_sum//cnt
        print(title + "平均分:",average)
        #return average

def max_col(nrows,ncols):
    title = ''
    for i in range(1,ncols):   #统计列
        title = sheet.cell(0,i).value
        value = 0
        max_score = 0
        for j in range(1,nrows):  #统计行
            value = sheet.cell(j,i).value
            if(max_score < value):
                max_score = value
        print(title + "最大值:",max_score)
        #return max_score
average_col(nrows,ncols)
average_row(nrows,ncols)
max_col(nrows,ncols)

运行结果：