Python读写Excel

阅读量：4696 次

发布时间：2019-06-09

本文共 4900 字，大约阅读时间需要 16 分钟。

读

#导入库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 xlrdfrom 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,xlwtole_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 = Truefont.height = 360style.font = font#边框borders = xlwt.Borders()borders.top = xlwt.Borders.THINborders.bottom= xlwt.Borders.THINborders.left= xlwt.Borders.THINborders.right = xlwt.Borders.THINstyle.borders = borders#对齐方式aligment = xlwt.Alignment()aligment.horz = xlwt.Alignment.HORZ_LEFTaligment.vert = xlwt.Alignment.VERT_TOPstyle.alignment = aligment

打开工作表，带格式复制，带格式填入舒服

#导入库import xlrd,xlwtfrom 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 = Truefont.height = 360style.font = fontborders = xlwt.Borders()borders.top = xlwt.Borders.THINborders.bottom= xlwt.Borders.THINborders.left= xlwt.Borders.THINborders.right = xlwt.Borders.THINstyle.borders = bordersaligment = xlwt.Alignment()aligment.horz = xlwt.Alignment.HORZ_LEFTaligment.vert = xlwt.Alignment.VERT_TOPstyle.alignment = aligmentstyle_red = xlwt.XFStyle()font_red = xlwt.Font()font_red .name  = '微软雅黑'font_red.colour_index = 2font_red .bold = Truefont_red .height = 360style_red .font = font_redborders_red  = xlwt.Borders()borders_red .top = xlwt.Borders.THINborders_red .bottom= xlwt.Borders.THINborders_red .left= xlwt.Borders.THINborders_red .right = xlwt.Borders.THINstyle_red .borders = borders_redaligment_red  = xlwt.Alignment()aligment_red .horz = xlwt.Alignment.HORZ_LEFTaligment_red .vert = xlwt.Alignment.VERT_TOPstyle_red .alignment = aligment_redstyle_18 = xlwt.XFStyle()font_18 = xlwt.Font()font_18 .name  = '隶书'font_18 .bold = Truefont_18 .height = 360style_18 .font = font_18borders_18  = xlwt.Borders()borders_18 .top = xlwt.Borders.THINborders_18 .bottom= xlwt.Borders.THINborders_18 .left= xlwt.Borders.THINborders_18 .right = xlwt.Borders.THINstyle_18 .borders = borders_18aligment_18  = xlwt.Alignment()aligment_18 .horz = xlwt.Alignment.HORZ_LEFTaligment_18 .vert = xlwt.Alignment.VERT_TOPstyle_18 .alignment = aligment_18style_20 = xlwt.XFStyle()font_20 = xlwt.Font()font_20 .name  = '隶书'font_20 .bold = Truefont_20 .height = 400style_20 .font = font_20borders_20  = xlwt.Borders()borders_20 .top = xlwt.Borders.THINborders_20 .bottom= xlwt.Borders.THINborders_20 .left= xlwt.Borders.THINborders_20 .right = xlwt.Borders.THINstyle_20 .borders = borders_20aligment_20  = xlwt.Alignment()aligment_20 .horz = xlwt.Alignment.HORZ_LEFTaligment_20 .vert = xlwt.Alignment.VERT_TOPstyle_20 .alignment = aligment_20stylex = 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,保存的格式则出错。比如无合并单元格。

转载于:https://www.cnblogs.com/qianslup/p/11146585.html

你可能感兴趣的文章

[NYIST15]括号匹配（二）（区间dp）

查看>>

json_value.cpp : fatal error C1083: 无法打开编译器生成的文件:No such file or directory

HDU-1085 Holding Bin-Laden Captive-母函数

查看>>

php提示undefined index的几种解决方法

Linux: Check version info

查看>>

stl学习之测试stlen，cout等的运行速度

Python和Singleton （单件）模式[转载]

查看>>

httpclient设置proxy与proxyselector

查看>>

IT常用单词

查看>>