Python__模块(office-表格)__xlwt / xlrd

xlwt（简介）

表格写入模块

---

xlwt（安装）

pip install xlwt

---

xlwt（参考代码）

创建 Excel

import xlwt
book = xlwt.Workbook(encoding="utf-8")          # 编码
worksheet = book.add_sheet("My Worksheet")      # 表名
worksheet.write(0, 0, label="this is test1")    # 参数:行,列,label='内容'
worksheet.write(0, 1, label="this is test2")
book.save("Excel_test.xls")  # 保存文件(文件名)

Excel 样式

import xlwt
workbook = xlwt.Workbook(encoding="ascii")
worksheet = workbook.add_sheet("My Worksheet")
style = xlwt.XFStyle()      # 初始化样式
font = xlwt.Font()          # 为样式创建字体
font.name = "Times New Roman"
font.bold = True            # 黑体
font.underline = True       # 下划线
font.italic = True          # 斜体字
style.font = font           # 设定样式
worksheet.write(0, 0, "Unformatted value")       # 不带样式的写入
worksheet.write(1, 0, "Formatted value", style)  # 带样式的写入
workbook.save("formatting.xls")                  # 保存文件

Excel 设置单元格宽度

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("My Sheet")
worksheet.write(0, 0, "My Cell Contents")
# 设置单元格宽度
worksheet.col(0).width = 3333
workbook.save("cell_width.xls")

Excel 添加连接

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("My Sheet")
worksheet.write(0, 0, xlwt.Formula("HYPERLINK('http://www.xxx.com';'test')"))
workbook.save("Excel_Workbook.xls")

Excel 合并单元格

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("My Sheet")
worksheet.write_merge(0, 0, 0, 3, "First Merge")  # Merges row 0's columns 0 through 3.
font = xlwt.Font()      # Create Font
font.bold = True        # Set font to Bold
style = xlwt.XFStyle()  # Create Style
style.font = font       # Add Bold Font to Style
worksheet.write_merge(
    1, 2, 0, 3, "Second Merge", style
)  # Merges row 1 through 2's columns 0 through 3.
workbook.save("Excel_Workbook.xls")

Excel 颜色设置

import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet("My Sheet")
pattern = xlwt.Pattern()         # Create the Pattern
pattern.pattern = (
    xlwt.Pattern.SOLID_PATTERN
)  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5  # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
style = xlwt.XFStyle()           # Create the Pattern
style.pattern = pattern          # Add Pattern to Style
worksheet.write(0, 0, "Cell Contents", style)
workbook.save("Excel_Workbook.xls")

---

xlrd（简介）

表格读取模块

---

xlrd（安装）

pip install xlrd

---

xlrd（参考代码）

Excel 读取

import xlrd

excel = xlrd.open_workbook("test.xls")
all_sheet_names = excel.sheet_names()  # sheet名

""" 获取第一个sheet """
print(all_sheet_names)
# shett=excel.sheets()[0]

""" 通过名称获取 sheet """
sheet = excel.sheet_by_name("My Sheet")
print(sheet)

""" 获取sheet表的 行数 """
row = sheet.nrows
print(row)

""" 获取第一行的所有内容,返回列表 """
row_value = sheet.row(0)
print(row_value)

""" 返回指定行的有效长度 """
print(sheet.row_len(0))

""" 获取sheet表的 列数 """
col = sheet.ncols
print(col)

""" 获取第一列的所有内容,返回列表 """
col_value = sheet.col(0)
print(col_value)

""" 返回指定列的所有单元格数值组成的列表，可以截取部分行 """
# worksheet.col_values(colx, start_colx=0, end_colx=None)
# 例子:sheet.col_values(1,0,10)
print("第2列0~10行之间内容输出: ", sheet.col_values(1, 0, 10))

"""指定行列取值 """
value = sheet.cell_value(0, 1)
print(value)

---