python 操作excel文件

xlrd模块：用于读取excel表中的数据

xlwt模块：用于将数据写入excel表中

一、xlrd模块读取excel文件

xlrd模块既可以读取xls文件也可以读取xlsx文件。

获取工作簿对象：book = xlrd.open_workbook('excel文件名称')

获取所有工作表名称：names = book.sheet_names()结果为列表

根据索引获取工作表对象：sheet = book.sheet_by_index(i)

根据名称获取工作表对象：sheet = book.sheet_by_name('工作表名称')

获取工作表行数：rows = sheet.nrows

获取工作表列数：cols = sheet.ncols

获取工作表某一行的内容：row = sheet.row_values(i)结果为列表

获取工作表某一列的内容：col = sheet.col_values(i)结果为列表

获取工作表某一单元格的内容：cell = sheet.cell_value(m, n)

案例：

 

excel表格中是日期格式的单元格，输出的是浮点型，则需要首先判断单元格的类型，python读取excel中单元格的内容返回的有5种类型：

ctype : 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

所以我们只要判断单元格的类型是否等于3，如果等于3，那么就是date类型，然后我们在进行时间格式处理，即：

第一步：

# 判断单元格类型是否为date
sheet.cell(rows,cols).ctype == 3

第二步：（有两种处理方式）

  方式一：使用xlrd的 xldate_as_datetime来处理

if sheet.cell(rows, cols).ctype == 3:  # 判断单元格是否为date格式
    val = sheet.cell_value(rows, cols)
    date_tmp = xlrd.xldate_as_datetime(val, book.datemode).strftime("%Y-%m-%d")

  方式二：使用xlrd的xldate_as_tuple来处理

if sheet.cell(rows, cols).ctype == 3:  # 判断单元格是否为date格式
    val = sheet.cell_value(rows, cols)
    date_value = xlrd.xldate_as_tuple(val, book.datemode)
    date_tmp = date(*date_value[:3]).strftime('%Y-%m-%d')

二、xlwt模块写入excel文件

使用xlwt模块只能写xls文件，不能写xlsx文件（写xlsx程序不会报错，但最后文件无法直接打开，会报错）

创建工作簿：book = xlwt.Workbook()，如果写入中文为乱码，可添加参数encoding = 'utf-8'

创建工作表：sheet = book.add_sheet('Sheet1')

向单元格写入内容：sheet.write(m,n,'内容1')、sheet.write(x,y,'内容2')

保存工作簿：book.save('excel文件名称')，默认保存在py文件相同路径下，如果该路径下有相同文件，会被新创建的文件覆盖，即xlwt不能修改文件。

import xlwt

book = xlwt.Workbook()

sheet = book.add_sheet('Sheet1')

sheet.write(0,0,'hello')

sheet.write(1,0,'你好')

book.save('hello.xls')

逐个单元格写入excel比较麻烦，可以按行或者列写入

import xlwt

proj = ['名称','单价/元','库存/kg']

fruit = ['苹果','梨','香蕉','橘子']

price = [8,3.5,4.5,3.8]

storage = [150,130,100,300]

book = xlwt.Workbook()

sheet = book.add_sheet('Sheet1')
for i in range(0,len(proj)):
    sheet.write(0,i,proj[i]) #按行插入行标题
for i in range(0,len(fruit)):
    sheet.write(i+1,0,fruit[i])#插入第一列水果名称
for i in range(0,len(price)):
    sheet.write(i+1,1,price[i])#插入第二列单价
for i in range(0,len(storage)):
    sheet.write(i+1,2,storage[i])#插入第三列库存
book.save('fruit2.xls')

 

三、openpyxl模块读写excel文件

openpyxl模块可实现对excel文件的读、写、修改，只能处理xlsx文件，不能处理xls文件。

对于openpyxl，行数和列数都是从1开始，单元格的行和列也从1开始。

读操作：

获取工作簿对象：book = openpyxl.load_workbook('excel文件名称')

获取所有工作表名称：names = book.sheetnames

