Python3 读取和写入excel xlsx文件 使用openpyxl

首先给大家推荐一下我老师大神的人工智能教学网站。教学不仅零基础,通俗易懂,而且非常风趣幽默,还时不时有内涵黄段子!点这里可以跳转到网站 python处理excel已经有大量包,主流代表有:

•xlwings:简单强大,可替代VBA

•openpyxl:简单易用,功能广泛

•pandas:使用需要结合其他库,数据处理是pandas立身之本

•win32com:不仅仅是excel,可以处理office;不过它相当于是 windows COM 的封装,新手使用起来略有些痛苦。

•Xlsxwriter:丰富多样的特性,缺点是不能打开/修改已有文件,意味着使用 xlsxwriter 需要从零开始。

•DataNitro:作为插件内嵌到excel中,可替代VBA,在excel中优雅的使用python

•xlutils:结合xlrd/xlwt,老牌python包,需要注意的是你必须同时安装这三个库

openpyxl的使用

openpyxl(可读写excel表)专门处理Excel2007及以上版本产生的xlsx文件,xls和xlsx之间转换容易

注意:如果文字编码是“gb2312” 读取后就会显示乱码,请先转成Unicode

openpyxl定义多种数据格式

最重要的三种:
NULL空值:对应于python中的None,表示这个cell里面没有数据。
numberic: 数字型,统一按照浮点数来进行处理。对应于python中的float。
string: 字符串型,对应于python中的unicode。

Excel文件三个对象

workbook: 工作簿,一个excel文件包含多个sheet。
sheet:工作表,一个workbook有多个,表名识别,如“sheet1”,“sheet2”等。
cell: 单元格,存储数据对象

1创建一个workbook(工作簿)

wb = Workbook()  # 一个工作簿(workbook)在创建的时候同时至少也新建了一张工作表(worksheet)。

2 打开一个已有的workbook:
 wb = load_workbook('file_name.xlsx')
3 打开sheet:
通过名字
    ws = wb["frequency"] 或ws2 = wb.get_sheet_by_name('frequency')




不知道名字用index
    sheet_names = wb.get_sheet_names()  #
    ws = wb.get_sheet_by_name(sheet_names[index])# index为0为第一张表 

或者(调用得到正在运行的工作表)

    ws =wb.active或ws = wb.get_active_sheet() #通过_active_sheet_index设定读取的表,默认0读第一个表
    活动表表名wb.get_active_sheet().title
4 新建sheet(工作表)
ws1 = wb.create_sheet() #默认插在最后
ws2 = wb.create_sheet(0) #插在开头 ,




ws.title = "New Title" #修改表名称
简化 ws2 = wb.create_sheet(title="Pi")
5 读写单元格

当一个工作表被创建时,其中是不包含单元格。只有当单元格被获取时才被创建。这种方式下,我们不会创建我们使用不到的单元格,从而减少了内存消耗。

可以直接根据单元格的索引直接获得
c = ws['A4']     #读取单元格,




可以通过cell()方法获取单元格(行号列号从1开始)
d = ws.cell(row = 4, column = 2) #通过行列读
d = ws.cell('A4')




写入单元格(cell)值
ws['A4'] = 4      #写单元格 
ws.cell(row = 4, column = 2).value = 'test'
ws.cell(row = 4, column = 2, value = 'test')
6 访问多个单元格
cell_range = ws['A1':'C2']    #




get_cell_collection()     #读所有单元格数据
7 按行、按列操作
逐行读
 ws.iter_rows(range_string=None, row_offset=0, column_offset=0) #返回一个生成器,
 例如:
  for row in ws.iter_rows('A1:C2'):
      for cell in row:
          print cell
迭代文件中所有的行或者列:
ws.rows         #迭代读取行row 
ws.columns      #迭代读取列column




直接读取行列数据 
print rows[n]      #显示第n行数据 
print columns[n]   #显示第n列数据




逐行写,添加一行到当前sheet的最底部。 
1,如果是list,将list从头到尾顺序添加。 2,如果是dict,按照相应的键添加相应的键值。
 append([‘This is A1’, ‘This is B1’, ‘This is C1’])
 append({‘A’ : ‘This is A1’, ‘C’ : ‘This is C1’})
 append({1 : ‘This is A1’, 3 : ‘This is C1’})
通过公式计算产生写入的值
ws["A1"] = "=SUM(1, 1)"
ws["A1"] = "=SUM(B1:C1)"




8 显示有多少张sheet表
wb.get_sheet_names()  
#显示表名,表行数,表列数   
print ws.title  
print ws.max_row
print ws.max_column




9 获得列号的字母
from openpyxl.utils import get_column_letter
for  x  in  range( 1, len(record)+ 1 ):  
    col = get_column_letter(x)    #
    ws.cell( '%s%s' %(col, i)).value = x




通过列字母获取多个excel数据块
cell_range = "E3:{0}28".format(get_column_letter(bc_col))
ws["A1"] = "=SUM(%s)"%cell_range
10 excel文件是gbk编码,读入时需要先编码为gbk,再解码为unicode,再编码为utf8
cell_value.encode('gbk').decode('gbk').encode('utf8')  








11保存到文件

wb = Workbook()

wb.save(‘balances.xlsx’)

save()会在不提示的情况下用现在写的内容,覆盖掉原文件中的所有内容





写入例子一

