Python是一种代表简单主义思想的语言。Python提供了高效的高级数据结构，还能简单有效地面向对象编程。Python语法和动态类型，以及解释型语言的本质，使它成为多数平台上写脚本和快速开发应用的编程语言。为方便日常工作，如下整理出常用模板，以备不时之需。
class
# importing the module
import pandas as pd
# Define the path and worksheet name to which source and target files are saved
# ============================================================================
# dfn=>data_fram_name
# ssn=>sour_sheet_name
# dsn=>dest_sheet_name
# suc=>sour_use_cols
# dsr=>dest_startrow
# dsc=>dest_startcool
# ============================================================================
loc = "D:\\py\\folder\\"
sourFile = loc+"p.xlsx"
destFile = loc+"gongyu.xlsx"
ssn_21 = "公寓IPTV"
ssn_22 = "公寓UPS"
ssn_23 = "公寓综合布线"
ssn_24 = "公寓监控"
ssn_25 = "公寓门禁"
ssn_26 = "公寓网络"
ssn_27 = "公寓扩声"
ssn_28 = "公寓讨论"
# =============================================================================
dsn_21 = "公寓IPTV"
dsn_22 = "公寓UPS"
dsn_23 = "公寓布线"
dsn_24 = "公寓监控"
dsn_25 = "公寓门禁"
dsn_26 = "公寓网络"
dsn_27 = "公寓扩声"
dsn_28 = "公寓讨论"
# a class # Import data from the Excel Sheet data source
class isusee:
writer = pd.ExcelWriter(destFile, engine="xlsxwriter")
def append_data(self, dfn, ssn, suc, dsn, dsc):
# Create a Pandas Excel writer using XlsxWriter as the engine.
dfn = pd.read_excel(sourFile, sheet_name=ssn, header=1, usecols=suc)
# Write a dataframe to the worksheet.
dfn.to_excel(self.writer, sheet_name=dsn, startrow=2,
startcol=dsc, na_rep='无', index=False, header=False)
self.dfn = dfn
self.dsn = dsn
def style_table(self):
workbook = self.writer.book
worksheet = self.writer.sheets[self.dsn]
self.worksheet = worksheet
# Add a number format for cells with num.
num_format = workbook.add_format(
{'num_format': '0', 'valign': 'vcenter'})
# Add a number format for cells with money.
money_format = workbook.add_format(
{'num_format': '¥#,##0.00', 'valign': 'vcenter'})
# Add a text format for cells with text.
text_format = workbook.add_format(
{'num_format': '@', 'valign': 'vcenter', 'text_wrap': True})
text_format.set_font_size(10)
# Add a number format for cells with money.
money_format = workbook.add_format(
{'num_format': '#,##0.00', 'valign': 'vcenter'})
# Adding formats for header row.
fmt_header = workbook.add_format({
'bold': True,
'text_wrap': True,
'valign': 'top',
'fg_color': '#5DADE2',
'font_color': '#FFFFFF',
'border': 1})
# Setting the zoom
worksheet.set_zoom(100)
# https://xlsxwriter.readthedocs.io/example_tables.html
# https://xlsxwriter.readthedocs.io/working_with_tables.html#tables
# Prime Cost : 原价|成本价
# unit price : 单价
total_pc = '=PRODUCT([@数量]*[@成本])'
total_up = '=PRODUCT([@数量]*[@单价])'
# Some sample data for the table.
data = []
# Options to use in the table.
# User defined functions or formulas aren't supported.
# options里不支持例如对列宽，单元格样式的设置
options = {'data': data,
'columns': [
{'header': '序号'},
{'header': '名称'},
{'header': '型号'},
{'header': '品牌'},
{'header': '数量'},
{'header': '成本'},
{'header': '单价'},
{'header': '单位'},
{'header': '成本小计', 'formula': total_pc},
{'header': '单价小计', 'formula': total_up},
{'header': '参数说明'}
]}
(max_row, max_col) = self.dfn.shape
# print("A2:"f"K{max_row}")
mx_row = max_row + 2
# Add a table to the worksheet.
worksheet.add_table("A2:"f"K{mx_row}", options)
# 冻结标题行，第一个参数 2 冻结指第二行，第二个参数 1 指冻结第一列
worksheet.freeze_panes(2, 1)
# Create a format to use in the merged range.
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# Create a format to use in the merged range.
# https://xlsxwriter.readthedocs.io/working_with_pandas.html
worksheet.merge_range('A1:K1', self.dsn, merge_format)
# Set the columns widths.
worksheet.set_column('A:A', 6, text_format)
worksheet.set_column('B:B', 22, text_format)
worksheet.set_column('C:C', 11.25, text_format)
worksheet.set_column('D:D', 6, text_format)
worksheet.set_column('E:E', 6, num_format)
worksheet.set_column('F:F', 10, money_format)
worksheet.set_column('G:G', 10, money_format)
worksheet.set_column('H:H', 6, text_format)
worksheet.set_column('I:I', 9.88, money_format)
worksheet.set_column('J:J', 9.88, money_format)
worksheet.set_column('K:K', 49, text_format)
def sheet_print(self):
# =====================================================================
#
# This program shows several examples of how to set up headers and
# footers with XlsxWriter.
#
# The control characters used in the header/footer strings are:
#
# Control Category Description
# ======= ======== ===========
# &L Justification Left
# &C Center
# &R Right
#
# &P Information Page number
# &N Total number of pages
# &D Date
# &T Time
# &F File name
# &A Worksheet name
#
# &fontsize Font Font size
# &"font,style" Font name and style
# &U Single underline
# &E Double underline
# &S Strikethrough
# &X Superscript
# &Y Subscript
#
# &[Picture] Images Image placeholder
# &G Same as &[Picture]
#
# && Miscellaneous Literal ampersand &
#
# =====================================================================
#
# 将工作表的打印页面的方向设置为横向
self.worksheet.set_landscape()
# 将工作表数据水平居中在打印页面的边距之间
self.worksheet.center_horizontally()
# 页面边距
self.worksheet.set_margins(left=0.3, right=0.3, top=0.61, bottom=0.37)
# 页眉 and 页脚
self.worksheet.set_header("", {'margin': 0.1})
self.worksheet.set_footer("", {'margin': 0.1})
# &L->left &C->center &R->right
# header = '&LPage &P of &N' + '&CFilename: &F' + '&RSheetname: &A'
# footer = capsheet2 + '&CPage &P of &N' + '&RCurrent DateTime: &D &T'
footer = '&R:: Page &P of &N :: Print Time: &D &T'
self.worksheet.set_footer(footer)
# ===== class end =============================================================
# Object instantiation
# =============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
aa = isusee()
aa.append_data('dfaa_b', ssn_21, 'B', dsn_21, 1)
aa.append_data('dfaa_c', ssn_21, 'C', dsn_21, 2)
aa.append_data('dfaa_d', ssn_21, 'D', dsn_21, 10)
aa.append_data('dfaa_e', ssn_21, 'E', dsn_21, 3)
aa.append_data('dfaa_f', ssn_21, 'F', dsn_21, 7)
aa.append_data('dfaa_g', ssn_21, 'G', dsn_21, 4)
aa.append_data('dfaa_h', ssn_21, 'H', dsn_21, 6)
aa.style_table()
aa.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
bb = isusee()
bb.append_data('dfbb_b', ssn_22, 'B', dsn_22, 1)
bb.append_data('dfbb_c', ssn_22, 'C', dsn_22, 3)
bb.append_data('dfbb_d', ssn_22, 'D', dsn_22, 2)
bb.append_data('dfbb_e', ssn_22, 'E', dsn_22, 7)
bb.append_data('dfbb_f', ssn_22, 'F', dsn_22, 4)
bb.append_data('dfbb_f', ssn_22, 'G', dsn_22, 6)
bb.append_data('dfbb_i', ssn_22, 'I', dsn_22, 10)
bb.style_table()
bb.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
cc = isusee()
cc.append_data('dfcc_b', ssn_23, 'B', dsn_23, 1)
cc.append_data('dfcc_c', ssn_23, 'C', dsn_23, 2)
cc.append_data('dfcc_d', ssn_23, 'D', dsn_23, 3)
cc.append_data('dfcc_e', ssn_23, 'E', dsn_23, 7)
cc.append_data('dfcc_f', ssn_23, 'F', dsn_23, 4)
cc.append_data('dfcc_g', ssn_23, 'G', dsn_23, 6)
cc.append_data('dfcc_i', ssn_23, 'I', dsn_23, 10)
cc.style_table()
cc.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
dd = isusee()
dd.append_data('dfdd_b', ssn_24, 'B', dsn_24, 1)
dd.append_data('dfdd_c', ssn_24, 'C', dsn_24, 2)
dd.append_data('dfdd_d', ssn_24, 'D', dsn_24, 10)
dd.append_data('dfdd_e', ssn_24, 'E', dsn_24, 3)
dd.append_data('dfdd_f', ssn_24, 'F', dsn_24, 7)
dd.append_data('dfdd_g', ssn_24, 'G', dsn_24, 4)
dd.append_data('dfdd_h', ssn_24, 'H', dsn_24, 6)
dd.style_table()
dd.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
ee = isusee()
ee.append_data('dfee_c', ssn_25, 'C', dsn_25, 1)
ee.append_data('dfee_d', ssn_25, 'D', dsn_25, 3)
ee.append_data('dfee_e', ssn_25, 'E', dsn_25, 2)
ee.append_data('dfee_f', ssn_25, 'F', dsn_25, 7)
ee.append_data('dfee_g', ssn_25, 'G', dsn_25, 6)
ee.append_data('dfee_h', ssn_25, 'H', dsn_25, 4)
ee.append_data('dfee_j', ssn_25, 'J', dsn_25, 10)
ee.style_table()
ee.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
ff = isusee()
ff.append_data('dfff_b', ssn_26, 'B', dsn_26, 1)
ff.append_data('dfff_c', ssn_26, 'C', dsn_26, 2)
ff.append_data('dfff_d', ssn_26, 'D', dsn_26, 10)
ff.append_data('dfff_e', ssn_26, 'E', dsn_26, 3)
ff.append_data('dfff_f', ssn_26, 'F', dsn_26, 7)
ff.append_data('dfff_g', ssn_26, 'G', dsn_26, 4)
ff.append_data('dfff_h', ssn_26, 'H', dsn_26, 6)
ff.style_table()
ff.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
gg = isusee()
gg.append_data('dfgg_b', ssn_27, 'B', dsn_27, 1)
gg.append_data('dfgg_c', ssn_27, 'C', dsn_27, 3)
gg.append_data('dfgg_d', ssn_27, 'D', dsn_27, 2)
gg.append_data('dfgg_e', ssn_27, 'E', dsn_27, 10)
gg.append_data('dfgg_f', ssn_27, 'F', dsn_27, 4)
gg.append_data('dfgg_g', ssn_27, 'G', dsn_27, 7)
gg.append_data('dfgg_h', ssn_27, 'H', dsn_27, 6)
gg.style_table()
gg.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4:数量 5:成本
# 6:单价 7:单位 8:成计 9:单计 10:参数
hh = isusee()
hh.append_data('dfhh_b', ssn_28, 'B', dsn_28, 1)
hh.append_data('dfhh_c', ssn_28, 'C', dsn_28, 3)
hh.append_data('dfhh_d', ssn_28, 'D', dsn_28, 2)
hh.append_data('dfhh_e', ssn_28, 'E', dsn_28, 10)
hh.append_data('dfhh_f', ssn_28, 'F', dsn_28, 4)
hh.append_data('dfhh_g', ssn_28, 'G', dsn_28, 7)
hh.append_data('dfhh_h', ssn_28, 'H', dsn_28, 6)
hh.style_table()
hh.sheet_print()
hh.writer.save()
print('OK.')# importing the module
import pandas as pd
# Define the path and worksheet name to which source and target files are saved
# ============================================================================
# dfn=>data_fram_name
# ssn=>sour_sheet_name
# dsn=>dest_sheet_name
# suc=>sour_use_cols
# dsr=>dest_startrow
# dsc=>dest_startcool
# ============================================================================
loc = "D:\\py\\folder\\"
sourFile = loc+"p.xlsx"
destFile = loc+"destFile.xlsx"
ssn_01 = "会展布线"
ssn_02 = "会展机房"
ssn_03 = "会展监控"
ssn_04 = "会展楼控"
ssn_05 = "会展照明"
ssn_06 = "会展票务"
ssn_07 = "会展网络"
ssn_08 = "会展无线对讲"
ssn_09 = "会展信息发布"
ssn_10 = "会展巡更"
ssn_11 = "会展公共广播"
ssn_12 = "会展241人会议室"
ssn_13 = "会展138会议室"
ssn_14 = "会展68人视频会议室 "
ssn_15 = "会展46人视频会议室 "
ssn_16 = "会展18人会议室"
ssn_17 = "会展多功能厅"
# =============================================================================
dsn_01 = "综合布线"
dsn_02 = "会展机房"
dsn_03 = "会展监控"
dsn_04 = "会展楼控"
dsn_05 = "会展照明"
dsn_06 = "会展票务"
dsn_07 = "会展网络"
dsn_08 = "会展对讲"
dsn_09 = "会展发布"
dsn_10 = "会展巡更"
dsn_11 = "会展广播"
dsn_12 = "会展241人会议室"
dsn_13 = "会展138人会议室"
dsn_14 = "会展68人视频会议室"
dsn_15 = "会展46人视频会议室"
dsn_16 = "会展18人会议室"
dsn_17 = "会展多功能厅"
# a class # Import data from the Excel Sheet data source
class isusee:
writer = pd.ExcelWriter(destFile, engine="xlsxwriter")
def append_data(self, dfn, ssn, suc, dsn, dsc):
# Create a Pandas Excel writer using XlsxWriter as the engine.
dfn = pd.read_excel(sourFile, sheet_name=ssn, header=1, usecols=suc)
# Write a dataframe to the worksheet.
dfn.to_excel(self.writer, sheet_name=dsn, startrow=2,
startcol=dsc, na_rep='无', index=False, header=False)
self.dfn = dfn
self.dsn = dsn
def style_table(self):
workbook = self.writer.book
worksheet = self.writer.sheets[self.dsn]
self.worksheet = worksheet
# Add a number format for cells with num.
num_format = workbook.add_format(
{'num_format': '0', 'valign': 'vcenter'})
# Add a number format for cells with money.
money_format = workbook.add_format(
{'num_format': '¥#,##0.00', 'valign': 'vcenter'})
# Add a text format for cells with text.
text_format = workbook.add_format(
{'num_format': '@', 'valign': 'vcenter', 'text_wrap': True})
text_format.set_font_size(10)
# Add a number format for cells with money.
money_format = workbook.add_format(
{'num_format': '#,##0.00', 'valign': 'vcenter'})
# Adding formats for header row.
fmt_header = workbook.add_format({
'bold': True,
'text_wrap': True,
'valign': 'top',
'fg_color': '#5DADE2',
'font_color': '#FFFFFF',
'border': 1})
# Setting the zoom
worksheet.set_zoom(100)
# https://xlsxwriter.readthedocs.io/example_tables.html
# https://xlsxwriter.readthedocs.io/working_with_tables.html#tables
# Prime Cost : 原价|成本价
# unit price : 单价
total_pc = '=PRODUCT([@数量]*[@成本])'
total_up = '=PRODUCT([@数量]*[@单价])'
# Some sample data for the table.
data = []
# Options to use in the table.
# User defined functions or formulas aren't supported.
# options里不支持例如对列宽，单元格样式的设置
options = {'data': data,
'columns': [
{'header': '序号'},
{'header': '名称'},
{'header': '型号'},
{'header': '品牌'},
{'header': '数量'},
{'header': '成本'},
{'header': '单价'},
{'header': '单位'},
{'header': '成本小计', 'formula': total_pc},
{'header': '单价小计', 'formula': total_up},
{'header': '参数说明'}
]}
(max_row, max_col) = self.dfn.shape
# print("A2:"f"K{max_row}")
mx_row = max_row + 2
# Add a table to the worksheet.
worksheet.add_table("A2:"f"K{mx_row}", options)
# 冻结标题行，第一个参数 2 冻结指第二行，第二个参数 1 指冻结第一列
worksheet.freeze_panes(2, 1)
# Create a format to use in the merged range.
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# Create a format to use in the merged range.
# https://xlsxwriter.readthedocs.io/working_with_pandas.html
worksheet.merge_range('A1:K1', self.dsn, merge_format)
# Set the columns widths.
worksheet.set_column('A:A', 6, text_format)
worksheet.set_column('B:B', 22, text_format)
worksheet.set_column('C:C', 11.25, text_format)
worksheet.set_column('D:D', 6, text_format)
worksheet.set_column('E:E', 6, num_format)
worksheet.set_column('F:F', 10, money_format)
worksheet.set_column('G:G', 10, money_format)
worksheet.set_column('H:H', 6, text_format)
worksheet.set_column('I:I', 9.88, money_format)
worksheet.set_column('J:J', 9.88, money_format)
worksheet.set_column('K:K', 49, text_format)
def sheet_print(self):
# =====================================================================
#
# This program shows several examples of how to set up headers and
# footers with XlsxWriter.
#
# The control characters used in the header/footer strings are:
#
# Control Category Description
# ======= ======== ===========
# &L Justification Left
# &C Center
# &R Right
#
# &P Information Page number
# &N Total number of pages
# &D Date
# &T Time
# &F File name
# &A Worksheet name
#
# &fontsize Font Font size
# &"font,style" Font name and style
# &U Single underline
# &E Double underline
# &S Strikethrough
# &X Superscript
# &Y Subscript
#
# &[Picture] Images Image placeholder
# &G Same as &[Picture]
#
# && Miscellaneous Literal ampersand &
#
# =====================================================================
#
# 将工作表的打印页面的方向设置为横向
self.worksheet.set_landscape()
# 将工作表数据水平居中在打印页面的边距之间
self.worksheet.center_horizontally()
# 页面边距
self.worksheet.set_margins(left=0.3, right=0.3, top=0.61, bottom=0.37)
# 页眉 and 页脚
self.worksheet.set_header("", {'margin': 0.1})
self.worksheet.set_footer("", {'margin': 0.1})
# &L->left &C->center &R->right
# header = '&LPage &P of &N' + '&CFilename: &F' + '&RSheetname: &A'
# footer = capsheet2 + '&CPage &P of &N' + '&RCurrent DateTime: &D &T'
footer = '&R:: Page &P of &N :: Print Time: &D &T'
self.worksheet.set_footer(footer)
# ===== class end =============================================================
# Object instantiation
a = isusee()
a.append_data('dfa_b', ssn_01, 'B', ssn_01, 1)
a.append_data('dfa_c', ssn_01, 'C', ssn_01, 2)
a.append_data('dfa_d', ssn_01, 'D', ssn_01, 3)
a.append_data('dfa_e', ssn_01, 'E', ssn_01, 7)
a.append_data('dfa_f', ssn_01, 'F', ssn_01, 4)
a.append_data('dfa_g', ssn_01, 'G', ssn_01, 6)
a.append_data('dfa_i', ssn_01, 'I', ssn_01, 10)
a.style_table()
a.sheet_print()
# ============================================================================
b = isusee()
b.append_data('dfb_b', ssn_02, 'B', dsn_02, 1)
b.append_data('dfb_c', ssn_02, 'C', ssn_02, 3)
b.append_data('dfb_d', ssn_02, 'D', ssn_02, 2)
b.append_data('dfb_e', ssn_02, 'E', ssn_02, 7)
b.append_data('dfb_f', ssn_02, 'F', ssn_02, 4)
b.append_data('dfb_g', ssn_02, 'G', ssn_02, 6)
b.append_data('dfb_i', ssn_02, 'I', ssn_02, 10)
b.style_table()
b.sheet_print()
# ============================================================================
c = isusee()
c.append_data('dfc_b', ssn_03, 'B', dsn_03, 1)
c.append_data('dfc_c', ssn_03, 'C', dsn_03, 2)
c.append_data('dfc_d', ssn_03, 'D', dsn_03, 10)
c.append_data('dfc_e', ssn_03, 'E', dsn_03, 3)
c.append_data('dfc_f', ssn_03, 'F', dsn_03, 7)
c.append_data('dfc_g', ssn_03, 'G', dsn_03, 4)
c.append_data('dfc_h', ssn_03, 'H', dsn_03, 6)
c.style_table()
c.sheet_print()
# ============================================================================
d = isusee()
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
d.append_data('dfd_b', ssn_04, 'B', dsn_04, 1)
d.append_data('dfd_c', ssn_04, 'C', dsn_04, 3)
d.append_data('dfd_d', ssn_04, 'D', dsn_04, 2)
d.append_data('dfd_e', ssn_04, 'E', dsn_04, 7)
d.append_data('dfd_f', ssn_04, 'F', dsn_04, 4)
d.append_data('dfd_g', ssn_04, 'G', dsn_04, 6)
d.append_data('dfd_i', ssn_04, 'I', dsn_04, 10)
d.style_table()
d.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
e = isusee()
e.append_data('dfe_b', ssn_05, 'B', dsn_05, 1)
e.append_data('dfe_c', ssn_05, 'C', dsn_05, 3)
e.append_data('dfe_d', ssn_05, 'D', dsn_05, 2)
e.append_data('dfe_e', ssn_05, 'E', dsn_05, 7)
e.append_data('dfe_f', ssn_05, 'F', dsn_05, 4)
e.append_data('dfe_g', ssn_05, 'G', dsn_05, 6)
e.append_data('dfe_i', ssn_05, 'I', dsn_05, 10)
e.style_table()
e.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
f = isusee()
f.append_data('dff_b', ssn_06, 'B', dsn_06, 1)
f.append_data('dff_c', ssn_06, 'C', dsn_06, 7)
f.append_data('dff_d', ssn_06, 'D', dsn_06, 3)
f.append_data('dff_e', ssn_06, 'E', dsn_06, 2)
f.append_data('dff_f', ssn_06, 'F', dsn_06, 4)
f.append_data('dff_g', ssn_06, 'G', dsn_06, 6)
f.append_data('dff_i', ssn_06, 'I', dsn_06, 10)
f.style_table()
f.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
g = isusee()
g.append_data('dfg_b', ssn_07, 'B', dsn_07, 1)
g.append_data('dfg_c', ssn_07, 'C', dsn_07, 2)
g.append_data('dfg_d', ssn_07, 'D', dsn_07, 10)
g.append_data('dfg_e', ssn_07, 'E', dsn_07, 3)
g.append_data('dfg_f', ssn_07, 'F', dsn_07, 7)
g.append_data('dfg_g', ssn_07, 'G', dsn_07, 4)
g.append_data('dfg_h', ssn_07, 'H', dsn_07, 6)
g.style_table()
g.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
h = isusee()
h.append_data('dfh_b', ssn_08, 'B', dsn_08, 1)
h.append_data('dfh_c', ssn_08, 'C', dsn_08, 2)
h.append_data('dfh_d', ssn_08, 'D', dsn_08, 3)
h.append_data('dfh_e', ssn_08, 'E', dsn_08, 4)
h.append_data('dfh_f', ssn_08, 'F', dsn_08, 7)
h.append_data('dfh_g', ssn_08, 'G', dsn_08, 6)
h.append_data('dfh_i', ssn_08, 'I', dsn_08, 10)
h.style_table()
h.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
i = isusee()
i.append_data('dfi_b', ssn_09, 'B', dsn_09, 1)
i.append_data('dfi_c', ssn_09, 'C', dsn_09, 7)
i.append_data('dfi_d', ssn_09, 'D', dsn_09, 3)
i.append_data('dfi_e', ssn_09, 'E', dsn_09, 2)
i.append_data('dfi_f', ssn_09, 'F', dsn_09, 4)
i.append_data('dfi_g', ssn_09, 'G', dsn_09, 6)
i.append_data('dfi_i', ssn_09, 'I', dsn_09, 10)
i.style_table()
i.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
j = isusee()
j.append_data('dfj_b', ssn_10, 'B', dsn_10, 1)
j.append_data('dfj_c', ssn_10, 'C', dsn_10, 2)
j.append_data('dfj_d', ssn_10, 'D', dsn_10, 3)
j.append_data('dfj_e', ssn_10, 'E', dsn_10, 7)
j.append_data('dfj_f', ssn_10, 'F', dsn_10, 4)
j.append_data('dfj_g', ssn_10, 'G', dsn_10, 6)
j.append_data('dfj_i', ssn_10, 'I', dsn_10, 10)
j.style_table()
j.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
k = isusee()
k.append_data('dfk_b', ssn_11, 'B', dsn_11, 1)
k.append_data('dfk_c', ssn_11, 'C', dsn_11, 3)
k.append_data('dfk_d', ssn_11, 'D', dsn_11, 2)
k.append_data('dfk_e', ssn_11, 'E', dsn_11, 4)
k.append_data('dfk_f', ssn_11, 'F', dsn_11, 7)
k.append_data('dfk_g', ssn_11, 'G', dsn_11, 6)
k.style_table()
k.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
l = isusee()
l.append_data('dfl_b', ssn_12, 'B', dsn_12, 1)
l.append_data('dfl_c', ssn_12, 'C', dsn_12, 3)
l.append_data('dfl_d', ssn_12, 'D', dsn_12, 2)
l.append_data('dfl_e', ssn_12, 'E', dsn_12, 10)
l.append_data('dfl_f', ssn_12, 'F', dsn_12, 4)
l.append_data('dfl_g', ssn_12, 'G', dsn_12, 7)
l.append_data('dfl_h', ssn_12, 'H', dsn_12, 6)
l.style_table()
l.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
m = isusee()
m.append_data('dfm_b', ssn_13, 'B', dsn_13, 1)
m.append_data('dfm_c', ssn_13, 'C', dsn_13, 3)
m.append_data('dfm_d', ssn_13, 'D', dsn_13, 2)
m.append_data('dfm_e', ssn_13, 'E', dsn_13, 10)
m.append_data('dfm_f', ssn_13, 'F', dsn_13, 4)
m.append_data('dfm_g', ssn_13, 'G', dsn_13, 7)
m.append_data('dfm_h', ssn_13, 'H', dsn_13, 6)
m.style_table()
m.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
n = isusee()
n.append_data('dfn_b', ssn_14, 'B', dsn_14, 1)
n.append_data('dfn_c', ssn_14, 'C', dsn_14, 3)
n.append_data('dfn_d', ssn_14, 'D', dsn_14, 2)
n.append_data('dfn_e', ssn_14, 'E', dsn_14, 10)
n.append_data('dfn_f', ssn_14, 'F', dsn_14, 4)
n.append_data('dfn_g', ssn_14, 'G', dsn_14, 7)
n.append_data('dfn_h', ssn_14, 'H', dsn_14, 6)
n.style_table()
n.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
o = isusee()
o.append_data('dfo_b', ssn_15, 'B', dsn_15, 1)
o.append_data('dfo_c', ssn_15, 'C', dsn_15, 3)
o.append_data('dfo_d', ssn_15, 'D', dsn_15, 2)
o.append_data('dfo_e', ssn_15, 'E', dsn_15, 10)
o.append_data('dfo_f', ssn_15, 'F', dsn_15, 4)
o.append_data('dfo_g', ssn_15, 'G', dsn_15, 7)
o.append_data('dfo_h', ssn_15, 'H', dsn_15, 6)
o.style_table()
o.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
p = isusee()
p.append_data('dfp_b', ssn_16, 'B', dsn_16, 1)
p.append_data('dfp_c', ssn_16, 'C', dsn_16, 3)
p.append_data('dfp_d', ssn_16, 'D', dsn_16, 2)
p.append_data('dfp_e', ssn_16, 'E', dsn_16, 10)
p.append_data('dfp_f', ssn_16, 'F', dsn_16, 4)
p.append_data('dfp_g', ssn_16, 'G', dsn_16, 7)
p.append_data('dfp_h', ssn_16, 'H', dsn_16, 6)
p.style_table()
p.sheet_print()
# ============================================================================
# 1:名称 2:型号 3:品牌 4: 数量 5: 成本 6: 单价
# 7:单位 8:成计 9:单计 10:参数
q = isusee()
q.append_data('dfq_b', ssn_17, 'B', dsn_17, 1)
q.append_data('dfq_c', ssn_17, 'C', dsn_17, 3)
q.append_data('dfq_d', ssn_17, 'D', dsn_17, 2)
q.append_data('dfq_e', ssn_17, 'E', dsn_17, 10)
q.append_data('dfq_f', ssn_17, 'F', dsn_17, 4)
q.append_data('dfq_g', ssn_17, 'G', dsn_17, 7)
q.append_data('dfq_h', ssn_17, 'H', dsn_17, 6)
q.style_table()
q.sheet_print()
q.writer.save()
print('OK.')H
import pandas as pd
loc = "D:\\py\\folder\\"
df_b = pd.read_excel(loc+"sourFile.xlsx", '会展布线', header=1, usecols='B')
df_c = pd.read_excel(loc+"sourFile.xlsx", '会展布线', header=1, usecols='C')
writer_2 = pd.ExcelWriter(loc+"1FFile.xlsx", engine="xlsxwriter")
df_b.to_excel(writer_2, sheet_name="综合布线", startrow=2,
startcol=1, na_rep='无', index=False, header=False)
df_c.to_excel(writer_2, sheet_name="综合布线", startrow=2,
startcol=3, na_rep='无', index=False, header=False)
workbook_2 = writer_2.book
worksheet_2 = writer_2.sheets["综合布线"]
# Adding simple number format.
fmt_number = workbook_2.add_format({
"num_format": "0"
})
# Adding currency format
fmt_currency = workbook_2.add_format({
"num_format": "$#,##0.00", "bold": False, 'text_wrap': True
})
# Adding percentage format.
fmt_rate = workbook_2.add_format({
"num_format": "%0.0", "bold": False
})
# Add a text format for cells with text.
text_format = workbook_2.add_format({'num_format': '@', 'text_wrap': True})
# Add a number format for cells with num.
nums_format = workbook_2.add_format({'num_format': '#,##0'})
# Add a number format for cells with money.
money_format = workbook_2.add_format({'num_format': '¥#,##0.00'})
# Add a text format for cells with text.
text_format = workbook_2.add_format(
{'num_format': '@', 'align': 'left', 'valign': 'vcenter', 'text_wrap': True})
# Add a number format for cells with num.
nums_format = workbook_2.add_format({'num_format': '#,##0'})
# Add a number format for cells with money.
money_format = workbook_2.add_format({'num_format': '¥#,##0.00'})
# Adding formats for header row.
fmt_header = workbook_2.add_format({
'bold': True,
'text_wrap': True,
'valign': 'top',
'fg_color': '#5DADE2',
'font_color': '#FFFFFF',
'border': 1})
# Setting the zoom
worksheet_2.set_zoom(100)
# https://xlsxwriter.readthedocs.io/example_tables.html
# https://xlsxwriter.readthedocs.io/working_with_tables.html#tables
# Prime Cost : 原价|成本价
# unit price : 单价
total_pc = '=PRODUCT([@数量]*[@成本])'
total_up = '=PRODUCT([@数量]*[@单价])'
# Some sample data for the table.
data = []
# Options to use in the table.
# User defined functions or formulas aren't supported.
# options里不支持例如对列宽，单元格样式的设置
options = {'data': data,
'columns': [
{'header': '序号'},
{'header': '名称'},
{'header': '品牌'},
{'header': '型号'},
{'header': '数量'},
{'header': '成本'},
{'header': '单价'},
{'header': '单位'},
{'header': '成本小计', 'formula': total_pc},
{'header': '单价小计', 'formula': total_up},
{'header': '参数说明'}
]}
# if '综合布线' in writer_2.book.sheetnames:
# print(writer_2.book.sheetnames)
# maxrow = writer_2.book['综合布线'].max_row
# Add a table to the worksheet.表格区域 A2 到 K20，A1 为标头，所以，从 A2 开始为数据填充区域
# worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})
(max_row, max_col) = df_b.shape
# msg['Subject'] = f"Auto Hella Restart Report {sys.argv[1]}"
# print("A2:"f"K{max_row}")
mx_row = max_row + 2
worksheet_2.add_table("A2:"f"K{mx_row}", options)
# 冻结标题行，第一个参数 2 冻结指第二行，第二个参数 1 指冻结第一列
worksheet_2.freeze_panes(2, 1)
# Create a format to use in the merged range.
merge_format = workbook_2.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# 合并 A1 到 K1 单元格并应用 merge_format 样式作为标头用
# https://xlsxwriter.readthedocs.io/working_with_pandas.html
worksheet_2.merge_range('A1:K1', '会布线....', merge_format)
# worksheet_2.set_column("B:B", 12, fmt_currency)
# worksheet_2.set_column("K:M", 10, fmt_currency)
# worksheet_2.set_column("D:D",20,fmt_number)
# worksheet_2.set_column("N:N",10,fmt_rate)
# Set the columns widths.
worksheet_2.set_column('A:A', 6, text_format)
worksheet_2.set_column('B:B', 22, text_format)
worksheet_2.set_column('C:C', 6, text_format)
worksheet_2.set_column('D:D', 11.25, text_format)
worksheet_2.set_column('E:E', 6, fmt_number)
worksheet_2.set_column('F:F', 8, money_format)
worksheet_2.set_column('G:G', 8, money_format)
worksheet_2.set_column('H:H', 6, text_format)
worksheet_2.set_column('I:I', 9.88, money_format)
worksheet_2.set_column('J:J', 9.88, money_format)
worksheet_2.set_column('K:K', 53, text_format)
for col, value in enumerate(df_b.columns.values):
worksheet_2.write(-1, col, value)
# for col , value in enumerate(df_c.columns.values):
# worksheet_2.write(0, col, value, fmt_header)
writer_2.save()
# https://medium.com/analytics-vidhya/exporting-a-data-frame-to-custom-formatted-excel-a82a35116e92
Function
datacleaning
# import pandas library as pd
import pandas as pd
# Define the path and worksheet name to which source and target files are saved
sourFile = 'D:\\py\\folder\\sourFile.xlsx'
saveFile = 'D:\\py\\folder\\saveFile.xlsx'
# Import data from the Excel Sheet data source
def datacleaning(dataframe, sour_sheet_name, sour_usecols, dest_sheet_name, dest_start_row, dest_start_col):
# Create a Pandas dataframe from sourFile
dataframe = pd.read_excel(
sourFile, sheet_name=sour_sheet_name, header=2, usecols=sour_usecols)
# Create a Pandas Excel writer
# object using XlsxWriter as the engine.
# # Create a Pandas Excel writer using XlsxWriter as the engine.
writer_object = pd.ExcelWriter(saveFile, engine='xlsxwriter')
# Write a dataframe to the worksheet.
dataframe.to_excel(writer_object, sheet_name=dest_sheet_name, na_rep='无',
index=False, header=False, startrow=dest_start_row, startcol=dest_start_col)
workbook = writer_object.book
# Create xlsxwriter worksheet object
worksheet_object = writer_object.sheets[dest_sheet_name]
# 将工作表的打印页面的方向设置为横向
worksheet_object.set_landscape()
# 将工作表数据水平居中在打印页面的边距之间
worksheet_object.center_horizontally()
# 页面边距
worksheet_object.set_margins(left=0.3, right=0.3, top=0.61, bottom=0.37)
# 页眉 and 页脚
worksheet_object.set_header("", {'margin': 0.1})
worksheet_object.set_footer("", {'margin': 0.1})
# &L->left &C->center &R->right
# header = '&LPage &P of &N' + '&CFilename: &F' + '&RSheetname: &A'
# footer = capsheet2 + '&CPage &P of &N' + '&RCurrent DateTime: &D &T'
footer = '&R:: Page &P of &N :: Print Time: &D &T'
worksheet_object.set_footer(footer)
# Some sample data for the table.
data = []
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [
{'header': '序号'},
{'header': '名称'},
{'header': '品牌'},
{'header': '型号'},
{'header': '数量'},
{'header': '成本'},
{'header': '单价'},
{'header': '单位'},
{'header': '成本小计'},
{'header': '单价小计'},
{'header': '参数说明'}
]}
# Add a table to the worksheet.表格区域 A2 到 K20，A1 为标头，所以，从 A2 开始为数据填充区域
worksheet_object.add_table('A2:K39', options)
nums_format = workbook.add_format({'num_format': '#,##0'})
# text_format = workbook.add_format()
# text_format.set_num_format('@') # @ - This is text format in excel
text_format = workbook.add_format(
{'num_format': '@', 'align': 'left', 'valign': 'vcenter', 'text_wrap': True})
# fmt = wb_name.add_format({'align': 'left', 'valign': 'vcenter', 'text_wrap': True})
# format1.set_num_format('@') # @ - This is text format in excel
# Add a number format for cells with money.
# money_format = workbook.add_format({'num_format': '#,##0;-#,##0'})
money_format = workbook.add_format({'num_format': '#,##0.00'})
# 单元格自动换行
# cell_wrap = wb_name.add_format()
# cell_wrap.set_text_wrap()
# Create a format to use in the merged range.
merge_format = workbook.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# Set the columns widths.
worksheet_object.set_column('A:A', 6, text_format)
worksheet_object.set_column('B3:B39', 22, text_format)
worksheet_object.set_column('C:C', 6, text_format)
worksheet_object.set_column('D:D', 11.25, text_format)
worksheet_object.set_column('E:E', 6, nums_format)
worksheet_object.set_column('F:F', 8, money_format)
worksheet_object.set_column('G:G', 8, money_format)
worksheet_object.set_column('H:H', 6, text_format)
worksheet_object.set_column('I:I', 9.88, money_format)
worksheet_object.set_column('J:J', 9.88, money_format)
worksheet_object.set_column('K:K', 53, text_format)
# 冻结标题行，第一个参数 2 冻结指第二行，第二个参数 1 指冻结第一列
worksheet_object.freeze_panes(2, 1)
# 合并 A1 到 K1 单元格并应用 merge_format 样式作为标头用
worksheet_object.merge_range('A1:K1', dest_sheet_name, merge_format)
# Close the Pandas Excel writer
writer_object.save()
# ==================================================================================================== **
# Source sheet name
ss_01 = '数据看板'
ss_02 = '会展布线'
# Target Sheet Name
ds_01 = '数据看板'
ds_02 = '会展布线'
# datacleaning(dataframe, sour_sheet_name, sour_usecols, dest_sheet_name, dest_start_row, dest_start_col)
datacleaning('df_b', ss_02, 'B', ds_02, 2, 1)
print('Data cleansing has completed successfully.')Functions
创建自属风格的 Excel 多表
# importing the module
import xlsxwriter
def sheet_page_setup(wb_obj_name):
# =====================================================================
#
# This program shows several examples of how to set up headers and
# footers with XlsxWriter.
#
# The control characters used in the header/footer strings are:
#
# Control Category Description
# ======= ======== ===========
# &L Justification Left
# &C Center
# &R Right
#
# &P Information Page number
# &N Total number of pages
# &D Date
# &T Time
# &F File name
# &A Worksheet name
#
# &fontsize Font Font size
# &"font,style" Font name and style
# &U Single underline
# &E Double underline
# &S Strikethrough
# &X Superscript
# &Y Subscript
#
# &[Picture] Images Image placeholder
# &G Same as &[Picture]
#
# && Miscellaneous Literal ampersand &
#
# =====================================================================
#
# 将工作表的打印页面的方向设置为横向
wb_obj_name.set_landscape()
# 将工作表数据水平居中在打印页面的边距之间
wb_obj_name.center_horizontally()
# 页面边距
wb_obj_name.set_margins(left=0.3, right=0.3, top=0.61, bottom=0.37)
# 页眉 and 页脚
wb_obj_name.set_header("", {'margin': 0.1})
wb_obj_name.set_footer("", {'margin': 0.1})
# &L->left &C->center &R->right
# header = '&LPage &P of &N' + '&CFilename: &F' + '&RSheetname: &A'
# footer = capsheet2 + '&CPage &P of &N' + '&RCurrent DateTime: &D &T'
footer = '&R:: Page &P of &N :: Print Time: &D &T'
wb_obj_name.set_footer(footer)
def sheet_table_setup(wb_obj_name, wb_name, sheet_name):
# Some sample data for the table.
data = []
# Options to use in the table.
options = {'data': data,
'total_row': 1,
'columns': [
{'header': '序号'},
{'header': '名称'},
{'header': '品牌'},
{'header': '型号'},
{'header': '数量'},
{'header': '成本'},
{'header': '单价'},
{'header': '单位'},
{'header': '成本小计'},
{'header': '单价小计'},
{'header': '参数说明'}
]}
# Add a table to the worksheet.表格区域 A2 到 K39，A1 为表头，所以，从 A2 开始为数据填充区域
wb_obj_name.add_table('A2:K39', options)
nums_format = wb_name.add_format({'num_format': '#,##0'})
# text_format = workbook.add_format()
# text_format.set_num_format('@') # @ - This is text format in excel
text_format = wb_name.add_format({'num_format': '@', 'text_wrap':True})
# format1.set_num_format('@') # @ - This is text format in excel
# Add a number format for cells with money.
# money_format = workbook.add_format({'num_format': '#,##0;-#,##0'})
money_format = wb_name.add_format({'num_format': '¥#,##0.00'})
# Create a format to use in the merged range.
merge_format = wb_name.add_format({
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'})
# Set the columns widths.
wb_obj_name.set_column('A:A', 6, text_format)
wb_obj_name.set_column('B:B', 22, text_format)
wb_obj_name.set_column('C:C', 6, text_format)
wb_obj_name.set_column('D:D', 11.25, text_format)
wb_obj_name.set_column('E:E', 6, nums_format)
wb_obj_name.set_column('F:F', 8, money_format)
wb_obj_name.set_column('G:G', 8, money_format)
wb_obj_name.set_column('H:H', 6, text_format)
wb_obj_name.set_column('I:I', 9.88, money_format)
wb_obj_name.set_column('J:J', 9.88, money_format)
wb_obj_name.set_column('K:K', 53, text_format)
# 冻结标题行，第一个参数 2 指冻结第二行，第二个参数 1 指冻结第一列
wb_obj_name.freeze_panes(2, 1)
# 合并 A1 到 K1 单元格并应用 merge_format 样式作为标头用
wb_obj_name.merge_range('A1:K1', sheet_name, merge_format)
# =====================================================================
sourFile = 'D:\\py\\folder\\sourFile.xlsx'
saveFile = 'D:\\py\\folder\\saveFile.xlsx'
cs_01 = '数据看板'
cs_02 = '会展布线'
cs_03 = '会展机房'
cs_04 = '会展监控'
cs_05 = '会展楼控'
wb = xlsxwriter.Workbook(saveFile)
ws_01 = wb.add_worksheet(cs_01)
ws_02 = wb.add_worksheet(cs_02)
sheet_page_setup(ws_02)
sheet_table_setup(ws_02, wb, cs_02)
ws_03 = wb.add_worksheet(cs_03)
sheet_page_setup(ws_03)
sheet_table_setup(ws_03, wb, cs_03)
ws_04 = wb.add_worksheet(cs_04)
sheet_page_setup(ws_04)
sheet_table_setup(ws_04, wb, cs_04)
ws_05 = wb.add_worksheet(cs_05)
sheet_page_setup(ws_05)
sheet_table_setup(ws_05, wb, cs_05)
wb.close()
print('The Excel table has been created successfully.')Functions
根据条件提取填充数据
import os
import pandas as pd
from openpyxl import load_workbook
def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.
@param filename: File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
@param df: DataFrame to save to workbook
@param sheet_name: Name of sheet which will contain DataFrame.
(default: 'Sheet1')
@param startrow: upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the next row...
@param truncate_sheet: truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
@param to_excel_kwargs: arguments which will be passed to `DataFrame.to_excel()`
[can be a dictionary]
@return: None
Usage examples:
# >>> append_df_to_excel('d:/temp/test.xlsx', df)
# >>> append_df_to_excel('d:/temp/test.xlsx', df, header=None, index=False)
# >>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2',
index=False)
# >>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2',
index=False, startrow=25)
(c) [MaxU](https://stackoverflow.com/users/5741205/maxu?tab=profile)
"""
# Excel file doesn't exist - saving and exiting
if not os.path.isfile(filename):
df.to_excel(
filename,
sheet_name=sheet_name,
startrow=startrow if startrow is not None else 0,
**to_excel_kwargs)
return
# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')
# try to open an existing workbook
writer.book = load_workbook(filename)
# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
# index of [sheet_name] sheet
idx = writer.book.sheetnames.index(sheet_name)
# remove [sheet_name]
writer.book.remove(writer.book.worksheets[idx])
# create an empty sheet [sheet_name] using old index
writer.book.create_sheet(sheet_name, idx)
# copy existing sheets
writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
if startrow is None:
startrow = 0
# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)
# save the workbook
writer.save()
sourFile = 'D:\\py\\folder\\sourFile.xlsx'
saveFile = 'D:\\py\\folder\\saveFile.xlsx'
cs_01 = '数据看板'
cs_02 = '会展布线'
cs_03 = '会展机房'
cs_04 = '会展监控'
cs_05 = '会展楼控'
# 报错：Positional argument cannot appear after keyword argumentsPylance
# https://stackoverflow.com/questions/9450656/positional-argument-v-s-keyword-argument
# 注意在命名参数时，要在个变量前面都加上形参的名字
# python 3.8 引入了位置参数，
# 位置参数、关键字参数、必需参数和可选参数经常被混淆。位置参数与必需参数不同。和关键字参数与可选参数不同。
# 位置参数是可以通过它们在函数定义中的位置来调用的参数。
# 关键字参数是可以通过名称调用的参数。
# 必需参数是必须传递给函数的参数。
# 可选参数是不能传递给函数的参数。在 python 中，可选参数是具有默认值的参数。
# 以通过/在参数列表中使用 来指定仅位置参数
# 通过使用*字符来获得仅关键字参数，可以将任意序列的位置参数或关键字参数传递给函数。
# ws_name :定义源表中数据输出的sheet表名
# heard=1 :定义源表中第一行为标头行，不做数据提取
# usecols='B' :定义源表中要输出的列
# wd_name :定义目标表中要填充数据的sheet表名
# na_rep='无' :定义目标表中的空值，以"无"填充
# index=False :无索引
# header=None :目标表不填充表头数据
# startrow :目标表填充数据从哪一行开始
# startcol :目标表填充数据从哪一列开始
def importdata(df_name, ws_name, source_select_cols, wd_name, start_row=2, start_col=1):
df_name = pd.read_excel(sourFile, ws_name, header=1,
usecols=source_select_cols)
append_df_to_excel(saveFile, df_name, sheet_name=wd_name, na_rep='无',
index=False, header=None, startrow=start_row, startcol=start_col)
# 行、列的序号默认由0开始，第一行，其实下标是0，第二行，下标为1
importdata('df_b', cs_02, 'B', cs_02, 2, 1)
importdata('df_c', cs_02, 'C', cs_02, 2, 3)
importdata('df_d', cs_02, 'D', cs_02, 2, 2)
importdata('df_e', cs_02, 'E', cs_02, 2, 7)
importdata('df_f', cs_02, 'F', cs_02, 2, 4)
importdata('df_g', cs_02, 'G', cs_02, 2, 6)
importdata('df_i', cs_02, 'I', cs_02, 2, 10)pandas
摘取数据
# 引入 pandas 库并别名为 pd import pandas as pd # 定义源和目标文件 sourFile = 'D:\\py\\folder\\sourFile.xlsx' saveFile = 'D:\\py\\folder\\saveFile.xlsx' # 读取sheet名为会展布线工作表 # na_rep ：缺失值填充 ，可以设置为字符串，为bool值，则写入excel时改为0和1 # header ：指定作为列名的行。默认0，即取第一行为列名，这里设值为1，即以第二行数据为列名，相应的，提取数据时，提取此行以下的数据；若数据不含列名，则设定 header = None df = pd.read_excel(sourFile,sheet_name = '会展布线', header = 1) # columns ：选择输出的的列存入 # df.to_excel(saveFile,sheet_name = '公寓布线',na_rep = '无',index = False, header = None, columns=['设备、材料'], startrow = 1,startcol = 1, index_label='a')
多sheet合并 – 01
import pandas as pd # 合并多个 sheet
data = pd.read_excel("D:\\SourceFolder\\SourceFile.xlsx", None)
cols = list(data.keys())
newdata = pd.DataFrame()
for i in cols:
df = data[i]
newdata = pd.concat([newdata.df])
newdata.to_excel(
'D:\\DestinationFolder\\DestinationFile.xlsx', index=False)多sheet合并 – 02
首先，需要安装模块：xlrd、xlsxwriter
#python3.* pip3 install xlrd xlsxwriter
接着，创建 Python 脚本，例如创建的脚本命名为：merge.py
# -*- coding:utf-8 -*-
import xlrd,xlsxwriter,os
#待合并excel目标文件夹
path="C:\\Users\\52840\\Desktop\\merge"
#目标excel
end_xls="C:\\Users\\52840\\Desktop\\merge.xlsx"
#获取目标目录中所有excel名称
def get_filenames(path):
filenames = []
for i in os.walk(path):
for filename in i[-1]:
full_filename = os.path.join(i[0],filename)
filenames.append(full_filename)
return filenames
#打开文件
def open_xls(file):
try:
fh=xlrd.open_workbook(file)
return fh
except Exception as e:
print("open file error:"+e)
#根据excel名以及子表名获取数据
def get_file_value(filename,sheetnum):
rvalue=[]
fh=open_xls(filename)
sheet=fh.sheets()[sheetnum]
row_num=sheet.nrows
for rownum in range(0,row_num):
rvalue.append(sheet.row_values(rownum))
return rvalue
#获取第一个excel的sheet个数以及名字作为标准
allxls=get_filenames(path)
first_file_fh=open_xls(allxls[0])
first_file_sheet=first_file_fh.sheets()
first_file_sheet_num=len(first_file_sheet)
sheet_name=[]
for sheetname in first_file_sheet:
sheet_name.append(sheetname.name)
#定义一个目标excel
endxls=xlsxwriter.Workbook(end_xls)
all_sheet_value=[]
#把所有内容都放到列表all_sheet_value中
for sheet_num in range(0,first_file_sheet_num):
all_sheet_value.append([])
for file_name in allxls:
print("opening"+file_name+"the"+str(sheet_num+1)+"label")
file_value=get_file_value(file_name,sheet_num)
all_sheet_value[sheet_num].append(file_value)
num=-1
sheet_index=-1
#将列表all_sheet_value的内容写入目标excel
for sheet in all_sheet_value:
sheet_index+=1
end_xls_sheet=endxls.add_worksheet(sheet_name[sheet_index])
num+=1
num1=-1
for sheet1 in sheet:
for sheet2 in sheet1:
num1+=1
num2=-1
for sheet3 in sheet2:
num2+=1
end_xls_sheet.write(num1,num2,sheet3)
endxls.close()最后，运行脚本
python merge.py
https://xlsxwriter.readthedocs.io/working_with_tables.html
https://katestrykermcm-blog.tumblr.com/post/84145108354/directing-excel-with-python-using-win32com
# format
https://python-pptx.readthedocs.io/en/latest/api/enum/ExcelNumFormat.html
#
https://github.com/jmcnamara/XlsxWriter
# data type
https://xlsxwriter.readthedocs.io/format.html?highlight=text_wrap#format-methods-and-format-properties
Howdy! Do you know if they make any plugins to safeguard against hackers?
I’m kinda paranoid about losing everything I’ve worked hard
on. Any tips?
Sorry, I have just learned Python, so I don’t have any good suggestions for you. Of course, with the further study, there will be better methods in the future, I will share here to everyone. Thank you for your attention.