openpyxl

一 | openpyxl 安装

pip3 install openpyxl

二 | 打开或创建文件

1.创建新文件

from openpyxl import *

# 实例化
wb = Workbook()
# 激活sheet
ws = wb.active

2.打开原有文件

from openpyxl import *

wb = Workbook('文件名称.xlsx')
ws = wb.active

三 | 储存数据

# 方式一：数据可以直接分配到单元格中(可以输入公式)
ws['A1'] = 42
# 方式二：可以附加行，从第一列开始附加(从最下方空白处，最左开始)(可以输入多行)
ws.append([1, 2, 3])
# 方式三：Python 类型会被自动转换
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")

四 | 创建表（sheet）

# 方式一：插入到最后(default)
>>> ws1 = wb.create_sheet("Mysheet") 
# 方式二：插入到最开始的位置
>>> ws2 = wb.create_sheet("Mysheet", 0)

五 | 选择表（sheet）

# sheet 名称可以作为 key 进行索引
>>> ws3 = wb["New Title"]
>>> ws4 = wb.get_sheet_by_name("New Title")
>>> ws is ws3 is ws4
True

六 | 查看表名（sheet）

# 显示所有表名
>>> print(wb.sheetnames)
['Sheet2', 'New Title', 'Sheet1']
# 遍历所有表
>>> for sheet in wb:
... print(sheet.title)

七 | 访问单元格（cell）

1.单一单元格访问

# 方法一
>>> c = ws['A4']
# 方法二：row 行；column 列
>>> d = ws.cell(row=4, column=2, value=10)
# 方法三：只要访问就创建
>>> for i in range(1,101):
...     for j in range(1,101):
...      ws.cell(row=i, column=j)

2.多单元格访问

# 通过切片
>>> cell_range = ws['A1':'C2']
# 通过行(列)
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
# 通过指定范围(行 → 行)
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...  for cell in row:
...    print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>　
# 通过指定范围(列 → 列)
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
...  for cell in row:
...    print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
# 遍历所有 方法一
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
...
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
# 遍历所有 方法二
>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
...
<Cell Sheet.C8>,
<Cell Sheet.C9>))

八 | 获取单元格内值

value = ws.cell(row=4, column=2).value

九 | 保存数据

>>> wb.save('文件名称.xlsx')

十 | 其他

1. 改变 sheet 标签按钮颜色

ws.sheet_properties.tabColor = "1072BA"

2. 获取最大行，最大列

# 获得最大列和最大行
print(sheet.max_row)
print(sheet.max_column)

3. 获取每一行，每一列

sheet.rows为生成器, 里面是每一行的数据，每一行又由一个tuple包裹。
sheet.columns类似，不过里面是每个tuple是每一列的单元格。

# 因为按行，所以返回A1, B1, C1这样的顺序
for row in sheet.rows:
  for cell in row:
    print(cell.value)
 
# A1, A2, A3这样的顺序
for column in sheet.columns:
  for cell in column:
    print(cell.value)

4.根据数字得到字母，根据字母得到数字

from openpyxl.utils import get_column_letter, column_index_from_string
 
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4

5.删除工作表

# 方式一
wb.remove(sheet)
# 方式二
del wb[sheet]

6. 矩阵置换（行 → 列）

rows = [
  ['Number', 'data1', 'data2'],
  [2, 40, 30],
  [3, 40, 25],
  [4, 50, 30],
  [5, 30, 10],
  [6, 25, 5],
  [7, 50, 10]]
 
list(zip(*rows))
 
# out
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]
 
# 注意 方法会舍弃缺少数据的列(行)
rows = [
  ['Number', 'data1', 'data2'],
  [2, 40  ], # 这里少一个数据
  [3, 40, 25],
  [4, 50, 30],
  [5, 30, 10],
  [6, 25, 5],
  [7, 50, 10],
]
# out
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]

十一 | 设置单元格风格

1. 需要导入的类

from openpyxl.styles import Font, colors, Alignment

2.字体

