Python读写Excel
读
#导入库 import xlrd #打开工作表 file = '7月表.xlsx') table = xlrd.open_workbook() #打开工作簿 sheet_n = xlsx.sheet_by_name("7月表") sheet_i = xlsx.sheet_by_index(0) #读取单元格 print(table.cell_value(2, 2)) print(table.cell(2, 2).value) print(table.row(2)[2].value)
写
#导入库 import xlwt #创建工作表 new_book = xlwt.Workbook() #创建工作簿 new_sheet = new_book.add_sheet("sheet_name") #sheet_name:工作簿名称 #写入单元格 new_sheet.write(0,0,'new_cell') #保存 file = r'D:\new_file\ceshi.xls' #ceshi:工作表名称 new_book.save(file)
复制工作表(无格式)
#导入库 import xlrd from xlutils.copy import copy #打开工作表 old_file = '7月表.xlsx' old_book = xlrd.open_workbook(old_file) #复制工作表 new_book = copy(old_book) #保存工作表 file = r'D:\new_file\ceshi.xls' #ceshi:工作表名称 new_book.save(file)
遍历工作簿
import xlrd,xlwt ole_tabel = xlrd.open_workbook('d:/7月表.xlsx') old_sheet = xlsx.sheet_by_index(0) new_workbook = xlwt.Workbook() new_sheet = new_workbook.add_sheet('new_test') #遍历工作簿中的单元格 for i in range(0,old_sheet.nrows): for j in range(0,old_sheet.ncols): worksheet.write(i, j, old_sheet.cell_value(i, j)) new_workbook.save('d:/test.xls')
写入Excel带格式
#样式名称 style = xlwt.XFStyle() #字体 font = xlwt.Font() font.name = '微软雅黑' font.bold = True font.height = 360 style.font = font #边框 borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.bottom= xlwt.Borders.THIN borders.left= xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders = borders #对齐方式 aligment = xlwt.Alignment() aligment.horz = xlwt.Alignment.HORZ_LEFT aligment.vert = xlwt.Alignment.VERT_TOP style.alignment = aligment
打开工作表,带格式复制,带格式填入舒服
#导入库 import xlrd,xlwt from xlutils.copy import copy #打开旧工作簿 old_file = r'D:/12 用Python自动办公,做职场高手(完结)/01.文件/【12.20更新课程代码】用Python自动办公做职场高手/CourseCode/Chapter1/S1-1-2/LessonCode/日统计.xls' old_book = xlrd.open_workbook(old_file,formatting_info= True) old_sheet = old_book.sheet_by_index(0) #复制工作簿 new_book = copy(old_book) new_sheet = new_book.get_sheet(0) #输入样式 style = xlwt.XFStyle() font = xlwt.Font() font.name = '微软雅黑' font.bold = True font.height = 360 style.font = font borders = xlwt.Borders() borders.top = xlwt.Borders.THIN borders.bottom= xlwt.Borders.THIN borders.left= xlwt.Borders.THIN borders.right = xlwt.Borders.THIN style.borders = borders aligment = xlwt.Alignment() aligment.horz = xlwt.Alignment.HORZ_LEFT aligment.vert = xlwt.Alignment.VERT_TOP style.alignment = aligment style_red = xlwt.XFStyle() font_red = xlwt.Font() font_red .name = '微软雅黑' font_red.colour_index = 2 font_red .bold = True font_red .height = 360 style_red .font = font_red borders_red = xlwt.Borders() borders_red .top = xlwt.Borders.THIN borders_red .bottom= xlwt.Borders.THIN borders_red .left= xlwt.Borders.THIN borders_red .right = xlwt.Borders.THIN style_red .borders = borders_red aligment_red = xlwt.Alignment() aligment_red .horz = xlwt.Alignment.HORZ_LEFT aligment_red .vert = xlwt.Alignment.VERT_TOP style_red .alignment = aligment_red style_18 = xlwt.XFStyle() font_18 = xlwt.Font() font_18 .name = '隶书' font_18 .bold = True font_18 .height = 360 style_18 .font = font_18 borders_18 = xlwt.Borders() borders_18 .top = xlwt.Borders.THIN borders_18 .bottom= xlwt.Borders.THIN borders_18 .left= xlwt.Borders.THIN borders_18 .right = xlwt.Borders.THIN style_18 .borders = borders_18 aligment_18 = xlwt.Alignment() aligment_18 .horz = xlwt.Alignment.HORZ_LEFT aligment_18 .vert = xlwt.Alignment.VERT_TOP style_18 .alignment = aligment_18 style_20 = xlwt.XFStyle() font_20 = xlwt.Font() font_20 .name = '隶书' font_20 .bold = True font_20 .height = 400 style_20 .font = font_20 borders_20 = xlwt.Borders() borders_20 .top = xlwt.Borders.THIN borders_20 .bottom= xlwt.Borders.THIN borders_20 .left= xlwt.Borders.THIN borders_20 .right = xlwt.Borders.THIN style_20 .borders = borders_20 aligment_20 = xlwt.Alignment() aligment_20 .horz = xlwt.Alignment.HORZ_LEFT aligment_20 .vert = xlwt.Alignment.VERT_TOP style_20 .alignment = aligment_20 stylex = lambda x: style_red if x > 10 else style #输入数字 zs_n = int(input("请输入张三:")) ls_n = int(input("请输入ls:")) ws_n = int(input("请输入ws:")) zl_n = int(input("请输入zl:")) #填入数据 new_sheet.write(0,0,old_sheet.cell_value(0,0),style_20) new_sheet.write(1,1,old_sheet.cell_value(1,1),style_18) new_sheet.write(1, 0, old_sheet.cell_value(1, 0), style_18) """new_sheet.write(1, 1, old_sheet.cell_value(1, 1), style_18) new_sheet.write(2, 0, old_sheet.cell_value(2, 0), style_18) new_sheet.write(3, 0, old_sheet.cell_value(3, 0), style_18) new_sheet.write(4, 0, old_sheet.cell_value(4, 0), style_18) new_sheet.write(5, 0, old_sheet.cell_value(5, 0), style_18)""" for i in range(1,6): new_sheet.write(i,0,old_sheet.cell_value(i,0),style_18) new_sheet.write(2,1,zs_n,stylex(zs_n)) new_sheet.write(3,1,ls_n,stylex(ls_n)) new_sheet.write(4,1,ws_n,stylex(ws_n)) new_sheet.write(5,1,zl_n,stylex(zl_n)) new_file = r'C:/Users/Administrator/Desktop/新建文件夹/1_2_p2.xls' new_book.save(new_file)
注意:
- 保存的文件格式为 xls,而不是xlsx,否则容易出错。
-
old_book = xlrd.open_workbook(old_file,formatting_info= True。如果为False,保存的格式则出错。比如无合并单元格。