python怎么操作excel文件_Python 操作Excel文件

(一)简介

python操作excel主要用到xlrd和xlwt这两个库，即xlrd是读excel，xlwt是写excel的库。可从这里下载https://pypi.python.org/pypi。下面分别记录python读和写excel。

安装xlrd和xlwt两个库

pip3 install xlrd和pip3 install xlwt

(二)使用介绍

1.解析Excel文件，获取Book对象

book=xlrd.open_workbook("test.xlsx") #test.xlsx 你要打开的文件

2.从工作薄中获取一张工作表的三种方法

table = book.sheets()[0] #通过索引顺序获取

table= book.sheet_by_index(0) #通过索引顺序获取

table= book.sheet_by_name(u'Sheet1')#通过名称获取

3.获取行总数和列总数

#1.获取行总数

rows =table.nrows#2.获取列总数

cols = table.ncols

4.获取整行和整列的值

#1.获取具体行，也可以获取所有行数据。

table.row_values(i)#2.获取具体列，也可以获取所有列数据。

table.col_values(i)

5.循环遍历获取行和列数据

for i inrange(nrows ):print(table.row_values(i))

6.获取工作表中具体单元个数据

方式一：

cell_A1=table.cell(0,0).value

cell_C4= table.cell(2,3).value

方式二：

cell_A1=table.row(0)[0].value

cell_A2= table.col(1)[0].value

7.向工作表中写入数据

row =0

col=0#类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error

ctype = 1 value = '单元格的值'xf= 0 #扩展的格式化

table.put_cell(row, col, ctype, value, xf)

table.cell(0,0)#单元格的值'

table.cell(0,0).value #单元格的值'

(三)代码演示

如图：

1.打印所有的工作表

importxlrd#1.解析Excel文件返回一个Book对象

book=xlrd.open_workbook("test.xlsx")#2.调用sheets()方法，返回工作薄中的所有工作表

for sheet inbook.sheets():#3.遍历打印每张工作表的名字

print(sheet.name)

结果演示：

员工信息表

材料信息表

工资信息表

2.获取工作表的三种方式

importxlrd

book=xlrd.open_workbook("test.xlsx")#1.通过列表的索引访问,获取工作表对象

table01=book.sheets()[0]#2.通过工作表的排列顺序

table02=book.sheet_by_index(0)#3.通过工作表的名字获取

table03=book.sheet_by_name('员工信息表')

3.获取工作表的总行数和总列数

importxlrd

book=xlrd.open_workbook("test.xlsx")#1.通过列表的索引访问,获取工作表对象

table01=book.sheets()[0]#2.通过工作表的排列顺序

table02=book.sheet_by_index(0)#3.通过工作表的名字获取

table03=book.sheet_by_name('员工信息表')#4.获取工作表的总行数和总列数

print("员工信息表总行数："+str(table03.nrows))print("员工信息表总列数："+str(table03.ncols))

4.获取整行和整列的值

importxlrd

xlrd.book.Book#1.返回一个Book对象

book=xlrd.open_workbook("test.xlsx")

table=book.sheet_by_name('员工信息表')#2.打印表格中的每一行数据

for i inrange(table.nrows):print(table.row_values(i))

结果演示：

['姓名', '年龄', '职业', '工资']

['张三', 23.0, '销售', 5000.0]

['李四', 32.0, '产品经理', 15000.0]

['王五', 26.0, '滴滴司机', 8000.0]

5.获取工作表中具体单元个数据

importxlrd

xlrd.book.Book#1.返回一个Book对象

book=xlrd.open_workbook("test.xlsx")

table=book.sheet_by_name('员工信息表')for i inrange(table.nrows):#2.获取某一列的所有行数据

print(table.cell(i,3).value)

结果演示：

工资

5000.0

15000.0

8000.0

6.向已经存在的excel文件中追加数据

1.已经存在的xls文件中，写入新的行，新的数据，对应的逻辑为：打开(xlrd) 拷贝(xlutils) 添加(xlwt).2.用xlrd.open_workbook打开已有的xls文件，注意添加参数formatting_info=True，得以保存之前数据的格式。3.然后用from xlutils.copy importcopy，之后的copy去从打开的xlrd的Book变量中，拷贝出一份，成为新的xlwt的Workbook变量。4.然后对于xlwt的Workbook变量，就是正常的： 通过get_sheet去获得对应的sheet，拿到sheet变量后，就可以往sheet中，写入新的数据。5.写完新数据后，最终save保存。

代码：

importxlrdfrom xlutils.copy importcopy#1.打开Excel创建xlrd.book.Book对象

book=xlrd.open_workbook("test.xls")#2.复制Excel,返回Workbook对象

workbook=copy(book)#3.获取工作表对象

sheet=workbook.get_sheet(0)#4.在指定单元格写入数据

sheet.write(4,0,'Kaina')#5.保存文件