from openpyxl import Workbook

wb = Workbook()

激活 worksheet

ws = wb.active

数据可以直接分配到单元格中

ws['A1'] = 42

可以附加行,从第一列开始附加

ws.append([1, 2, 3])

# Python 类型会被自动转换

import datetime

ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")

保存文件

wb.save("sample.xlsx")

写入例子二

# workbook相关

from openpyxl import Workbook

from openpyxl.compat import range

from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active

ws1.title = "range names"

for row in range(1, 40):

    ws1.append(range(600))

ws2 = wb.create_sheet(title="Pi")

ws2['F5'] = 3.14

ws3 = wb.create_sheet(title="Data")

for row in range(10, 20):

    for col in range(27, 54):

        _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))

print(ws3['AA10'].value)

wb.save(filename=dest_filename)

读取例子一



from openpyxl.reader.excel import load_workbook
import json

# 读取excel2007文件
wb = load_workbook(filename=r’test_book.xlsx’)

# 显示有多少张表
print “Worksheet range(s):”, wb.get_named_ranges()
print “Worksheet name(s):”, wb.get_sheet_names()

# 取第一张表
sheetnames = wb.get_sheet_names()
ws = wb.get_sheet_by_name(sheetnames[0])

# 显示表名,表行数,表列数
print “Work Sheet Titile:”, ws.title
print “Work Sheet Rows:”, ws.max_row
print “Work Sheet Cols:”, ws.max_column


# 建立存储数据的字典
data_dic = {}

# 把数据存到字典中
for rx in range(1, ws.max_row + 1):
    temp_list = []
    pid = rx
    w1 = ws.cell(row=rx, column=1).value
    w2 = ws.cell(row=rx, column=2).value
    w3 = ws.cell(row=rx, column=3).value
    w4 = ws.cell(row=rx, column=4).value
    temp_list = [w1, w2, w3, w4]

    data_dic[pid] = temp_list

# 打印字典数据个数
print ‘Total:%d’ % len(data_dic)
print json.dumps(data_dic, encoding=”UTF-8″, ensure_ascii=False)

 读取结果:

Worksheet range(s): []

Worksheet name(s): [u'\u6d3b\u52a8\u8868', u'\u7528\u6237\u4fe1\u606f', u'Sheet3']

Work Sheet Titile: 活动表

Work Sheet Rows: 3

Work Sheet Cols: 5

Total:3

{"1": ["张三", 18, "男", "广州"], "2": ["李四", 20, "女", "湖北"], "3": ["王五", 25, "女", "北京"]}

实例

from openpyxl import Workbook
from openpyxl.compat import range
from openpyxl.cell import get_column_letter




dest_filename = 




wb = Workbook()
ws1 = wb.active
ws1.title = 
for row in range(1, 40):
   ws1.append(range(600))




ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
for col in range(27, 54):
       _ = ws3.cell(column=col, row=row, value="%s" % get_column_letter(col))
print(ws3['AA10'].value)
wb.save(filename = dest_filename)




sheet_ranges = wb['range names']
print(sheet_ranges['D18'].value)




ws['A1'] = datetime.datetime(2010, 7, 21)
ws['A1'].number_format #输出'yyyy-mm-dd h:mm:ss'




rows = [
    ['Number', 'Batch 1', 'Batch 2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10],
]




rows = [
    ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015,9, 1), 40, 30, 25],
    [date(2015,9, 2), 40, 25, 30],
    [date(2015,9, 3), 50, 30, 45],
    [date(2015,9, 4), 30, 25, 40],
    [date(2015,9, 5), 25, 35, 30],
    [date(2015,9, 6), 20, 40, 35],
]




for row in rows:
    ws.append(row)

excel中图片的处理,PIL模块

try:
from openpyxl.drawing import image
import PIL            
except ImportError, e:
print"[ERROR]",e




    report_file = self.excel_path + "/frquency_report_%d.xlsx" %id
    shutil.copyfile(configs.PATTEN_FILE, report_file)
ifnot os.path.exists(report_file):
print"generate file failed: ", report_file
       sys.exit(1)




    wb = load_workbook(report_file)
    ws = wb.get_sheet_by_name('frequency')
    img_f = configs.IMAGE_LOGO
if os.path.exists(img_f):
try:
            img = image.Image(img_f)
            ws.add_image(img, 'A1')
except Exception, e:
print"[ERROR]%s:%s" % (type(e), e)
            ws['A1'] = "程序化营销平台"
else:
            ws['A1'] = "程序化营销平台"




        font1 = Font(size=22)
        ws['A1'].font = font1
        ws['B4'] = ad_plan #等同ws.cell('B4') = ad_plan
        ws['B5'] = ad_names
        ws['B6'] = str(start_d) + '  to  ' + str(end_d)




        wb.save(report_file)








try:
        wb = load_workbook(report_file)
        ws = wb.get_sheet_by_name('frequency')            
        row = 
for it in query_result:
            one_row = it.split('\t')
print one_row
if'10' == one_row[0]:
                one_row[0] = '10+'
            col = 
for one_cell in one_row:
                ws.cell(row = row, column = col).value = one_cell
                col = col + 
            row = row + 
except Thrift.TException, tx:
print'[ERROR] %s' % (tx.message)
else:
        wb.save(report_file)
finally:
pass

点这里可以跳转到人工智能网站