第三十篇 openpyxl模块，python玩转excel数据存储，读取操作

心得：今天下班比较晚，但还是想把自己学到得东西，给记录下来，供自己和大家参考学习，加油。

今天写一篇关于对excel数据存储，数据读取的用法，学会了这个模块，处理数据会变得更加便捷，来一起看看怎么用吧：

步骤：
1.导入模块
2.实例化对象
3.操纵表格，读或者写
4.保存表格

一、写入数据时

看一个简单的例子：

import openpyxl #导入模块

wb=openpyxl.Workbook() #实例化对象

ws=wb.active  #选择活跃的sheet页

ws.append([1,2,3])  #将列表添加到excel中
 
wb.save("lianxi.xlsx") #保存excel

结果：

看看具体的介绍把：

1.实例化对象：也就是创建工作表

创建新的工作表

wb=openpyxl.Workbook() #实例化对象

导入已存在的工作表

wb=openpyxl.load_workbook("lianxi.xlsx")

2.选择我们需要操作的sheet页

选择存在的sheet页

ws=wb.active  #选择默认的活跃sheet页
ws3=wb["Hello1"] #选择名称为Hello1的sheet页进行操作

创建新的sheet页

ws2=wb.create_sheet("hello")  #名字为hello
ws3=wb.create_sheet("Hello",0) #名字为Hello，排在第一个的sheet页

删除sheet页

wb.remove(wb['IDLE'])

查excel中sheet的名称有哪些

wb.sheetnames

3.写入数据

指定某一单元格，填入数据

ws['A3']=666
ws.cell(2,3).value="田田"
ws.cell(row=2,column=3).value="tiantian"

批量操作性的填入数据，按行加入表格中

A=[1,2,3,4,5,6,7,8,9,10]
B=[2,3,4,5,6,7,8,9,1,2]
ws.append(A)
ws.append(B)

按列写入表格中

A=[1,2,3,4,5,6,7,8,9,10]
B=[2,3,4,95,6,7,8,9,1,2]
ws.append(["A","B"])
for i in range(len(A)):
    ws.append([A[i],B[i]])

假如两列长度不一样时，我们就需要加入判断，选择最长的那一列去遍历，这样就不会遗漏数据

A=[1,2,3,4,5,6,7,8,9,10]
B=[2,3,6,7,8,9,1,2]
if len(A)>len(B):
    num=A
else:
    num=B
ws.append(["A","B"])
for i in range(len(num)):
    if num==A:
        try:
            ws.append([A[i],B[i]])
        except:
            ws.append([A[i]," "])
    else:
        try:
            ws.append([A[i],B[i]])
        except:
            ws.append([" ",B[i]])

二、读数据

获取最大行数和列数

print("row",ws.max_row)  #最大行数
print("column",ws.max_column) #最大列数
del wb['hello']  #删除hello的sheet页

结果：

c:/Users/TianJian/Desktop/python/openxl1121/write1121.py
row 39
column 10

指定单元格读取

d=ws["A3"]
print(d.value)

col=ws["B"]  #指定列
row=ws[1]  #指定行，需要遍历
for i in row:
    print(i.value)

多行单元格读取

for i in ws.iter_rows(min_row=1,min_col=1,max_row=3,max_col=3):
    for j in i:
        print(j.value,end=" ")
    print()

结果：

PS C:\Users\TianJian\Desktop\python> & C:/Users/TianJian/AppData/Local/Microsoft/WindowsApps/python.exe c:/Users/TianJian/Desktop/python/openxl1121/write1121.py
1 2 3
2 2 田田
666 2 3
PS C:\Users\TianJian\Desktop\python>

按多列进行读取

for i in ws.iter_cols(min_row=1,min_col=1,max_row=3,max_col=3):
    for j in i:
        print(j.value,end=" ")
    print()

结果：

PS C:\Users\TianJian\Desktop\python> & C:/Users/TianJian/AppData/Local/Microsoft/WindowsApps/python.exe c:/Users/TianJian/Desktop/python/openxl1121/write1121.py
1 2 666
2 2 2
3 田田 3
PS C:\Users\TianJian\Desktop\python>

存储数据：

wb.save("lianxi.xlsx") #保存excel

三、类方法实现

# -*- coding: UTF-8 -*-
""""=================================================
@Project -> File   ：Django -> 二叉树之有序列表
@IDE    ：PyCharm
@Author ：爱跳水的温文尔雅的laughing
@Date   ：2020/4/2 21:56
@Desc   ：
=================================================="""
import openpyxl


