关于Excel操作

一、基础的读取操作

读取表格中的学生成绩

1、获取BOOK对象

xlrd.open_workbook('25.xls')

2、获取Sheet页内容

workbook.sheet_by_name("Sheet1")

3、获取行数

sheet.nrows

便利所有行内容

import xlrd
​
​
def read_input_ex():
 workbook = xlrd.open_workbook('25.xls')  # (1)取得excel book 
 sheet = workbook.sheet_by_name("Sheet1")  # (2)取得sheet 
 rows = sheet.nrows  # (3)获得总行数
 title = sheet.row_values(0)
 for r in range(1, rows):
 row = sheet.row_values(r)  # (4)获取行数据
 name = title[0], row[0]
 chinese = title[1], row[1]
 maths = title[2], row[2]
 english = title[3], row[3]
 physics = title[4], row[4]
 chemistry = title[5], row[5]
 biological = title[6], row[6]
 print(name, chinese, maths, english, physics, chemistry, biological)
​
​
if __name__ == '__main__':
 read_input_ex()

遍历标题读取内容

def read_input_ex():
 workbook = xlrd.open_workbook('25.xls')  # (1)取得excel book对象
 sheet = workbook.sheet_by_name("Sheet1")  # (2)取得sheet对象
 rows = sheet.nrows  # (3)获得总行数
 title = sheet.row_values(0)
 for r in range(1, rows):
 row = sheet.row_values(r)  # (4)获取行数据
 i = -1
 value = ''
 for t in title:
 i = i + 1
 value = value,t , row[i]
​
 print(value)

字符串拼接形式

def read_input_ex():
 workbook = xlrd.open_workbook('25.xls')  # (1)取得excel book对象
 sheet = workbook.sheet_by_name("Sheet1")  # (2)取得sheet对象
 rows = sheet.nrows  # (3)获得总行数
 title = sheet.row_values(0)
 for r in range(1, rows):
 row = sheet.row_values(r)  # (4)获取行数据
 i = -1
 value = ''
 for t in title:
 i = i + 1
 value = value   + "  " +  t   + "：" +  str(row[i])
 print(value)

最完美的拼接内容

def read_input_ex():
 workbook = xlrd.open_workbook('25.xls')  # (1)取得excel book对象
 sheet = workbook.sheet_by_name("Sheet1")  # (2)取得sheet对象
 rows = sheet.nrows  # (3)获得总行数
 title = sheet.row_values(0)
 for r in range(1, rows):
 edata = []
 row = sheet.row_values(r)  # (4)获取行数据
 i = -1
 for t in title:
 i = i + 1
 edata.append(t)
 edata.append(row[i])
 print(edata)

1580031061382.png 1580031112772.png

读取其他的Excel也是类似的操作