Python Excel Operations Summary

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.