#练习：创建Excel 如果要创建的Excel已经存在并打开，会报错 from openpyxl import Workbook wb=Workbook() #创建文件对象 ws=wb.active #获取第一个sheet ws["A1"]=22 #写入数字 ws["B1"]="王某某"+"automation test" #写入中文 ws.append([1,2,3]) #写入多个单元格,一行 import datetime import time ws["A2"]=datetime.datetime.now() #写入一个当前时间 ws["A3"]=time.strftime("%Y%m%d %H%M%S",time.localtime()) #写入一个自定义的时间格式 #在Excel中写入100个数字 #方法一： for i in range(100): ws.append(i) #方法二： x=1 data=map(lambda x:"A"+str(x),range(1,101)) for i in data: ws[i]=x x+=1 wb.save("e:\test4\sample.xlsx") #保存Excel #练习： from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("Mysheet",0) #创建一个sheet ws1.title = "New Title" #设定一个sheet的名字 ws2 = wb.create_sheet("Mysheet", 1) #设定sheet的插入位置 ws2.title = u"王某某" #设定一个sheet的名字 ws1.sheet_properties.tabColor = "1072BA" #设定sheet的标签的背景颜色 #获取某个sheet对象 print wb["New Title" ] #获取全部sheet 的名字，遍历sheet名字 print wb.sheetnames for sheet_name in wb.sheetnames: print sheet_name print "*"*50 for sheet in wb: print sheet.title #复制一个sheet wb["New Title" ]["A1"]=u"哈哈哈哈" source = wb["New Title" ] target = wb.copy_worksheet(source) #target=wb.copy_worksheet(wb["new title"]) # Save the file wb.save("e:\sample.xlsx") #练习：#生成一个excel文件，生成3个sheet，名称包含中文,每个sheet的a1写一下sheet的名称。每个sheet有个底色 from openpyxl import Workbook wb=Workbook() ws1 = wb.create_sheet("Mysheet1",0) ws1.title = u"王Wangjing1" ws1["A1"]=ws1.title ws1.sheet_properties.tabColor = "1072BA" ws2 = wb.create_sheet("Mysheet2",1) ws2.title = u"王Wangjing2" ws2["A1"]=ws1.title ws2.sheet_properties.tabColor = "1072BA" ws3 = wb.create_sheet("Mysheet3",2) ws3.title = u"王Wangjing3" ws3["A1"]=ws1.title ws3.sheet_properties.tabColor = "1072BA" wb.copy_worksheet(wb["wangjing3"]) wb.save("e:\sample.xlsx") #练习：给固定单元格赋值 from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("Mysheet") #创建一个sheet ws1["A1"]=3.1415 ws1["B2"]="王某某" d = ws1.cell(row=4, column=2, value=10) print ws1["A1"].value print ws1["B2"].value print d.value print ws1.cell(row=4, column=2, value=10).value # Save the file wb.save("e:\sample.xlsx") #练习：获取遍历某一列的值 from openpyxl import Workbook wb = Workbook() ws1 = wb.create_sheet("wangjing") #创建一个sheet ws1["A1"]=1 ws1["A2"]=2 ws1["A3"]=3 ws1["B1"]=4 ws1["B2"]=5 ws1["B3"]=6 ws1["C1"]=7 ws1["C2"]=8 ws1["C3"]=9 #操作单列 print ws1["A"] for cell in ws1["A"]: print cell.value #遍历A到C列，然后打印每一个单元格的值 print ws1["A:C"] for column in ws1["A:C"]: for cell in column: print cell.value #操作多行，然后打印每个单元格的值 row_range = ws1[1:3] print row_range for row in row_range: for cell in row: print cell.value #操作某一行 for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3): for cell in row: print cell.value #获取所有行 print ws1.rows for row in ws1.rows: print row print "*"*50 #获取所有列 print ws1.columns for col in ws1.columns: print col # Save the file wb.save("e:\sample.xlsx")
import xlrd #打开Excel文件 data=xlrd.open_workbook("e:\test4\s.xlsx") print type(data) #通过索引获取sheet tableList=data.sheets()[0] print type(tableList) print tableList #通过名字获取指定sheet table=data.sheet_by_name("wangjing") print type(table) print table #通过索引号获取 table=data.sheet_by_index(0) print type(table) print table print u"索引号为0的工作表为：",table.name #获取整行,索引从0开始，返回的是一个存有该行所有内容的list rowList=table.row_values(2) print type(rowList) print rowList #获取整列，索引从0开始，返回一个存有该列所有内容的list columnList=table.col_values(0) print type(columnList) print columnList #获取行数 rowNum=table.nrows print u"行数：",rowNum #获取列数 colNum=table.ncols print u"列数：",colNum #获取某个单元格的值 cell=table.cell(2,1).value print u"单元格的值为：",cell #创建Excel对象 import xlrd,xlwt workbook=xlwt.Workbook(encoding="utf-8") print type(workbook) #创建excel表 worksheet=workbook.add_sheet("s2.xlsx") print u"创建的Excel表的表名为：",worksheet.name #写单元格和保存 import xlrd,xlwt style=xlwt.easyxf("pattern: pattern solid, fore_color green") #创建workbook对象 workbook=xlwt.Workbook(encoding="utf-8") #创建工作表 worksheet=workbook.add_sheet("s2") print u"创建的Excel表的表名为：",worksheet.name #像工作表中写内容，并设置单元格格式 worksheet.write(r=1,c=2,label=u"这是一个测试",style=style) #将创建好的Excel写入硬盘 workbook.save("e:\test4\Excel_test.xls")