python写Excel的三个选项：xlwt, xlsxwriter和openpyxl

xlwt并不能新建xlsx

确切的说是新建并写完数据之后微软打不开，WPS等等其它的都打得开， 也就奇了怪微软打不开，一定报错：
“
Excel无法打开文件__，因为文件格式或文件名无效。请却确定文件未损坏，并且文件扩展名与文件的格式未损坏。
”

解决的办法是改用xlsxwriter, 它的语法与xlwt是相似的，但是如名所述，xlsxwriter写不了xls老版文件。

改写只能用openpyxl

以上两者一个可以正常写xls，一个可以正常写xlsx，但仅限于新建。如果想进行增添报表的操作，例如在原始文件的基础上新添分析页，再保存更改，就只能使用openpyxl.

改进openpyxl写入规模数据的接口

而openpyxl的写入功能不是一般的奇葩，整出个双重iterator接口挑战程序员的忍耐力，仿佛是专门不想让人把一方块数据舒舒服服的写进去。逼得我花了一个多小时写了函数接口——写一行，写一列，写多行，写多列，而且位置可以指定起始第几行第几列。代码如下：

openpyxl_writers.py

# this is to improve the ease to write values by openpyxl
# simply copy and paste codes below or from openpyxl_writers import *
# Author = 阎兆珣 (Zhaoxun Yan)

# row and column index or start follow excel convention starting from 1 (not 0)

# I ) write a single line with data in the form of python list

# Write one row in a particular sheet starting from particular column(default 1)
def write_row( datalist, sheetobj, rowindex, colstart=1):
    cur_col = colstart
    for data in datalist:
        sheetobj.cell(row=rowindex, column=cur_col).value = data
        cur_col += 1

# Write one column in a particular sheet starting from particular row(default 1)
def write_col( datalist, sheetobj, colindex, rowstart=1):
    cur_row = rowstart
    for data in datalist:
        sheetobj.cell(row=cur_row, column=colindex).value = data
        cur_row += 1

# II) Write multiple lines with data in the form of list on list
# list on list example: [range(1,5), range(6,8)] not necessarily in rectangle

# Flush rows by data as list on list from the upleft cell
def flush_rows( listonlist, sheetobj, rowstart=1, colstart=1):
    cur_row = rowstart
    for line in listonlist:
        write_row(line, sheetobj, cur_row, colstart)
        cur_row += 1

# Flush columns by data as list on list from the upleft cell
def flush_cols( listonlist, sheetobj, rowstart=1, colstart=1):
    cur_col = colstart
    for line in listonlist:
        write_col(line, sheetobj, cur_col, rowstart)
        cur_col += 1

# reading data can be abtained by library "xlrd" hence omitted here
# note xlrd follows python convention starting row and column from index 0

将以上代码保存为openpyxl_writers用时调用，或者将需要的函数粘贴进你调用openpyxl的脚本即可。