xlrd读取Excel（仅支持xls格式）import xlrddata=xlrd.open_workbook("excel.xls")# # 判断0工作表是否被加载# print(data.sheet_loaded(0))# # 卸载工作表后再输出为false表示未加载# data.unload_sheet(0)# print(data.sheet_loaded(0))# 打印所有sheet 与 索引为1的sheetprint(data.sheets())print(data.sheets()[0])# 根据索引名字获取 Sheet 0:<Sheet1>print(data.sheet_by_index(0))print(data.sheet_by_name("Sheet1"))# 获取所有工作表名字 ['Sheet1', 'Sheet2', 'Sheet3']print(data.sheet_names())# 获取sheets总数 3print(data.nsheets)# 操作行列sheet=data.sheet_by_index(0)# 获取总行数print(sheet.nrows)# 获取索引行内容print(sheet.row(0))# 获取行类型 array('B', [1, 0, 0, 0]) 数字表示类型print(sheet.row_types(0))# 第一行第一个单元格print(sheet.row(0)[0])print(sheet.row(0)[0].value)# 纯获取行直print(sheet.row_values(0))# 行长度print(sheet.row_len(0))# 列数print(sheet.ncols)print(sheet.col(0))# 获取指定单元格值print(sheet.col(0)[0].value)# 列所有值 类型print(sheet.col_values(0))print(sheet.col_types(0))# 操作单元格 坐标行列print(sheet.cell(0,0))print(sheet.cell(0,0).value)print(sheet.cell_value(0,0))# 查看单元格类型 ctype亦可print(sheet.cell_type(0,0))print(sheet.cell(0,0).ctype)xlwt写出Excelimport xlwt# excel写入(xlwt) 创建工作簿# 初始化样式titlestyle = xlwt.XFStyle()titlefont = xlwt.Font()titlefont.name = "宋体"titlefont.bold = Truetitlefont.height = 11 * 20 # 11字号 20单位titlefont.colour_index = 0x08titlestyle.font = titlefont# 对齐方式cellalign = xlwt.Alignment()# 水平居中cellalign.horz = 0x02# 垂直居中cellalign.vert = 0x01titlestyle.alignment = cellalign# 边框borders = xlwt.Borders()borders.right = xlwt.Borders.DASHEDborders.bottom = xlwt.Borders.DASHEDtitlestyle.borders = borderswb = xlwt.Workbook()# 背景颜色样式title1style = xlwt.XFStyle()# 颜色模式bgcolor = xlwt.Pattern()bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN# 设置颜色bgcolor.pattern_fore_colour = 22title1style.pattern = bgcolor# 创建sheetws = wb.add_sheet("2020年度")# 填充数据 参数：r1 r2 c1 c2 文字，即行列范围合并并填充文字ws.write_merge(0, 1, 0, 5, "2020年报表", titlestyle)# 写入数据data = (("title1", "title2", "title3", "title4", "title5"), ("1", "2", "3", "4", "5"), ("5", "4", "3", "2", "1"))# enumerate循环返回索引for i, item in enumerate(data): for j, val in enumerate(item): if j == 0: ws.write(i + 2, j, val, title1style) else: # 跳过前两行 ws.write(i + 2, j, val)# 插入第二个sheet 添加图片wsimg = wb.add_sheet("img")# 插入图片wsimg.insert_bitmap("2020.bmp", 0, 0)# 写入到当前目录wb.save("2020_BG.xls")

普通替换，不保留样式# Open Excel file from a user imputimport xlrd, xlwtfilename = raw_input("Enter Excel file name with extension (.xls) and path")oldbook = xlrd.open_workbook(filename)newbook = xlwt.Workbook()# For all the sheets in the workbookfor sheetname in oldbook.sheet_names(): oldsheet = oldbook.sheet_by_name(sheetname) newsheet = newbook.add_sheet(sheetname) # For all the rows and all the columns in an excel for ii in range(oldsheet.nrows): for jj in range(oldsheet.ncols): # Replace CellString=str(oldsheet.cell(ii, jj).Value) CellString=CellString.replace("%", "Perc") CellString=CellString.replace(" ", "_") newsheet.write(ii, jj, CellString)# Save the file in a desired location with the desired namesavelocation = raw_input("Enter a new path and file name with extension (.xls) to save the new Excel spread sheet ")newbook.save(savelocation)运用xlutils可保留样式import xlrdfrom xlutils.filter import process, XLRDReader, XLWTWriterrb = xlrd.open_workbook('excel.xls', formatting_info=True)# 参考xlutils.copy库内的用法 参考xlutils.filter内的参数定义style_listw = XLWTWriter()process(XLRDReader(rb, 'unknown.xls'), w)wb = w.output[0][1]style_list = w.style_listpeple = ("张三", "李四", "王五")for i in range(len(peple)): for j in range(i): left = peple[i] right = peple[j] for n, sheet in enumerate(rb.sheets()): sheet2 = wb.get_sheet(n) for r in range(sheet.nrows): for c, cell in enumerate(sheet.row_values(r)): style = style_list[sheet.cell_xf_index(r, c)] val = sheet.cell_value(r, c) if val == "{left}": val = left elif val == "{right}": val = right sheet2.write(r, c, val, style) # print(str(i) + str(j)+left+right) wb.save('比赛(' + left + "vs" + right + ').xls')