python操作Excel的几种方式
Python对Excel的读写主要有xlrd、xlwt、xlutils、openpyxl、xlsxwriter几种。
1.xlrd主要是用来读取excel文件
1
2
3
4
5
6
7
8
|
import xlrd data = xlrd.open_workbook( 'abcd.xls' ) # 打开xls文件 table = data.sheets()[ 0 ] # 打开第一张表 nrows = table.nrows # 获取表的行数 for i in range (nrows): # 循环逐行打印 if i = = 0 : # 跳过第一行 continue print (table.row_values(i)[: 13 ]) # 取前十三列 |
示例2:
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
#coding=utf-8 ####################################################### #filename:test_xlrd.py #author:defias #date:xxxx-xx-xx #function:读excel文件中的数据 ####################################################### import xlrd #打开一个workbook workbook = xlrd.open_workbook( 'E:\\Code\\Python\\testdata.xls' ) #抓取所有sheet页的名称 worksheets = workbook.sheet_names() print ( 'worksheets is %s' % worksheets) #定位到sheet1 worksheet1 = workbook.sheet_by_name(u 'Sheet1' ) """ #通过索引顺序获取 worksheet1 = workbook.sheets()[0] #或 worksheet1 = workbook.sheet_by_index(0) """ """ #遍历所有sheet对象 for worksheet_name in worksheets: worksheet = workbook.sheet_by_name(worksheet_name) """ #遍历sheet1中所有行row num_rows = worksheet1.nrows for curr_row in range (num_rows): row = worksheet1.row_values(curr_row) print ( 'row%s is %s' % (curr_row,row)) #遍历sheet1中所有列col num_cols = worksheet1.ncols for curr_col in range (num_cols): col = worksheet1.col_values(curr_col) print ( 'col%s is %s' % (curr_col,col)) #遍历sheet1中所有单元格cell for rown in range (num_rows): for coln in range (num_cols): cell = worksheet1.cell_value(rown,coln) print cell """ #其他写法: cell = worksheet1.cell(rown,coln).value print cell #或 cell = worksheet1.row(rown)[coln].value print cell #或 cell = worksheet1.col(coln)[rown].value print cell #获取单元格中值的类型,类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error cell_type = worksheet1.cell_type(rown,coln) print cell_type """ |
更多详细使用:https://www.cnblogs.com/linyfeng/p/7123423.html
2.xlwt主要是用来写excel文件
1
2
3
4
5
|
import xlwt wbk = xlwt.Workbook() sheet = wbk.add_sheet( 'sheet 1' ) sheet.write( 0 , 1 , 'test text' ) #第0行第一列写入内容 wbk.save( 'test.xls' ) |
3.xlutils结合xlrd可以达到修改excel文件目的
1
2
3
4
5
6
7
|
import xlrd from xlutils.copy import copy workbook = xlrd.open_workbook(u '有趣装逼每日数据及趋势.xls' ) workbooknew = copy(workbook) ws = workbooknew.get_sheet( 0 ) ws.write( 3 , 0 , 'changed!' ) workbooknew.save(u '有趣装逼每日数据及趋势copy.xls' ) |
4.openpyxl可以对excel文件进行读写操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
from openpyxl import Workbook from openpyxl import load_workbook from openpyxl.writer.excel import ExcelWriter workbook_ = load_workbook(u "新歌检索失败1477881109469.xlsx" ) sheetnames = workbook_.get_sheet_names() #获得表单名字 print sheetnames sheet = workbook_.get_sheet_by_name(sheetnames[ 0 ]) print sheet.cell(row = 3 ,column = 3 ).value sheet[ 'A1' ] = '47' workbook_.save(u "新歌检索失败1477881109469_new.xlsx" ) wb = Workbook() ws = wb.active ws[ 'A1' ] = 4 wb.save( "新歌检索失败.xlsx" ) |
示例2:
1
2
3
4
5
6
7
|
import openpyxl # 新建文件 workbook = openpyxl.Workbook() # 写入文件 sheet = workbook.activesheet[ 'A1' ] = 'A1' # 保存文件 workbook.save( 'test.xlsx' ) |
5.xlsxwriter可以写excel文件并加上图表
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
|
import xlsxwriter def get_chart(series): chart = workbook.add_chart({ 'type' : 'line' }) for ses in series: name = ses[ "name" ] values = ses[ "values" ] chart.add_series({ 'name' : name, 'categories' : 'A2:A10' , 'values' :values }) chart.set_size({ 'width' : 700 , 'height' : 350 }) return chart if __name__ = = '__main__' : workbook = xlsxwriter.Workbook(u 'H5应用中心关键数据及趋势.xlsx' ) worksheet = workbook.add_worksheet(u "每日PV,UV" ) headings = [ '日期' , '平均值' ] worksheet.write_row( 'A1' , headings) index = 0 for row in range ( 1 , 10 ): for com in [ 0 , 1 ]: worksheet.write(row,com,index) index + = 1 series = [{ "name" : "平均值" , "values" : "B2:B10" }] chart = get_chart(series) chart.set_title ({ 'name' : '每日页面分享数据' }) worksheet.insert_chart( 'H7' , chart) workbook.close() openpyxl |
示例2:
1
2
3
4
5
6
7
8
9
|
import xlsxwriter as xw #新建excel workbook = xw.Workbook( 'myexcel.xlsx' ) #新建工作薄 worksheet = workbook.add_worksheet() #写入数据 worksheet.wirte( 'A1' , 1 ) #关闭保存 workbook.close() |
合并表格实例:
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
38
|
#coding:utf-8 import xlsxwriter import xlrd #新建excel workbook = xlsxwriter.Workbook( '广东.xlsx' ) #新建工作薄 worksheet = workbook.add_worksheet() count = 1 worksheet.write( "A%s" % count, "公司名称" ) worksheet.write( "B%s" % count, "法人" ) worksheet.write( "C%s" % count, "电话" ) worksheet.write( "D%s" % count, "注册资金" ) worksheet.write( "E%s" % count, "注册时间" ) count + = 1 for i in range ( 1 , 153 ): data = xlrd.open_workbook( 'ah (%s).xls' % i) # 打开xls文件 table = data.sheets()[ 0 ] # 打开第一张表 nrows = table.nrows # 获取表的行数 for i in range (nrows): # 循环逐行打印 if i = = 0 : # 跳过第一行 continue # print (table.row_values(i)[:5]) # 取前十三列 print (count,table.row_values(i)[: 5 ][ 0 ]) #写入数据 #设定第一列(A)宽度为20像素 A:E表示从A到E worksheet.set_column( 'A:A' , 30 ) worksheet.set_column( 'B:E' , 20 ) worksheet.write( "A%s" % count,table.row_values(i)[: 5 ][ 0 ]) worksheet.write( "B%s" % count,table.row_values(i)[: 5 ][ 1 ]) worksheet.write( "C%s" % count,table.row_values(i)[: 5 ][ 2 ]) worksheet.write( "D%s" % count,table.row_values(i)[: 5 ][ 3 ]) worksheet.write( "E%s" % count,table.row_values(i)[: 5 ][ 4 ]) count + = 1 #关闭保存 workbook.close() |