0x01

在用python写excel时，发现写10000行数据，每行50列时， 最快的excel库也要10秒，而在我的项目中用的xlsxwriter耗时10多秒，测试代码如下。这对于一个web服务来说，耗时实在是太长了。

测试

import sys
from time import clock

import openpyxl
import pyexcelerate
import xlsxwriter
import xlwt

from openpyxl.utils import get_column_letter


# Default to 1000 rows x 50 cols.
if len(sys.argv) > 1:
    row_max = int(sys.argv[1])
    col_max = 50
else:
    row_max = 10000
    col_max = 50

if len(sys.argv) > 2:
    col_max = int(sys.argv[2])
def print_elapsed_time(module_name, elapsed):
    """ Print module run times in a consistent format. """
    print("    %-22s: %6.2f" % (module_name, elapsed))


def time_xlsxwriter():
    """ Run XlsxWriter in default mode. """
    start_time = clock()

    workbook = xlsxwriter.Workbook('xlsxwriter.xlsx')
    worksheet = workbook.add_worksheet()

    for row in range(row_max // 2):
        for col in range(col_max):
            worksheet.write_string(row * 2, col, "Row: %d Col: %d" % (row, col))
        for col in range(col_max):
            worksheet.write_number(row * 2 + 1, col, row + col)

    workbook.close()

    elapsed = clock() - start_time
    print_elapsed_time('xlsxwriter', elapsed)


def time_xlsxwriter_optimised():
    """ Run XlsxWriter in optimised/constant memory mode. """
    start_time = clock()

    workbook = xlsxwriter.Workbook('xlsxwriter_opt.xlsx',
                                   {'constant_memory': True})
    worksheet = workbook.add_worksheet()

    for row in range(row_max // 2):
        for col in range(col_max):
            worksheet.write_string(row * 2, col, "Row: %d Col: %d" % (row, col))
        for col in range(col_max):
            worksheet.write_number(row * 2 + 1, col, row + col)

    workbook.close()

    elapsed = clock() - start_time
    print_elapsed_time('xlsxwriter (optimised)', elapsed)


def time_openpyxl():
    """ Run OpenPyXL in default mode. """
    start_time = clock()

    workbook = openpyxl.workbook.Workbook()
    worksheet = workbook.active

    for row in range(row_max // 2):
        for col in range(col_max):
            colletter = get_column_letter(col + 1)
            worksheet.cell('%s%s' % (colletter, row * 2 + 1)).value = "Row: %d Col: %d" % (row, col)
        for col in range(col_max):
            colletter = get_column_letter(col + 1)
            worksheet.cell('%s%s' % (colletter, row * 2 + 2)).value = row + col

    workbook.save('openpyxl.xlsx')

    elapsed = clock() - start_time
    print_elapsed_time('openpyxl', elapsed)
def time_openpyxl_optimised():
    """ Run OpenPyXL in optimised mode. """
    start_time = clock()

    workbook = openpyxl.workbook.Workbook()
    worksheet = workbook.create_sheet()

    for row in range(row_max // 2):
        string_data = ["Row: %d Col: %d" % (row, col) for col in range(col_max)]
        worksheet.append(string_data)

        num_data = [row + col for col in range(col_max)]
        worksheet.append(num_data)

    workbook.save('openpyxl_opt.xlsx')

    elapsed = clock() - start_time
    print_elapsed_time('openpyxl   (optimised)', elapsed)


def time_pyexcelerate():
    """ Run pyexcelerate in "faster" mode. """
    start_time = clock()

    workbook = pyexcelerate.Workbook()
    worksheet = workbook.new_sheet('Sheet1')

    for row in range(row_max // 2):
        for col in range(col_max):
            worksheet.set_cell_value(row * 2 + 1, col + 1, "Row: %d Col: %d" % (row, col))
        for col in range(col_max):
            worksheet.set_cell_value(row * 2 + 2, col + 1, row + col)

    workbook.save('pyexcelerate.xlsx')
    elapsed = clock() - start_time

    print_elapsed_time('pyexcelerate', elapsed)


def time_xlwt():
    """ Run xlwt in default mode. """
    start_time = clock()

    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('Sheet1')

    for row in range(row_max // 2):
        for col in range(col_max):
            worksheet.write(row * 2, col, "Row: %d Col: %d" % (row, col))
        for col in range(col_max):
            worksheet.write(row * 2 + 1, col, row + col)

    workbook.save('xlwt.xls')

    elapsed = clock() - start_time
    print_elapsed_time('xlwt', elapsed)

print("")
print("Versions:")
print("    %-12s: %s" % ('python', sys.version[:5]))
print("    %-12s: %s" % ('openpyxl', openpyxl.__version__))
print("    %-12s: %s" % ('pyexcelerate', pyexcelerate.__version__))
print("    %-12s: %s" % ('xlsxwriter', xlsxwriter.__version__))
print("    %-12s: %s" % ('xlwt', xlwt.__VERSION__))
print("")

print("Dimensions:")
print("    Rows = %d" % row_max)
print("    Cols = %d" % col_max)
print("")

print("Times:")
time_pyexcelerate()
time_xlwt()
time_xlsxwriter_optimised()
time_xlsxwriter()
time_openpyxl_optimised()
time_openpyxl()
print("")

0x02

为了提高python的效率，很自然的就想到了c， 查询相关资料后，发现写c代码的方式有几种。

• 第一种， 直接利用ctype 调用动态链接库

 from ctypes import windll # 首先导入 ctypes 模块的 windll 子模块
 somelibc = windll.LoadLibrary(some.dll) # 使用 windll 模块的 LoadLibrary 导入动态链接库

• 第二种 利用ctypes来写搭起c与python的桥梁

• 第三种直接用c来封装c代码，并生成动态链接库。

#include <Python.h>
#include <string.h>

/* module functions */
static PyObject *                                 /* returns object */
message(PyObject *self, PyObject *args)           /* self unused in modules */
{                                                 /* args from Python call */
    char *fromPython, result[64];
    if (! PyArg_Parse(args, "(s)", &fromPython))  /* convert Python -> C */
        return NULL;                              /* null=raise exception */
    else {
        strcpy(result, "Hello, ");                /* build up C string */
        strcat(result, fromPython);               /* add passed Python string */
        return Py_BuildValue("s", result);        /* convert C -> Python */
    }
}

/* registration table  */
static struct PyMethodDef hello_methods[] = {
    {"message", message, 1},       /* method name, C func ptr, always-tuple */
    {NULL, NULL}                   /* end of table marker */
};

/* module initializer */
void inithello( )                       /* called on first import */
{                                      /* name matters if loaded dynamically */
    (void) Py_InitModule3("hello", hello_methods);   /* mod name, table ptr */
}

这种方式代码效率最高，缺点是与py版本不兼容

• 第四种，利用cython生成c代码，这种方式是最先进的，也是最推荐的。

0x03

用cython可以参考cython的官网。

首先写.pxd文件，类似于c语言的.h头文件，定义函数签名等

这里我直接调用了c的excel库libxlsxwriter，并且安装libxlsxwriter到系统路径中去了。c中的函数签名直接copy到.pxd文件中就可以了，需要注意的是，如果c定于的是一个struct，如lxw_error，那么在.pxd中直接写上pass就好了，cython在生成代码的时候会自动帮我们找到这个struct。

#cexcel.pyx
cdef extern from "xlsxwriter/format.h":
    ctypedef struct lxw_error:
        pass

cdef extern from "xlsxwriter/common.h":

    ctypedef int lxw_col_t
    ctypedef int lxw_row_t
    ctypedef struct lxw_format:
        pass

cdef extern from "xlsxwriter/worksheet.h":
    ctypedef struct lxw_worksheet:
        pass

    lxw_error worksheet_write_string(lxw_worksheet *worksheet,
                                    lxw_row_t row,
                                    lxw_col_t col,
                                    const char *string,
                                    lxw_format *cformat);

    lxw_error worksheet_write_number(lxw_worksheet *worksheet,
                                    lxw_row_t row,
                                    lxw_col_t col,
                                    double number,
                                    lxw_format *cformat);


    lxw_error worksheet_set_column(lxw_worksheet *worksheet,
                                lxw_col_t first_col,
                                lxw_col_t last_col,
                                double width, lxw_format *format);

cdef extern from "xlsxwriter/workbook.h":
    ctypedef struct lxw_workbook:
        pass
    ctypedef struct  lxw_workbookoptions:
        pass

    lxw_workbook *new_workbook(const char *filename);

    lxw_worksheet *workbook_add_worksheet(lxw_workbook *workbook,
                                        const char *sheetname);

    lxw_error workbook_close(lxw_workbook *workbook);

cdef extern from "xlsxwriter/custom.h":
    lxw_format *get_my_style(lxw_workbook *workbook, int name)

定义好.pxd文件后，下面就开始写我们的代码逻辑了，定义在.pyx文件中代码如下，其中cexcel就是之前我们定义的cexcel.pxd文件，在WorkBook类中，如果需要使用一个c变量， 那么我们需要用cdef语句先声明这个变量的类型。

#excel.pyx
cimport cexcel

cdef class WorkBook:
    cdef cexcel.lxw_workbook *_c_workbook
    cdef cexcel.lxw_worksheet *_c_worksheet
    cdef cexcel.lxw_format *_c_header
    cdef cexcel.lxw_format *_c_str
    cdef cexcel.lxw_format *_c_num
    cdef cexcel.lxw_format *cformat
    def __cinit__(self, const char *filename):
        self._c_workbook = cexcel.new_workbook(filename)
        self._c_header = cexcel.get_my_style(self._c_workbook, 0)
        self._c_str = cexcel.get_my_style(self._c_workbook, 1)
        self._c_num = cexcel.get_my_style(self._c_workbook, 2)

    def add_worksheet(self, const char *sheetname):
        self._c_worksheet = cexcel.workbook_add_worksheet(self._c_workbook, sheetname)
        return self

    def write_header(self, cexcel.lxw_row_t row, cexcel.lxw_col_t col, const char *string):
        cexcel.worksheet_write_string(self._c_worksheet, row, col, string, self._c_header)

    def write_string(self, cexcel.lxw_row_t row, cexcel.lxw_col_t col, const char *string):
        cexcel.worksheet_write_string(self._c_worksheet, row, col, string, self._c_str)

    def write_number(self, cexcel.lxw_row_t row, cexcel.lxw_col_t col, number):
        if number in {'--', ''}:
            cexcel.worksheet_write_string(self._c_worksheet, row, col, number, self._c_num)
        else:
            cexcel.worksheet_write_number(self._c_worksheet, row, col, number, self._c_num)

    def write_percent(self, cexcel.lxw_row_t row, cexcel.lxw_col_t col, const char *string):
        cexcel.worksheet_write_string(self._c_worksheet, row, col, string, self._c_num)

    def close(self):
        cexcel.workbook_close(self._c_workbook)

    def set_column(self, cexcel.lxw_col_t first_col, cexcel.lxw_col_t last_col, double width):
        cexcel.worksheet_set_column(self._c_worksheet, first_col, last_col, width, NULL)

0x04

写完模块的业务逻辑之后，我们只需要编写setup.py文件，利用disutls把我们的cython模块安装到系统路径或者虚拟环境中
代码如下

from Cython.Build import cythonize
from setuptools import setup, find_packages, Extension


ext_modules = cythonize([
    Extension("py_c_xlsxwriter", ["excel.pyx"],
              libraries=["xlsxwriter"], include_dirs=['/home/linl/Desktop/py_c_xlsxwriter/libxlsxwriter/lib'])])

setup(
  name = "cpexcel",
  version = '0.0.4',
  keywords = 'c xlsxwriter cython',
  license = 'MIT License',
  url = 'https://github.com/drinksober',
  install_requires = ['Cython'],
  author = 'drinksober',
  author_email = 'drinksober@foxmail.com',
  packages = find_packages(),
  platforms = 'any',
  ext_modules = cythonize(ext_modules)
)

然后执行python setup.py install，一个完成的cython模块就完成了。