Expand sheet row

De Wiki Clusterlab.com.br
Revisão de 21h41min de 20 de dezembro de 2021 por Damato (discussão | contribs)
Ir para navegação Ir para pesquisar

Input

A B C D
a1 b1 c1 d1a
d1b
d1c

Source

from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
class OutputSheet:
    def __init__(self, filename):
        self.filename = filename
        self.wbc = Workbook()
    def create_title(self,title):
        self.ws1 = self.wbc.create_sheet(title=title)
    def inserir(self,row,col,data):
        self.ws1.cell(column=col,row=row,value=data)
    def save(self):
        self.wbc.save(filename=self.filename)
class SourceSheet:
    def __init__(self, filename):
        try:
            self.wbr = load_workbook(filename=filename, read_only=True)
        except FileNotFoundError as e:
            print(f"File Not Found {filename}, MSG={e}")
    def get_sheet_name(self):
        return self.wbr.sheetnames
    def expand_Col(self, rowdata, index, lenght, incomingdata=[]):
        localexpanded = []
        for column in str(rowdata[index].value).split("\n"):
            localdata = incomingdata + [column]
            if index == lenght:
                localexpanded.append(localdata)
            else:
                returneddata = self.expand_Col(rowdata, index + 1, lenght, localdata)
                if returneddata != None:
                    localexpanded = localexpanded + returneddata
        return localexpanded

    def get_sheet_data(self,sheet_name):
        try:
            sh = self.wbr[sheet_name]
            data = []
            for r in sh.rows:
                data = data + self.expand_Col(rowdata=r, index=0, lenght=len(r) - 1)
            return data
        except KeyError as e:
            print(f"Fail to read tab with kyname { sheet_name}, MSG={e}")
        except Exception as e:
            print(f"General exception, MSG=e")

if __name__ == '__main__':
    ss = SourceSheet(filename='source.xlsx')
    os = OutputSheet('output.xlsx')
    os.create_title('output')
    datas = ss.get_sheet_data('source_tab_name')
    try:
        for d in datas:
            print(d)
        drow = 0
        for row in datas:
            drow = drow + 1
            dcol = 0
            for col in row:
                dcol = dcol + 1
                os.inserir(row=drow, col=dcol, data=col)
        os.save()
    except TypeError as e:
        print(f"Fail to print row, MSG={e}")
    except Exception as e:
        print(f"General exception, MSG={e}")