一、安装
pip install openpyxl
二、样例代码
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Font,Alignment,colors
def openExcelFile():
workBook = load_workbook('./file_path/example.xlsx')
sheet1 = workBook.active
sheet2 = workBook['Sheet1']
print(f'======SheetNames:{workBook.sheetnames}======')
for sheet in workBook:
print(f'#####SheetTitle:{sheet.title}#####')
pass
def getCellValue():
workBook = load_workbook('./file_path/example.xlsx')
sheet = workBook.active
value1 = sheet['A1'].value
value2 = sheet.cell(row=1, column=2).value
# 切片
cell1 = sheet['c2':'d3']
# 整行整列
cell_row = sheet[3]
cell_col = sheet['c']
# 全部数据
for row in sheet.iter_rows(1,sheet.max_row,1,sheet.max_column):
for cell in row:
print(cell.value)
pass
pass
def createExcelFile():
workBook = Workbook()
sheet = workBook.active
sheet2 = workBook['sheetName',0] # 0可选,表示将该工作表设置为第一个工作表
workBook.save('./file_path/exampleCreate.xlsx')
pass
def setValueAndFont():
workBook = load_workbook('./file_path/example.xlsx')
sheet = workBook.active
bold_italic_red_34_font = Font(name='Arial',size=34,bold=True,italic=True,color=colors.RED)
sheet['A1'] = 'hello'
sheet['A1'].font = bold_italic_red_34_font
data = ['a','b','c','d','e','f','g']
for i,d in enumerate(data):
sheet.cell(i + 1, 2).value = d
workBook.save('./file_path/exampleSetValueAndFont.xlsx')
def setStyle():
workBook = load_workbook('./file_path/example.xlsx')
sheet = workBook.active
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['A'].width = 30
data = ['Python','Java','C++','C#','PHP','Ruby','Perl','Go','Swift','Kotlin','Rust','Julia']
for i,d in enumerate(data):
sheet.cell(i+1,1).value = d
sheet.cell(i+1,1).alignment = Alignment(horizontal='center',vertical='center')
workBook.save('./file_path/exampleSetStyle.xlsx')
def drawChart():
from openpyxl.chart import LineChart,BarChart,PieChart,Reference
# 线形图
workBook = Workbook()
sheet = workBook.active
rows = [
['Name','Batch 1','Batch 2',"Batch 3"],
['aaa',40,30,25],
['bbb',40,25,30],
['ccc',30,30,45],
]
for row in rows:
sheet.append(row)
pass
lineChart = LineChart()
lineChart.title = "Line Chart"
lineChart.x_axis.title = "Batch"
lineChart.y_axis.title = "Amount"
data = Reference(sheet,min_col=2,min_row=2,max_col=4,max_row=4)
lineChart.add_data(data, titles_from_data=True)
sheet.add_chart(lineChart,"E1")
# 柱状图
from openpyxl.chart import series
data=[
('Number','Batch 1','Batch 2'),
(2,10,30),
(3,40,60),
(4,50,70),
]
sheet2 = workBook.create_sheet('BarChart')
for row in data:
sheet2.append(row)
barChart = BarChart()
barChart.type = 'col'
barChart.style = 10
barChart.title = 'Bar Chart'
barChart.x_axis.title = 'Test_number'
barChart.y_axis.title = 'Sample length(mm)'
data = Reference(sheet2,min_col=2,min_row=1,max_col=3,max_row=7)
cats = Reference(sheet2,min_col=1,min_row=2,max_row=7)
barChart.add_data(data,titles_from_data=True)
barChart.set_categories(cats)
barChart.shape = 4
sheet2.add_chart(barChart,'A10')
# 饼图
from openpyxl.chart.series import DataPoint
data=[
['名称','数值'],
['苹果',50],
['草莓',30],
['椰子',10],
]
sheet3 = workBook.create_sheet('PieChart')
for row in data:
sheet3.append(row)
pie = PieChart()
labels = Reference(sheet3,min_col=1,min_row=2,max_row=3)
data = Reference(sheet3,min_col=2,min_row=1,max_row=3)
pie.add_data(data,titles_from_data=True)
pie.set_categories(labels)
pie.title = 'Pies sold by category'
slice = DataPoint(idx=0, explosion=10) # Cut the first slice out of the pie
pie.series[0].data_points = [slice] # Cut the first slice out of the pie
sheet3.add_chart(pie,'D1')
pass
def merge_Excel():
import os
workBook = Workbook()
sheet = workBook.active
all_data = []
for name in os.listdir('./file_path'):
path = f'./file_path/{name}'
tmp_wb = load_workbook(path)
tmp_sheet = tmp_wb.active
for r in range(1, tmp_sheet.max_row + 1):
row_data = [] # 获取每一行的数据
for c in range(1, tmp_sheet.max_column + 1):
cell_value = tmp_sheet.cell(row=r, column=c).value
row_data.append(cell_value)
if row_data not in all_data: # 去重
all_data.append(row_data)
for data in all_data:
sheet.append(data)
workBook.save('./file_path/test.xlsx')
def merge_Excel_inDifferentSheet():
import os
workBook = Workbook()
for name in os.listdir('./file_path'):
path = f'./file_path/{name}'
tmp_wb = load_workbook(path)
tmp_sheet = tmp_wb.active
sheet = workBook.create_sheet(name[:-5])
for r in range(1, tmp_sheet.max_row + 1):
row_data = []
for c in range(1, tmp_sheet.max_column + 1):
cell_value = tmp_sheet.cell(row=r, column=c).value
row_data.append(cell_value)
sheet.append(row_data)
del workBook['Sheet'] # 删除默认自动创建的sheet
workBook.save('./file_path/test.xlsx')
def split_Excel():
# 按行拆分为单独文件
workBook = load_workbook('./file_path/test.xlsx',data_only=True)
sheet = workBook.active
title = ['column1','column2','column3']
for i,row in enumerate(sheet.rows):
if i == 0:
continue
else:
temp_workBook = Workbook()
temp_sheet = temp_workBook.active
temp_sheet.append(title)
row_data = [cell.value for cell in row]
temp_sheet.append(row_data)
temp_workBook.save(f'./file_path/{row_data[0]}.xlsx')
def gapColor_changeStyle4Excel():
from openpyxl.styles import PattenFill
from datetime import date
workBook = Workbook()
sheet = workBook.active
rows = [
['Date','Batch 1','Batch 2','Batch 3'],
[date(2020,12,1),40,30,25],
[date(2020,12,2),40,25,30],
]
for row in rows:
sheet.append(row)
backColor = PattenFill('solid', fgColor="FF0000")
for i in range(1, sheet.max_row + 1):
if i % 2 == 0:
for cell in range(1, sheet.max_column + 1):
sheet.cell(row=i, column=cell).fill = backColor
workBook.save('./file_path/test.xlsx')
def calculateTimeInExcel():
workBook = load_workbook('./file_path/TimeData.xlsx',data_only=True)
sheet = workBook.active
data = []
for i in range(2,sheet.max_row+1):
t_data = []
for j in range(1,sheet.max_column+1):
t_data.append(sheet.cell(row=i,column=j).value)
# 统计
h,m = t_data[2].split(':')
full = int(h)*60 + int(m)
tmp = full - 18 * 60
t_data.append(tmp)
# 处理时间问题
t_data[0] = t_data[0].date()
data.append(t_data)
wb = Workbook() # 保存
new_sh = wb.active
for row in data:
new_sh.append(row)
workBook.save('./file_path/test.xlsx')
def markDuplicate4Excel():
from openpyxl.styles import PatternFill
workBook = load_workbook('./file_path/test.xlsx',data_only=True)
sheet = workBook.active
index = []
tmp = [] # 没有重复的数据
for i,c in enumerate(sheet['B']):
if c.value not in tmp:
tmp.append(c.value)
else:
index.append(i)
fill = PatternFill('solid', fgColor='AEEEEE')
for i,r in enumerate(sheet.rows):
if i in index:
for c in r:
c.fill = fill
print(f'第{i+1}行是重复数据')
workBook.save('./file_path/test.xlsx')
if __name__ == '__main__':
openExcelFile()
getCellValue()
createExcelFile()
setValueAndFont()
setStyle()
drawChart()
merge_Excel()
merge_Excel_inDifferentSheet()
split_Excel()
gapColor_changeStyle4Excel()
calculateTimeInExcel()
markDuplicate4Excel()