12.pandas将二维数组写入excel中

# encoding:utf-8

import pandas as pd
from openpyxl import load_workbook

'''
本案例获取所有符合的列数据
去除了每个栏位的空格
去除了空行数据
去除了第一列为空的整行数据
多个sheet

对多个sheet数据整合在一个sheet中

对整合在一个sheet中的数据只获取想要的列

获取对应行对应列的数据写到excel中
即第一列与后面每一列一一对应放入到新的一行中
组成一个2维数组，写入倒新的EXCEL中
'''

class OpenMyXLS():
    def __init__(self, filepath):
        self.filepath = filepath
        self.openxls()

    def openxls(self):
        '''获取第一个sheet的数据'''
        df = pd.read_excel(self.filepath, sheet_name=0)
        '''获取行数列数'''
        hanglie = df.shape
        hang = hanglie[0]
        lie = hanglie[1]
        print(hanglie)

        '''将第i行第j列的值加到excel中,组成只有两列的数据'''
        tofilepath = "../HSGtotest81_zh2_dange.xlsx"
        result2 = self.get8170lists(df, hang, lie)
        self.writerexcel(tofilepath,result2)

    '''将二维数据写入excel中'''
    def writerexcel(self, tofilepath, result2):
        writer = pd.ExcelWriter(tofilepath)
        df = pd.DataFrame(result2)
        df.to_excel(writer, sheet_name="allhsgmlb", index=False)
        writer.save()
        writer.close()

    '''获取81跟70配对,组合成2维数组'''
    def get8170lists(self, df, hang, lie):
        n = 0
        m = 1
        h_list = []
        h_lists = []
        while n < hang:
            while m < lie:
                valueij = df.values[n, 0]
                h_list.append(valueij)

                valueij = df.values[n, m]
                h_list.append(valueij)
                # print(h_list)

                h_lists.append(h_list)
                print(h_lists)
                h_list = []
                m += 1
            n += 1
            m = 1
        return  h_lists

    '''获取excel中所有sheet的名称'''
    def getSheetNames(self, filepath):
        df = pd.read_excel(filepath, sheet_name=None)
        print("所有的sheet名称：")
        sheetNameList = list(df)
        return sheetNameList

    '''根据sheet名称获取对应列名的数据,写入新的excel中'''
    def foundtopnamedata(self, sheetNameList, file1):
        k = 0
        writer = pd.ExcelWriter(file1)
        while k < len(sheetNameList):
            sheetName = sheetNameList[k]
            print(sheetName)
            df = pd.read_excel(self.filepath, sheet_name=sheetName)
            print("获取表头")
            topNames = (df.keys()).values
            print(topNames)
            print("查找是否有81BOM列")
            findArr = self.returnFiindHSG(topNames)
            # findArr = self.returnFiindMLB(topNames)
            print("整合后的表头数据")
            print(findArr)

            if findArr:
                print("获取对应列的所有数据")
                d = self.returnDict(findArr, df)
                todf = pd.DataFrame(d)
                print("删除空行")
                todf = todf.dropna(how='all')
                print("删除81BOM列中有nan的行")
                todf = todf.dropna(subset='81BOM')
                todf.to_excel(writer, sheet_name=sheetName, index=False)
            else:
                print("没有找到想要的列表头")
            k += 1
        writer.save()
        writer.close()
        print("创建excel OK!!!")

    '''整合所有sheet放在第一个sheet中'''
    def zhengheALLsheet(self, readfilepath, tofilepath1, tofilepath2):
        writer1 = pd.ExcelWriter(tofilepath1)
        writer2 = pd.ExcelWriter(tofilepath2)

        sheetNameList = self.getSheetNames(readfilepath)
        print(len(sheetNameList))

        k = 0
        frames = []
        while k < len(sheetNameList):
            sheetName = sheetNameList[k]
            print(sheetName)
            df1 = pd.read_excel(readfilepath, sheet_name=sheetName)
            #print(df1)
            frames.append(df1)
            k += 1
        print(frames)

        result1 = pd.concat(frames, keys=sheetNameList)
        result1.to_excel(writer1, sheet_name="allhsgmlb")
        writer1.save()
        writer1.close()

        result2 = pd.concat(frames)
        result2.to_excel(writer2, sheet_name="allhsgmlb", index=False)
        writer2.save()
        writer2.close()
        print("数据整合到一个sheet中成功！")

    '''去除重复行的数据'''
    def qcfxls(self, readfilepath, sheetNameList, tofilepath):
        writer = pd.ExcelWriter(tofilepath)
        # 读取excel中的数据
        j = 0
        while j < len(sheetNameList):
            sheetName = sheetNameList[j]
            data = pd.DataFrame(pd.read_excel(readfilepath, sheetName))
            # 查看获取的数据
            #print(data)
            # 查看去除重复行的数据
            no_re_row = data.drop_duplicates()
            print(no_re_row)
            # 将去除重复行的数据输出到excel表中
            no_re_row.to_excel(writer, sheet_name=sheetName, index=False)
            j +=1
        writer.save()
        writer.close()
        print("去除重复数据OK")

    '''返回插入EXCEL数据的字典形式'''
    def returnDict(self, findArr, df):
        i = 0
        d = {}
        while i < len(findArr):
            allData1 = df.loc[:, findArr[i]].values
            allData1a = self.qukongge(allData1)
            d[findArr[i]] = allData1a
            i += 1
        return d

    '''返回符合要求的表头'''
    def returnFiindMLB(self, topNames):
        findArr = []
        for topName in topNames:
            if topName == '81BOM':
                findArr.append(topName)
                print(findArr)
            if '70 MLB Bin' in topName:
                findArr.append(topName)
                print(findArr)
        return findArr

    '''返回符合要求的表头'''
    def returnFiindHSG(self, topNames):
        findArr = []
        for topName in topNames:
            if topName == '81BOM':
                findArr.append(topName)
                print(findArr)
            if '70 HSG Bin' in topName:
                findArr.append(topName)
                print(findArr)
        return findArr

    '''返回符合要求的表头'''
    def returnFiindAll(self, topNames):
        findArr = []
        for topName in topNames:
            if topName == '81BOM':
                findArr.append(topName)
                print(findArr)
            if '70 HSG Bin' in topName:
                findArr.append(topName)
                print(findArr)
            if '70 MLB Bin' in topName:
                findArr.append(topName)
                print(findArr)
        return findArr

    '''去取字符串左右空格'''
    def qukongge(self, lieDatas):
        allData_strip = []
        for allData in lieDatas:
            if isinstance(allData, str):
                # print(allData.strip())
                allData_strip.append(allData.strip())
            else:
                # print(allData)
                allData_strip.append(allData)
        return allData_strip


if __name__ == '__main__':
    filepath = '../HSGtotest81_zh2.xlsx'
    oxl = OpenMyXLS(filepath)