下面的代码指定了等线24号，加粗斜体，字体颜色红色。直接使用cell的font属性，将Font对象赋值给它。

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
sheet['A1'].font = bold_itatic_24_font

3. 对齐方式

也是直接使用cell的属性aligment，这里指定垂直居中和水平居中。除了center，还可以使用right、left等等参数。

# 设置B1中的数据垂直居中和水平居中
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center')

4.设置行高和列宽

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30

5.合并和拆分单元格

*所谓合并单元格，即以合并区域的左上角的那个单元格为基准，覆盖其他单元格使之称为一个大的单元格。
*相反，拆分单元格后将这个大单元格的值返回到原来的左上角位置。

# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1') # 合并一行中的几个单元格
sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格

*合并后只可以往左上角写入数据，也就是区间中:左边的坐标。
*如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
*以下是拆分单元格的代码。拆分后，值回到A1位置。

sheet.unmerge_cells('A1:C3')

最后举个例子

import re
import time
from openpyxl import *
from openpyxl.styles import PatternFill
import winreg


class CheckInfo():
    def __init__(self, name, path):
        self.path = path
        self.name = name
        self.wb = load_workbook(self.path + f'\\{self.name}.xlsx')   
        self.ws = self.wb['汇总']    # 绑定'汇总'sheet
        self.max = self.ws.max_row  # 最大行数
        print('获取路径为')
        print(path)

    '''检测时间是否重叠函数'''
    def time_check(self, i, j):
        '''
        startdate1 <=enddate2 and enddate1>=startdate2
        '''

        start_time = self.time1[:19]
        end_time = self.time1[20:]
        c_start_time = self.time2[:19]
        c_end_time = self.time2[20:]

        timeArray_start = time.strptime(start_time,"%Y-%m-%d %H:%M:%S")
        timeArray_end = time.strptime(end_time, "%Y-%m-%d %H:%M:%S")
        timeArray_c_start = time.strptime(c_start_time, "%Y-%m-%d %H:%M:%S")
        timeArray_c_end = time.strptime(c_end_time,"%Y-%m-%d %H:%M:%S")

        timeStamp_start = int(time.mktime(timeArray_start))
        timeStamp_end = int(time.mktime(timeArray_end))
        timeStamp_c_start = int(time.mktime(timeArray_c_start))
        timeStamp_c_end = int(time.mktime(timeArray_c_end))

        if timeStamp_start <= timeStamp_c_end and timeStamp_end >= timeStamp_c_start:

            self.decorate_func(i, j)                   # 重叠调用装饰函数 

    '''获取excel表格内值'''
    def get_value(self):
        
        for i in range(2, self.max):
            value1 = self.ws.cell(i, 1).value
            for j in range(3, self.max+1):
                value2 = self.ws.cell(j, 1).value
                if value1 == value2 and i < j:
                    self.time1 = self.ws.cell(i, 3).value
                    self.time2 = self.ws.cell(j, 3).value
                    self.time_check(i, j)

        self.wb.save(self.path + f'\\Done{self.name}.xlsx')  # 退出保存，名字为new.xlsx 

    '''装饰数据函数'''
    def decorate_func(self, i, j):

        fill = PatternFill("solid", fgColor="29BD01")   # 增加背景色「绿色」
        self.ws.cell(i, 3).fill = fill
        self.ws.cell(j, 3).fill = fill
        for k in range(15, 30):                         # 标记与其重复行数
            if self.ws.cell(i, k).value:
                continue
            else:
                self.ws.cell(i, k).value = j
                break
        for l in range(15, 30):
            if self.ws.cell(j, l).value:
                continue
            else:
                self.ws.cell(j, l).value = i
                break

def get_desktop():
    key = winreg.OpenKey(winreg.HKEY_CURRENT_USER,r'Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders')
    return winreg.QueryValueEx(key, "Desktop")[0]

def main():
    path = get_desktop()
    while True:
        try:
            name = input()
            check = CheckInfo(name, path)
            check.get_value()
        except Exception:
            print('||')                         
        else:                       
            print('')
    
if __name__ == '__main__':
    main()