python操作Excel的5种常用方式

python操作Excel的5种常用方式

Python对Excel的读写主要有xlrd、xlwt、xlutils、openpyxl、xlsxwriter几种。

1.xlrd主要是用来读取excel文件

import xlrddata = 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：

########################################################filename:test_xlrd.py#author:defias#date:xxxx-xx-xx#function：读excel文件中的数据#######################################################import xlrd#打开一个workbookworkbook = xlrd.open_workbook('E:\\Code\\Python\\testdata.xls')#抓取所有sheet页的名称worksheets = workbook.sheet_names()print('worksheets is %s' %worksheets)#定位到sheet1worksheet1 = 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中所有行rownum_rows = worksheet1.nrowsfor curr_row in range(num_rows):row = worksheet1.row_values(curr_row)print('row%s is %s' %(curr_row,row))#遍历sheet1中所有列colnum_cols = worksheet1.ncolsfor curr_col in range(num_cols):col = worksheet1.col_values(curr_col)print('col%s is %s' %(curr_col,col))#遍历sheet1中所有单元格cellfor rown in range(num_rows):for coln in range(num_cols):cell = worksheet1.cell_value(rown,coln)print cell"""#其他写法：cell = worksheet1.cell(rown,coln).valueprint cell#或cell = worksheet1.row(rown)[coln].valueprint cell#或cell = worksheet1.col(coln)[rown].valueprint cell#获取单元格中值的类型，类型 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 errorcell_type = worksheet1.cell_type(rown,coln)print cell_type"""

2.xlwt主要是用来写excel文件

import xlwtwbk = xlwt.Workbook()sheet = wbk.add_sheet('sheet 1')sheet.write(0,1,'test text')#第0行第一列写入内容wbk.save('test.xls')

3.xlutils结合xlrd可以达到修改excel文件目的

import xlrdfrom xlutils.copy import copyworkbook = 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文件进行读写操作

from openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.writer.excel import ExcelWriter workbook_ = load_workbook(u"新歌检索失败1477881109469.xlsx")sheetnames =workbook_.get_sheet_names() #获得表单名字print sheetnamessheet = workbook_.get_sheet_by_name(sheetnames[0])print sheet.cell(row=3,column=3).valuesheet['A1'] = '47'workbook_.save(u"新歌检索失败1477881109469_new.xlsx") wb = Workbook()ws = wb.activews['A1'] = 4wb.save("新歌检索失败.xlsx")

示例2：

import openpyxl# 新建文件workbook = openpyxl.Workbook()# 写入文件sheet = workbook.activesheet['A1']='A1'# 保存文件workbook.save('test.xlsx')

5.xlsxwriter可以写excel文件并加上图表

'''学习中遇到问题没人解答？小编创建了一个Python学习交流QQ群：711312441寻找有志同道合的小伙伴，互帮互助,群里还有不错的视频学习教程和PDF电子书！'''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()

示例2：

import xlsxwriter as xw#新建excelworkbook = xw.Workbook('myexcel.xlsx')#新建工作薄worksheet = workbook.add_worksheet()#写入数据worksheet.wirte('A1',1)#关闭保存workbook.close()

合并表格实例：

import xlsxwriterimport xlrd#新建excelworkbook = xlsxwriter.Workbook('广东.xlsx')#新建工作薄worksheet = workbook.add_worksheet() count = 1worksheet.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+=1for 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()

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。