Python利用xlsxwriter读写Excel文件(持续补充)

前言

有时我们要针对一些数据做统计报告，在文件过多亦或数据量大，excel操作重复操作又过多等情况下，我们可以利用Python进行数据分析处理。

例子

读取文本数据

假设有文本数据如下：

201801136 61.0 68.0 60.0
201801137 94.0 64.0 75.0
201801138 88.0 77.0 82.0
201801139 87.0 84.0 72.0
201801140 93.0 69.0 74.0

我们利用python读取并将除了第一列学号外的数据，整理到numpy矩阵中，代码如下：

def read_data(file):
    with open(file, "r") as f:
        id, subject_1, subject_2, subject_3 =[], [], [], []
        for line in f.readlines():
            line = line.strip('\n')  # 去掉列表中每一个元素的换行符
            line = line.split(' ')
            id.append((line[0]))
            subject_1.append(float(line[1]))
            subject_2.append(float(line[2]))
            subject_3.append(float(line[3]))
        row, col = len(subject_1), 3
        table = np.zeros((row, col))
        table[:, 0], table[:, 1], table[:, 2] = subject_1, subject_2, subject_3
        # head = ['subject_1', 'subject_2', 'subject_3']
        # data = pd.DataFrame(table)
        # data.columns = head
        return table

数据写入Excel

1. 简化Demo

def write_excel(file_name):
    workbook = xlsxwriter.Workbook(file_name)
    # 在文件中创建一个名为TEST的sheet,不加名字默认为sheet1
    worksheet = workbook.add_worksheet(u'Score')  
    worksheet.write(0, 0, '数学')
    worksheet.write(1, 1