Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to use Python batch processing of rows, columns and cells, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Precisely 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 = 20workbook.save () batch change the data format of multiple workbooks

Steps:

List all child files in the folder

Traversing and opening subfiles

Traverse the worksheet

Get the last row of the worksheet

Modify the specified column from top to bottom

Core code

# traverse the worksheet for j in workbook.sheets: # get the last line row_num = j ['A1']. Current_region.last_cell.row # data format modification j [' A2current_region.last_cell.row A {} '.format (row_num)] .number _ format =' mUnip d' j ['D2purD {}' .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 cell range. The value of this property is a string that represents a specific format, which is the same as the "format cells" of Excel

The format set under the number tab in the dialog box corresponds to.

Bulk change the appearance format of the workbook

The difficulty should be how the appearance format has been changed. Here are some commonly used appearance formats.

J is the worksheet

Set the font format and modify the font to Arial j ['A1api.Font.Bold= True H1']. Api.Font.Name =' Arial 'modify font size j [' A1purH1']. Api.Font.Size= 10 # 10 point font bold j ['A1Suzhou H1']. Api.Font.Bold= True font color # white j [' A1Suzhou H1']. Api.Font.Color= xw.utils.rgb_to_int (255255255)) Cell fill Color # fill The filling color is black j ['A1RH 1']. Color= xw.utils.rgb_to_int ((0jue 0) 0) alignment # horizontal alignment is centered j ['A1api.VerticalAlignment= xw.constants.VAlign.xlVAlignCenter H1']. Api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter# vertical alignment is centered j [' A1Suzhou H1']. Api.VerticalAlignment= xw.constants.VAlign.xlVAlignCenter

If the above is the text of the column, j ['A1Suzhou H1'] becomes j [' A2']. Table' ('text) is fine.

Add a border of appropriate thickness # iterate through all cells for cell in j ['A1']. For b in range (7 table'): # set the border linetype of the cell cell.api.Borders (b). LineStyle = 1 # set the border thickness of the cell cell.api. Borders (b) .Weight = 2 to replace the row data of the workbook

Core code

# traversal worksheet for j in eorkbook.sheets: # get worksheet data value = j ['A2'] .worksheet data (' table'). Value # traverse worksheet data by row for index,val in enumerate (value): # determine whether the row data belongs to this if val = ['knapsack' 16jue 65]: # replace it with new data value [index] = ['shoulder bag', 36 Magi 79] # write the replacement data to the worksheet j ['A2'] .resume (' table'). Value = valueworkbook.save ()

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

Enumerate (sequenxe, [start=0]) # start is the starting position of the index

What if it is to modify the specified line? Because the column is in the first cell of the row, we can modify it as follows

# traverse worksheet data for index,val in enumerate (value) by row: # modify the third cell, row index, column 3, subscript is val [2] = val [2] + 1 # replace the entire row of data value [index] = val to extract the specified data

Import xlwings as xwimport pandas as pdapp = xw.App (visible = False Add_book = False) workbook = app.books.open ('purchase table .xlsx') worksheet = workbook.sheetsdata = [] for i in worksheet: values = i.range ('A1'). Expand (). Options (pd.DataFrame). Value # one-time extraction of all eligible rows from the worksheet according to filtered = values [values ['purchased goods'] = 'photocopy paper'] if not filtered.empty: data. Append (filtered) new_workbook = xw.books.add () new_worksheet = new_workbook.sheets.add ('copy paper') new_worksheet.range ('A1'). Value = pd.concat (data Ignore_index = False) new_workbook.save ('copy paper .xlsx') workbook.close () app.quit () extract column data

Import xlwings as xwimport pandas as pdapp = 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'). 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 ('extract table .xlsx') workbook.close () app.quit () append row data

Import osimport xlwings as xwnewContent = [['shoulder bag','64', '110'], ['waist bag','23' App = xw.apps.add () file_path = 'segment information' 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 ['Product Classification'] values = worksheet.range ( 'A1'). Expand () number = values.shape [0] worksheet.range (number + 1 1). Value = newContent workbook.save () workbook.close () app.quit () extract the unique value of all worksheets import xlwings as xwapp = xw.App (visible = True, add_book = False) workbook = app.books.open ('first half sales statistics .xlsx') data = [] for I Worksheet in enumerate (workbook.sheets): values = worksheet ['A2'] .cake (' down'). Value data = data + valuesdata = list (set (data)) data.insert (0 ) new_workbook = xw.books.add () new_worksheet = new_workbook.sheets.add ('title') new_worksheet ['A1'] .options (transpose = True). Value = datanew_worksheet.autofit () new_workbook.save (' title of the book') workbook.close () app.quit () Thank you for reading this article carefully I hope the article "how to use Python to batch process rows, columns and cells" shared by the editor is helpful to everyone. At the same time, I also hope that you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.