Python operations Excel

Python operations Excel requires the use of XLRD (for reading Excel), XLWT (for writing Excel), xlutils (used to modify Excel), three modules, using PIP to install

1. Read Excel

Wb=xlrd.open_workbook (' abc.xlsx ') #打开excel, this Excel must exist, there will be an error

#获取所有sheet页的名字

Wb.sheet_names ()

#获取sheet页

Mode 1: Get through Index

Wb.sheet_by_index (0) #一般都通过index获取 because the name varies by table and can vary

Mode 2: Get through name

Wb.sheet_by_name (' name ')

# Gets the value to a cell
Cell_a2=sheet.cell (0,1). Value
Print (CELL_A2)
#获取sheet页的行数和列数
Print (sheet.nrows)
Print (Sheet.ncols)
#按行取值, take a row
For rownum in range (sheet.nrows):
    Print (Sheet.row_values (rownum))
#按列取值, one column, one column
For Colnum in range (Sheet.ncols):
    Print (Sheet.col_values (colnum))
2. Write Excel

#新建一个表格
WBK=XLWT. Workbook ()
#新建一个sheet页, and named
Sheet=wbk.add_sheet (' Sheet1 ')
title=[' name ', ' age ', ' gender '
content=[
    [' Zhu ', ', ' NV '],
    [' Yue ', ' na '],
    [' Juan ',%, ' NV ']
]
#写title
For I in range (len (title)):
    Sheet.write (0,i,title[i])
#循环写入每行
For I in range (len (content)):
    For j in Range (Len (Content[i])):
        Sheet.write (I+1,j,content[i][j])
#保存文件, the suffix is xls
Wbk.save (' Stu1.xls ')
3. Modify the form

From XLRD import Open_workbook
From xlutils.copy Import copy
#打开一个表格
Wb=open_workbook (' Stu1.xls ')
#复制一个表格
Wbc=copy (WB)
#获取到新表格里面的sheet页
Wbs=wbc.get_sheet (0)
#修改表格内的值
Wbs.write (1,0, ' new ')
#保存表格
Wbc.save (' Stunew.xls ')

Python operations Excel