1)下载openpyxl模块:https://pypi.python.org/pypi/openpyxl
2)解压到指定文件目录:tar -xzvf openpyxl.tar.gz
3)进入目录,找到setup.py文件,执行命令:python setup.py install
如果报错No module named setuptools 就使用命令“easy_install openpyxl”,easy_install for win32,会自动安装setuptools。
这里注意,如果不能自动安装,基本上python的模块都通过命令 python 模块名.py install 来安装,如果setuptools模块没有,直接去官网下载,然后前面命令安装就可以了
4)处理图片还需要安装pillow(PIL)
To be able to include images (jpeg, png, bmp,...) into an openpyxl file, you will also need the “pillow” library that can be installed with:
pip install pillow
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color, Fill
from openpyxl.styles import colors
from openpyxl.styles import Fill,fills
from openpyxl.formatting.rule import ColorScaleRule
a)逐行读
ws.iter_rows(range_string=None, row_offset=0, column_offset=0): range-string(string)-单元格的范围:例如('A1:C4') row_offset-添加行 column_offset-添加列
返回一个生成器, 注意取值时要用value,例如:
for row in ws.iter_rows('A1:C2'):
for cell in row:
print cell
读指定行、指定列:
rows=ws.rows#row是可迭代的
columns=ws.columns#column是可迭代的
打印第n行数据
print rows[n]#不需要用.value
print columns[n]#不需要用.value
b)逐行写
(http://openpyxl.readthedocs.io/en/default/_modules/openpyxl/worksheet/worksheet.html#Worksheet.append)
ws.append(iterable)
添加一行到当前sheet的最底部 iterable必须是list,tuple,dict,range,generator类型的。 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’})
8) #显示有多少张表
wb.get_sheet_names()
#显示表名,表行数,表列数
print ws.title
print ws.max_row
print ws.max_column
ws.get_highest_row() #UserWarning: Call to deprecated function
ws.get_highest_column()# UserWarning: Call to deprecated function
9) 获得列号x的字母 col = get_column_letter(x), x从1开始
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