Python Excel Operations Summary

Import of 1.OPENPYXL packages

Dos command Line input pip install openpyxl==2.3.3

Note here that the version of the OPENPYXL package issue version is too high There are many APIs are not supported, so I use the 2.3.3

To verify that the installation was successful: Python interactive mode Importing Packages Import OPENPYXL

2. a simple in the Excel the operation to write data in

#未从文件系统生成真的excel文件, just an instance of workbook is instantiated

WB = Workbook () #workbook类似一个excel文件

# wb.active Gets the first sheet. Active defaults to the first sheet

WS = Wb.active

Ways to write Data in Excel:

# method 1: Assign values directly in the table

ws[' A1 '] = 42

ws["A2"]=u "China" #写入中文

ws["A3"]= ' 12% ' #写入12%

ws["A4"]=31.75 #写入小数

ws["A5"]=datetime.datetime.now () #写入日期

# Method 2: Add a row to the table with one cell per element

#备注: If the first line of Excel already has data, use Ws.append to replace the first row of data. If you do not want to be overwritten, use ws[' xx ' = xx method

Ws.append ([1, "Chinese", "China"])

Ws.append ([' QRP ', 18])

Ws.append ([' QRP ', ' Love swimming ')

# Write date objects in Excel, automatically converted to strings

Import datetime

Print type (Datetime.datetime.now ())

ws[' A3 '] = Datetime.datetime.now ()

#生成真实的excel文件, save

Wb.save (U "2nd file. xlsx")

3. Operation of the sheet

Excel has a sheet by default (Note that there is a different number of default sheet depending on which Excel is used )

#0. Modify the default sheet the method

Ws0=wb.active #active默认为第一个sheet

Print Ws0.title

ws0.title= "Python"

Print Ws0.title

#1. in the Excel the first place to add a sheet

WS2 = Wb.create_sheet ("Mysheet", 0)

#2. in the Excel The last position to add a sheet (usually added to the last one by default)

WS1 = Wb.create_sheet (U "Road of Glory")

#3. Modify sheet 's name

Ws2.title=u "Python Excel operation Exercise"

You can modify the sheet name when you create a new sheet, for example

WS2 = Wb.create_sheet (title=u "New Sheet2")

#4. get a name from a sheet Object

WS3 = wb[u "python Excel operation Exercise"]

#5. get a name from a sheet the second method of the object

WS4 = Wb.get_sheet_by_name (U "Road of Glory")

#6. Gets an index number from the sheet the object's first three kind of Method

Ws5=wb.get_sheet_by_name (Wb.sheetnames[0])

Print Ws5.title

#7. Print all sheet the name of the two methods, with a list to store all the names

Print Wb.get_sheet_names ()

Print Wb.sheetnames

#8. setting sheet background-like color

#颜色编码的网址: http://www.computerhope.com/htmcolor.htm

Ws1.sheet_properties.tabColor = "FFFF00"

Ws2.sheet_properties.tabColor = "ffa62f"

#9. Print sheet 's name

Print Ws3.title

Print Ws4.title

#10. Traverse Output Excel all of the sheet name

For sheet in WB:

Print (Sheet.title)

4. manipulating cell cells

#coding Utf-8

From OPENPYXL import Workbook

Wb=workbook ()

WS = Wb.create_sheet (U "Road of Glory")

#1. Adding data to cells

Ws.append ([' GILR ', ' 18 '])

ws[' A2 ']=u ' boys '

ws[' B2 ']= ' 20 '

#2. Modify and read the contents of a cell

Cell.value=u "30"

Print Cell.value

#3. get objects by cell letter ID

cell=ws[' B2 ']

Print Cell.value,type (cell.value)

Print ws[' A2 '].value,type (ws[' A2 '].value)

#4. set the value of a Cell object by cell ordinal

#单元格和列, numbering starts at 1

Cell2=ws.cell (row=4, column=1, value=u "I'm adding in through the ranks")

Print Cell2.value

#5. Create 100 cells from a loop

For I in Range (1,11):

For j in Range (1,11):

Ws.cell (Row=i, Column=j, Value=str (i) +u "Row" +str (j) +u "column")

#6 get maximum row and maximum columns

Print Ws.max_row

Print Ws.max_column

Print Ws.min_row

Print Ws.min_column

#7. get values and coordinate values for all cells

For row in Ws.iter_rows (): #获取了所有的行

For cell in row: #获取每一行中每个单元格

Print cell,cell.value,cell.coordinate #打印单元格, cell value, cell.coordinate represents the coordinates of the cell

#range_string = "A1:j10"

For row in Ws.iter_rows (range_string= "A1:j10"): #获取了所有的行

For cell in row: #获取每一行中每个单元格

Print cell,cell.value,cell.coordinate #打印单元格, cell value, cell.coordinate represents the coordinates of the cell

#8. Get_column_letter (col) function to get the letter to the column

#{0}.format (XX) string template assigns the value of XX to {0}

For row in range (10,20):

For Col in Range (10,20):

Ws1.cell (Row=row, Column=col, value= ' {0} '. Format (Get_column_letter (col)))

#定义excel文件保存后的文件名

dest_filename = U ' Excel rename file. xlsx '

Wb.save (Dest_filename)

5. Picture Operation

to install a picture before the Operation PiL's bag. The Pip install command is not good for making direct use of executable files.

From OPENPYXL import Workbook

From openpyxl.drawing.image import image

WB = Workbook ()

WS = Wb.active

ws[' A1 ' = ' should see three logos below '

# Create an image

IMG1 = Image (' 1.png ')

Img2=image ("1.png")

# Add to worksheet and anchor next to cells

Ws.add_image (IMG1, "A3")

Ws.add_image (Img2, "C5")

#相同的图片对象没办法赋给多个单元格, if you do this will cause the pictures to overlap.

#但是可以生成两个不同的图片对象来给多个单元格赋值.

Wb.save (U ' picture operation. xlsx ')

6. getting row and column operations

From OPENPYXL import Workbook

#未从文件系统生成真的excel文件, just an instance of workbook is instantiated

WB = Workbook ()

Ws=wb.active

Ws.append ([up])

Ws.append ([3,4])

Print Ws.rows #打印所有的行

Print Ws.rows[0][0] #打印第一行第一个 Note that the angle mark starts at 0

Print Ws.rows[0][0].value #打印第一行第一个的值

Print Ws.columns #打印所有的列

Print Ws.columns[1][1]

Print Ws.columns[1][1].value

Wb.save (U "gets the values of rows and columns. xlsx")

default parameters in 7.workbook

FileName (str or File-like object): Is the path to an Excel file or a class file object.
READ_ONLY (BOOL): read-only mode, non-editable files. False by default
Use_iterators (BOOL): Whether to invoke lazy loading. False by default
KEEP_VBA (BOOL): whether to keep the contents of VBA. False by default
Guess_type (BOOL): Gets the type of the contents of the cell and cannot read him from the file. False by default
Date_only (BOOL): Controls whether cells containing formulas have any formulas, or stores the last reading of an Excel table

Python Excel Operations Summary