One, Python operation Excel,python operation Excel uses XLRD, XLWT and Xlutils module, XLRD module is read Excel, XLWT module is write Excel, Xlutils is used to modify Excel. These modules are installed using PIP, the following are the use of these modules.
Second, xlrd module, XLRD module for reading Excel, the specific usage is as follows:
- Import xlrd
- #打开excel
- Wb=xlrd.open_workbook (' abc.xlsx ') #打开的这个excel必须存在, or will error
- Print (Wb.sheet_names ()) #获取所有sheet页的名字
- Sheet=wb.sheet_by_name (' ABC2 ') #根据sheet页的名字获取sheet页
- Sheet = wb.sheet_by_index (0) #根据sheet页的索引获取sheet页
- #获取sheet页的行数和列数
- Print (sheet.nrows)
- Print (Sheet.ncols)
- #打印每行信息
- For rownum in range (sheet.nrows): #循环取每行的数据
- Print (Sheet.row_values (rownum)) #取每行的数据 Colnum
- #按照索引打印对应单元格内容
- Cell_a2=sheet.cell (0,1). value# gets the value of the specified cell, the first value is a column, the second value is a row
- Print (CELL_A2)
Three, XLWT module, XLWT module used to write Excel, write a new Excel
- Import XLWT
- title = [' Name ', ' age ', ' gender ', ' score ']
- Stus = [[' Mary ', 20, ' female ', 89.9],[' Mary ', 20, ' female ', 89.9],[' Mary ', 20, ' female ', 89.9],[' Mary ', 20, ' female ', 89.9]]
- #新建一个excel对象
- WBK = XLWT. Workbook ()
- Sheet page for #添加一个名为 curriculum
- Sheet = wbk.add_sheet (' stu ')
- For I in range (len title): #写入表头
- Sheet.write (0,i,title[i]) #写入每行, the first value is a row, the second value is a column, and the third is the value written
- For I in range (len (stus)):
- For j in Range (4):
- Sheet.write (I+1,j,stus[i][j]) #循环写入每行数据
- #保存数据到 ' Test.xls ' file
- Wbk.save (' Szz.xls ') #保存excel必须使用后缀名是. xls, not can be. xlsx
Four, xlutils module, xlutils module to modify the contents of Excel, can not directly modify the original Excel content, you must first copy a new Excel, and then the new Excel to modify the following usage:
- From XLRD import open_workbook# importing the Excel module in the XLRD module
- Copy Excel module for import xlutils module from xlutils.copy import copy#
- RB = Open_workbook (' Szz.xls ')
- Sheet obtained by #通过sheet_by_index ()
- rs = rb.sheet_by_index (0)
- #复制一个excel
- WB = Copy (RB)
- #通过获取到新的excel里面的sheet页
- WS = Wb.get_sheet (0)
- Ws.write (1, 0, ' Lily ') #写入excel, the first value is a row, the second value is a column
- Wb.save (' Szz_new.xls ') #保存新的excel, save Excel must use the suffix name of. xls, not can be. xlsx
Python Basics (vi) Python operations Excel
