python读写Excel的方式有三种:
用xlrd和xlwt进行Excel读写;
用openpyxl进行Excel读写;
用pandas进行Excel读写;
首先新建了一个xlsx文件,第一个工作表sheet1区域“A1:F5”的内容如下,用于测试读Excel的代码:
用xlrd和xlwt进行excel读写(xlwt不支持xlsx)
首先是安装第三方模块xlrd和xlwt,在cmd中输入”pip install xlrd”和”pip install xlwt”
xlrd读Excel
import xlrd
book = xlrd.open_workbook('xlrdReadDemo.xlsx')
sheet1 = book.sheets()[0]
nrows = sheet1.nrows
print('表格总行数',nrows)
ncols = sheet1.ncols
print('表格总列数',ncols)
row1_values = sheet1.row_values(0)
print('第1行值',row1_values)
col1_values = sheet1.col_values(0)
print('第1列值',col1_values)
cell_1_1 = sheet1.cell(0,0).value
print('第1行第1列的单元格的值:',cell_1_1)
xlwt写Excel
import xlwt # 不支持excel2007的xlsx格式
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('test')
worksheet.write(0,0,'A1data')
workbook.save('xlwtWriteDemo.xls')
利用openpyxl读写excel(只能是xlsx类型的excel)
首先是安装第三方模块openpyxl,在cmd中输入”pip install openpyxl”
openpyxl读Excel
import openpyxl
workbook = openpyxl.load_workbook('openpyxlReadDemo.xlsx')
worksheet = workbook.get_sheet_by_name('Sheet1')
row1=[item.value for item in list(worksheet.rows)[0]]
print('第1行值',row1)
col1=[item.value for item in list(worksheet.columns)[0]]
print('第1行值',col1)
cell_1_1=worksheet.cell(row=0,column=0).value
print('第1行第1列值',cell_1_1)
max_row=worksheet.max_row
print('最大行',max_row)
openpyxl写Excel
import openpyxl
workbook = openpyxl.Workbook()
sheet=workbook.active
sheet['A1']='a,b'
workbook.save('openpyxlWriteDemo.xlsx')
用pandas读取Excel
首先是安装第三方模块pandas,在cmd中输入”pip install pandas”,如果用pip install pandas安装后运行出错,可以考虑安装以前的版本:pip install pandas==0.22
pandas是一个数据处理的包,本身提供了许多读取文件的函数,像read_csv(读取csv文件),read_excel(读取excel文件)等,只需一行代码就能实现文件的读取
pandas读Excel
import pandas as pd
prd = pd.read_excel(r'pandasReadDemo.xlsx',sheetname=0)
print(prd.head())
pandas写Excel
from pandas import DataFrame
data={
'name':['张三','李四','王五'],
'age':[11,12,13]
'sex':'男','女','男']
}
df=DataFrame(data)
df.to_excel('pandasWriteDemo.xlsx")