Excel operations in Python

I. Application of excel in Python

Save test data

Sometimes large quantities of data, we need to be stored in the database, in the test can be used . The test is read from the database. This is very important!

Save test results

two. The three main objects in Excel:

• Workbook
• Sheet
• Cell

In Excel, the primary action is to read, write, and modify data in sheet

Operation Flow:

1. Open an Excel file, or create an Excel file that generates a workbook (workbook) object

2. Get a form (sheet) object

3. Read, write, and modify data in cells in a Form object

three. Excel Library

There are several modules in Python that are related to excel operations

XLRD Library: Read data from Excel, support XLS, xlsx

XLWT Library: Modify the Excel operation. Modifications to the XLSX format are not supported

Xlutils Library: In XLWT and xlrd, modify an existing file

Official statement:

OPENPYXL is a Python library to read/write Excel XLSX/XLSM/XLTX/XLTM files

Website: http://www.python-excel.org/

four. openpyxl--Common Operation

From OPENPYXL import Load_workbook

#打开一个已存在的excel文件

WB = Load_workbook ("sample.xlsx")

From OPENPYXL import Workbook

#创建一个新的excel文件

WB = Workbook ("hello.xlsx")

#获取sheet对象, Get_sheet_by_name is the old method, the new method is Wb[sheetname]

SH = wb.get_sheet_by_name ("Sheet1")

#创建一个新的sheet

Sh_new = Wb.create_sheet ("New sheet")

#获取指定单元格中的值

CL = Sh.cell (Row=1, column=1). Value

#修改指定单元格的值

Sh.cell (Row=1, column=1). Value = "Hello"

#获取当前sheet的数据总行数

rows = Sh.max_row

#获取当前sheet的数据总列数

columns = Sh.max_column

#保存当前workbook中所有的修改

Wb.save ("Sample.xlsx")

Note: The starting subscript for columns and rows in OPENPYXL is 1

Five. Example

#Lead Storage fromOpenpyxlImportLoad_workbook#load an Excel file into the workbookWB = Load_workbook ("testdata.xlsx")#gets the specified formSH = wb["Sheet1"]#get data in formName = Sh.cell (row=2, Column=1). ValuePrint(name)#modify data in a form--modify the data, and note that you must close the Excel file before writingSh.cell (row=2, column=3). Value = 18#save changes to data--save dataWb.save ("testdata.xlsx")#get total number of rows and columnsPrint(Sh.max_row)Print(Sh.max_column)Print(Sh.cell (row=3, column=3). Value) Sh.cell (Row=3, column=3). Value = 25Print(Sh.cell (row=3, column=3))#Read all data, read by row forIndexinchRange (1, Sh.max_row + 1):    Print("Line number:", index) forSub_iinchRange (1, Sh.max_column + 1):        Print("column number:", Sub_i,"content:", Sh.cell (Row=index, column=sub_i). Value) Results: Xiao Zhai3318<cell'Sheet1'. C3>Line number:1column number:1Content: Name column number:2content: Sex column number:3content: Age line number:2column number:1content: Small Sanlie No.:2Content: Male column number:3 Content: 18Line number:3column number:1Content: Biei sub-column number:2content: Female column number:3 Content: 25

Attached Excel content:

Excel operations in Python