Python+xlrd+xlwt operation excel
Introduction
Xlrd (read operation), xlwt (write operation)
After the above software is downloaded, it is decompressed separately, and then run in the corresponding directory under the cmd command.
Python setup.py install
If you are prompted that setuptools is missing during the run, run python ez_setup.py first and repeat the above steps
The version of PS:office should not be too high. It is recommended to use version 03 with the suffix of xls.
After the source code bug repair installs the xlwt3, locate the formula.py file and change the
_ slots__ = ["_ _ init__", "_ _ s", "_ _ parser", "_ _ sheet_refs", "_ _ xcall_refs"]
Modify to
_ slots__ = ["_ _ s", "_ _ parser", "_ _ sheet_refs", "_ _ xcall_refs"]
Actual combat
No nonsense, code it up.
Import xlrd
Import xlwt3
Path = 'path of excel-Xiaoqiang Test Brand'
# Open excel
Def open_excel (path):
Try:
Workbook = xlrd.open_workbook (path)
Print ("excel opened successfully")
Return workbook
Except Exception as e:
Print (str (e))
Open_excel (path)
# read the information of excel
Def show_excel (path):
Workbook=xlrd.open_workbook (path)
# get sheet
Sheets = workbook.sheet_names ()
Print ("get the sheet name that exists in excel", sheets)
Sheet = workbook.sheets () [0] # get a sheet by indexing order
Print ("get a sheet object by indexing order", sheet)
Sheet = workbook.sheet_by_index (0) # get a sheet by indexing order
Print ("get a sheet object by indexing order", sheet)
# sheet = workbook.sheet_by_name ('Sheet1') # get by name
# get the number of rows, columns and cells
Print ("get Total number of rows", sheet.nrows) # Total number of rows
Print ("get Total number of columns", sheet.ncols) # Total number of columns
Print ("value of row 1", sheet.row_values (0)) # gets the contents of the entire line
Print ("value of column 2", sheet.col_values (1)) # gets the contents of the entire column
Print ("value of row 2, column 2", sheet.cell_value (1)) # gets the value of the cell
Show_excel (path)
# write data
Def write_excel (path):
Wb=xlwt3.Workbook () # create workbook
Sheet=wb.add_sheet ("xlwt3 data Test Table", cell_overwrite_ok=True) # create worksheet
Value = [[name "," Xiaoqiang python Automation Test practice "," Xiaoqiang performance Test practice "], [" Price "," 52.3 "," 45 "]
For i in range (0Pol 2):
For j in range (0jinlen (value [I])):
Sheet.write (iMagazine, j, value, [I] [j]) # three parameter sub-tables represent rows, columns and values
Wb.save (path)
Print ("data written successfully")
Write_excel (path)
# read data
Def read_excel (path,by_index=0):
Workbook = open_excel (path)
Table = workbook.sheets () [by_index]
Nrows = table.nrows # rows
Ncols = number of table.ncols # columns
Print ("first form of output")
For i in range (0Jing nrows):
Row=table.row (I)
For j in range (0Jing Ncols):
Print (table.cell_value (iMagnej), "", end= "") # plus the final parameter end does not need to wrap
Print ()
Print ("second form of output")
For i in range (0Jing nrows):
Ss = table.row_values (I) # get the data list of row I (the whole row)
Print ('ss=', ss)
For i in range (0, len (ss)):
Print (ss [I])
Print ('-')
Read_excel (path)
