Python 操作Excel文件

Python 操作Excel文件

Excel 是我们在日常办公中可谓是无处不见，Excel主要又两种文件后缀名格式——（.xls/.xlsx）。现在使用的最多的是以.xlsx为后缀名的文件，它比.xls占的空间更少。

• 对于Excel的处理日常我们所用 的工具以微软开发的Excel为主，它是一种图像化界面操作很容易上手。处理这类文件的最大疼点——函数的使用————工作量大（存在大量重复的工作）
• exce文件的结构包括两大部分: 工作簿和工作表，一个工作部里面可以同时存在多个工作表。

1. 用到的三方库

• 这主要介绍excel 文件的读写的主流的三方库
• xlrd ----- 用于读以.xlsx文件，新版本只能读.xlsx结尾的文件。对于1.2.0的版本可以同时读.xls和.xlsx两种版本的excel文件，再安装三方库发时候可以安装指定版本的库，pip install xlrd==1.2.0.
• xlwt ---- 用于写excel 文件.
• openpyxl----同时可以支持excel的文件的读写，是处理excel首选的处理方法

2. 读excel文件

• xlrd读excel文件

  步骤：打开工作簿创建工作簿对象————>获取工作表对象————>得到每个单元格对象————>遍历单元格里面的值。

  import xlrd
  
  wb = xlrd.open_workbook('data/阿里巴巴2020年股票数据.xlsx')  # 打开工作簿创建工作簿对象
  sheet_name = wb.sheet_names()[0]  # 获取表单名
  # print(sheet_name)
  sheet = wb.sheet_by_name(sheet_name)  # 通过表单名创建工作区对象
  # print(sheet.nrows, sheet.ncols)  # 获取行数和列数
  for row in range(sheet.nrows):
      for col in range(sheet.ncols):
          value = sheet.cell(row, col).value
          # 对除首行外的其他行进行数据格式化处理
          if row > 0:
              # 第1列的xldate类型先转成元组再格式化为“年月日”的格式
              if col == 0:
                  # xldate_as_tuple函数的第二个参数只有0和1两个取值
                  # 其中0代表以1900-01-01为基准的日期，1代表以1904-01-01为基准的日期
                  value = xlrd.xldate_as_tuple(value, 0)  #返回一个六元组
                  value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'
              # 其他列的number类型处理成小数点后保留两位有效数字的浮点数
              else:
                  value = f'{value:.2f}'
          print(value, end='\t\t')
      print()
  
  

• openpyxl读取excel文件

import openpyxl

# 获取工作簿对象
wb = openpyxl.load_workbook('data/阿里巴巴2020年股票数据.xlsx')
# print(type(wb))
print(wb.sheetnames)  # 获取sheet表的名字,返回一个列表
# 获取sheet对象
sheet = wb.worksheets[0]
print(type(sheet))

for row in range(2, sheet.max_row+1):  # 索引从1开始
    for col in range(1, sheet.max_column+1):
        value = sheet.cell(row, col).value
        if col == 1:
            print(value.strftime('%y-%m-%d'), end='\t')
        else:
            print(value, end='\t')
    print()

• 从上面的两段代码可以看出openpyxl库处理excel文件更占更大的优势。基本上都是相同的生成wookbook对象一个是打开一个是加载文件。

3. 写excel 文件

• xlwt写excel文件
• 步骤和读的步骤一样

import random

import xlwt

# 创建工作簿
wb = xlwt.Workbook('data/成绩.xls')

# 添加工作表
sheet = wb.add_sheet('学生成绩')  # type: xlwt.Worksheet

# 向单元格写入数据
title = ['姓名', '语文', '数学', '英语']
for index, value in enumerate(title):				
    sheet.write(0, index, value)  # 第一个参数row,第二个参数column ,第三个参数要写入的内容

name = ['张华', '刘谦', '吴昊', '龙源', '杨柳']
for row in range(1, 6):
    sheet.write(row, 0, name[row-1])  # 将姓名写入第一列
    for col in range(1, 4):
        sheet.write(row, col, random.randrange(50, 101))
# 保存
wb.save('data/成绩.xls')

​ 需要注意的是xlwt对表的行列的所有都是从0开始.

• openpyxl 写excel文件

import random

import openpyxl
from xlutils.copy import copy

wb1 = openpyxl.Workbook()
sheet1 = wb1.create_sheet('sheet1')
sheet1.title = '成绩'
name = ['张三', '李四', '王二', '麻子', '龙王']
scour = ['语文', '数学', '英语']
for index, value in enumerate(name):
    sheet1.cell(index + 2, 1, value)
for index, value in enumerate(scour):
    sheet1.cell(1, index + 2, value)

for row in range(2, 7):
    for col in range(2, 5):
        sheet1.cell(row, col, random.randrange(60, 101))

wb1.save('data/四班成绩.xlsx')

​ openpyxl 写excel文件对表的行和列都是从1开始

简单的总结

常工作效率，解决大量的繁琐工作。在处理excel文件当以.xls为后缀名用xlrd和xlwt分别对文件进行读和写操作，以.xlsx为后缀名用openpyxl可以同时对文件进行读写操作。excel也是我们工作中非常重要的一部分，用python可以实现办公自动化。