python用openpyxl操作excel

python操作excel方法

1）自身有Win32 COM操作office但讲不清楚，可能不支持夸平台，linux是否能用不清楚，其他有专业处理模块，如下2）xlrd:（读excel）表,xlrd读大表效率高于openpyxl3）xlwt:（写excel）表, xlrd和xlwt对版本上兼容不太好，很多新版excel有问题。

新版excel处理：

openpyxl（可读写excel表）专门处理Excel2007及以上版本产生的xlsx文件，xls和xlsx之间转换容易注意：如果文字编码是“gb2312” 读取后就会显示乱码，请先转成Unicode

安装openpyxl

1）下载openpyxl模块：https://pypi.python.org/pypi/openpyxl2）解压到指定文件目录：tar -xzvf openpyxl.tar.gz3）进入目录，找到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

---

pthon学习资料

python 学习小组http://www.thinksaas.cn/group/show/368/page/4官网：    https://pypi.python.org/pypi/openpyxl    http://openpyxl.readthedocs.io/en/default/good：    http://blog.csdn.net/suofiya2008/article/details/6284208    http://blog.csdn.net/zzukun/article/details/49946147    http://www.thinksaas.cn/topics/0/501/501962.html

openpyxl的使用

openpyxl定义多种数据格式

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

Excel文件三个对象

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

1）导入

from openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font, Color, Fillfrom openpyxl.styles import colorsfrom openpyxl.styles import Fill,fillsfrom openpyxl.formatting.rule import ColorScaleRule

2）打开workbook：

    wb = load_workbook('file_name.xlsx')

3）open sheet：

通过名字    ws = wb["frequency"]     等同于 ws2 = wb.get_sheet_by_name('frequency')    验证命令ws is ws2 is ws3 输出True不知道名字用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）建新表

ws1 = wb.create_sheet() #默认插在最后ws2 = wb.create_sheet(0) #插在开头   建表后默认名按顺序，如sheet1，sheet2...    ws.title = "New Title" #修改表名称   简化 ws2 = wb.create_sheet(title="Pi")

5）backgroud color of tab( be white by default)

ws.sheet_properties.tabColor = "1072BA" # set with RRGGBB color code

6）单元格使用

c = ws['A4'] #read 等同于 c = ws.cell('A4') ws['A4'] = 4 #write #ws.cell有两种方式，行号列号从1开始d = ws.cell(row = 4, column = 2) #行列读写d = ws.cell('A4') 写入cell值    ws.cell(row = 4, column = 2).value = 'test'    ws.cell(row = 4, column = 2, value = 'test')

7）访问多个单元格

cell_range = ws['A1':'C2']读所有单元格数据get_cell_collection()

8) 按行操作，按列操作

   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]#不需要用.valueb)逐行写 (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_rowprint ws.max_columnws.get_highest_row() #UserWarning: Call to deprecated functionws.get_highest_column()# UserWarning: Call to deprecated function

9) 获得列号x的字母 col = get_column_letter(x), x从1开始

from openpyxl.utils import get_column_letterfor  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编码，读入时需要先encode为gbk，再decode为unicode，再encode为utf8

cell_value.encode('gbk').decode('gbk').encode('utf8')  

11) 公式计算formulae

ws["A1"] = "=SUM(1, 1)"ws["A1"] = "=SUM(B1:C1)"

代码实例实例（直接修改使用）

from openpyxl import Workbookfrom openpyxl.compat import rangefrom openpyxl.cell import get_column_letterdest_filename = 'empty_book.xlsx'wb = Workbook()ws1 = wb.activews1.title = "range names"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)    if not 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 = 9        for it in query_result:            one_row = it.split('\t')            print one_row            if '10' == one_row[0]:                one_row[0] = '10+'            col = 1            for one_cell in one_row:                ws.cell(row = row, column = col).value = one_cell                col = col + 1            row = row + 1          except Thrift.TException, tx:        print '[ERROR] %s' % (tx.message)    else:        wb.save(report_file)    finally:        pass

#

    from openpyxl.writer.excel import ExcelWriter       wb1=Workbook()#新建工作簿    ewb1=ExcelWriter(workbook=wb1)#新建一个ExcelWriter，用来写wb1      ws1=wb1.worksheets[0]#取得wb1的第一个工作表ws1     one_cell = ws1.cell(row = row, column = col).value    ws1.cell(row = row, column = col).value = one_cell    ewb1.save(filename=dest_filename)#保存一定要有，否则不会有结果