xlrd读excel文件、xlwt写入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 -*-
# @Time    :  14:32
# @Author  : 107
# @File    : xlrd_xlwt.py
# @Software: PyCharm Community Edition
# @explain :
import xlrd
import xlwt
import json
import time
# xlrd读取的数据有类型，xldate_as_datetime用来处理时间类型的
from xlrd import xldate_as_datetime

from datetime import datetime


def read_xls():
    path = r"test.xls"
    work_book = xlrd.open_workbook(path)
    # 所有表格名称
    # sheet_names = work_book.sheet_names()
    # print(sheet_names)
    sheet1 = work_book.sheet_by_index(0)
    sheet2 = work_book.sheet_by_index(1)
    sheet3 = work_book.sheet_by_index(2)
    sheet4 = work_book.sheet_by_index(3)
    # print(f"表格行数：{nrows}, 表格列数：{ncols}")
    for row in range(sheet1.nrows):
        s = sheet1.row_values(row)
        print(s)
    for row in range(sheet2.nrows):
        s = sheet2.row_values(row)
        print(s)
    for row in range(sheet3.nrows):
        s = sheet3.row_values(row)
        print(s)
    for row in range(sheet4.nrows):
        s = sheet4.row_values(row)
        print(s)


def write_xls():
    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")

    wk = xlwt.Workbook()
    sheet1 = wk.add_sheet("地区", cell_overwrite_ok=True)
    sheet1.write(0, 0, "代码")
    sheet1.write(0, 1, "地区")
    sheet1_count = 0
    for k, v in area_info.items():
        sheet1.write(sheet1_count, 0, k)
        sheet1.write(sheet1_count, 1, v)
        sheet1_count += 1

    sheet2 = wk.add_sheet("姓", cell_overwrite_ok=True)
    sheet2.write(0, 0, "姓")
    sheet2_count = 1
    for x in xing:
        sheet2.write(sheet2_count, 0, x)
        sheet2_count += 1

    sheet3 = wk.add_sheet("男名", cell_overwrite_ok=True)
    sheet3.write(0, 0, "男名")
    sheet3_count = 1
    for x in man:
        sheet3.write(sheet3_count, 0, x)
        sheet3_count += 1

    sheet4 = wk.add_sheet("女名", cell_overwrite_ok=True)
    sheet4.write(0, 0, "女名")
    sheet4_count = 1
    for x in girl:
        sheet4.write(sheet4_count, 0, x)
        sheet4_count += 1

    wk.save("test.xls")


if __name__ == '__main__':
    start_time = int(time.time() * 1000)
    # read_xls()     # 106毫秒
    # write_xls()  # 188毫秒
    end_time = int(time.time() * 1000)
    print(f"共花费 {end_time - start_time} 毫秒")