如何利用Python批量处理行、列和单元格详解

目录

精确调整工作表的行高和列宽
批量更改多个工作簿的数据格式
批量更改工作簿的外观格式
设置字体格式修改字体为宋体修改字号字体加粗字体颜色单元格填充颜色对齐方式添加合适粗细的边框替换工作簿的行数据
提取指定数据
提取列数据
追加行数据
提取所有工作表的唯一值总结

精确调整工作表的行高和列宽

步骤

打开工作簿。
遍历所有工作表

核心代码

for i in workbook.sheets:
	value = i.range(\'A1\').expand(\'table\')
	value.column_width = 12
	value.row_height = 20
workbook.save()

批量更改多个工作簿的数据格式

步骤：

列出文件夹中所有子文件
遍历打开子文件
遍历工作表
获取工作表的最后一行
将指定列从上到下修改

核心代码

#遍历工作表
for j in workbook.sheets:
	# 获取最后一行
	row_num = j[\'A1\'].current_region.last_cell.row
	# 数据格式修改
	j[\'A2:A{}\'.format(row_num)].number_format = \'m/d\'
	j[\'D2:D{}\'.format(row_num)].number_format = \'￥#,##0.00\'
workbook.save()
workbook.close()

使用xwings模块中的number format属性来设置单元格区域中数据的格式。该属性的取值为一个代表特定格式的字符串，与Excel的“设置单元格格式”

对话框中“数字”选项卡下设置的格式对应。

批量更改工作簿的外观格式

难点应该是外观格式如何更改了吧，这里介绍一些常用的外观格式。

j 是工作表

设置字体格式

修改字体为宋体

j[\'A1:H1\'].api.Font.Name = \'宋体\'

修改字号

j[\'A1:H1\'].api.Font.Size= 10  # 10磅

字体加粗

j[\'A1:H1\'].api.Font.Bold= True  

字体颜色

# 白色
j[\'A1:H1\'].api.Font.Color= xw.utils.rgb_to_int((255,255,255)) 

单元格填充颜色

# 填充颜色为黑色
j[\'A1:H1\'].Color= xw.utils.rgb_to_int((0,0,0)) 

对齐方式

# 水平对齐方式为 居中
j[\'A1:H1\'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter

# 垂直对齐方式为 居中
j[\'A1:H1\'].api.VerticalAlignment= xw.constants.VAlign.xlVAlignCenter

以上是一列的如果是正文，j[\’A1:H1\’]变成 j[\’A2\’].expand(\’table\’) 就可以了

添加合适粗细的边框

# 遍历所有的单元格
for cell in j[\'A1\'].expand(\'table\'):
	for b in range(7,12):
		# 设置单元格的边框线型
		cell.api.Borders(b).LineStyle = 1
		# 设置单元格的边框粗细
		cell.api.Borders(b).Weight = 2

替换工作簿的行数据

核心代码

# 遍历工作表
for j in eorkbook.sheets:
	# 获取工作表数据
	value = j[\'A2\'].expand(\'table\').value
	#按行遍历工作表数据
	for index,val in enumerate(value):
		# 判断行数据是否是这个
		if val == [\'背包\',16,65]:
			# 是的话替换为新数据
			value[index] = [\'双肩包\',36,79]
	#将完成替换的数据写入工作表中
	j[\'A2\'].expand(\'table\').value = value
workbook.save()

enumerate()是Python的内置函数，用于将一个可遍历的数据对象(如列表、元组或字符串等)组合为一个索引序列，可同时得到数据对象的索引及对应的值，一般用在for语句当中。该函数的语法格式和常用参数含义如下。

enumerate(sequenxe,[start=0])
# start 是索引的起始位置

如果是修改指定行什么办呢？因为列是在行的第几个单元格中我们可以按如下代码进行修改

#按行遍历工作表数据
for index,val in enumerate(value):
	# 修改第三个单元格，即第index行，第 3列，下标是从0开始的
	val[2] = val[2] + 1
	# 替换整行数据
	value[index] = val

提取指定数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open(\'采购表.xlsx\')  
worksheet = workbook.sheets
data = []
for i in worksheet:
    values = i.range(\'A1\').expand().options(pd.DataFrame).value
    # 一次性提取工作表中所有符合条件的行数据
    filtered = values[values[\'采购物品\'] == \'复印纸\'] 
    if not filtered.empty: 
        data.append(filtered)
new_workbook = xw.books.add() 
new_worksheet = new_workbook.sheets.add(\'复印纸\') 
new_worksheet.range(\'A1\').value = pd.concat(data, ignore_index = False) 
new_workbook.save(\'复印纸.xlsx\')
workbook.close()
app.quit()

提取列数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open(\'采购表.xlsx\')
worksheet = workbook.sheets 
column = [\'采购日期\', \'采购金额\']
data = []
for i in worksheet:
    values = i.range(\'A1\').expand().options(pd.DataFrame, index = False).value
    filtered = values[column]  
    data.append(filtered)
new_workbook = xw.books.add()  
new_worksheet = new_workbook.sheets.add(\'提取数据\') 
new_worksheet.range(\'A1\').value = pd.concat(data, ignore_index = False).set_index(column[0])
new_workbook.save(\'提取表.xlsx\') 
workbook.close()
app.quit()

追加行数据

import os
import xlwings as xw
newContent = [[\'双肩包\', \'64\', \'110\'], [\'腰包\', \'23\', \'58\']]
app = xw.apps.add()
file_path = \'分部信息\'  
file_list = os.listdir(file_path)  
for i in file_list:  
    if os.path.splitext(i)[1] == \'.xlsx\':  
        workbook = app.books.open(file_path + \'\\\\\' + i)  
        worksheet = workbook.sheets[\'产品分类表\']
        values = worksheet.range(\'A1\').expand()
        number = values.shape[0]  
        worksheet.range(number + 1, 1).value = newContent  
        workbook.save()
        workbook.close()
app.quit()

提取所有工作表的唯一值

import xlwings as xw
app = xw.App(visible = True, add_book = False)
workbook = app.books.open(\'上半年销售统计表.xlsx\')
data = []
for i, worksheet in enumerate(workbook.sheets):
    values = worksheet[\'A2\'].expand(\'down\').value
    data = data + values
data = list(set(data))
data.insert(0, \'书名\')
new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add(\'书名\')
new_worksheet[\'A1\'].options(transpose = True).value = data
new_worksheet.autofit()
new_workbook.save(\'书名.xlsx\')
workbook.close()
app.quit()

总结