python操作Excel的包 | xlrd | xlxt | openpyxl

import xlrd



# 01.打开excel文件,创建一个文件数据对象
data = xlrd.open_workbook('/home/python/Desktop/excel/test1.xls')
# print(data)

# 02.获取一张表
# table = data.sheets()[0]  # 1.通过索引获取
# table = data.sheet_by_index(0)  # 2.通过函数获取索引
table = data.sheet_by_name('Sheet1')  # 3.通过表名获取
# print(table)

# 03.获取行或者列的值(数组)
# print(table.row_values(0, start_colx=1, end_colx=3))  # 获取行,限制列
# row_values()包含3个参数:
#       1.第几行,从0开始;
#       2.首列:start_colx=0;
#       3.尾列:end_colx=None;(左闭右开)
# >>> [1.0, '大神', '裸男', '狗东西']
# >>> ['大神', '裸男']
# print(table.col_values(0, start_rowx=1, end_rowx=5))  # 获取列,限制行
# >>> ['大神', '裸男']
# >>> [2.0, 3.0, 4.0, 5.0]

# 04.获取行数和列数
# print(table.nrows)
# print(table.ncols)

import xlwt


# 1.创建excel文件
test_excel = xlwt.Workbook(encoding="ascii")

# 2.创建表
test_sheet = test_excel.add_sheet('sheet01')

# 3.往表中内写入数据
test_sheet.write(0, 0, label="写一点东西")  # 指定单元格写入数据

# 4.保存excel文件
test_excel.save('/home/python/Desktop/excel/test3.xls')

import xlrd
import xlwt
from xlutils.copy import copy


# 打开1.矩阵表20180214起.xlsx中第二张表
data = xlrd.open_workbook('1.矩阵表20180214起.xlsx')

# 获取 矩阵表到员20180913更新 表
table = data.sheet_by_name('矩阵表到员20180913更新')

# 遍历所有行，将W,H列构建字典
groups = table.col_values(7, start_rowx=7, end_rowx=724)
tels = table.col_values(55, start_rowx=7, end_rowx=724)
keys = table.col_values(22, start_rowx=7, end_rowx=724)

total_dict = {}

for i in range(len(keys)):
    if keys[i] and keys[i] != "/":
        total_dict[keys[i]] = {
            "group": groups[i],
            "tel": tels[i],
        }

# 读取
data02 = xlrd.open_workbook('员工工号20181011.xlsx')

table02 = data02.sheets()[0]
# 读取人名
name_list = table02.col_values(9, start_rowx=2, end_rowx=473)
temp_list = []
for name in name_list:
    try:
        name = total_dict[name]
    except:
        name = ""
    temp_list.append(name)
# 写入
new_excel = copy(data02)

sheet01 = new_excel.get_sheet(0)  # 14
j = 0
for i in range(2, 474):  # 3-474
    try:
        group_name = temp_list[j]["group"]
    except:
        group_name = ""
    try:
        tel = temp_list[j]["tel"]
    except:
        tel = ""
    sheet01.write(i, 14, group_name)
    sheet01.write(i, 15, tel)
    j += 1

new_excel.save("new_fileName.xls")

import xlrd
import xlwt
from xlutils.copy import copy


# 打开1.矩阵表20180214起.xlsx中第二张表
data = xlrd.open_workbook('1.矩阵表20180214起.xlsx')

# 获取 矩阵表到员20180913更新 表
table = data.sheet_by_name('矩阵表到员20180913更新')

# 遍历所有行，将W,H列构建字典
groups = table.col_values(7, start_rowx=7, end_rowx=724)
tels = table.col_values(55, start_rowx=7, end_rowx=724)
keys = table.col_values(22, start_rowx=7, end_rowx=724)

total_dict = {}

for i in range(len(keys)):
    if keys[i] and keys[i] != "/":
        total_dict[keys[i]] = {
            "group": groups[i],
            "tel": tels[i],
        }

# 读取
data02 = xlrd.open_workbook('员工工号20181011.xlsx')

table02 = data02.sheets()[0]
# 读取人名
name_list = table02.col_values(9, start_rowx=2, end_rowx=473)
temp_list = []
for name in name_list:
    try:
        name = total_dict[name]
    except:
        name = ""
    temp_list.append(name)
# 写入
new_excel = copy(data02)

sheet01 = new_excel.get_sheet(0)  # 14
j = 0
for i in range(2, 474):  # 3-474
    try:
        group_name = temp_list[j]["group"]
    except:
        group_name = ""
    try:
        tel = temp_list[j]["tel"]
    except:
        tel = ""
    sheet01.write(i, 14, group_name)
    sheet01.write(i, 15, tel)
    j += 1

new_excel.save("new_fileName.xls")