python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實例
更新時間：2019年08月27日 09:12:41 作者：大長胡子
這篇文章主要介紹了python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實例,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
這篇文章主要介紹了python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實例,文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下
貼上一個例子，里面設(shè)計很多用法，根據(jù)將相同日期的某些行合并處理。
from openpyxl import Workbook
from openpyxl.styles import Font, Fill, Alignment, Border, Side, PatternFill
from handlers.boss_accountant import PbOrderManageBase
from handlers.base.pub_func import ConfigFunc
from dal.models import Shop
from dal.db_configs import DBSession
def export_excel(filename, sheetname, content_body):
"""
Excel表格導(dǎo)出
:param filename: 表格名稱
:param sheetname: 工作表名稱
:param content_body: 內(nèi)容體
:return: None
"""
workbook = Workbook()
if not filename:
filename = "導(dǎo)出表格.xlsx"
workbook_sheet = workbook.active
if not sheetname:
sheetname = "工作表"
workbook_sheet.title = sheetname
merge_dict, sheet_row_len, sheet_column_len = merge_content(content_body)
print(merge_dict)
# 數(shù)據(jù)寫入
for row in content_body:
workbook_sheet.append(row)
# 合并處理
for key in merge_dict.keys():
merge_data = merge_dict.get(key)
if key == "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))
for i in [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)
# 合計求和
for i in [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]
for each_cell in last_row:
each_cell.fill = PatternFill("solid", fgColor="00CDCD")
# 邊框設(shè)置
for each_common_row in workbook_sheet.iter_rows("A1:{}".format(format_value(sheet_row_len, sheet_column_len))):
for each_cell in each_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)
def merge_content(content_body):
"""
合并統(tǒng)計
:param content_body: 數(shù)據(jù)體
: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
for data in data_content:
current_row += 1
x = data[1]
if data[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
return merge_dict, sheet_row_len, sheet_column_len
def format_value(row, column):
"""數(shù)字轉(zhuǎn)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)
return str(column)+str(row)
def export_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
)
if not if_success:
raise ValueError(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 = [
"序號", "日期", "貨品名", "數(shù)量", "重量/{}".format(get_weight_unit_text), "單價", "貨品小記", "押金小計", fee_text_total,
"賒賬金額","待還款", "賒賬小記", "已還款", "待還款小計"
]
file_name_begin = "客戶還款"
data_content.append(header_content)
# 還款數(shù)據(jù)
index_num = 0
for single_data in query_data:
index_num += 1
sales_time = single_data.get("sales_time", "")
if sales_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)
if not config.enable_deposit:
index_deposit_total = data_content[0].index("押金小計")
for data in data_content:
data.pop(index_deposit_total)
if not config.enable_commission:
index_commission_total = data_content[0].index(fee_text_total)
for data in data_content:
data.pop(index_commission_total)
file_name = "{}流水記錄導(dǎo)出_{}~{}.xlsx".format(file_name_begin, from_date, to_date)
return file_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(["日期", "貨品銷售", "自營銷售", "代賣銷售", "聯(lián)營銷售", "總價"])
# 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"])
以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
這篇文章主要手把手教你用Python創(chuàng)建微信聊天機器人，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-03-03
初學(xué)者經(jīng)常會遇到如何移除list中重復(fù)元素的問題。 這個問題在其他語言中可能需要for循環(huán)什么的，而在python中不用這樣，非常簡單，下面來通過這篇文章主要介紹了關(guān)于python列表去重方法以及拓展,需要的朋友可以參考借鑒，一起來看看吧。2017-01-01
這篇文章主要介紹了Python使用matplotlib繪制余弦的散點圖,涉及Python操作matplotlib的基本技巧與散點的設(shè)置方法,需要的朋友可以參考下2018-03-03
CNN卷積函數(shù)Conv2D()各參數(shù)的含義及用法解讀
這篇文章主要介紹了CNN卷積函數(shù)Conv2D()各參數(shù)的含義及用法解讀，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
SVM算法的理解及其Python實現(xiàn)多分類和二分類問題
這篇文章主要介紹了SVM算法的理解及其Python實現(xiàn)多分類和二分類問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-02-02
今天小編就為大家分享一篇django多文件上傳,form提交,多對多外鍵保存的實例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-08-08
關(guān)于python的list相關(guān)知識(推薦)
下面小編就為大家?guī)硪黄P(guān)于python的list相關(guān)知識(推薦)。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-08-08
重疊柱狀圖?(Overlapped?Bar)?是一種比較圖，可以將兩個柱狀圖疊加在一起，顯示兩個相關(guān)變量之間的差異，所以本文就來用Matplotlib繪制一個簡單的重疊柱狀圖吧2023-06-06
python ndarray數(shù)組對象特點及實例分享
在本篇文章里小編給大家分享的是一篇關(guān)于python ndarray數(shù)組對象特點及實例相關(guān)內(nèi)容，有需要的朋友們跟著學(xué)習(xí)下。2021-10-10
tensorflow: variable的值與variable.read_value()的值區(qū)別詳解
今天小編就為大家分享一篇tensorflow: variable的值與variable.read_value()的值區(qū)別詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-07-07