6 Python 基础知识

Excel表格的读操作

Excel表格的读操作

#Python读取Excel文件

#导入xlrd包

import xlrd

#打开Excel表格

mybook = xlrd.open_workbook("kang.xlsx")

#获取工作薄中所有sheet表的名称,以列表格式输出

tables1_name = mybook.sheet_names()

print(tables1_name)

#获取工作薄中所有sheet表的名称,单独输出

for k in range(0,3):

tables2 = mybook.sheet_names()[k] #table1 = table[i]

print(tables2+" ",end = "")

print()

#获取工作薄中第一个sheet表的名称

table1_name = mybook.sheet_names()[0] #table1 = tables1[0]或者tables2[0]

print(table1_name)

#获取第一个sheet对象

table1_content = mybook.sheets()[0]

print(table1_content)

#获取工作薄中所有sheet对象

for a in range(0,3):

table_all_content = mybook.sheets()[a]

print(table_all_content)

#获取第一个sheet的行数与列数

nrows = table1_content.nrows

ncols = table1_content.ncols

print(nrows)

print(ncols)

#获取所有sheet的行数与列数

for a in range(0,3):

table_all_content = mybook.sheets()[a]

print("第"+str(a+1)+"个sheet表的"+"行数为："+str(table_all_content.nrows)+" ",end = "")

print("列数为: "+str(table_all_content.ncols)+"; ",end = "")

print()

#获取第一行的值

rows1_values = table1_content.row_values(0)

print(rows1_values)

#h获取所有行的值

for rx in range(0,table1_content.nrows):

print(table1_content.row_values(rx))

#获取第一列的值

col1_values = table1_content.col_values(0)

print(col1_values)

#h获取所有列的值

for cx in range(table1_content.ncols):

print(table1_content.col_values(cx))

#获取某一个单元格中的值

cell_one_value = table1_content.cell_value(rowx = 3,colx = 4)

print(cell_one_value)

print()

print("利用Python运算数据如下：")

print()

#遍历Excel表格

for i in range(0,table1_content.nrows):

for j in range(0,table1_content.ncols):

cell_all_values = table1_content.cell_value(rowx = i,colx = j)

#获取全部值

#print(str(cell_all_values)+",... ",end = "")

#关于excel的相关计算可以写于此部分

q = table1_content.cell_value(rowx = i,colx = j)

w = table1_content.cell_value(rowx = i,colx = i)

e = table1_content.cell_value(rowx = j,colx = j)

#预期计算公式

p = q/(w**0.5 * e**0.5)

r = 1-p

print(str(r)+",",end = "")

print()