python读写Excel数据的几种方式

xlrd/xlwt、 openpyxl 、pandas 几个模块

都需要实现安装模块： pip install 模块名  

pip list可查看已经安装了哪些模块。

学习链接：https://geek-docs.com/python/python-tutorial/python-pandas.html

 

import xlrd
import xlwt


data = xlrd.open_workbook(r'C:\Users\wb.liangshuiqing\Desktop\autodata.xls')


sheet = data.sheets()[0]

#获取行数、列数
nrows = sheet.nrows
ncols = sheet.ncols

print("行数为：",nrows)
print("列数为：",ncols)


#获取行、列
getvalue = sheet.row_values(2)
getcol = sheet.col_values(1)

print("第3行数据为：",getvalue)
print("第2列数据为：",getcol)


#获取指定单元格
A1 = sheet.cell(0,0).value
B1 = sheet.cell(0,1).value
A2 = sheet.cell(1,0).value
B2 = sheet.cell(1,1).value
A3 = sheet.cell(2,0).value
B3 = sheet.cell(2,1).value

print("以下输出Excel内容：")
print("A1单元格值：%s, B1单元格值为：%s"% (A1,B1))
print("A1单元格值：{0}, B1单元格值为：{1}".format(A1,B1))
print(A1,B1)
print(A2,B2)
print(A3,B3)

#=============================================
#以下为xlwt write
#=============================================
'''
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('test')
worksheet.write(0,1,'datatest')
workbook.save('autodata1.xls')
'''

#=============================================
#以下为 openpyxl
#=============================================

import openpyxl
workbook = openpyxl.load_workbook('openpyxldata.xlsx')
worksheet = workbook.worksheets[0]
row3 = [item.value for item in list(worksheet.rows)[2]]
print('第3行值',row3)
col3=[item.value for item in list(worksheet.columns)[2]]
print('第3列值',col3)


cell_2_3=worksheet.cell(row=2,column=3).value
print('第2行第3列值',cell_2_3)

#获取行、列数
rows = worksheet.max_row
cols = worksheet.max_column

print(rows)
print(cols)