python Excel处理

pip install pandas
pip install openpyxl


import pandas as pd
class Excel():
    def __init__(self):
        pass
    def get_index(self):
        datas = pd.read_excel('satisfaction.xlsx',sheetname='Sheet1')
        all_rigion = list(datas.ix[:,1])

        series_list = []
        rigion = ['Northeast Europe','Western Europe','Middle East']
        NE_index,WE_index,ME_index, = [],[],[]
        row_index = [NE_index,WE_index,WE_index]
        for index, item in enumerate(all_rigion):
            for i in range(len(row_index)):
                if item == rigion[i]:
                    row_index[i].append(index)
        for j in range(len(row_index)):
            for k in row_index[j]:
                series_list.append(datas.ix[k,:])
            result = pd.DataFrame(series_list)
            result.to_excel(rigion[j]+'.xlsx',index=False)

t = Excel()
t.get_index()

 

def read_info():
    data = xlrd.open_workbook('tmp_info.xlsx')
    table = data.sheets()[0]
    info_list, tmp_list = [], []
    for i in range(1, table.nrows):
        tmp = table.row_values(i)
        for j in range(3):
            val = re.sub(' ', '', tmp[j])       # .encode('utf-8')
            val = re.sub('#', '', val)
            val = re.sub('\$', '-', val)
            val = re.sub('&', '-', val)
            val = re.sub('\+', '-', val)
            if len(tmp[2]) > 50:
                val = re.sub('_', '', val)
                val = re.sub('$', '', val)
            tmp_list.append(val)
        info_list.append(tmp_list)
        tmp_list = []
    return info_list