openpyxl读写excel

【openpyxl】、【pandas】、【xlrd、xlwt】读写excel的区别

openpyxl读写excel

　　https://www.cnblogs.com/hudieren/p/16792200.html

pandas读写excel

　　https://www.cnblogs.com/hudieren/p/16792215.html

xlrd读excel文件、xlwt写入excel文件

　　https://www.cnblogs.com/hudieren/p/16792234.html

建议使用openpyxl写入，使用xlrd读取，xlwt速度也快，不使用的原因是只可以生成.xls文件

# -*- coding: utf-8 -*-
# @Author  : 107
# @File    : openpyxl读写.py
# @explain :
import time
import json
from openpyxl import Workbook, load_workbook
"""
单元格样式
http://www.360doc.com/content/22/1010/10/77611998_1051128279.shtml
pip install openpyxl
"""

def read_xlsx():
    wb = load_workbook("test.xlsx", read_only=True, data_only=True)

    sheet1 = wb["地区"]
    print(sheet1['A1'].value)
    print(sheet1['B1'].value)
    for row in sheet1.rows:
        data = [d.value for d in row]
        print(data)

    sheet2 = wb["姓"]

    for row in sheet2.rows:
        data = [d.value for d in row]
        print(data)

    sheet3 = wb["男名"]
    for row in sheet3.rows:
        data = [d.value for d in row]
        print(data)

    sheet4 = wb["女名"]
    for row in sheet4.rows:
        data = [d.value for d in row]
        print(data)


def write_xlsx():
    with open("person.json", "r", encoding="utf-8") as r:
        data = json.loads(r.read())
    area_info = data.get("area_info")
    xing = data.get("xing")
    man = data.get("man")
    girl = data.get("girl")

    wb = Workbook()

    sheet1 = wb.create_sheet("地区", 0)  # 创建一个 地区 的工作表，放在第一位
    # sheet1.title = "呵呵呵"    # 修改sheet名字
    # sheet1["A1"] = "张三"     # 修改单个单元格内容
    # sheet1.append(["代码", "地区"])     # 同时写入多个数据
    # sheet1["A6"] = "=sum(A4:A5)"    # 公式：对A4、A5求和放入A6
    sheet1.cell(row=1, column=1, value="代码")  # 第一行第一列都是从1开始的
    sheet1.cell(row=1, column=2, value="地区")
    sheet1_count = 2
    for k, v in area_info.items():
        sheet1.cell(sheet1_count, 1, k)
        sheet1.cell(sheet1_count, 2, v)
        sheet1_count += 1

    sheet2 = wb.create_sheet("姓", 1)
    sheet2.cell(row=1, column=1, value="姓")
    sheet2_count = 2
    for x in xing:
        sheet2.cell(sheet2_count, 1, x)
        sheet2_count += 1

    sheet3 = wb.create_sheet("男名", 2)  # 创建一个 地区 的工作表，放在第一位
    sheet3.cell(row=1, column=1, value="男名")
    sheet3_count = 2
    for x in man:
        sheet3.cell(sheet3_count, 1, x)
        sheet3_count += 1

    sheet4 = wb.create_sheet("女名", 3)  # 创建一个 地区 的工作表，放在第一位
    sheet4.cell(row=1, column=1, value="女名")
    sheet4_count = 2
    for x in girl:
        sheet4.cell(sheet4_count, 1, x)
        sheet4_count += 1

    wb.save("test.xlsx")


if __name__ == '__main__':
    start_time = int(time.time() * 1000)
    # read_xlsx()  # 364 毫秒
    # write_xlsx()  # 366 毫秒
    end_time = int(time.time() * 1000)
    print(f"共花费 {end_time - start_time} 毫秒")