python操作excel方法

1）自身有Win32 COM操作office但讲不清楚，可能不支持夸平台，linux是否能用不清楚，其他有专业处理模块，如下

2）xlrd:（读excel）表,xlrd读大表效率高于openpyxl

3）xlwt:（写excel）表,

xlrd和xlwt对版本上兼容不太好，很多新版excel有问题。

新版excel处理：

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

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

安装openpyxl

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

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

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]#不需要用.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

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 Workbook

from openpyxl.compat import range

from openpyxl.cell import get_column_letter

dest_filename = 'empty_book.xlsx'

wb = Workbook()

ws1 = wb.active

ws1.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)#保存一定要有，否则不会有结果