不灭的焱

革命尚未成功,同志仍须努力

作者:php-note.com  发布于:2021-09-14 22:34  分类:Python/数据采集分析  编辑

一、openpyxl特点

openpyxl(可读写excel表)专门处理Excel2007及以上版本产生的xlsx文件,xls和xlsx之间转换容易。注意:如果文字编码是“gb2312”,读取后就会显示乱码,请先转成Unicode。

1、openpyxl 读写单元格时,单元格的坐标位置起始值是(1,1),即下标最小值为1,否则报错!

tableTitle = ['userName', 'Phone', 'age', 'Remark']

# 维护表头
# if row < 1 or column < 1:
#     raise ValueError("Row or column values must be at least 1")
# 如上,openpyxl 的首行、首列 是 (1,1)而不是(0,0),如果坐标输入含有小于1的值,提示 :Row or column values must be at least 1,即最小值为1.
for col in range(len(tableTitle)):
    c = col + 1
    ws.cell(row=1, column=c).value = tableTitle[col]

2、openpyxl 支持直接横纵坐标访问,如A1,B2...

ws['A4'] = 4 #直接赋值

二、openpyxl操作excel

Excel文件三个对象

  • workbook: 工作簿,一个excel文件包含多个sheet。
  • sheet:工作表,一个workbook有多个,表名识别,如“sheet1”,“sheet2”等。
  • cell: 单元格,存储数据对象

1、新建表

A workbook至少创建一个worksheet.

通过openpyxl.workbook.Workbook.active()得到worksheet

wb = Workbook(encoding='UTF-8')
ws = wb.active

注意:

该方法使用_active_sheet_index属性, 默认会设置0,也就是第一个worksheet。除非手动修改,否则使用active方法得到都是第一个worksheet。

你也可以创建worksheets,通过 openpyxl.workbook.Workbook.create_sheet() 方法:

ws = wb.create_sheet("Mysheet") #插入到最后(default)
#或者
ws = wb.create_sheet("Mysheet", 0) #插入到最开始的位置

默认的sheet的tab是白色的,可以通过 RRGGBB颜色来修改sheet_properties.tabColor属性从而修改sheet tab按钮的颜色:

ws.sheet_properties.tabColor = "1072BA"

当你设置了sheet的名称,可以将其看成workbook中的一个key。也可以使用openpyxl.workbook.Workbook.get_sheet_by_name() 方法

单元格赋值

#设定单元格的值,三种方式
sheet.cell(row=2,column=5).value=99
sheet.cell(row=3,column=5,value=100)
ws['A4'] = 4 #write 

逐行写

