openpyxl | Python操作Excel利器

Python-openpyxl操作生成Excel

初识

安装

pip install openpyxl

逻辑

一个excel文档就是一个工作簿 Workbook，每个工作簿对应可以有很多个表格sheet，每个表格页，由单元格组成。openpyxl通过操作这三个层级，完成对excel的操作。

工作簿：

表格页：

单元格：

基本用法

创建工作簿 Workbook

from openpyxl import Workbook

#创建一个工作簿对象
workbook = Workbook()
#保存这个工作簿，命名为test
workbook.save('test.xlsx')

打开现有工作簿

from openpyxl  import load_workbook
#打开当前路径下的test表格
workbook = load_workbook('test.xlsx')

创建表

from openpyxl import load_workbook
#打开当前路径下的test表格
workbook = load_workbook('test.xlsx')
#创建一个名字叫sheet的表格
sheet = wb.create_sheet('test.xlsx')
#任何操作都需要保存
workbook.save('test.xlsx')

选择现有的表

from openpyxl  import load_workbook
#打开当前路径下的test表格
workbook = load_workbook('test.xlsx')
#选择名字为first_sheet的表格页
sheet = workbook['first_sheet']

删除表

from openpyxl import load_workbook
#打开当前路径下的test表格
workbook = load_workbook('test.xlsx')
#选择名字为first_sheet的表格页
sheet = workbook['first_sheet']
#删除这张表
workbook.remove(sheet)

访问单元格

#方法1
cell1 = sheet['A1']
#方法2
cell2 = sheet.cell(row=1,column=2)

#设置单元格的值
cell1.value = '123456'

#合并A1和A2单元格
sheet.merge_cells('A1:A2')

高级用法

设置表格样式

先设置表格的样式模板

再将模板赋予需要更改样式的单元格

from openpyxl.styles import *

#先设置表格的样式模板，再将模板赋予需要更改样式的单元格
#设置样式模板，这里只列举一部分常用的样式
#表格边缘线的样式，thin表示有框线，框线颜色为黑色
thin = Side(border_style='thin', color='000000')
#设置表格上下左右都有黑色框线样式
all_border = Border(top=thin, left=thin, right=thin, bottom=thin)
#设置字体样式，设置字体为 微软雅黑，单下划线，颜色为蓝色,字体加粗
yahei_font_u = Font(name=u'微软雅黑',underline='single',color='0000FF',bold=True)
#设置背景颜色，设置充满方式为充满，颜色为黄色
yellow_fill = PatternFill(fill_type='solid',fgColor='FFD700')
#设置字体在表格中的位置，设置字体为上下区中，字符长度超过表格宽度时自动换行
alignment_wrap_content = Alignment(wrap_text=True,horizontal='center',vertical='center')

#设置完成后，改变单元格样式,这样这个单元格的样式就更改为模板的样式了
cell = sheet.row(row=1,column=2)
cell.fill = self.yellow_fill
cell.font = self.yahei_font_u
cell.border = self.all_border
cell.alignment = self.alignment_wrap_content

#如果要设置的单元格为合并后的，那么多个单元格样式都要设置

隐藏和折叠

#隐藏第二行
sheet.row_dimensions[2].hidden=1
#折叠第2行到第5行
sheet.row_dimensions.group(2,5,hidden=True)

设置超链接

#设置这个单元格的超链接为 跳转到 test工作簿 Sheet表格的 A1单元格
cell.hyperlink = 'test.xlsx#Sheet!A1'

实战

原始数据

转换效果

from openpyxl import *
from openpyxl.styles import *

class MakeExcel():

    def __init__(self):
        self.dict = {
        '英雄联盟': 'https://lol.qq.com/main.shtml',
        '地下城与勇士': 'https://dnf.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '使命召唤Online': 'https://codol.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '疾风之刃': 'https://jf.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '冒险岛2':'https://mxd2.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '天涯明月刀':'https://wuxia.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '御龙在天': 'https://yl.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '剑灵': 'https://bns.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '流放之路': 'https://poe.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '轩辕传奇2':  'https://xy2.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        '斗战神':  'https://dzs.qq.com/?ADTAG=media.innerenter.gamecom.navigation',
        'QQ三国': 'https://sg.qq.com/web201706/index.shtml?ADTAG=media.innerenter.gamecom.navigation',
        }
        self.excel_template()
        self.make()

    #表格样式模板
    def excel_template(self):

        # 表格边缘线的样式，thin表示有框线，框线颜色为黑色
        self.thin = Side(border_style='thin', color='000000')

        # 设置表格上下左右都有黑色框线样式
        self.all_border = Border(top=self.thin, left=self.thin, right=self.thin, bottom=self.thin)

        # 设置普通字体样式
        self.yahei_normal = Font(name=u'微软雅黑')

        #设置标题字体样式
        self.yahei_title = Font(name=u'微软雅黑',bold=True)

        # 设置链接字体样式，设置字体为 微软雅黑，单下划线，颜色为蓝色
        self.yahei_u = Font(name=u'微软雅黑', underline='single', color='0000FF')

        # 设置背景颜色，设置充满方式为充满，颜色为黄色
        self.yellow_fill = PatternFill(fill_type='solid', fgColor='FFD700')

        # 设置字体在表格中的位置，设置字体为上下区中，字符长度超过表格宽度时自动换行
        self.alignment_wrap = Alignment(wrap_text=True, horizontal='center', vertical='center')

    #设置表格样式，根据传入的样式设置传入的单元格对象的样式
    def make_cell_style(self,cell,style):

        #普通单元格样式
        if style == 'normal':
            cell.font = self.yahei_normal
            cell.border = self.all_border
            cell.alignment = self.alignment_wrap
        #标题单元格样式
        elif style == 'title':
            cell.fill = self.yellow_fill
            cell.font = self.yahei_title
            cell.border = self.all_border
            cell.alignment = self.alignment_wrap
        #地址单元格样式
        elif style == 'addr':
            cell.font = self.yahei_u
            cell.border = self.all_border
            cell.alignment = self.alignment_wrap

    #生成表格
    def make(self):
        #创建一个工作簿，选用Sheet这张表
        workbook = Workbook()
        sheet = workbook['Sheet']

        #设置标题的名称
        cell1 = sheet['A1']
        cell2 = sheet['B1']
        cell1.value = '名称'
        cell2.value = '地址'

        #设置列框
        sheet.column_dimensions['A'].width = 20.0
        sheet.column_dimensions['B'].width = 100.0

        #设置标题样式
        self.make_cell_style(cell1, 'title')
        self.make_cell_style(cell2, 'title')

        #设置游戏和地址的样式
        cur_row = 2
        for key in self.dict.keys():

            #设置名称和地址的值
            temp_cell_A = sheet.cell(row=cur_row, column=1)
            temp_cell_B = sheet.cell(row=cur_row, column=2)
            temp_cell_A.value = key
            temp_cell_B.value = self.dict[key]
            #设置地址超链接
            temp_cell_B.hyperlink = self.dict[key]
            #设置名称和地址的样式
            self.make_cell_style(temp_cell_A, 'normal')
            self.make_cell_style(temp_cell_B, 'addr')
            cur_row = cur_row + 1

        #从第六行开始折叠
        sheet.row_dimensions.group(6, cur_row, hidden=True)
        
        workbook.save('游戏.xlsx')

MakeExcel()