class Excel():
    def __init__(self, file):
        self.file = file
        self.wb = openpyxl.load_workbook(file)
        self.sheetname = self.wb.sheetnames
        self.ws = self.wb.active  # 活跃页
        self.ws = self.wb['first']  # 根据名字来选择
        self.ws = self.wb[self.sheetname[0]]  # 排第一个

    def creat_sheet(self, sheetname):
        self.wb.create_sheet(sheetname, 0)
        return self.wb[sheetname]

    def delete_sheet(self, sheetname):
        self.wb.remove(self.wb[sheetname])
        return "delete ok"

    def read_cell_value(self, col, row):
        """
        根据坐标求值
        :param col:
        :param row:
        :return:
        """
        try:
            return self.ws.cell(column=col, row=row).value
            # return self.ws.['A3'].value
        except Exception as e:
            return None

    def get_sheet_rows(self):
        """
        获取最大行
        :return:
        """
        return self.ws.max_row

    def get_sheet_cols(self):
        return self.ws.max_column

    def get_row_value(self, row):
        """
        获取某一行数据
        :param row:
        :return:
        """
        row = self.ws[row]
        lis = []
        for i in row:
            lis.append(i.value)
        return lis

    def get_col_value(self, col):
        """
        获取某一列的值
        :param col:
        :return:
        """
        col = self.ws[col]
        lis = []
        for i in col:
            lis.append(i.value)
        return lis

    def get_muti_value(self, min_row=1, min_col=1, max_row=3, max_col=3):
        """
        获取指定单元格的值
        :return:
        """
        all_lis = []
        for row in self.ws.iter_rows(min_row=min_row, min_col=min_col, max_row=max_row, max_col=max_col):
            lis = []
            for col in row:
                lis.append(col)
            all_lis.append(lis)
        return all_lis

    def insert_cell_value(self, row, col, value):
        """
        指定某一单元格，填入数据
        :param row:
        :param col:
        :param value:
        :return:
        """
        try:
            self.ws.cell(row, col).value(value)
            self.wb.save(self.file)
        except:
            return None

    def insert_row_value(self, lis):
        """
                批量操作性的填入数据，按行加入表格中
                :param row:
                :param col:
                :param lis:
                :return:
                """
        try:
            self.ws.append(lis)
            self.wb.save(self.file)
        except:
            return None

    def insert_col_value(self, lis):
        """
        按列插入数据
        :param lis:
        :return:
        """
        try:
            self.ws.append(["A", "B"])
            for i in range(len(lis)):
                self.ws.append([lis[i]])
        except:
            return None

四、改变Excel样式

官方文档 https://openpyxl.readthedocs.io/en/default/styles.html

标准样式

>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> font = Font(name='Calibri',
...                 size=11,
...                 bold=False,
...                 italic=False,
...                 vertAlign=None,
...                 underline='none',
...                 strike=False,
...                 color='FF000000')
>>> fill = PatternFill(fill_type=None,
...                 start_color='FFFFFFFF',
...                 end_color='FF000000')
>>> border = Border(left=Side(border_style=None,
...                           color='FF000000'),
...                 right=Side(border_style=None,
...                            color='FF000000'),
...                 top=Side(border_style=None,
...                          color='FF000000'),
...                 bottom=Side(border_style=None,
...                             color='FF000000'),
...                 diagonal=Side(border_style=None,
...                               color='FF000000'),
...                 diagonal_direction=0,
...                 outline=Side(border_style=None,
...                              color='FF000000'),
...                 vertical=Side(border_style=None,
...                               color='FF000000'),
...                 horizontal=Side(border_style=None,
...                                color='FF000000')
...                )
>>> alignment=Alignment(horizontal='general',
...                     vertical='bottom',
...                     text_rotation=0,
...                     wrap_text=False,
...                     shrink_to_fit=False,
...                     indent=0)
>>> number_format = 'General'
>>> protection = Protection(locked=True,
...                         hidden=False)
>>>

单元格样式

>>> from openpyxl.styles import colors
>>> from openpyxl.styles import Font, Color
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> a1 = ws['A1']
>>> d4 = ws['D4']
>>> ft = Font(color=colors.RED)
>>> a1.font = ft
>>> d4.font = ft
>>>
>>> a1.font.italic = True # is not allowed 斜体
>>>
>>> # If you want to change the color of a Font, you need to reassign it::
>>>
>>> a1.font = Font(color=colors.RED, italic=True) # the change only affects A1

合并单元格

workSheet.merge_cells('A1:A2')
workSheet.merge_cells(start_row=None, start_column=None, end_row=None, end_column=None)
workSheet.cell(1,1).value = '合并的单元格内容'

设置单元格大小（行高和列宽）

from openpyxl import load_workbook
 
wb = load_workbook('test.xlsx')
print(wb.sheetnames)
ws = wb[wb.sheetnames[0]]
 
# 调整列宽
ws.column_dimensions['A'].width = 20.0
 
# 调整行高
ws.row_dimensions[1].height = 40