How to Batch Process Rows Columns and Cells with Python

The directory accurately adjusts the row height and column width of the worksheet Change the data format of multiple workbooks in batches Change the appearance format of workbooks in batches Format fonts, modify fonts, modify font sizes, fonts, bold fonts, colors, cells, fill colors, alignment, add borders of appropriate thickness to replace row data in the workbook Extract specified data Extract column data Append row data Extract the only 1-value summary of all worksheets

Accurately adjust the row height and column width of the worksheet

Steps

Open the workbook. Traverse all worksheets

Core code

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

Change the data format of multiple workbooks in batches

Steps:

List all subfiles in the folder Traverse open subfiles Traversing a worksheet Get the last row of the worksheet Modifies the specified column from top to bottom

Core code

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

Use the number format property in the xwings module to format the data in the range of cells. The value of this property is 1 string representing a specific format, which is the same as Excel's "Format Cells"

Dialog box under the Number tab.

Change the appearance format of workbooks in batches

The difficulty should be how to change the appearance format. Here, we introduce some commonly used appearance formats.

j is a worksheet

Format fonts

Modify the font to Song style

j['A1:H1'].api.Font.Name = ' Song Style '

Modify the font size

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

Bold font

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

Font color

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

Cell fill color

#  Fill color is black 
j['A1:H1'].Color= xw.utils.rgb_to_int((0,0,0)) 

Alignment

#  Horizontal alignment is   Center 
j['A1:H1'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter

#  Vertical alignment is   Center 
j['A1:H1'].api.VerticalAlignment= xw.constants.VAlign.xlVAlignCenter

If the above column is a body, j ['A1: H1'] becomes j ['A2']. expand ('table')

Add a border of appropriate thickness

#  Traverse all the cells 
for cell in j['A1'].expand('table'):
	for b in range(7,12):
		#  Set the border line type of the cell 
		cell.api.Borders(b).LineStyle = 1
		#  Set the border thickness of the cell 
		cell.api.Borders(b).Weight = 2

Replace the row data of the workbook

Core code

#  Traversing a worksheet 
for j in eorkbook.sheets:
	#  Get worksheet data 
	value = j['A2'].expand('table').value
	# Traversing worksheet data by row 
	for index,val in enumerate(value):
		#  Determine whether the row data is this 
		if val == [' Backpack ',16,65]:
			#  If yes, replace it with new data 
			value[index] = [' Backpack ',36,79]
	# Write the data that has been replaced to the worksheet 
	j['A2'].expand('table').value = value
workbook.save()

enumerate () is a built-in function of Python, which is used to combine a traversible data object (such as list, tuple or string, etc.) into an index sequence, and can get the index and corresponding value of the data object at the same time, which is generally used in for statements. The syntax format of this function and the meanings of common parameters are as follows.

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

0

What if it is to modify the designated bank? Because the column is in which cell of the row, we can modify it as follows

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

1

Extract specified data

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

2

Extract column data

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open(' Purchase table .xlsx')
worksheet = workbook.sheets 
column = [' Date of purchase ', ' Purchase amount ']
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(' Extract data ') 
new_worksheet.range('A1').value = pd.concat(data, ignore_index = False).set_index(column[0])
new_workbook.save(' Extraction table .xlsx') 
workbook.close()
app.quit()

Append row data

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

4

Extract the only 1 value of all worksheets

# Traversing a worksheet 
for j in workbook.sheets:
	#  Get the last 1 Row 
	row_num = j['A1'].current_region.last_cell.row
	#  Data format modification 
	j['A2:A{}'.format(row_num)].number_format = 'm/d'
	j['D2:D{}'.format(row_num)].number_format = ' $ #,##0.00'
workbook.save()
workbook.close()

5

Summarize