1. xlrd模块
- xlrd是第三方模块,读取Excel表格数据
- xlrd常用函数
- xlrd操作Excel列
- xlrd操作Excel行
- xlrd操作Excel单元格
示例代码
import xlrd
data = xlrd.open_workbook(r'F:\base\elementInfo.xls')
print(data.sheet_loaded(0))
print(data.sheets())
print(data.sheets()[0])
print(data.sheet_by_name('Sheet1'))
print(data.sheet_by_index(0))
print(data.sheet_names())
print(data.nsheets)
sheet = data.sheet_by_index(0)
print(sheet.nrows)
print(sheet.row(0))
print(sheet.row(1))
print(sheet.row_types(3))
print(sheet.row(4)[1])
print(sheet.row(4)[1].value)
print(sheet.row_values(4))
print(sheet.row_len(4))
sheet = data.sheet_by_index(0)
print(sheet.ncols)
print(sheet.col(1))
print(sheet.col(1)[9].value)
print(sheet.col_values(1))
print(sheet.col_types(1))
sheet = data.sheet_by_index(0)
print(sheet.cell(9, 1))
print(sheet.cell_type(9, 1))
print(sheet.cell(9, 1).ctype)
print(sheet.cell(9, 1).value)
print(sheet.cell_value(9, 1))
2. xlwt模块
- xlwt是第三方模块,写入Excel表格数据
- 支持创建工作表、指定单元格、指定单元格样式等
- xlwt支持excel的03~2013版Excel。
- 保存格式只支持xls格式,不支持07版以后的xlsx格式
import xlwt
titleStyle = xlwt.XFStyle()
titleFont = xlwt.Font()
titleFont.name = '宋体'
titleFont.bold = True
titleFont.height = 11 * 20
titleFont.colour_index = 0x35
titleStyle.font = titleFont
cellalign = xlwt.Alignment()
cellalign.horz = 0x02
cellalign.vert = 0x01
titleStyle.alignment = cellalign
borders = xlwt.Borders()
borders.right = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DOTTED
titleStyle.borders = borders
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('工作表1')
ws.write_merge(0, 1, 0, 5, "2020年人民币兑换表", style=titleStyle)
data = (("日期", "拉拉", "啦啦啦", "哈哈", "哈哈哈"), ("05/27/2020", 100000, 999999, 666666, 6.3333),
("05/27/2020", 100000, 999999, 666666, 6.3333), ("05/27/2020", 100000, 999999, 666666, 6.3333),
("05/27/2020", 100000, 999999, 666666, 6.3333))
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('兰彩云')
wsimage.insert_bitmap("meinv.bmp", 0, 0)
wb.save("test.xls")
3 xlsxwriter模块
- xlsxwriter是python的第三方模块,用于向生成的Excel表格插入数据、图表等操作
最终生成xlsx格式的Excel文件 - 功能比xlwt模块更加强大,支持更多的Excel功能
- 100%兼容Excel xlsx文件、支持Excel 2003 2007等版本
- 处理速度更快,支持大文件写入
- 写入时可以启用内存优化模式来快速写入
- 不足:不支持读取和修改Excel文件(读取Excel文件可以使用xlrd模块)
import xlsxwriter
wb = xlsxwriter.Workbook('yinlei.xlsx')
cell_format = wb.add_format({'bold': True})
cell_format1 = wb.add_format()
cell_format1.set_bold()
cell_format1.set_font_color('orange')
cell_format1.set_font_size(15)
cell_format1.set_align('center')
cell_format2 = wb.add_format()
cell_format2.set_bg_color('#FF00FF')
sheet = wb.add_worksheet("yinleisheet")
sheet.write(0, 0, "2020年", cell_format)
sheet.merge_range(1, 0, 2, 2, '全国女朋友彩礼统计', cell_format=cell_format1)
data = (
["四川", 5, 15],
["江西", 30, 50],
["乐山", 0, 10],
)
sheet.write_row(3, 0, ["地区", "最低金额(单位:万)", "最高金额(单位:万)"], cell_format=cell_format2)
for index, item in enumerate(data):
sheet.write_row(index+4, 0, item)
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
sheet.write_url(9, 0, "http://yinleilei.com", string="跳转到个人网站")
sheet.insert_image(10, 0, "plant.png")
chart = wb.add_chart({'type': 'line'})
chart.set_title({'name': '全国女朋友彩礼统计'})
chart.set_x_axis({'name': '地区'})
chart.set_y_axis({'name': '金额'})
chart.add_series({
'name': '最低彩礼',
'categories': '=yinleisheet!$A$5:$A$7',
'values': ['yinleisheet', 4, 1, 6, 1]
})
chart.add_series({
'name': '最高彩礼',
'categories': '=yinleisheet!$A$5:$A$7',
'values': ['yinleisheet', 4, 2, 6, 2],
'data_labels': {'value': True},
})
sheet.insert_chart('E4', chart=chart)
wb.close()