book = openpyxl.load_workbook('fruit2.xlsx')
names = book.sheetnames
print(names)

运行结果：

['Sheet1', 'Sheet']

获取工作表对象：sheet1 = book.worksheets[n], sheet2 = book['工作表名称'], sheet3 = book[book.sheetnames[n]]

sheet1 = book.worksheets[0]
print(sheet1)
sheet2 = book['Sheet1']
print(sheet2)
sheet3 = book[book.sheetnames[0]]
print(sheet3)

运行结果：

<Worksheet "Sheet1">
<Worksheet "Sheet1">
<Worksheet "Sheet1">

获取工作表名称：title = sheet1.title

title = sheet1.title
print(title)

运行结果：

Sheet1

获取工作表行数：rows = sheet1.max_row

rows = sheet1.max_row
print(rows)

运行结果：

5

获取工作表列数：cols = sheet1.max_column

cols = sheet1.max_column
print(cols)

运行结果：

3

获取某一单元格内容：cell = shjeet.cell(1, 2).value

cell = sheet1.cell(1, 2).value
print(cell)

运行结果：

单价/元

获取工作表的行内容：row_values = sheet.rows结果是一个生成器

row_values = sheet1.rows
print(row_values)
for row in list(row_values)[0]:     #获取第一行的值
    print(row.value)

运行结果：

<generator object Worksheet._cells_by_row at 0x10e9d1d60>
名称
单价/元
库存/kg

获取工作表的列内容：col_values = sheet.columns结果是一个生成器

 

col_values = sheet1.columns
print(col_values)
for col in list(col_values)[0]:    #获取第一列的值
    print(col.value)

运行结果：

<generator object Worksheet._cells_by_col at 0x110faad60>
名称
苹果
香蕉
梨
橘子

写操作：

创建工作簿：book = openpyxl.Workbook()如果写入中文为乱码，可以添加 encoding = 'utf-8'

创建工作表：sheet = book.create_sheet('工作表名称',0)0表示创建的工作表在工作簿最前面

向单元格写入内容：sheet.cell(m, n,'内容1')

保存工作簿：book.save('excel文件名称')

import openpyxl

book = openpyxl.Workbook()
sheet = book.create_sheet('Sheet1',0)
proj = ['名称','单价/元','库存/kg']
fruit = ['苹果','香蕉','梨','橘子']
price = [8,3.5,4.5,3.8]
storage = [150,130,300,100]
for i in range(len(proj)):
    sheet.cell(1,i+1,proj[i])
for i in range(len(fruit)):
    sheet.cell(i+2,1,fruit[i])
for i in range(len(price)):
    sheet.cell(i+2,2,price[i])
for i in range(len(storage)):
    sheet.cell(i+2,3,storage[i])

book.save('fruit2.xlsx')

 修改操作：

sheet.insert_rows(m)和sheet.insert_cols(n)分别表示在m行、n列前面插入行、列

sheet.delete_rows(m)和sheet.delete_cols(n)分别表示删除第m行、第n列

rows = sheet1.max_row
sheet1.insert_rows(rows+2)
cherry = ['樱桃',17,80]
for j in cherry:
    sheet1.cell(rows+1,cherry.index(j)+1,j)
book.save('fruit2.xlsx')

修改单元格内容：

sheet.cell(m, n) = '内容1' 或者sheet['B3'] = '内容2'

sheet1.cell(3,3,4)
sheet1['B3'] = 5
book.save('fruit2.xlsx')

在最后追加行：

straberry = ['草莓',20,50]
sheet1.append(straberry)
book.save('fruit2.xlsx')

四、xlsxwriter模块写excel文件

只能操作xlsx，只能写，在excel中插入图片

创建excel文件：workbook = xlsxwriter.Workbook('pngxls.xlsx')

创建工作表：worksheet1 = workbook.add_worksheet('png') 括号内为工作表名称

写入内容：worksheet1.write('A1','hello')第一个参数是插入的起始单元格，第二个参数是写入的内容

