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