如何利用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
	# 數(shù)據(jù)格式修改
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = '￥#,##0.00'
workbook.save()
workbook.close()

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

# 遍歷所有的單元格
for cell in j['A1'].expand('table'):
	for b in range(7,12):
		# 設(shè)置單元格的邊框線型
		cell.api.Borders(b).LineStyle = 1
		# 設(shè)置單元格的邊框粗細
		cell.api.Borders(b).Weight = 2

# 遍歷工作表
for j in eorkbook.sheets:
	# 獲取工作表數(shù)據(jù)
	value = j['A2'].expand('table').value
	#按行遍歷工作表數(shù)據(jù)
	for index,val in enumerate(value):
		# 判斷行數(shù)據(jù)是否是這個
		if val == ['背包',16,65]:
			# 是的話替換為新數(shù)據(jù)
			value[index] = ['雙肩包',36,79]
	#將完成替換的數(shù)據(jù)寫入工作表中
	j['A2'].expand('table').value = value
workbook.save()

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

#按行遍歷工作表數(shù)據(jù)
for index,val in enumerate(value):
	# 修改第三個單元格，即第index行，第 3列，下標是從0開始的
	val[2] = val[2] + 1
	# 替換整行數(shù)據(jù)
	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
    # 一次性提取工作表中所有符合條件的行數(shù)據(jù)
    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('提取數(shù)據(jù)') 
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['產(chǎn)品分類表']
        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('上半年銷售統(tǒng)計表.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()