13: openpyxl 读写 xlsx文件

1.1 openpyxl 基本使用

　　1、openpyxl 将xlsx读成json格式

#! /usr/bin/env python
# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
import json
from openpyxl import load_workbook


def read_xlsx_to_json(file_home):
    wb = load_workbook(filename= file_home)
    sheet_ranges = wb['Sheet1']
    ws = wb['Sheet1']
    rows = ws.rows    #获取表格所有行和列，两者都是可迭代的
    thead = [col.value for col in rows.next()]  # 表格中第一行作为key
    data = []
    for row in rows:
        line = [col.value for col in row]
        tmp_dic = {}
        for index, val in enumerate(thead):
            tmp_dic[val] = line[index]
        data.append(tmp_dic)
    return json.dumps(data, ensure_ascii=False,indent=4)


# print read_xlsx_to_json('dd.xlsx')


'''
[
    {
        "员工编号": null, 
        "部门名称": "HLT集团", 
        "手机号": 1393999934, 
        "角色": null, 
        "直属上级": null, 
        "职位": "CEO", 
        "企业微信唯一标识": null, 
        "相关部门": null, 
        "姓名": "王五", 
        "邮箱": "zhangsan@qq.com", 
        "职能": null, 
        "性别": "男"
    }, 
    {
        "员工编号": null, 
        "部门名称": "政府事业部", 
        "手机号": 61616116161616, 
        "角色": null, 
        "直属上级": "王五", 
        "职位": "首席运营官", 
        "企业微信唯一标识": null, 
        "相关部门": null, 
        "姓名": "李四", 
        "邮箱": "lisi@qq.com", 
        "职能": null, 
        "性别": "女"
    }
]
'''

　　2、openpyxl 创建一个 xlsx 文件

#! /usr/bin/env python
# -*- coding: utf-8 -*-
import json
from openpyxl import Workbook


# 创建 一个 xlsx 文件
def create_xlsx(data_list):
    wb = Workbook()  # 创建工作簿
    ws = wb.active  # 激活工作表
    ws1 = wb.create_sheet("Mysheet")  # 创建mysheet表
    ws.title = "New Title"  # 表明改为New Title
    ws.sheet_properties.tabColor = "1072BA"  # 颜色

    for row_index, row in enumerate(data_list):
        thead_list = data_list[0]  # ['id', 'name', 'sex', 'age']
        row_index += 1  # excel中 行是从 1 开始计算的

        for column_index, thead in enumerate(thead_list):
            column_index += 1  # excel中 行是从 1 开始计算的

            if row_index == 1:            #第一步： 填充第一行数据，表头（id  name  sex  age）
                d = ws.cell(row=1, column=column_index, value=thead)
            else:                         #第二步： 新建表中数据
                d = ws.cell(row=row_index, column=column_index, value=row[column_index-1])
        wb.save('test.xlsx')#保存


data_list = [
    ['id','name','sex','age'],
    ['1','张三','男','18'],
    ['2','李四','男','19'],
    ['3','王五','女','20'],
]
create_xlsx(data_list)

　　　　　　

　　3、openpyxl 修改 第十列的值

#! /usr/bin/env python
# -*- coding: utf-8 -*-
import json
from openpyxl import load_workbook


# 将表中第十行的值统一修改为 ‘new val’
def change_xlsx(file_home):
    wb = load_workbook(filename= file_home)
    sheet_ranges = wb['Sheet1']
    ws = wb['Sheet1']
    rows = ws.rows    #获取表格所有行和列，两者都是可迭代的
    for row, line in enumerate(rows):
        if row != 0:
            row += 1
            ws.cell(row=row, column=10).value = 'new val'
    wb.save('f5.xlsx')


change_xlsx('dd.xlsx')

#! /usr/bin/env python
# -*- coding: utf-8 -*-
from openpyxl import load_workbook

file_home = 'f5.xlsx'
wb = load_workbook(filename= file_home)
sheet_ranges = wb['Sheet1']
ws = wb['Sheet1']

# 将第二行，第二列的值修改成 '修改后的值'
ws.cell(row=2, column=2).value = '修改后的值'
wb.save('f6.xlsx')

　　4、在xlsx文件后面添加新的内容（插入时按照数据顺序）

#! /usr/bin/env python
# -*- coding: utf-8 -*-
import json, sys
from openpyxl import load_workbook


# 在xlsx文件最后添加新的数据，会按照顺序插入到xlsx文件对应位置
def append_xlsx(file_home, data_list):
    '''
    :param file_home: xlsx文件路径
    :param data_list: 插入的数据
    '''
    wb = load_workbook(filename= file_home)
    sheet_ranges = wb['Sheet1']
    ws = wb['Sheet1']
    print data_list
    ws.append(data_list)
    wb.save('f7.xlsx')


data_list = ['邮箱', '', '手机号', '性别']
append_xlsx('dd.xlsx',data_list)