import xlwt
import pandas as pd
class Excel_style:
"""excel表格样式类"""
def __init__(self, font_heigh):
self.font_heigh = font_heigh # 字体大小：磅
@property
def cell_style(self):
"""表格内容样式"""
style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style.alignment = al
font=xlwt.Font()
font.height = 20*self.font_heigh
style.font=font
return style
@property
def title_style(self):
"""表格标题样式"""
style = xlwt.XFStyle() # 创建一个样式对象，初始化样式
al = xlwt.Alignment()
al.horz = 0x02 # 设置水平居中
al.vert = 0x01 # 设置垂直居中
style.alignment = al
font=xlwt.Font()
font.height = 20*self.font_heigh
font.bold = True
style.font=font
return style
def fit_width(self,max_width,value):
"""调整自适应列宽"""
width = len(str(value).encode('utf-8'))*256
if width < 2960:
max_width = 2960
if width > max_width:
max_width = width
return int(max_width + max_width * 1/10)
@property
def fit_heigh(self):
"""调整自适应行高"""
heigh_style = xlwt.easyxf('font:height %s;'%(20 * (self.font_heigh+4)))
return heigh_style
class Excel_writer:
"""excel表格写入类"""
def __init__(self, excel_path, df):
self.excel_path = excel_path
self.df = (df.drop(columns='index') if 'index' in df.columns else df)
self.cols = list(self.df.columns.values) # 行索引值列表
self.index_len = self.df.index.size # 索引长度
self.column_number = {col: idx for idx, col in enumerate(self.cols)} # 得到 行索引值:索引
self.excel = xlwt.Workbook(excel_path)
self.worksheet = self.excel.add_sheet('sheet1')
self.style_obj = Excel_style(10)
def write_to_exel(self):
"""pandas 写入 excel 表格"""
self.df.to_excel(self.excel_path, index=False)
def _get_group_idx(self, key_col):
"""合并单元格的索引数据"""
"""
return [[1,2],[3,4]]
"""
groups = self.df.groupby(key_col)
idxs = [[groups.get_group(i).index.min() + 1, groups.get_group(i).index.max() + 1] for i in groups.size().index]
return idxs, len(idxs)
def _fit_value(self,value):
"""调整 pandas 值，适应 excel 表格"""
if isinstance(value,str):
return str(value)
if isinstance(value,pd._libs.tslibs.timestamps.Timestamp):
value = str(value)
if '.' in value:
value = value.split('.')[0]
return value
return int(value)
def write_merged_by_col_to_excel(self, key_col, merged_cols):
"""根据 key列，合并设定的单元格，写入 excel 表格，有样式"""
if key_col not in self.cols: # 校验key_cols中各元素 是否都包含与对象的列
return
if not all([v in self.cols for i, v in enumerate(merged_cols)]): # 校验merge_cols中各元素 是否都包含与对象的列
return
self.write_head()
self.write_merge_body(key_col,merged_cols)
self.save()
def write_style_to_excel(self):
"""写入excel，有样式"""
self.write_head()
self.write_body()
self.save()
def write_head(self):
"""写入标题，有样式"""
for col_value, i in self.column_number.items(): # 写表头
heigh_style = self.style_obj.fit_heigh
self.worksheet.row(0).set_style(heigh_style)
self.worksheet.write(0, i, col_value, self.style_obj.title_style)
def write_body(self):
"""写入内容，有样式"""
for col_value, idx in self.column_number.items():
max_width = 2960
for i in range(self.index_len):
value = self.df.at[i, col_value]
if pd.isna(value):
value = ''
max_width = self.style_obj.fit_width(max_width,value)
self.worksheet.col(idx).width = max_width
heigh_style = self.style_obj.fit_heigh
self.worksheet.row(i + 1).set_style(heigh_style)
self.worksheet.write(i + 1, int(idx), self._fit_value(value),self.style_obj.cell_style)
def write_merge_body(self, key_col, merged_cols):
""""写入内容，合并单元格，有样式"""
for col_value, idx in self.column_number.items():
max_width = 2960
if col_value not in merged_cols:
for i in range(self.index_len):
value = self.df.at[i, col_value]
if pd.isna(value):
value = ''
max_width = self.style_obj.fit_width(max_width,value)
self.worksheet.col(idx).width = max_width
heigh_style = self.style_obj.fit_heigh
self.worksheet.row(i + 1).set_style(heigh_style)
self.worksheet.write(i + 1, int(idx), self._fit_value(value),self.style_obj.cell_style)
else:
idxs, _ = self._get_group_idx(key_col)
for j in idxs:
value = self.df.at[j[0] - 1, col_value]
if pd.isna(value):
value = ''
max_width = self.style_obj.fit_width(max_width,value)
self.worksheet.col(idx).width = max_width
heigh_style = self.style_obj.fit_heigh
self.worksheet.row(int(j[0])).set_style(heigh_style)
self.worksheet.write_merge(int(j[0]), int(j[1]), int(idx), int(idx), self._fit_value(value),self.style_obj.cell_style)
def save(self):
"""保存 excel 表格"""
self.excel.save(self.excel_path)
if __name__ == '__main__':
te = {'A': ['aaa', 'aaa', 'aaa', 'aaa', 'CCC', 'CCC'], 'B': ['bbbb', 'aaa', 'aaa', 'aaa', 'aaa', 'aaa'], 'C': ['aaa', 'aaa', 'aaa', 'aaa', 'aaa', 'aaa']}
t_f = pd.DataFrame(te)
DF = Excel_writer(r'D:\EL_gitee\el_app\src\app_vi\000_1.xls', t_f)
# DF.merged_by_col_to_excel('A',['A', 'B', 'C'])
DF.write_style_to_excel()
