python操作Excel的几种方式

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