PYTHON-OPENPYXL manipulating Excel

Installing the OPENPYXL Plugin

1. Unzip the OPENPYXL

2.cmd command line, go to directory, recruit to setup.py file, execute command: Python setup.py install

CMD-
D:
CD D:\py libs\openpyxl-2.4.1
Python setup.py Install

To see if the installation was successful

Cmd

Python

Import Module Name

To manipulate Excel steps:

1. Know the address of Excel

2. Get worksheets in Excel (sheet)

3. Through the sheet, the operation of Excel (add data \ query data \ Delete data \ Modify data)

4. Save the Saved

Initial Excel table

For the above table, do the following:

Get Action permissions for worksheets in Excel

ImportOPENPYXLImport Time" "OPENPYXL only the name of the xlsx suffix is supported" "Path='D:/python workspace/excel/excel.xlsx'#opens the Excel file, returns the tag bit to WBWB =Openpyxl.load_workbook (path)Print("Get Workbook All sheet name:", Wb.get_sheet_names ()) sheet= Wb.get_sheet_by_name ('Main')Print("gets the specified worksheet", Sheet.title) Sheet2=Wb.get_active_sheet ()Print("get the active worksheet", Sheet2.title)

Results:

Get workbook all sheet name: [' main ', ' Sheet2 ', ' Sheet3 ']
Gets the specified worksheet main
Gets the active worksheet main

Next, manipulate the cell to get the cell data

# get cell data, main worksheet cell A1 data Print (" get cell A1 value ", sheet['A1'].value) # The benefits of this writing are easily traversed with a for loop, through rows and columns Print ( get cell A1 value , Sheet.cell (row=1,column=1). Value)

Results:

Get cell A1 Value name
Get cell A1 Value name

Next up, the data is written to the cell

# Sheet.cell (row = 5,column = 3). Value = "Test" # sheet[' B10 '] = "test1"

Gets the maximum number of rows and columns of valid data

# gets the maximum number of columns and rows for which the cell has data Print (" get Maximum number of columns ", Sheet.max_column) Print (" get Maximum number of rows ", Sheet.max_row)

Results:

Get maximum number of columns 4
Get maximum number of rows 2

Loop value

Gets the value of a row

# all values in the first row  for  in sheet["1"]:    print(i.value)

Results:

Name
Function
Automation
Performance

Get all values for a column

# all the values in column C  for  in sheet["C"]:    print(i.value)

Results:

Automation
62

Traverse the value of the entire table, note +1

# traverse the value of the entire table, note +1 # For Loop, one row for each line gets  for  in range (1,sheet.max_row+1):    for in range ( 1,sheet.max_column+1):         Print (Sheet.cell (row = I,column = j). Value)

Name
Function
Automation
Performance
Digital
52
62
72

When the operation is complete, be careful to save

# specifies the path to save, path represents the specified path and the       new name # wb.save (path) # no path, is the original address overwrite save wb.save (path)

The final result:

PYTHON-OPENPYXL manipulating Excel