因最近工作需要将数据写入excel，并设置格式，编写此Excel操作模块，以便未来还要用到。
# !/usr/bin/env python
# -*- coding: utf-8 -*-
"""
@Time : 2021/7/21 11:31
@Author : luoshixiang
@Email : luoshixiang@shsnc.com
@File : Parse_Excel.py
"""
import os
import openpyxl
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font,colors
from openpyxl.drawing.image import Image
from openpyxl.chart import Series,LineChart,Reference
def create_excel(sheetname, filename):
# 创建一个workbook对象
wb = openpyxl.Workbook()
# 激活一个sheet
sheet = wb.active
# 为sheet设置表名sheetname
# sheet.title = 'Sheet1'
for i in range(len(sheetname)):
wb.create_sheet(sheetname[i])
ws = wb['Sheet']
wb.remove(ws)
wb.save(filename)
import openpyxl
def write_excel(title, sheet_name, data, filename):
# 创建一个workbook对象
wb = openpyxl.Workbook()
# 激活一个sheet
sheet = wb.active
# 为sheet设置一个表名sheetname
sheet.title = sheet_name
# 添加表头（不需要表头可以不用加）
for i in range(len(title)):
sheet.cell(row=1, column=i + 1, value=title[i])
# 添加表内容
for row_index, row_item in enumerate(data): # 获取索引和内容
for col_index, col_item in enumerate(row_item):
sheet.cell(row=row_index + 2, column=col_index + 1, value=col_item) # 写入
wb.save(filename)
return filename
class Parse_Excel(object):
"""解析excel文件"""
def __init__(self, filename, sheet_name = None):
try:
if os.path.exists(filename) == True:
# 获取绝对路径下的文件名称
self.filename = os.path.realpath(filename)
# 打开文件，获取excel文件的workbook（工作簿）对象
self.__wb = openpyxl.load_workbook(self.filename, data_only=True)
if sheet_name:
self.__sheet_name = sheet_name
else:
# 获取活跃表单
self.__ws = self.__wb.active # self.__ws的值与self.__wb[sheet_name]的值相同
# 获取活跃表名称
self.__sheet_name = self.__ws.title
else:
# 创建一个workbook对象
self.__wb = openpyxl.Workbook()
# 激活一个sheet
self.__ws = self.__wb.active
# 为sheet设置表名sheetname
if sheet_name:
self.__wb.create_sheet(sheet_name)
self.__sheet_name = sheet_name
# self.__wb.save(filename)
else:
self.__ws.title = 'Sheet1'
self.__ws = self.__wb['Sheet']
self.__wb.remove(self.__ws)
self.__wb.save(filename)
# 获取绝对路径下的文件名称
self.filename = os.path.realpath(filename)
except FileNotFoundError as e:
raise e
def save(self):
self.__wb.save(self.filename)
def create_sheets(self, sheet_name):
for i in range(len(sheet_name)):
self.__wb.create_sheet(sheet_name[i])
def remove_sheets(self, sheet_name):
for i in range(len(sheet_name)):
self.__wb.remove(sheet_name[i])
def get_max_row_num(self):
"""获取最大行号"""
sheet_name = self.__sheet_name
max_row_num = self.__wb[sheet_name].max_row
return max_row_num
def get_max_column_num(self):
"""获取最大列号"""
sheet_name = self.__sheet_name
max_column = self.__wb[sheet_name].max_column
return max_column
def get_row_value(self, row):
"""获取某一行的数据"""
sheet_name = self.__sheet_name
column_num = self.get_max_column_num()
row_value = []
if isinstance(row, int):
for column in range(1, column_num + 1):
values_row = self.__wb[sheet_name].cell(row, column).value
row_value.append(values_row)
# while None in values_row: # 去除列表中的None值和nall值
# values_row.remove(None)
return row_value
else:
raise TypeError('row must be type int')
def get_column_value(self, column):
"""获取某一列数据"""
sheet_name = self.__sheet_name
row_num = self.get_max_row_num()
column_value = []
if isinstance(column, int):
for row in range(1, row_num + 1):
values_column = self.__wb[sheet_name].cell(row, column).value
column_value.append(values_column)
# while None in column_value: # 去除列表中的None值和nall值
# column_value.remove(None)
return column_value
else:
raise TypeError('column must be type int')
def get_all_value(self):
"""获取指定表单的所有数据(除去表头)"""
sheet_name = self.__sheet_name
max_row_num = self.get_max_row_num()
max_column = self.get_max_column_num()
values = []
for row in range(2, max_row_num + 1):
value_list = []
for column in range(1, max_column + 1):
value = self.__wb[sheet_name].cell(row, column).value
value_list.append(value)
values.append(value_list)
return values
def get_all_value2(self):
"""获取指定表单的所有数据(包含表头)"""
sheet_name = self.__sheet_name
max_row_num = self.get_max_row_num()
max_column = self.get_max_column_num()
values = []
for row in range(1, max_row_num + 1):
value_list = []
for column in range(1, max_column + 1):
value = self.__wb[sheet_name].cell(row, column).value
value_list.append(value)
values.append(value_list)
return values
def get_excel_title(self):
"""获取sheet表头"""
sheet_name = self.__sheet_name
title_key = tuple(self.__wb[sheet_name].iter_rows(max_row=1, values_only=True))[0]
return title_key
def get_cell(self,location):
sheet_name = self.__sheet_name
cell_value = self.__wb[sheet_name][location].value
return cell_value
def get_cell_value(self,row,column):
sheet_name = self.__sheet_name
cell_value = self.__wb[sheet_name].cell(row,column).value
return cell_value
def my_border(self,t_border, b_border, l_border, r_border):
# 定义边框样式
border = Border(top=Side(border_style=t_border, color=colors.BLACK),
bottom=Side(border_style=b_border, color=colors.BLACK),
left=Side(border_style=l_border, color=colors.BLACK),
right=Side(border_style=r_border, color=colors.BLACK))
return border
# 初始化制定区域边框为所有框线
def format_border(self,s_column, s_index, e_column, e_index):
sheet_name = self.__sheet_name
for row in tuple(self.__wb[sheet_name][s_column + str(s_index):e_column + str(e_index)]):
for cell in row:
cell.border = self.my_border('thin', 'thin', 'thin', 'thin')
def set_solid_border(self,area_list):
"""给指定区域设置粗匣框线"""
# 调用方式：pe.set_solid_border([['A', 3, 'D', 5], ['A', 6, 'D', 7],['A', 3, 'A', 10], ['B', 3, 'C', 10]])
sheet_name = self.__sheet_name
for area in area_list:
s_column = area[0]
s_index = area[1]
e_column = area[2]
e_index = area[3]
#设置左粗框线
for cell in self.__wb[sheet_name][s_column][s_index - 1:e_index]:
cell.border = self.my_border(cell.border.top.style, cell.border.bottom.style,
'medium', cell.border.right.style)
# 设置右粗框线
for cell in self.__wb[sheet_name][e_column][s_index - 1:e_index]:
cell.border = self.my_border(cell.border.top.style, cell.border.bottom.style,
cell.border.left.style, 'medium')
# 设置上粗框线
for row in tuple(self.__wb[sheet_name][s_column + str(s_index):e_column + str(s_index)]):
for cell in row:
cell.border = self.my_border('medium', cell.border.bottom.style,
cell.border.left.style, cell.border.right.style)
# 设置下粗框线
for row in tuple(self.__wb[sheet_name][s_column + str(e_index):e_column + str(e_index)]):
for cell in row:
cell.border = self.my_border(cell.border.top.style, 'medium',
cell.border.left.style, cell.border.right.style)
def pattern_fill(self,rows,columns):
"""将单元格背景填充红色"""
sheet_name = self.__sheet_name
fill_set = PatternFill("solid", fgColor="00FF0000")
# fill_set = PatternFill("solid", fgColor="00FFFFFF")
font_set = Font(bold=True)
for i in range(2, columns ):
if int(self.__wb[sheet_name].cell(row = rows, column = i).value) >= 1:
self.__wb[sheet_name].cell(row = rows, column = i).fill = fill_set
self.__wb[sheet_name].cell(row=rows, column=i).font = font_set
def pattern_fill_cell(self, row, column):
# 将单元格标红
sheet_name = self.__sheet_name
fill_set = PatternFill("solid", fgColor="00FF0000")
font_set = Font(bold=True)
self.__wb[sheet_name].cell(row, column).border = self.my_border('thin', 'thin', 'thin', 'thin')
self.__wb[sheet_name].cell(row, column).fill = fill_set
self.__wb[sheet_name].cell(row, column).font = font_set
def set_border_cells(self,rows,columns):
"""将rows行加粗"""
sheet_name = self.__sheet_name
fill_set = PatternFill("solid", fgColor="00FF0000")
font_set = Font(bold=True)
for i in range(1, columns + 1):
self.__wb[sheet_name].cell(row=rows, column=i).font = font_set
def write_cell(self, row, column, value):
sheet_name = self.__sheet_name
if isinstance(row, int) and isinstance(column, int):
try:
cell_obj = self.__wb[sheet_name].cell(row, column)
cell_obj.value = value
except Exception as e:
raise e
else:
raise TypeError('row and column must be type int')
def write_sheet(self, data, title=None, value=None, bold=False):
sheet_name = self.__sheet_name
if title:
# 添加表头（不需要表头可以不用加）
self.write_sheet_title(title)
# 开始遍历数组
for row_index, row_item in enumerate(data): # 获取索引和内容
for col_index, col_item in enumerate(row_item):
# 写入
self.__wb[sheet_name].cell(row=row_index + 2, column=col_index + 1, value=col_item)
def write_sheet_from_column(self, data, column, title=None, value=None, bold=False):
sheet_name = self.__sheet_name
if title:
# 添加表头（不需要表头可以不用加）
self.write_sheet_title(title)
# 开始遍历数组
for row_index, row_item in enumerate(data): # 获取索引和内容
for col_index, col_item in enumerate(row_item):
# 写入
self.__wb[sheet_name].cell(row=row_index + 2, column=col_index +1 + column, value=col_item)
def write_sheet_from_positive_cell_row(self, data, row=2, column=1, title=None, value=None, bold=False):
sheet_name = self.__sheet_name
if title:
# 添加表头（不需要表头可以不用加）
self.write_sheet_title(title)
# 开始遍历数组
for row_index, row_item in enumerate(data): # 获取索引和内容
for col_index, col_item in enumerate(row_item):
# 写入
self.__wb[sheet_name].cell(row=row_index + row, column=col_index + column, value=col_item)
def write_sheet_from_positive_cell_col(self, data, row=2, column=1, title=None, value=None, bold=False):
sheet_name = self.__sheet_name
if title:
# 添加表头（不需要表头可以不用加）
self.write_sheet_title(title)
# 开始遍历数组
for col_index, row_item in enumerate(data): # 获取索引和内容
for row_index, col_item in enumerate(row_item):
# 写入
self.__wb[sheet_name].cell(row=row_index + row, column=col_index + column, value=col_item)
def write_sheet_title(self,sheet_title):
# 添加表头
sheet_name = self.__sheet_name
for i in range(len(sheet_title)):
self.__wb[sheet_name].cell(row=1, column=i + 1, value=sheet_title[i])
def write_row_data(self,rows, sheet_data):
# 追加一行数据
sheet_name = self.__sheet_name
for i in range(len(sheet_data)):
self.__wb[sheet_name].cell(row=rows + 1, column=i+1, value=sheet_data[i])
def write_column_data(self,columns, sheet_data):
# 向max_column右边追加一列数据,从第二行开始写
sheet_name = self.__sheet_name
for i in range(len(sheet_data)):
self.__wb[sheet_name].cell(row=i + 2, column=columns+1, value=sheet_data[i])
def write_column_data2(self,columns, sheet_data):
# 向max_column右边追加一列数据,从第一行开始写
sheet_name = self.__sheet_name
for i in range(len(sheet_data)):
self.__wb[sheet_name].cell(row=i + 1, column=columns+1, value=sheet_data[i])
def delete_row_v(self, rows, columns, values):
sheet_name = self.__sheet_name
for i in range(2, rows +1):
if self.__wb[sheet_name].cell(row = i, column = columns).value == values:
self.__wb[sheet_name].delete_rows(i, 1)
def delete_row(self, row):
sheet_name = self.__sheet_name
self.__wb[sheet_name].delete_rows(row, 1)
def delete_all_value(self,row=1):
sheet_name = self.__sheet_name
max_row = self.get_max_row_num()
self.__wb[sheet_name].delete_rows(row,max_row)
# for i in range(max_row+1):
# self.__wb[sheet_name].delete_rows(i)
def get_tail10_value(self, row_num , num):
"""获取后10行的数据"""
sheet_name = self.__sheet_name
column_num = self.get_max_column_num()
if isinstance(row_num, int):
if row_num < num:
for row in range(2, row_num + 1):
row_value = []
for column in range(1, column_num + 1):
values_row = self.__wb[sheet_name].cell(row, column).value
row_value.append(values_row)
print(row_value)
else:
for row in range(row_num - num, row_num + 1):
row_value = []
for column in range(1, column_num + 1):
values_row = self.__wb[sheet_name].cell(row, column).value
row_value.append(values_row)
print(row_value)
else:
raise TypeError('row must be type int')
def get_bf_value(self, row_num):
"""获取后10行的数据"""
sheet_name = self.__sheet_name
column_num = self.get_max_column_num()
if isinstance(row_num, int):
row_value = []
for row in range(row_num - 11, row_num + 1):
col_value = []
for column in range(1, column_num + 1):
value_col = self.__wb[sheet_name].cell(row, column).value
col_value.append(value_col)
row_value.append(col_value)
return row_value
else:
raise TypeError('row must be type int')
def get_bf_value2(self, row_num):
"""获取后10行的数据"""
sheet_name = self.__sheet_name
column_num = self.get_max_column_num()
if isinstance(row_num, int):
row_value = []
for row in range(row_num - 11, row_num + 1):
col_value = []
for column in range(1, column_num + 1):
value_col = self.__wb[sheet_name].cell(row, column).value
if type(value_col) == int:
value_col = "%.2f%%" % (value_col * 100)
col_value.append(value_col)
row_value.append(col_value)
return row_value
else:
raise TypeError('row must be type int')
def get_bf_value3(self,row_begin, row_end, col_begin, col_end):
"""获取后10行的数据"""
sheet_name = self.__sheet_name
# column_num = self.get_max_column_num()
if isinstance(row_end, int):
row_value = []
for row in range(row_begin, row_end + 1):
col_value = []
for column in range(col_begin, col_end + 1):
value_col = self.__wb[sheet_name].cell(row, column).value
if type(value_col) == float:
value_col = "%.2f%%" % (value_col * 100)
col_value.append(value_col)
row_value.append(col_value)
return row_value
else:
raise TypeError('row must be type int')
def set_percent(self,row,column,row2=None, column2=None):
# 将单元格格式设置为百分比显示
sheet_name = self.__sheet_name
fill_set = PatternFill("solid", fgColor="00FF0000")
font_set = Font(bold=True)
if not row2:
row2 = self.get_max_row_num()
if not column2:
column2 = self.get_max_column_num()
for i in range(row, row2 + 1):
for j in range(column, column2 +1):
self.__wb[sheet_name].cell(i, j).number_format = '0.00%'
def set_percent_column(self,column):
# 将单元格格式设置为百分比显示
sheet_name = self.__sheet_name
max_row =self.get_max_row_num()
for i in range(1,max_row + 1):
self.__wb[sheet_name].cell(i, column).number_format = '0.00%'
def sum_column(self,column):
# 对列中数字类型单元格进行求和，将结果写在列的最后一行
data = self.get_column_value(column)
sum = 0
for i in data:
if type(i) == int:
sum += i
return sum
def set_date_row(self,row):
# 将单元格格式设置为百分比显示
sheet_name = self.__sheet_name
# 设置字体
font_set = Font(name='宋体', color='000000', size=12, bold=True)
max_col =self.get_max_column_num()
for i in range(1,max_col + 1):
self.__wb[sheet_name].cell(row, i).font = font_set
self.__wb[sheet_name].cell(row, i).number_format = 'mm月dd日'
def merge_cells(self,loc1,loc2): # loc1示例：'A1'
# 合并单元格
sheet_name = self.__sheet_name
# 设置对齐方式，水平是右对齐，垂直是居中
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
# 设置字体
font_set = Font(name='宋体', color='000000', size=11, bold=False)
self.__wb[sheet_name].merge_cells(loc1+":"+loc2)
# self.__wb[sheet_name].merge_cells(start_row=2, start_column=1, end_row=4, end_column=4) # 与上一条等价
self.__wb[sheet_name][loc1].alignment = align
self.__wb[sheet_name][loc1].font = font_set
def unmerge_cells(self,sr,sc,er,ec):
# 分割单元格
sheet_name = self.__sheet_name
# self.__wb[sheet_name].unmerge_cells('A2:D2')
# self.__wb[sheet_name].unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4) # 与上一条等价
self.__wb[sheet_name].unmerge_cells(start_row=sr, start_column=sc, end_row=er, end_column=ec)
def add_image(self,img_path,loc): # loc示例：'A1'
# 插入图像
sheet_name = self.__sheet_name
# 制作一个图片
img = Image(img_path)
# 在单元格旁边添加工作表和锚
self.__wb[sheet_name].add_image(img, loc)
def column_dimensions(self,col_name1,col_name2): # col_name1示例：'A'
# 折叠列
sheet_name = self.__sheet_name
self.__wb[sheet_name].column_dimensions.group(col_name1,col_name2, hidden=True)
def add_formula(self,loc,formula):
# 添加公式
sheet_name = self.__sheet_name
self.__wb[sheet_name][loc] = formula # ws["A1"] = "=SUM(1, 1)"
def add_line_chart(self,title, min_row, min_col, line_name, save_img, max_row=None, max_col=None):
sheet_name = self.__sheet_name
self.chart = LineChart()
self.chart.title = title # 图的标题
self.chart.style = 2 # 线条的style
self.chart.width = 17
self.chart.height = 6.5
# self.chart.y_axis.title = '次数' # y坐标的标题
# self.chart.x_axis.number_format = 'mm-dd' # 规定日期格式 这是月,年格式
# self.chart.x_axis.majorTimeUnit = "Months" # 规定日期间隔 注意days；Months大写
# self.chart.x_axis.title = "时间点" # x坐标的标题
if not max_row:
max_row = self.get_max_row_num()
if not max_col:
max_col = self.get_max_column_num()
for i in range(2,max_col+1):
# 选中要画图的数据列(Y轴)
data = Reference(self.__wb[sheet_name], min_col=i, min_row=min_row, max_col=i,
max_row=max_row) # 图像的数据 起始行、起始列、终止行、终止列
seriesObj = Series(data, title="'" + line_name[i - 2])
self.chart.append(seriesObj)
# self.chart.add_data(data, titles_from_data=True, from_rows=True)
# 指定X轴选取的数据列
dates = Reference(self.__wb[sheet_name], min_col=1, min_row=min_row, max_col=1, max_row=max_row)
self.chart.set_categories(dates)
self.__wb[sheet_name].add_chart(self.chart, save_img) # 将图表添加到 sheet中的：A1
其中有一部分内容：向excel中输入公式，仅适用于结论，不适用于中间处理过程，若excel公式处理结果还被调用的话，需要另想他法。