workbook.save("test.xls")

结果如图所示：

(四)使用XlsxWriter模块向Excel写入数据

1.按照XlsxWriter模块： pip install xlsxWriter

WorkBook的一些常用方法

方法说明

Workbook()

创建workbook对象

add_worksheet()

创建worksheet对象

add_chart()

创建报表对象

close()

关闭workbook对象

WorkSheet的一些常用方法

方法说明

add_format()

给单元格添加样式

write(row, column, token, [format])

向工作表中写入数据

write_number()

写入数字

write_blank()

写入空字符

write_formula()

写入公式

write_datetime()

写入日期

write_boolean()

写入boolean类型数据

write_url()

写入url

write_row()

写入的数据可以是列表和元组

write_column()

向worksheet中写入一列数据

insert_image()

向worksheet中插入图片

insert_chart()

向worksheet中插入报表

insert_textbox()

插入文本

write_comment()

添加注释

get_name()

获取工作表名称

activate()

等价add_worksheet()

2.创建Excel并添加数据

#encoding:utf-8#author:Kaina#date:2018/2/6

importxlsxwriter#1.创建工作簿对象

workbook = xlsxwriter.Workbook('hello.xlsx')#2.创建工作表对象

worksheet =workbook.add_worksheet()#3.向单元格中添加数据

worksheet.write('A1', '李四')

worksheet.write(0,1, '18')#4.关闭工作簿

workbook.close()

结果演示

3.向Excel中添加样式

from xlsxwriter importWorkbook

filename="test02.xlsx"workbook=Workbook(filename)

worksheet=workbook.add_worksheet()#1.添加一个粗体格式来突出显示单元格。

bold=workbook.add_format({"bold":True})#2.为单元格添加数字格式。

money = workbook.add_format({'num_format': '$#,##0'})#3.写标题栏信息

worksheet.write('A1', '项目', bold)

worksheet.write('B1', '消费', bold)#4.向worksheet写入一些数据

expenses =(

['租房', 1000],

['天然气', 100],

['用餐', 300],

['健身房', 50],

)#5.从标题下面的第一个单元格开始。

row = 1col=0#6.对数据进行迭代，一行一行写入数据

for item, cost in(expenses):

worksheet.write(row,col,item)

worksheet.write(row,col+1,cost,money)

row+= 1

#7.使用求和公式求和

worksheet.write(row,0,'总消费',bold)

worksheet.write(row,1,'=SUM(B2:B5)',money)

workbook.close()

结果演示

4.将不同类型的数据写入XLSX文件。

from datetime importdatetimefrom xlsxwriter importWorkbook

filename="test03.xlsx"workbook=Workbook(filename)

worksheet=workbook.add_worksheet()#1.添加一个粗体格式来突出显示单元格。

bold=workbook.add_format({"bold":1})#2.添加钱数据的格式

money_format = workbook.add_format({'num_format': '￥#,##0'})#3.向单元格中添加日期格式数据

date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})#4.调整列宽

worksheet.set_column(1, 1, 15)#5.添加标题信息

worksheet.write('A1', '项目', bold)

worksheet.write('B1', '日期', bold)

worksheet.write('C1', '消费', bold)#6向表中添加一些数据

expenses =(

['租房','2013-01-13',1000],

['天然气','2013-01-14',100],

['用餐', '2013-01-16', 300],

['健身房', '2013-01-20',50],

)#7.从标题下面的第一个单元格开始。

row = 1col=0for item, date_str, cost in(expenses):#将日期字符串转换为datetime对象。

date = datetime.strptime(date_str, "%Y-%m-%d")

worksheet.write_string(row, col, item)

worksheet.write_datetime(row, col+ 1, date, date_format)

worksheet.write_number(row, col+ 2, cost, money_format)

row+= 1worksheet.write(row, 0,'总消费',bold)

worksheet.write(row,2, '=SUM(C2:C5)', money_format)

结果演示

5.xlsxWriter模块生成报表

#encoding:utf-8#author:kaina#date:2018/2/6

from xlsxwriter importWorkbook

workbook=Workbook("chart.xlsx")

worksheet=workbook.add_worksheet()#1.创建报表对象

chart=workbook.add_chart({"type":"column"})#2.写一些数据添加到图表上

data =[

[1, 2, 3, 4, 5],

[2, 4, 6, 8, 10],

[3, 6, 9, 12, 15],

]#3.向每一列写入数据

worksheet.write_column('A1', data[0])

worksheet.write_column('B1', data[1])

worksheet.write_column('C1', data[2])#4.配置报表

chart.add_series({'values': '=Sheet1!$A$1:$A$5'})

chart.add_series({'values': '=Sheet1!$B$1:$B$5'})

chart.add_series({'values': '=Sheet1!$C$1:$C$5'})#5.在工作表中插入报表

worksheet.insert_chart('A7', chart)

workbook.close()

结果演示：