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

This article mainly shows you the "Python openpyxl module how to play Excel", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "Python openpyxl module how to play Excel" this article.

Openpyxl installation

Enter directly at the command prompt.

Pip install openpyxl

Or use Douban image to install.

Pip install-I https://pypi.douban.com/simple openpyxl

After the installation is successful, take a look at how to use the

Open / create a workbook

Sample workbook

Worksheet [first prize]

Worksheet [second prize]

(1) Open the local workbook

> from openpyxl import load_workbook > wb = load_workbook ('winning list .xlsx')

(2) create an empty workbook

> from openpyxl import Workbook > wb1 = Workbook () access worksheet

To create a new worksheet, you can specify the location to insert (0: first,-1: end).

> wb.create_sheet ('new_sheet', 0)

Gets all worksheets in the workbook.

> wb.sheetnames ['new_sheet',' first prize', 'second prize']

Use list-driven traversal to get all worksheet names.

> [sheet.title for sheet in wb] ['new_sheet',' first prize', 'second prize']

Use wb [sheetname] to get a worksheet object

> wb [second prize]

Get the activity table (that is, open the worksheet that first appears in the workbook).

> wb.active

Gets the row and column information of the worksheet.

> sheet1 = wb ['first prize'] > sheet1.max_column7 > sheet1.max_row6 gets cell information

Access a cell

> sheet1 ['D3'] > sheet1.cell (row=3, column=4)

Adding the value parameter to access the cell format modifies the value of the current cell.

> sheet1.cell (3,4) .value 'Information extraction and Management of Medical staff based on Spark and Python' > sheet1.cell (3,4, value='Python') > sheet1.cell (3,4). Value'Python'

Gets the value, coordinates, row index, and column index of the cell.

> sheet1 ['D3']. Value'Python' > sheet1 [' D3']. Coordinate'D3' > sheet1 ['D3'] .row3 > sheet1 [' D3'] .column4

Access multiple cells

Use slices to access multiple cells. The slice here is different from the list slice. The list slice is closed before and after opening, while the slice in Excel is closed before and after closing.

(1) Select the cell in the A1:B2 range.

> sheet1 ['A _ 1'((,), ())

Select a single column of data.

> sheet1 ['D'] (,...)

Select the data in column C of Brecast.

> sheet1 [(,...)] ((,...))

Select a single row of data.

Sheet1 [3] (,)

Select the 2nd and 3rd rows of data.

Sheet1 [2:3] ((,...), (,...))

Traversing to get data

Traverses the specified range (B2:C3) data by row.

> for row in sheet1.iter_rows (min_row=2, max_row=3, min_col=2, max_col=3): for cell in row: print (cell.coordinate) B2C2B3C3

Traverses the specified range (B2:C3) data by column.

> for col in sheet1.iter_cols (min_row=2, max_row=3, min_col=2, max_col=3): for cell in col: print (cell.coordinate) B2B3C2C3

If the parameter values_only=True is specified in iter_rows () / iter_cols (), only the value of the cell will be returned

Iterate through all the data by row.

> tuple (sheet1.rows) ((,...),... (,...))

Traverse all data by column.

> tuple (sheet1.columns) ((,...),... (...)) modify the worksheet

Cell assignment

Add a new column to calculate the number of authors.

> for row_index in range (2, sheet1.max_row + 1): sheet1.cell (row_index, 8). Value = len (sheet1.cell (row_index, 6). Value.split (',')

Use the formula to assign a value to the cell, and H7 counts the total number of authors.

> sheet1 ['H7'] =' = SUM (H1:H6)'

Append a row of data

Use the list to pass values in order.

> sheet1.append ([str (n) for n in range (6)])

Use a dictionary to specify the column index: column value.

> sheet1.append ({'axiajiajiaolong 1) >

Insert a blank line

Insert blank rows at the specified location, idx row index, insertion location; number of blank rows inserted by amount

> sheet1.insert_rows (idx=2, amount=2)

Delete worksheet

> wb.remove (wb ['new_sheet'])

Save the workbook

>

Font

Set the B2 cell font format to, and the color can be coded in hexadecimal.

> from openpyxl.styles import Font > new_font = Font (name=' Microsoft Yahei', size=20, color='3333CC', bold=True) > sheet1 ['B2'] .font = new_font

Cell background color

> from openpyxl.styles import PatternFill, colors > sheet1 ["A2"]. Fill = PatternFill ("solid", fgColor=colors.BLUE) > sheet1 ["A3"]. Fill = PatternFill ("solid", fgColor='FF66CC')

Alignment mode

Set the data in D2 to center vertically and horizontally.

> from openpyxl.styles import Alignment > sheet1 ['D2']. Alignment = Alignment (horizontal='center', vertical='center')

Row height / column width

Set the second row height to 40 and column C to 20.

> sheet1.row_dimensions [2] .height = 40 > sheet1.column_dimensions ['C'] .width = 20

Merge / split cells

Merge cells only need to specify the cell coordinates of the upper-left corner and the lower-right corner.

> sheet.merge_cells ('A1Rose C3')

When the cell is split, the value of the merged range is assigned to cell A1 in the upper-left corner.

> sheet.unmerge_cells ('A1Rose C3')

The above is all the content of the article "how to play Excel with openpyxl module in Python". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.