用python让excel飞起来（第5章 单元格操作）

本文来源于 《超简单用python让Excel飞起来》

• 在Excel中，行和列都是由一个个单元格组成的。因此，学习完行和列的操作，就需要接着学习单元格的操作。本章将详细介绍如何通过Python编程完成在单元格中输入内容、设置单元格格式、合并单元格等操作。

94在单元格中输入内容

• 本案例要通过Python编程新建一个工作簿，然后在工作表中的指定单元格内输入需要的数据。

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.add() #新建工作簿
worksheet = workbook.sheets.add(name='销售情况') #新增工作表
worksheet.range('A1').value=[['产品名称','销售数量','销售单价','销售额'],['大衣',15,400,6000],['羽绒服',20,500,10000]] #在单元格中输入内容
workbook.save('产品表.xlsx') #另存为工作簿

95 设置单元格数据的字体格式

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表.xlsx') # 打开要设置字体格式的工作簿
worksheet = workbook.sheets[0] #指定要设置字体格式的工作表
header = worksheet.range('A1:I1') # 选中表头所在的单元格区域
header.api.font.name = '微软雅黑' #设置表头的字体
header.api.font.size = 10 #设置表头的字号
header.api.font.bold = True #设置表头的字形为加粗
header.api.font.color = (255,255,255) #设置表头的字体颜色
header.api.color = (0,0,0) #设置表头的单元格填充颜色
data = worksheet.range('A2').expand('table') #选中数据行所在的单元格区域
data.font.name = '微软雅黑' #设置数据行的字体
data.font.size = 10 # 设置数据行的字号
workbook.save('销售表(列操作).xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

• 出现以下报错，未能解决

96 设置单元格数据的对齐方式

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表.xlsx') # 打开要设置对齐方式的工作簿
worksheet = workbook.sheets[0] #指定要设置对齐方式的工作表
header = worksheet.range('A1:I1') # 选中表头所在的单元格区域
header.api.HorizontalAlignment = -4108 #设置表头的水平对齐方式
header.api.VerticalAlignment = -4108 #设置表头的垂直对齐方式
data = worksheet.range('A2').expand('table') #选中数据行所在的单元格区域
header.api.HorizontalAlignment = -4152 #设置数据行的对齐方式
header.api.HorizontalAlignment = -4108 #设置数据行的垂直对齐方式
workbook.save('销售表11.xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

97 设置单元格的边框样式

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表.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('销售表(边框样式）.xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

修改单元格的数字格式

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表.xlsx') # 打开要设置对齐方式的工作簿
worksheet = workbook.sheets[1] #指定要设置数字格式的工作表
row_num = worksheet.range('A1').expand('table').last_cell.row #获取工作表中数据区域最后一行的行号
worksheet.range(f'B2:B{row_num}').number_format='yyyy年m月d日' # 将B列数据的数字格式全部修改为“xxxx年x月x日”
worksheet.range(f'D2:D{row_num}').number_format='￥#,##0' # 将D列数据的数字格式全部修改为带货币符号的整数
worksheet.range(f'E2:E{row_num}').number_format='￥#,##0' # 将E列数据的数字格式全部修改为带货币符号的整数
worksheet.range(f'G2:G{row_num}').number_format='￥#,##0.00' # 将G列数据的数字格式全部修改为带货币符号的的两位小数
workbook.save('销售表(修改单元格).xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

99合并单元格制作表格标题（方法一）

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表1.xlsx') # 打开要设置对齐方式的工作簿
worksheet = workbook.sheets[0] #指定要设置数字格式的工作表
title = worksheet.range('A1:I1') #指定要合并单元格的区域
title.merge() #合并单元格
title.api.font.size = 18 #设置标题字号
title.api.font.bold = True #设置标题的字形为加粗
title.api.HorizontalAlignment = -4108 #设置标题的水平对齐方式
title.api.VerticalAlignment = -4108 #设置标题的垂直对齐方式
title.row_height = 30 #设置标题单元格的行高
workbook.save('销售表(表格标题1).xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

100合并单元格制作表格标题（方法二）

from openpyxl import load_workbook 
from openpyxl.styles import Font,Alignment 
workbook = load_workbook('销售表1.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('销售表(表格标题2).xlsx') #另存工作簿

• 第7行代码用于设置合并单元格的对齐方式。其中参数horizontal用于设置水平对齐方式，可取的值有’general’、‘left’、‘center’、‘right’、‘fill’、‘justify’、‘centerContinuous’、‘distributed’，分别代表“常规”“靠左”“居中”“靠右”“填充”“两端对齐”“跨列居中”“分散对齐”；参数vertical用于设置垂直对齐方式，可取的值有’top’、‘center’、‘bottom’、‘justify’、‘distributed’，分别代表“靠上”“居中”“靠下”“两端对齐”“分散对齐”。读者可根据实际需求修改参数值。
• 第8行代码先用row_dimensions属性定位要设置行高的行，再通过height属性设置行高值。如果要设置列宽，则需要先用column_dimensions属性定位要设置列宽的列，然后通过width属性设置列宽值，如“worksheet.column_dimensions[‘A’].width=50”。

101合并内容相同的连续单元格

• 如下图所示为工作簿“订单金额表.xlsx”的工作表“Sheet1”中的数据表格，现在需要将“省份”列中含有相同省份的相邻单元格合并为一个单元格。

from openpyxl import load_workbook
workbook = load_workbook('订单金额表.xlsx')
worksheet = workbook['Sheet1'] #指定要合并单元格的工作表
lists = [] #创建一个空列表
num = 2 #为变量num赋值初始值
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}') # 合并A列相同内容的单元格
            s = e + 1
    if m == len(lists) - 1:
        e = m
        worksheet.merge_cells(f'A{s + 2}:A{e + 2}') #合并A列相同内容的单元格
workbook.save('订单金额1.xlsx') #另存工作簿

102 在空白单元格中填充数据

• 如下图所示，工作簿“销售表.xlsx”的工作表“1月”中有部分单元格的数据缺失，假设按照相关规定，这些单元格要填充为零值。下面通过Python编程完成这项工作。

import pandas as pd
data = pd.read_excel('销售表.xlsx',sheet_name='1月') #读取工作簿中指定工作表的数据
data['销售金额'].fillna(0,inplace=True) #在“销售金额”列的空白单元格中填充零值
data['利润'].fillna(0,inplace=True) #在“利润”列的空白单元格中填充零值
data.to_excel('销售表111.xlsx',sheet_name='1月',index=False) #将填充零值后的数据写入新工作簿的工作表中

删除工作表中的重复行

import pandas as pd
data = pd.read_excel('销售表11.xlsx',sheet_name='总表') #读取工作簿中指定工作表的数据
data = data.drop_duplicates() #删除重复行
data.to_excel('销售表1112.xlsx',sheet_name='总表',index=False) #将删除后的数据写入新工作簿的工作表中

• 第3行代码中的drop_duplicates()是pandas模块中DataFrame对象的函数，用于删除数据中的重复行。通过设置该函数的参数keep可达到不同的删除效果：设置为’first’或省略时，表示保留首次出现的重复行，删除后面的重复行；设置为’last’时，表示保留最后一次出现的重复行，删除前面的重复行；设置为False时，表示删除所有的重复行。演示代码如下：

import pandas as pd
a = pd.DataFrame([['Rick',28],['Tom',23],['Lucy',21],['Tom',23]],columns=['name','age'])
print(a)
c = a.drop_duplicates(keep='last')
print(c)

• 上述演示代码的第2行创建了一个DataFrame并赋给变量a。第4行删除a中的重复行，保留最后一次出现的重复行。代码运行结果如下：

   name  age
0  Rick   28
1   Tom   23
2  Lucy   21
3   Tom   23
   name  age
0  Rick   28
2  Lucy   21
3   Tom   23

104 将单元格中的公式转换为数值

• 如下图所示为工作簿“销售表.xlsx”的工作表“总表”中的数据表格，其中“产品成本”列的数据是通过公式计算出来的。例如，单元格G2中的公式为“=D2*F2”。现在要通过Python编程将“产品成本”列的所有公式转换为计算结果的数值。
  

import xlwings as xw
app = xw.App(visible=False,add_book=False) #启动Excel程序
workbook = app.books.open('销售表.xlsx') # 打开要操作的工作簿
worksheet = workbook.sheets[0] #指定要操作的工作表
data = worksheet.range('A1').expand('table').value #读取工作表中的数据
worksheet.range('A1').expand('table').value = data #将要读取的数据写入工作表
workbook.save('销售表22.xlsx') #另存工作簿
workbook.close() #关闭工作簿
app.quit() #退出Excel程序

• 运行本案例的代码后，打开生成的工作簿“销售表3.xlsx”，选中单元格G2，可看到该单元格中的公式已被转换成数值，如下图所示。