Python 自动化操作 Excel - 01 - xlrd 原创

Python 自动化操作 Excel

Python 自动化操作 Excel - 01 - xlrd

Python 自动化操作 Excel - 01 - xlrd

1.安装xlrd

pip install xlrd

或

pip3 install xlrd

2.建立表格

id	数值列1	数值列2
1	A	B
2	A	B
3	A	B
4	A	B
5	A	B
6	A	B

3.使用xlrd

import xlrd

# 1 读取xlsx
book = xlrd.open_workbook("./xlrd_excel.xlsx")

# 读取sheet
# 获取第1个sheet 方法1
sheet = book.sheets()[0]
# 方法2
# sheet = people.sheet_by_index(0)
# 方法3
# sheet = people.sheet_by_name("Sheet1")

# 读取表格
# sheet.cell_value(row, col)
data1 = sheet.cell_value(1, 0)
data2 = sheet.cell_value(1, 1)
print(f"data1: {data1}")
print(f"data2: {data2}")

# 读取所有的工作表
sheets = book.sheets()
for sheet in sheets:
    # 行数
    nrows = sheet.nrows
    # 列数
    ncols = sheet.ncols
    for row in range(0, nrows):
        for col in range(0, ncols):
            print(sheet.cell_value(row, col))

# 读入日期
time_value1 = sheet.cell_value(1, 6)
time_value2 = sheet.cell_value(2, 6)
# 将读入的日期转换为元组的形式
# (2019, 5, 6, 12, 10, 32)
time_tuple = xlrd.xldate_as_tuple(time_value1, 0)

# 将日期数据转换为datetime对象
time_datetime = xlrd.xldate_as_datetime(time_value1, 0)
# 将datetime对象格式化转化为对应的字符串
# 2019-05-06
time_str = time_datetime.strftime("%Y-%m-%d %H:%M:%S")