Python 操作Excel文件 (十五)

Four兄 2019-08-30

展开全文

(一)简介

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

安装xlrd和xlwt两个库

pip install xlrd和pip install xlwt

(二)使用介绍

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

book=xlrd.open_workbook('test.xlsx')1

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.ncols1
2
3
4

4.获取整行和整列的值

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

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

for i in range(nrows ):  print（table.row_values(i)）1
2
3

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

方式一：cell_A1 = table.cell(0,0).valuecell_C4 = table.cell(2,3).value方式二：cell_A1 = table.row(0)[0].valuecell_A2 = table.col(1)[0].value

7.向工作表中写入数据

row = 0col = 0# 类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorctype = 1 value = '单元格的值'xf = 0 # 扩展的格式化table.put_cell(row, col, ctype, value, xf)table.cell(0,0)  #单元格的值'table.cell(0,0).value #单元格的值'1
2
3
4
5
6
7
8

(三)代码演示

如图：
这里写图片描述

1.打印所有的工作表

import xlrd#1.解析Excel文件返回一个Book对象book=xlrd.open_workbook('test.xlsx')#2.调用sheets()方法，返回工作薄中的所有工作表for sheet in book.sheets():    #3.遍历打印每张工作表的名字    print(sheet.name)

结果演示：员工信息表材料信息表工资信息表1
2
3
4

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

import xlrdbook=xlrd.open_workbook('test.xlsx')#1.通过列表的索引访问,获取工作表对象table01=book.sheets()[0]#2.通过工作表的排列顺序table02=book.sheet_by_index(0)#3.通过工作表的名字获取table03=book.sheet_by_name('员工信息表')

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

import xlrdbook=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))1
2
3
4
5
6
7
8
9
10
11

4.获取整行和整列的值

 import xlrdxlrd.book.Book#1.返回一个Book对象book=xlrd.open_workbook('test.xlsx')table=book.sheet_by_name('员工信息表')#2.打印表格中的每一行数据for i in range(table.nrows):    print(table.row_values(i))

结果演示：['姓名', '年龄', '职业', '工资']['张三', 23.0, '销售', 5000.0]['李四', 32.0, '产品经理', 15000.0]['王五', 26.0, '滴滴司机', 8000.0]1
2
3
4
5

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

import xlrdxlrd.book.Book#1.返回一个Book对象book=xlrd.open_workbook('test.xlsx')table=book.sheet_by_name('员工信息表')for i in range(table.nrows):    #2.获取某一列的所有行数据    print(table.cell(i,3).value)

结果演示：工资5000.015000.08000.01
2
3
4
5

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

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

代码：

import xlrdfrom xlutils.copy import copy#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对象

参考：http://xlsxwriter./workbook.html

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()

参考：http://xlsxwriter./worksheet.html

2.创建Excel并添加数据

# encoding:utf-8# author:Kaina# date:2018/2/6import xlsxwriter#1.创建工作簿对象workbook = xlsxwriter.Workbook('hello.xlsx')#2.创建工作表对象worksheet = workbook.add_worksheet()#3.向单元格中添加数据worksheet.write('A1', '李四')worksheet.write(0,1, '18')#4.关闭工作簿workbook.close()1
2
3
4
5
6
7
8
9
10
11
12
13

结果演示
这里写图片描述

3.向Excel中添加样式

from xlsxwriter import Workbookfilename='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 import datetimefrom xlsxwriter import Workbookfilename='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)1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

结果演示
这里写图片描述

5.xlsxWriter模块生成报表

# encoding:utf-8# author:kaina# date:2018/2/6from xlsxwriter import Workbookworkbook=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()