本文来源于 《超简单用python让Excel飞起来》
在Excel中,行和列都是由一个个单元格组成的。因此,学习完行和列的操作,就需要接着学习单元格的操作。本章将详细介绍如何通过Python编程完成在单元格中输入内容、设置单元格格式、合并单元格等操作。
94在单元格中输入内容
本案例要通过Python编程新建一个工作簿,然后在工作表中的指定单元格内输入需要的数据。
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' )
95 设置单元格数据的字体格式
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.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( )
96 设置单元格数据的对齐方式
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.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( )
97 设置单元格的边框样式
import xlwings as xw
app = xw.App( visible= False,add_book= False)
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( )
修改单元格的数字格式
import xlwings as xw
app = xw.App( visible= False,add_book= False)
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日'
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'
workbook.save( '销售表(修改单元格).xlsx' )
workbook.close( )
app.quit( )
99合并单元格制作表格标题(方法一)
import xlwings as xw
app = xw.App( visible= False,add_book= False)
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( )
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
while True:
datas = worksheet.cell( num,1) .value
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' )
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)
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( )
运行本案例的代码后,打开生成的工作簿“销售表3.xlsx”,选中单元格G2,可看到该单元格中的公式已被转换成数值,如下图所示。