用python对excel进行单元格操作

本文代码及数据集来自《超简单：用Python让Excel飞起来（实战150例）》

# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""
# 在单元格中输入内容
import xlwings as xw
app = 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 xw
app = 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 = 10
header.font.bold = True
header.font.color = (255, 255, 255)
header.color = (0, 0, 0)
data = worksheet.range('A2').expand('table')
data.font.name = '微软雅黑' # 设置数据行的字体和字号
data.font.size = 10
workbook.save('订单表1.xlsx')
workbook.close()
app.quit()

# 设置单元格数据的对齐方式
import xlwings as xw
app = 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 = -4152
data.api.VerticalAlignment = -4108
workbook.save('订单表2.xlsx')
workbook.close()
app.quit()

# 设置单元格的边框样式
import xlwings as xw
app = 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 xw
app = 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 xw
app = 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 = 18
title.font.bold = True
title.api.HorizontalAlignment = -4108
title.api.VerticalAlignment = -4108
title.row_height = 30
workbook.save('订单表6.xlsx')
workbook.close()
app.quit()

# 合并单元格制作表格标题（方法二）
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment
workbook = 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 = 30
workbook.save('订单表6.xlsx')

# 合并内容相同的连续单元格
from openpyxl import load_workbook
workbook = load_workbook('订单金额表.xlsx')
worksheet = workbook['Sheet1']
lists = []
num = 2
while True: # 构造永久循环
    datas = worksheet.cell(num, 1).value # 逐个读取A列单元格的数据
    if datas: # 如果读取的数据不为空
        lists.append(datas) # 将该数据追加至列表中
    else:
        break
    num += 1
s = 0
e = 0
data = 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 pd
data = 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 pd
data = pd.read_excel('销售表1.xlsx', sheet_name='总表')
data = data.drop_duplicates()
data.to_excel('销售表2.xlsx', sheet_name='总表', index=False)

# 将单元格中的公式转换为数值
import xlwings as xw
app = 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 = data
workbook.save('销售表3.xlsx')
workbook.close()
app.quit()