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 in range(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.打印所有的工作表
import xlrd #1.解析Excel文件返回一个Book对象 book=xlrd.open_workbook("test.xlsx") #2.调用sheets()方法,返回工作薄中的所有工作表 for sheet in book.sheets(): #3.遍历打印每张工作表的名字 print(sheet.name)
结果演示:
员工信息表
材料信息表
工资信息表
2.获取工作表的三种方式
import xlrd 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.获取工作表的总行数和总列数
import xlrd 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.获取整行和整列的值
import xlrd xlrd.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]
5.获取工作表中具体单元个数据
import xlrd xlrd.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.0
15000.0
8000.0
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 xlrd from 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.readthedocs.io/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.readthedocs.io/worksheet.html
2.创建Excel并添加数据
# encoding:utf-8 # author:Kaina # date:2018/2/6 import xlsxwriter #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 import Workbook 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 = 1 col = 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 datetime from xlsxwriter import Workbook 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 = 1 col = 0 for 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 += 1 worksheet.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 import Workbook 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()
结果演示: