python操作openpyxl导出Excel 设置单元格格式及合并处理代码实例
这篇文章主要介绍了python操作openpyxl导出Excel设置单元格格式及合并处理代码实例,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
贴上一个例子，里面设计很多用法，根据将相同日期的某些行合并处理。
fromopenpyxlimportWorkbook
fromopenpyxl.stylesimportFont,Fill,Alignment,Border,Side,PatternFill
fromhandlers.boss_accountantimportPbOrderManageBase
fromhandlers.base.pub_funcimportConfigFunc
fromdal.modelsimportShop
fromdal.db_configsimportDBSession
defexport_excel(filename,sheetname,content_body):
"""
Excel表格导出
:paramfilename:表格名称
:paramsheetname:工作表名称
:paramcontent_body:内容体
:return:None
"""
workbook=Workbook()
ifnotfilename:
filename="导出表格.xlsx"
workbook_sheet=workbook.active
ifnotsheetname:
sheetname="工作表"
workbook_sheet.title=sheetname
merge_dict,sheet_row_len,sheet_column_len=merge_content(content_body)
print(merge_dict)
#数据写入
forrowincontent_body:
workbook_sheet.append(row)
#合并处理
forkeyinmerge_dict.keys():
merge_data=merge_dict.get(key)
ifkey=="title":
workbook_sheet.merge_cells(start_row=merge_data[0],start_column=merge_data[1],
end_row=merge_data[2],end_column=merge_data[3])
workbook_sheet.merge_cells(start_row=2,start_column=merge_data[1],
end_row=2,end_column=merge_data[3])
workbook_sheet['A1'].font=Font(size=20,bold=True)
workbook_sheet['A1'].alignment=Alignment(horizontal='center',vertical='center')
else:
#使用sum求值
workbook_sheet.cell(row=merge_data[0]+3,column=12).value='=SUM({}:{})'.format(
format_value(str(merge_data[0]+3),10),format_value(str(merge_data[1]+3),10))
workbook_sheet.cell(row=merge_data[0]+3,column=14).value='=SUM({}:{})'.format(
format_value(str(merge_data[0]+3),11),format_value(str(merge_data[1]+3),11))
workbook_sheet.cell(row=merge_data[0]+3,column=13).value='=({}-{})'.format(
format_value(str(merge_data[0]+3),12),format_value(str(merge_data[0]+3),14))
foriin[2,12,13,14]:
workbook_sheet.merge_cells(start_row=merge_data[0]+3,start_column=i,
end_row=merge_data[1]+3,end_column=i)
#合计求和
foriin[12,13,14]:
workbook_sheet.cell(row=sheet_row_len,column=i).value='=SUM({}:{})'.format(
format_value(3,i),format_value(sheet_row_len-1,i))
#单元格底色
last_row=workbook_sheet[sheet_row_len]
foreach_cellinlast_row:
each_cell.fill=PatternFill("solid",fgColor="00CDCD")
#边框设置
foreach_common_rowinworkbook_sheet.iter_rows("A1:{}".format(format_value(sheet_row_len,sheet_column_len))):
foreach_cellineach_common_row:
each_cell.border=Border(left=Side(style='thin',color='000000'),
right=Side(style='thin',color='000000'),
top=Side(style='thin',color='000000'),
bottom=Side(style='thin',color='000000')
)
workbook_sheet.column_dimensions['B'].width=15
workbook_sheet.column_dimensions['C'].width=20
workbook.save(filename)
defmerge_content(content_body):
"""
合并统计
:paramcontent_body:数据体
:return:合并字典
"""
sheet_column_len=len(content_body[3])
sheet_row_len=len(content_body)
merge_dict={}
data_content=content_body[3:-1]
merge_dict["title"]=(1,1,1,sheet_column_len)
current_data=data_content[0][1]
current_row=0
start_row=1
end_row=0
fordataindata_content:
current_row+=1
x=data[1]
ifdata[1]==current_data:
merge_dict[data[1]]=(start_row,current_row)
else:
merge_dict[data[1]]=(current_row,current_row)
current_data=data[1]
start_row=current_row
returnmerge_dict,sheet_row_len,sheet_column_len
defformat_value(row,column):
"""数字转ABC
"""
change_dict={
1:"A",2:"B",3:"C",4:"D",5:"E",6:"F",7:"G",8:"H",9:"I",10:"J",
11:"K",12:"L",13:"M",14:"N",15:"O",16:"P",17:"Q",18:"R",19:"S",20:"T",
21:"U",22:"V",23:"W",24:"X",25:"Y",26:"Z",
}
column=change_dict.get(column)
returnstr(column)+str(row)
defexport_func_new(args,session,shop_id):
#check_time=0
#debtor_id=2884
#debtor_name:肖小菜
#end_date:
#start_date:2019-07
#statistic_date:3
#data_type:1
data_content=[]
check_time=0
from_date="2019-07"
to_date=""
debtor_name="肖小菜"
if_success,query_data,*_=PbOrderManageBase.common_get_credit_stream(args,session,shop_id,export=True,
need_sum=False,check_time=check_time
)
ifnotif_success:
raiseValueError(query_data)
fee_text=ConfigFunc.get_fee_text(session,shop_id)
get_weight_unit_text=ConfigFunc.get_weight_unit_text(session,shop_id)
#表店铺、客户名称
shop_name=session.query(Shop.shop_name).filter_by(id=shop_id).first()
data_content.append([shop_name[0]])
data_content.append(["客户：{}".format(debtor_name)])
#表头
fee_text_total='{}小计'.format(fee_text)
header_content=[
"序号","日期","货品名","数量","重量/{}".format(get_weight_unit_text),"单价","货品小记","押金小计",fee_text_total,
"赊账金额","待还款","赊账小记","已还款","待还款小计"
]
file_name_begin="客户还款"
data_content.append(header_content)
#还款数据
index_num=0
forsingle_datainquery_data:
index_num+=1
sales_time=single_data.get("sales_time","")
ifsales_time:
sales_time=sales_time.split("")[0]
_payback_money=single_data["unpayback_money"]
single_content=[index_num,
sales_time,
single_data["only_goods_name"],
single_data["commission_mul"],
single_data["sales_num"],
"%s元/%s"%(single_data["fact_price"],
single_data["goods_unit"]),
single_data["goods_total"],
single_data["commission_mul"],
single_data["deposit_total"],
single_data["credit_cent"],
_payback_money,
0,
0,
0]
data_content.append(single_content)
#表尾合计
data_content.append(["合计"])
config=ConfigFunc.get_config(session,shop_id)
ifnotconfig.enable_deposit:
index_deposit_total=data_content[0].index("押金小计")
fordataindata_content:
data.pop(index_deposit_total)
ifnotconfig.enable_commission:
index_commission_total=data_content[0].index(fee_text_total)
fordataindata_content:
data.pop(index_commission_total)
file_name="{}流水记录导出_{}~{}.xlsx".format(file_name_begin,from_date,to_date)
returnfile_name,data_content
if__name__=="__main__":
filename="测试打印表格.xlsx"
sheetname="工作表2"
session=DBSession()
args={
"check_time":0,
"debtor_id":2884,
"debtor_name":"肖小菜",
"start_date":"2019-07",
"statistic_date":3,
"data_type":1
}
filename,content_body=export_func_new(args,session,104)
#filename="测试打印表格.xlsx"
#sheetname="工作表2"
#content_body=[]
#content_body.append(["打印表格表头"])
#content_body.append(["客户：肖某某"])
#content_body.append(["日期","货品销售","自营销售","代卖销售","联营销售","总价"])
#content_body.append(["1","2","3.1","4.1","5.1","5.1"])
#content_body.append(["1","2","3.1","4.1","5.1","5.1"])
#content_body.append(["1","2","3.1","4.1","5.1","5.1"])
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持毛票票。