python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實(shí)例
這篇文章主要介紹了python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實(shí)例,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
貼上一個(gè)例子,里面設(shè)計(jì)很多用法,根據(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) # 合計(jì)求和 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)計(jì) :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 = '{}小計(jì)'.format(fee_text) header_content = [ "序號", "日期", "貨品名", "數(shù)量", "重量/{}".format(get_weight_unit_text), "單價(jià)", "貨品小記", "押金小計(jì)", fee_text_total, "賒賬金額","待還款", "賒賬小記", "已還款", "待還款小計(jì)" ] 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) # 表尾合計(jì) data_content.append(["合計(jì)"]) config = ConfigFunc.get_config(session, shop_id) if not config.enable_deposit: index_deposit_total = data_content[0].index("押金小計(jì)") 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)營銷售", "總價(jià)"]) # 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)建微信聊天機(jī)器人
這篇文章主要手把手教你用Python創(chuàng)建微信聊天機(jī)器人,文中示例代碼介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2018-03-03Python使用matplotlib繪制余弦的散點(diǎn)圖示例
這篇文章主要介紹了Python使用matplotlib繪制余弦的散點(diǎn)圖,涉及Python操作matplotlib的基本技巧與散點(diǎn)的設(shè)置方法,需要的朋友可以參考下2018-03-03CNN卷積函數(shù)Conv2D()各參數(shù)的含義及用法解讀
這篇文章主要介紹了CNN卷積函數(shù)Conv2D()各參數(shù)的含義及用法解讀,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-02-02SVM算法的理解及其Python實(shí)現(xiàn)多分類和二分類問題
這篇文章主要介紹了SVM算法的理解及其Python實(shí)現(xiàn)多分類和二分類問題,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2023-02-02django多文件上傳,form提交,多對多外鍵保存的實(shí)例
今天小編就為大家分享一篇django多文件上傳,form提交,多對多外鍵保存的實(shí)例,具有很好的參考價(jià)值,希望對大家有所幫助。一起跟隨小編過來看看吧2019-08-08關(guān)于python的list相關(guān)知識(推薦)
下面小編就為大家?guī)硪黄P(guān)于python的list相關(guān)知識(推薦)。小編覺得挺不錯的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-08-08python ndarray數(shù)組對象特點(diǎn)及實(shí)例分享
在本篇文章里小編給大家分享的是一篇關(guān)于python ndarray數(shù)組對象特點(diǎn)及實(shí)例相關(guān)內(nèi)容,有需要的朋友們跟著學(xué)習(xí)下。2021-10-10tensorflow: variable的值與variable.read_value()的值區(qū)別詳解
今天小編就為大家分享一篇tensorflow: variable的值與variable.read_value()的值區(qū)別詳解,具有很好的參考價(jià)值,希望對大家有所幫助。一起跟隨小編過來看看吧2018-07-07