Python_excel基本操作二(Openpyxl)
六、现有文件的引用和操作(行操作)
6、现有文件的引用和操作(行操作) #coding=utf-8 from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('e:\\sample.xlsx') ws=wb.active rows=[] for row in ws.iter_rows(): rows.append(row) print (rows) #所有行 print (rows[0]) #获取第一行 print (rows[0][0]) #获取第一行第一列的单元格对象 print (rows[0][0].value) #获取第一行第一列的单元格对象的值 print (rows[len(rows)-1]) #获取最后行 print (rows[len(rows)-1][len(rows[0])-1]) #获取第后一行和最后一列的单元格对象 print (rows[len(rows)-1][len(rows[0])-1].value) #获取第后一行和最后一列的单元格对象的值
七、现有文件的引用和操作(列操作)
#coding=utf-8 from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('e:\\sample.xlsx') ws=wb.active cols=[] cols = [] for col in ws.iter_cols(): cols.append(col) print (cols) #所有列 print (cols[0]) #获取第一列 print (cols[0][0]) #获取第一列的第一行的单元格对象 print (cols[0][0].value) #获取第一列的第一行的值 print ("*"*30) print (cols[len(cols)-1]) #获取最后一列 print (cols[len(cols)-1][len(cols[0])-1]) #获取最后一列的最后一行的单元格对象 print (cols[len(cols)-1][len(cols[0])-1].value) #获取最后一列的最后一行的单元格对象的值
八、查看格式
# -*- coding: utf-8 -*- from openpyxl import Workbook from openpyxl import load_workbook import datetime wb = load_workbook('e:\\sample.xlsx') ws=wb.active wb.guess_types = True#猜测格式类型
ws["A1"]=datetime.datetime(2010, 7, 21) print (ws["A1"].number_format) ws["A2"]="12%" print (ws["A2"].number_format) ws["A3"]= 1.1 print (ws["A3"].number_format) ws["A4"]= "中国" print (ws["A4"].number_format) # Save the file wb.save("e:\\sample.xlsx")
运行结果:
九、打印公式的内容
(因为是excel执行的,打印的东西是公式)
from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('e:\\sample.xlsx') ws1=wb.active ws1["A1"]=1 ws1["A2"]=2 ws1["A3"]=3 ws1["A4"] = "=SUM(1, 1)" ws1["A5"] = "=SUM(A1:A3)" print (ws1["A4"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 print (ws1["A5"].value) #打印的是公式内容,不是公式计算后的值,程序无法取到计算后的值 # Save the file wb.save("e:\\sample.xlsx")
十、合并/拆分单元格
# -*- coding: utf-8 -*- from openpyxl import Workbook from openpyxl import load_workbook wb = load_workbook('e:\\sample.xlsx') ws1=wb.active from openpyxl.workbook import Workbook wb = Workbook() ws = wb.active ws.merge_cells('A2:D2')#合并 ws.unmerge_cells('A2:D2')#拆分 # or equivalently ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)#合并相关 ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)#拆分相关 # Save the file wb.save("e:\\sample.xlsx")