ws.append(iterable)
#添加一行到当前sheet的最底部(即逐行追加从第一行开始) iterable必须是list,tuple,dict,range,generator类型的。 1,如果是list,将list从头到尾顺序添加。 2,如果是dict,按照相应的键添加相应的键值。
ws.append([‘This is A1', ‘This is B1', ‘This is C1'])
ws.append({‘A' : ‘This is A1', ‘C' : ‘This is C1'})
ws.append({1 : ‘This is A1', 3 : ‘This is C1'})

2、读表操作

#打开文件:
from openpyxl import load_workbook
excel=load_workbook('E:/test.xlsx')
#获取sheet:
table = excel.get_sheet_by_name('Sheet1')  #通过表名获取 
#获取行数和列数:
rows=table.max_row  #获取行数
cols=table.max_column  #获取列数
#获取单元格值:
Data=table.cell(row=row,column=col).value #获取表格内容,是从第一行第一列是从1开始的,注意不要丢掉 .value

 

#通过名字
ws = wb["frequency"] 
#等同于 ws2 = wb.get_sheet_by_name('frequency')
#不知道名字用index
sheet_names = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheet_names[index])# index为0为第一张表 

#或者
ws =wb.active
# 等同于 ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表,默认0读第一个表
#活动表表名
wb.get_active_sheet().title

单元格使用

c = ws['A4'] 
d = ws.cell(row = 4, column = 2) #行列读写

逐行读取

ws.iter_rows(range_string=None, row_offset=0, column_offset=0)
# range-string(string)-单元格的范围:例如('A1:C4')
# row_offset-添加行
# column_offset-添加列
# 返回一个生成器, 注意取值时要用value,例如:
for row in ws.iter_rows('A1:C2'):
    for cell in row:
        print(cell)

# 读指定行、指定列:
rows = ws.rows  # row是可迭代的
columns = ws.columns  # column是可迭代的

# 打印第n行数据
print(rows[n])  # 不需要用.value
print(columns[n])  # 不需要用.value

显示有多少张表

wb.get_sheet_names() 

显示表名,表行数,表列数

print ws.title 
print ws.max_row
print ws.max_column

公式计算formulae

ws["A1"] = "=SUM(1, 1)"
ws["A1"] = "=SUM(B1:C1)"

3、操作实例

from openpyxl import Workbook
from openpyxl.reader.excel import load_workbook

import os
import time


def writeExcel():
    # 获取文件路径
    excelPath = os.path.join(os.getcwd(), 'ExcelData')
    print("****")
    print(excelPath)
    # 定义文件名称
    # invalid mode ('wb') or filename: 'Excel2017-09-21_20:15:57.xlsx'  这种方式明明文件,会提示保存失败,无效的文件名。
    # nameTime = time.strftime('%Y-%m-%d_%H:%M:%S')
    nameTime = time.strftime('%Y-%m-%d_%H-%M-%S')
    excelName = 'Excel' + nameTime + '.xlsx'
    ExcelFullName = os.path.join(excelPath, excelName)
    print(ExcelFullName)

    wb = Workbook()

    ws = wb.active

    tableTitle = ['userName', 'Phone', 'age', 'Remark']

    # 维护表头
    #    if row < 1 or column < 1:
    #     raise ValueError("Row or column values must be at least 1")
    # 如上,openpyxl 的首行、首列 是 (1,1)而不是(0,0),如果坐标输入含有小于1的值,提示 :Row or column values must be at least 1,即最小值为1.
    for col in range(len(tableTitle)):
        c = col + 1
        ws.cell(row=1, column=c).value = tableTitle[col]

    # 数据表基本信息
    tableValues = [['张学友', 15201062100, 18, '测试数据!'], ['李雷', 15201062598, 19, '测试数据!'],
                   ['Marry', 15201062191, 28, '测试数据!']]

    for row in range(len(tableValues)):
        ws.append(tableValues[row])
    # wb.save(ExcelFullName)
    wb.save(filename=ExcelFullName)
    return ExcelFullName


def readExcel(ExcelFullName):
    wb = load_workbook(ExcelFullName)
    # wb = load_workbook(filename=ExcelFullName)

    # 获取当前活跃的worksheet,默认就是第一个worksheet
    # ws = wb.active
    # 当然也可以使用下面的方法
    # 获取所有表格(worksheet)的名字
    sheets = wb.get_sheet_names()
    print(sheets)
    # # 第一个表格的名称
    sheet_first = sheets[0]
    # # 获取特定的worksheet
    #
    ws = wb.get_sheet_by_name(sheet_first)
    print("***")
    print(sheet_first)
    print(ws.title)
    print("^^^")
    # 获取表格所有行和列,两者都是可迭代的
    rows = ws.rows
    print(rows)

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

    # 通过坐标读取值

    print(ws['A1'].value)  # A表示列,1表示行
    print(ws.cell(row=1, column=1).value)


if __name__ == '__main__':
    ExcelFullName = writeExcel()
    readExcel(ExcelFullName)

显示表名,表行数,表列数

print("Work Sheet Titile:", ws.title)
print("Work Sheet Rows:", ws.max_row)
print("Work Sheet Cols:", ws.max_column)

 

 

参考:https://www.jb51.net/article/165629.htm