插入图片：worksheet1.insert_image('B2','foo.png')第一个参数是插入的起始单元格，第二个参数是图片的绝对路径

关闭对象：workbook.close()

import xlsxwriter

workbook = xlsxwriter.Workbook('pngxls.xlsx')
worksheet1 = workbook.add_worksheet('png')
worksheet1.write('A1','hello')
worksheet1.insert_image('B2','foo.jpeg')
workbook.close()

五、pandas读写excel文件

pandas即可以处理xls文件、也可以处理xlsx文件。

pandas读取的excel结果为一个DataFrame对象，

pd.read_excel('文件名')，默认读取文件的第一个sheet页，并将第一行当作column，添加一行从0开始的整数作为index。

常用参数sheet_name=''：表示读取文件的哪个sheet页，可以为sheet页名称，也可以使用数字，0表示第一个sheet页；None会读取所有有内容的sheet页。结果为一个字典，字典的key为sheet页名称，value为sheet页内容；默认读取第一个sheet页

index_col：将哪一列当作index列，默认添加一列从0开始的整数作为index，通过指定index_col='列名'指定索引列

header：将哪一行当作表头，即DataFrame的columns，默认将sheet页的第一行当作表头，header=1则将第二行当做表头

import pandas as pd
df = pd.read_excel('fruit.xlsx')
print(df)

运行结果：

   名称  单价/元  库存/kg
0  苹果   8.0    150
1  香蕉   5.0      4
2   梨   4.5    300
3  橘子   3.8    100
4  樱桃  17.0     80
5  樱桃  17.0     80
6  樱桃  17.0     80
7  草莓  20.0     50
8  草莓  20.0     50

import pandas as pd
df = pd.read_excel('fruit.xlsx')
print(df.values)

运行结果：

[['苹果' 8.0 150]
 ['香蕉' 5.0 4]
 ['梨' 4.5 300]
 ['橘子' 3.8 100]
 ['樱桃' 17.0 80]
 ['樱桃' 17.0 80]
 ['樱桃' 17.0 80]
 ['草莓' 20.0 50]
 ['草莓' 20.0 50]]

import pandas as pd
df = pd.read_excel('fruit2.xlsx')
print(df['名称'])    #可通过df[列名]获取某一列的内容

运行结果：

0    苹果
1    香蕉
2     梨
3    橘子
4    樱桃
5    樱桃
6    樱桃
7    草莓
8    草莓
Name: 名称, dtype: object

import pandas as pd
df = pd.read_excel('fruit2.xlsx')
print(df.loc[1])   #pandas自动生成的行索引从0开始，可通过pd.loc[n]获取索引值为n的行的内容

运行结果：

名称        香蕉
单价/元     5.0
库存/kg      4
Name: 1, dtype: object

import pandas as pd
df = pd.read_excel('fruit2.xlsx')
print(df.loc[2,'单价/元'])   #通过pd.loc[行索引,列索引]可获取具体单元格的内容

运行结果：

4.5

可通过df.set_index(列名)自行设定索引，如下示例

import pandas as pd
df = pd.read_excel('fruit.xlsx')  #也可在读取时直接设置index_col='名称'
df = df.set_index('名称')   #设置名称列为行索引
print(df)
print(df.values)
print(df['库存/kg'])
print(df.loc['草莓'])
print(df.loc['香蕉','单价/元'])

将DataFrame格式的数据写入excel

第一步：writer = pd.ExcelWriter('文件路径和名称')，文件名称必须指定，默认保存在当前文件的相同目录，文件名称需包含后缀名且只能为xls或xlsx

第二步：df.to_excel(writer,'sheet页名称')，sheet页参数可不指定，默认为Sheet1

第三步：writer.save()

df = pd.DataFrame(data=[['dog','a','b'],['cat','b','c'],['dog','c','a']],columns=(['animal','column1','column2']))
writer = pd.ExcelWriter('output.xlsx')
df.to_excel(writer,'sheet页名称', startcol=0, index=False)
writer.save()