python读取多个excel文件

代码如下，方法比较粗暴，以后继续改进：

import xlwt
import xlrd

#数模比赛中的excel文件的数据整理
#仅供参考，菜鸡数模T T

financials = ['Sales management', 'market/marketing', 'Sales ', 'Finance/auditing/tax']
IT = ['Computer software ', 'Computer hardware', 'Internet development and application', 'IT-Management',
      'IT-QM, technical support and more', 'Communications technology', 'Internet development and application']
industrials = ['Engineering/machinery/energy', 'Senior management', 'Art/graphics/Animation design',
               'Counsel/consulting', 'Construction/infrastructure/gardening',
               'QMS/safety/environmental protection', 'Technical work', 'HR', 'Logistics/warehousing',
               'Science & Technology', 'Transportation service', 'Office administration/logistics',
               'Production/operation', 'Procurement', 'Biology/chemicals/pharmaceuticals/medical equipment',
               'Electronics/appliances/semiconductor/instrumentation', 'Other','Gradu']
ConsumerDiscretionary = ['Education', 'Literature/screenwriting/writing', 'Beauty and personal care',
                         'Customer service and technical support', 'Movies, TV and recreation',
                         'Restaurants & recreation', 'Advertising', 'PRs and news media','Graduates']
HealthCare = ['Hospital/medical/care', 'Security/housekeeping/other']
ConsumerStaples = ['General merchandise/chains/retail']
property = ['Real property', 'Property management', 'Hotels/tourism']

it_num = []
finan_num = []
indus_num = []
discre_num = []
hea_num = []
stap_num = []
pro_num = []


# 读excel
def read_excel():
    # 打开文件
    # num = 1
    global it_num
    global finan_num
    global indus_num
    global discre_num
    global hea_num
    global stap_num
    global pro_num

    # file = "C://Users//cheer//Desktop//problem//2018 APMCM Problems//2018 APMCM Problem B//A//2016//"
    for page in range(1, 9):
        it_num.clear()
        finan_num.clear()
        indus_num.clear()
        discre_num.clear()
        hea_num.clear()
        stap_num.clear()
        pro_num.clear()
        if page < 10:
            k = str(page)
            name = "0" + k
            # print(name)
            file = "C://Users//cheer//Desktop//problem//2018 APMCM Problems//2018 APMCM Problem B//A//2018//" + name + ".xlsx"
        else:
            file = "C://Users//cheer//Desktop//problem//2018 APMCM Problems//2018 APMCM Problem B//A//2018//" + str(
                page) + ".xlsx"
        workbook = xlrd.open_workbook(file, "rb")
        sheet = workbook.sheet_by_index(0)
        # print(sheet.nrows)
        # 插入数据
        for row in range(3, sheet.nrows):  # row --行数
            data = str(sheet.cell_value(row, 1))
            for names in IT:
                if names == data:
                    # print(data)
                    if len(it_num) == 0:
                        for index in range(2, 12):
                            it_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            it_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in financials:
                if names == data:
                    # print(data)
                    if len(finan_num) == 0:
                        for index in range(2, 12):
                            finan_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            finan_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in industrials:
                if names == data:
                    # print(data)
                    # num += 1
                    if len(indus_num) == 0:
                        for index in range(2, 12):
                            indus_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            indus_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in ConsumerDiscretionary:
                if names == data:
                    # print(data)
                    if len(discre_num) == 0:
                        for index in range(2, 12):
                            discre_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            discre_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in HealthCare:
                if names == data:
                    # print(data)
                    if len(hea_num) == 0:
                        for index in range(2, 12):
                            hea_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            hea_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in ConsumerStaples:
                if names == data:
                    if len(stap_num) == 0:
                        for index in range(2, 12):
                            stap_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            stap_num[index - 2] += sheet.cell_value(row, index)
                    break
            for names in property:
                if names == data:
                    # print(data)
                    if len(pro_num) == 0:
                        for index in range(2, 12):
                            pro_num.append(sheet.cell_value(row, index))
                    else:
                        for index in range(2, 12):
                            pro_num[index - 2] += sheet.cell_value(row, index)
                    break
            # sum = 0
            # for i in range(0, 8):
            #     sum += i
            # it_num.append(sum)
            if len(stap_num)==0:
                for indexs in range(2,12):
                    stap_num.append(0)
        write_excel(page)



def write_excel(page):
    f = xlwt.Workbook()  # 创建工作簿
    # num = 1
    global it_num
    global finan_num
    global indus_num
    global discre_num
    global hea_num
    global stap_num
    global pro_num
    '''
    创建第一个sheet:
        sheet
    '''

    sheet = f.add_sheet(u'sheet')  # 创建sheet
    row0 = [u'profession', u'Junior middle school', u'	Senior middle school', u'Technical secondary school',
            u'Junior college', u'Bachelor \' s degree', u'Master\'s degree', u'Doctor\'s degree', u'MBA', u'Unlimited',
            u'the desired profession', u'job demand']
    column0 = [u'Information Technology', u'financials', u'industrials', u'Consumer Discretionary', u'Health Care',
               u'Consumer Staples', u'property']

    # 生成第一行
    for i in range(0, len(row0)):
        sheet.write(0, i, row0[i])

    # 生成第一列
    for i in range(0, len(column0)):
        sheet.write(i + 1, 0, column0[i])

    # Information Technology
    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(1, i, it_num[i])
        sum1 += it_num[i]
    # print(sum1)
    sheet.write(1, len(row0) - 1, it_num[0])
    sheet.write(1, len(row0) - 2, sum1)

    # sheet.write(1, len(row0) - 2, it_num[-1])
    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(2, i, finan_num[i])
        sum1 += finan_num[i]
    sheet.write(2, len(row0) - 1, finan_num[0])
    sheet.write(2, len(row0) - 2, sum1)

    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(3, i, indus_num[i])
        sum1 += indus_num[i]
    sheet.write(3, len(row0) - 1, indus_num[0])
    sheet.write(3, len(row0) - 2, sum1)

    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(4, i, discre_num[i])
        sum1 += discre_num[i]
    sheet.write(4, len(row0) - 1, discre_num[0])
    sheet.write(4, len(row0) - 2, sum1)

    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(5, i, hea_num[i])
        sum1 += hea_num[i]
    sheet.write(5, len(row0) - 1, hea_num[0])
    sheet.write(5, len(row0) - 2, sum1)

    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(6, i, stap_num[i])
        sum1 += stap_num[i]
    sheet.write(6, len(row0) - 1, stap_num[0])
    sheet.write(6, len(row0) - 2, sum1)

    sum1 = 0
    for i in range(1, len(row0) - 2):
        sheet.write(7, i, pro_num[i])
        sum1 += pro_num[i]
    sheet.write(7, len(row0) - 1, pro_num[0])
    sheet.write(7, len(row0) - 2, sum1)

    # file = "C://Users//cheer//Desktop//problem//2018 APMCM Problems//2018 APMCM Problem B//A//2016//"
    k = str(page)
    name = "0" + k + " done"
    # print(name)
    file = "C://Users//cheer//untitled//" + name + ".xls"
    f.save(file)  # 保存文件


read_excel()