Expand sheet row

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

Input

A B C D
a1 b1 c1 d1a
d1b
d1c
a1 b1 c1a
c1b
c1c
d1
a4
a4z
b1a
b1b
b1c
b1d
c1a
c1b
c1c
d1a
d1b
d1c
a1 b1 c1 d1

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}")