Python and Excel are both commonly used for data processing, so passing data back and forth and handling computations between them is unavoidable. This article mainly introduces the Python-Excel family of libraries, and how to use the xlrd and xlwt libraries.

1. Common Libraries

xlwings, openpyxl, pandas, win32com, xlsxwriter, DataNitro, xlutils

2. Environment Requirements

• xlutils only supports xls files, that is, versions before 2003
• win32com and DataNitro only support Windows
• After xlwings is installed successfully, if running it reports the error “ImportError: no module named win32api”, install the pypiwin32 or pywin32 package as well
• win32com is not a standalone extension library but is integrated into other libraries; installing the pypiwin32 or pywin32 package is enough to use it
• DataNitro is an Excel plugin and must be downloaded from its official website to install

3. Document Read/Write/Modify Capabilities

• xlsxwriter does not support opening or modifying existing files
• xlwings does not support naming newly created files
• As an Excel plugin, DataNitro depends on the software itself
• pandas needs to rely on other libraries to create new documents, and so on

4. Basic Features

• xlwings
  Can be combined with VBA to program Excel, with powerful data input and analysis capabilities and a rich set of interfaces; together with pandas/numpy/matplotlib it easily handles Excel data processing work.
• openpyxl
  Simple and easy to use, with broad functionality — cell formatting/images/tables/formulas/filtering/comments/file protection and more, and its charting feature is a highlight. Its drawback is that VBA support is not good enough.
• pandas
  Data processing is pandas’ reason for existing; Excel serves as the container for pandas’ input/output data.
• win32com
  As the name suggests, this is an extension for handling Windows applications, and Excel is only a small part of what the library can do. It also supports many Office operations. Note that the library does not exist on its own; it can be obtained by installing pypiwin32 or pywin32.
• xlsxwriter
  Rich in features, supporting images/tables/charts/filtering/formatting/formulas and more. Similar in function to openpyxl, and its advantage is that unlike openpyxl it also supports VBA file import, sparklines and other features. Its drawback is that it cannot open/modify existing files, which means using xlsxwriter requires starting from scratch.
• DataNitro
  Embedded into Excel as a plugin, it can completely replace VBA, letting you use Python scripts inside Excel. Since it is called “Python in Excel,” working together with other Python libraries is a trivial matter. However, it is a paid plugin…
• xlutils
  Built on xlrd/xlwt, an old-guard Python package and arguably a pioneer in this field, with unremarkable features; its bigger drawback is that it only supports xls files

5. Performance

Different libraries were each used to add and read 1000 rows * 700 columns of data, and the time taken was recorded, with repeated runs averaged

6. Library Selection Advice

• If you do not want to use the GUI but want to give Excel more capabilities, choose either openpyxl or xlsxwriter
• If you need scientific computing and to process large amounts of data, pandas+xlsxwriter or pandas+openpyxl is recommended;
• If you want to write Excel scripts and know Python but not VBA, consider xlwings or DataNitro;

7. Reading and Writing Excel with xlrd and xlwt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

# coding=utf-8
def list_wirte_to_excel(data_list):
    '''
    :param data_list = [(1, 2, 3),(11, 21, 31)]:
    '''
    import xlwt
    excel = xlwt.Workbook(encoding='utf-8')
    sheet1 = excel.add_sheet(u'sheet1', cell_overwrite_ok=True)  # 创建sheet1
    columns = [u'第一列', u'第二列', u'时间']

    # 创建列名栏
    for i in xrange(0, len(columns)):
        sheet1.write(0, i, columns[i])

    # 写入数据
    for i in xrange(0, len(data_list)):
        if len(data_list[i]) == len(columns):
            # write(行，列，数据，样式)
            sheet1.write(i + 1, 0, data_list[i][0])
            sheet1.write(i + 1, 1, data_list[i][1])
            sheet1.write(i + 1, 2, data_list[i][2])
    excel.save('excel.xls')


def excel_to_list(excel_path):
    '''
    :param excel_path 能访问的excel路径:
    :return包含全部数据的list:[(第一列数据), (第二列数据)]
    '''
    import xlrd
    wb = xlrd.open_workbook(excel_path)
    # 两种方式：索引和名字
    sheet = wb.sheet_by_index(0)

    data = [sheet.row_values(rownum) for rownum in xrange(sheet.nrows)]

    # 如果只想返回第一列数据：
    # sheet.col_values(0)
    # 通过索引读取数据
    # cell(行,列), 获取第一行，第一列数据
    # sheet.cell(0, 0).value
    return data[1:]

if __name__ == '__main__':
    import random
    import datetime
    data = [(random.randint(0, 1000), random.randint(0, 1000), datetime.datetime.now().strftime('%Y-%m'))
            for i in xrange(1000)]
    list_wirte_to_excel(data)

    print excel_to_list('./excel.xls')