python在excel中的高效办公
这里需要用到openpyxl模块,没有安装的需要安装一下,总的来说就是练习熟悉模块内的函数。
首先最简单,创建工作簿,获取工作簿的工作簿,保存
import openpyxl
wb = openpyxl.Workbook() # 创建工作簿
ws = wb.active # 获取工作簿的工作表
print(ws.title)
ws['A1'] = 520
ws.append([1,2,3])
import datetime
ws['A3'] = datetime.datetime.now()
wb.save("demo.xlsx")
下面是对文件内的工作表引用
# 打开excel文件
wb = openpyxl.load_workbook("demo.xlsx")
print(type(wb))
# 获取工作表,get_sheet_name 和 sheetnames查看表名称
print(wb.sheetnames)
# ws = wb.get_sheet_by_name('Sheet') # 出现警告,函数被弃用
ws = wb['Sheet']
创建和删除工作表
ws = wb.create_sheet(index = 0,title = 'wl') # index是插入位置
print(wb.sheetnames)
del wb['wl']
print(wb.sheetnames)
定位单元格
ws = wb['Sheet']
c = ws['A2']
print(c.row,c.column,c.value)
d = c.offset(-1,0) # 将c的位置向上走一行并返回对象给d
print("d.value is ",d.value)
单元格字母与列数之间的转换
# from openpyxl.utils import column_index_from_string
print(openpyxl.cell.cell.get_column_letter(496))
print(openpyxl.utils.cell.column_index_from_string('JB'))
访问多个单元格
for each in ws['A1':'B3']: # 相当于视作二维数组了,且先行后列
for a in each:
print(a.value,end=' ')
print()
for each in ws.rows:
print(each[0].value)
# 还有指定行列的迭代
# for each in ws.iter_rows(min_row=2,min_col=1,max_row=4,max_col=2):
个性化工作表标签栏
import openpyxl
wb = openpyxl.Workbook()
ws1 = wb.create_sheet(title="小甲鱼")
ws2 = wb.create_sheet(title="不二如是")
ws3 = wb.create_sheet(title="凤姐")
ws4 = wb.create_sheet(title="小泡")
ws1.sheet_properties.tabColor = "FF0000"
ws2.sheet_properties.tabColor = "00FF00"
ws3.sheet_properties.tabColor = "0000FF"
ws4.sheet_properties.tabColor = "880088"
wb.save("demo2.xlsx")
调整行高和列高
ws2.row_dimensions.height = 100
ws2.column_dimensions['C'].width = 50
wb.save("demo2.xlsx")
合并与拆分单元格
ws1.merge_cells("A1:C3")
wb.save("demo2.xlsx") # 要保存到文件中,操作才能生效
ws1['A1'] = "yeah!"
wb.save("demo.xlsx")
# 怎么合并的就怎么拆分
ws1.unmerge_cells("A1:C3")
冻结窗口
openpyxl.load_workbook("demo2.xlsx")
ws = wb.active
ws.freeze_panes = "B8"
wb.save("demo.xlsx")
ws.freeze_panes = None # 解冻
设置单元格字体
from openpyxl import Workbook
from openpyxl.styles import Font
wb = Workbook()
ws = wb.active
b2 = ws['B2']
b2.value = "WlFY"
bold_red_font = Font(bold=True,color="FF0000")
b2.font = bold_red_font
b3 = ws['B3']
b3.value = "WLYSY"
italic_strike_blue_16font = Font(size=16,italic=True,strike=True,color="0000FF")
b3.font = italic_strike_blue_16font
wb.save("demo2.xlsx")
填充单元格
from openpyxl.styles import PatternFill
yellow_fill = PatternFill(fill_type='solid',fgColor="FFFF00")
b2.fill = yellow_fill
wb.save("demo2.xlsx")
设置边框
from openpyxl.styles import Border,Side
thin_side = Side(border_style="thin",color="000000")
double_side = Side(border_style="double",color="FF0000")
b2.border = Border(diagonal=thin_side,diagonalUp=True,diagonalDown=True)
b3.border = Border(left=double_side,top=double_side,right=double_side,bottom = double_side)
wb.save("demo2.xlsx")
文本对齐
from openpyxl.styles import Alignment
ws.merge_cells('A1:C2')
ws['A1'].value = "I love WLFY!"
center_alignment = Alignment(horizontal="center",vertical="center") # 居中
ws['A1'].alignment = center_alignment
wb.save("demo2.xlsx")
命名样式
from openpyxl.styles import NamedStyle
hightlight = NamedStyle(name="highlight")
hightlight.font = Font(bold=True,size=20)
hightlight.alignment = Alignment(horizontal="center",vertical="center")
wb.add_named_style(hightlight)
ws['A1'].style = hightlight
ws['B5'].value = "LOVE"
ws['B5'].style = hightlight
wb.save("demo2.xlsx")
最后需要注意的地方,以后可能用到
数字格式
number_format调整数字格式,根据输入的数字调整单元格内的内容
函数公式的使用
for row in ws.iter_row(min_col=2,min_row=2,max_col=5,max_row=5):
ws[row[3].coordinate] = “SUM(%s:%s)” % (row[0].coordinate,row[2].coordinate)
ws[row[4].coordinate] = ‘=IF(%s>250,“A”,“B”)’ % (row[3].coordinate) 注意外面是单引号,否则excel不能识别单引号
注意LOOKUP函数,VLOOKUP