一、读表格
1. 打开工作表
import xlrd
data = xlrd.open_workbook("1.xlsx")
print(data.sheet_loaded(0))
data.unload_sheet(0)
print(data.sheet_loaded(0))
print(data.sheets())
print(data.sheets()[0])
print(data.sheet_by_index(0))
print(data.sheet_by_name("Sheet1"))
print(data.sheet_names())
print(data.nsheets)
2. 操作 excel 行
sheet = data.sheet_by_index(0)
print(sheet.nrows)
print(sheet.row(2))
print(sheet.row_types(2))
print(sheet.row(1)[2].value)
print(sheet.row_values(1))
print(sheet.row_len(1))
3. 操作 excel 列
sheet = data.sheet_by_index(0)
print(sheet.ncols)
print(sheet.col(1))
print(sheet.col(1)[2])
print(sheet.col(1)[2].value)
print(sheet.col_values(1))
print(sheet.col_types(5))
4. 操作 excel 单元格
sheet = data.sheet_by_index(0)
print(sheet.cell(1, 2))
print(sheet.cell_type(1, 2))
print(sheet.cell(1, 2).ctype)
print(sheet.cell(1, 2).value)
print(sheet.cell_value(1, 2))
5. 完整代码
import xlrd
data = xlrd.open_workbook("1.xlsx")
print(data.sheet_loaded(0))
data.unload_sheet(0)
print(data.sheet_loaded(0))
print(data.sheets())
print(data.sheets()[0])
print(data.sheet_by_index(0))
print(data.sheet_by_name("Sheet1"))
print(data.sheet_names())
print(data.nsheets)
sheet = data.sheet_by_index(0)
print(sheet.nrows)
print(sheet.row(2))
print(sheet.row_types(2))
print(sheet.row(1)[2].value)
print(sheet.row_values(1))
print(sheet.row_len(1))
sheet = data.sheet_by_index(0)
print(sheet.ncols)
print(sheet.col(1))
print(sheet.col(1)[2])
print(sheet.col(1)[2].value)
print(sheet.col_values(1))
print(sheet.col_types(5))
sheet = data.sheet_by_index(0)
print(sheet.cell(1, 2))
print(sheet.cell_type(1, 2))
print(sheet.cell(1, 2).ctype)
print(sheet.cell(1, 2).value)
print(sheet.cell_value(1, 2))
二、写表格
1. 实例化字体对象
titleFont = xlwt.Font()
titleFont.name = '宋体'
titleFont.bold = True
titleFont.height = 11 * 20
titleFont.colour_index = 0x08
titleStyle.font = titleFont
2. 实例化单元格对齐方式
cellAlign = xlwt.Alignment()
cellAlign.horz = 0x02
cellAlign.vert = 0x01
titleStyle.alignment = cellAlign
3. 实例化单元格边框样式
border = xlwt.Borders()
border.right = xlwt.Borders.DASHED
border.bottom = xlwt.Borders.DOTTED
titleStyle.borders = border
4. 实例化单元格背景颜色
dateStyle = xlwt.XFStyle()
bgcolor = xlwt.Pattern()
bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN
bgcolor.pattern_fore_colour = 22
dateStyle.pattern = bgcolor
5. 写入数据
wb = xlwt.Workbook()
ws = wb.add_sheet("CNY")
ws.write_merge(0, 1, 0, 5, "2015年货币兑换表", titleStyle)
data = (("Date", "英镑", "人民币", "港币", "日元", "美元"),
("01/01/2019", 234.242, 2342, 424, 4353, 345),
("01/04/2019", 53.242, 54, 557, 575, 2424))
for i, item in enumerate(data):
for j, val in enumerate(item):
if j == 0:
ws.write(i + 2, j, val, dateStyle)
else:
ws.write(i + 2, j, val)
wsimage = wb.add_sheet("image")
wsimage.insert_bitmap("3.png", 0 , 0)
wb.save('2019年——CNY.xls')
6. 完整代码
from turtle import bgcolor
import xlwt
titleStyle = xlwt.XFStyle()
titleFont = xlwt.Font()
titleFont.name = '宋体'
titleFont.bold = True
titleFont.height = 11 * 20
titleFont.colour_index = 0x08
titleStyle.font = titleFont
cellAlign = xlwt.Alignment()
cellAlign.horz = 0x02
cellAlign.vert = 0x01
titleStyle.alignment = cellAlign
border = xlwt.Borders()
border.right = xlwt.Borders.DASHED
border.bottom = xlwt.Borders.DOTTED
titleStyle.borders = border
dateStyle = xlwt.XFStyle()
bgcolor = xlwt.Pattern()
bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN
bgcolor.pattern_fore_colour = 22
dateStyle.pattern = bgcolor
wb = xlwt.Workbook()
ws = wb.add_sheet("CNY")
ws.write_merge(0, 1, 0, 5, "2015年货币兑换表", titleStyle)
data = (("Date", "英镑", "人民币", "港币", "日元", "美元"),
("01/01/2019", 234.242, 2342, 424, 4353, 345),
("01/04/2019", 53.242, 54, 557, 575, 2424))
for i, item in enumerate(data):
for j, val in enumerate(item):
if j == 0:
ws.write(i + 2, j, val, dateStyle)
else:
ws.write(i + 2, j, val)
wsimage = wb.add_sheet("image")
wsimage.insert_bitmap("3.png", 0 , 0)
wb.save('2019年——CNY.xls')