本文代码及数据集来自《超简单：用Python让Excel飞起来（实战150例）》
# -*- coding: utf-8 -*-"""Spyder EditorThis is a temporary script file."""# 在单元格中输入内容import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.add()worksheet = workbook.sheets.add(name='销售情况')worksheet.range('A1').value = [['产品名称', '销售数量', '销售单价', '销售额'], ['大衣', 15, 400, 6000], ['羽绒服', 20, 500, 10000]]workbook.save('产品表.xlsx')workbook.close()app.quit()# 设置单元格数据的字体格式import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('订单表.xlsx')worksheet = workbook.sheets[0]header = worksheet.range('A1:I1') # 选中工作表的表头所在的单元格区域header.font.name = '微软雅黑' # 设置表头的字体格式header.font.size = 10header.font.bold = Trueheader.font.color = (255, 255, 255)header.color = (0, 0, 0)data = worksheet.range('A2').expand('table')data.font.name = '微软雅黑' # 设置数据行的字体和字号data.font.size = 10workbook.save('订单表1.xlsx')workbook.close()app.quit()# 设置单元格数据的对齐方式import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('订单表1.xlsx')worksheet = workbook.sheets[0]header = worksheet.range('A1:I1') # 选中工作表的表头所在的单元格区域header.api.HorizontalAlignment = -4108 # 为表头设置水平对齐方式header.api.VerticalAlignment = -4108 # 垂直对齐方式data = worksheet.range('A2').expand('table') # 选中工作表的数据行所在的单元格区域data.api.HorizontalAlignment = -4152data.api.VerticalAlignment = -4108workbook.save('订单表2.xlsx')workbook.close()app.quit()# 设置单元格的边框样式import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('订单表2.xlsx')worksheet = workbook.sheets[0]area = worksheet.range('A1').expand('table')for i in area: for j in range(7, 11): i.api.Borders(j).LineStyle = 1 # 设置线型为实线 i.api.Borders(j).Weight = 2 # 粗细为细线 i.api.Borders(j).Color = xw.utils.rgb_to_int((255, 0, 0)) # 颜色为红色workbook.save('订单表3.xlsx')workbook.close()app.quit()# 修改单元格的数字格式import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('订单表3.xlsx')worksheet = workbook.sheets[0]row_num = worksheet.range('A1').expand('table').last_cell.row # 获取工作表中数据区域最后一行的行号worksheet.range(f'B2:B{row_num}').number_format = 'yyyy年m月d日' # “销售日期”列的数据设置日期格式worksheet.range(f'D2:D{row_num}').number_format = '￥#,##0' # 设置为带货币符号的整数worksheet.range(f'E2:E{row_num}').number_format = '￥#,##0'worksheet.range(f'G2:G{row_num}').number_format = '￥#,##0.00' # 设置为带货币符号的两位小数worksheet.range(f'H2:H{row_num}').number_format = '￥#,##0.00'worksheet.range(f'I2:I{row_num}').number_format = '￥#,##0.00'workbook.save('订单表4.xlsx')workbook.close()app.quit()# 合并单元格制作表格标题（方法一）import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('订单表5.xlsx')worksheet = workbook.sheets[0]title = worksheet.range('A1:I1') # 指定要合并的单元格区域title.merge() # 合并指定的单元格区域title.font.name = '微软雅黑'title.font.size = 18title.font.bold = Truetitle.api.HorizontalAlignment = -4108title.api.VerticalAlignment = -4108title.row_height = 30workbook.save('订单表6.xlsx')workbook.close()app.quit()# 合并单元格制作表格标题（方法二）from openpyxl import load_workbookfrom openpyxl.styles import Font, Alignmentworkbook = load_workbook('订单表5.xlsx')worksheet = workbook['总表']worksheet.merge_cells('A1:I1')worksheet['A1'].font = Font(name='微软雅黑', size=18, bold=True)worksheet['A1'].alignment = Alignment(horizontal='center', vertical='center')worksheet.row_dimensions[1].height = 30workbook.save('订单表6.xlsx')# 合并内容相同的连续单元格from openpyxl import load_workbookworkbook = load_workbook('订单金额表.xlsx')worksheet = workbook['Sheet1']lists = []num = 2while True: # 构造永久循环 datas = worksheet.cell(num, 1).value # 逐个读取A列单元格的数据 if datas: # 如果读取的数据不为空 lists.append(datas) # 将该数据追加至列表中 else: break num += 1s = 0e = 0data = lists[0]for m in range(len(lists)): if lists[m] != data: data = lists[m] e = m - 1 if e >= s: worksheet.merge_cells(f'A{s + 2}:A{e + 2}') s = e + 1 if m == len(lists) - 1: e = m worksheet.merge_cells(f'A{s + 2}:A{e + 2}')workbook.save('订单金额表1.xlsx')# 在空白单元格中填充数据import pandas as pddata = pd.read_excel('销售表.xlsx', sheet_name='总表')data['销售金额'].fillna(0, inplace=True) # 在“销售金额”列的空白单元格中填充零值data['利润'].fillna(0, inplace=True)data.to_excel('销售表1.xlsx', sheet_name='总表', index=False)# 删除工作表中的重复行import pandas as pddata = pd.read_excel('销售表1.xlsx', sheet_name='总表')data = data.drop_duplicates()data.to_excel('销售表2.xlsx', sheet_name='总表', index=False)# 将单元格中的公式转换为数值import xlwings as xwapp = xw.App(visible=False, add_book=False)workbook = app.books.open('销售表2.xlsx')worksheet = workbook.sheets[0]data = worksheet.range('A1').expand('table').value # 只会读取展示的数值，不会读取公式worksheet.range('A1').expand('table').value = dataworkbook.save('销售表3.xlsx')workbook.close()app.quit()