How to write table to different sheet in openpyxl

[Pedroski55]

I don't know what a sheet object is. I don't think openpyxl has a transpose function, but you can make your own function for that

This will copy columns and write them as rows, leaving the actual source file untouched.
In my case, I have a datetime in column 2 of the source file. Transposed you need to adjust the column width to display nicely. openpyxl can do that.

def myApp():
    import openpyxl

    # the folder with the XL file
    # the source sheet has 26 rows and 4 columns
    # the target sheet will have 4 rows and 26 columns
    path2XL = '/home/pedro/myPython/openpyxl/Delta_blanko.xlsx'
    savepath = '/home/pedro/myPython/openpyxl/Delta_blanko_transposed.xlsx'
    sourceFile = openpyxl.load_workbook(path2XL)    
    sourceSheetNames = sourceFile.sheetnames
    for sheet in sourceSheetNames:
        print('This XL has the sheets:', sheet)
    
    new_sheet_name = input('Enter the name of the new sheet you want to create ... ')
    # in case the new sheet name already exists
    while new_sheet_name in sourceSheetNames:
        print('This sheet name exists, please choose a different name ... ')
        new_sheet_name = input('Enter the name of the new sheet you want to create ... ')

    new_sheet = sourceFile.create_sheet(new_sheet_name)
    # read the sheets again to get the new sheet
    sourceSheetNames = sourceFile.sheetnames
    for sheet in sourceSheetNames:
        print('This XL has the sheets:', sheet)

    # acquire the sheet names for source and target
    # maybe there are a lot of sheets
    source_sheet = input('what sheet do you want to copy? ')
    target_sheet = input('What is the name of the sheet you want to copy to? ')

    # a function to read columns and write the values to rows
    # this could be optimized I think, can't see how right now
    def copyCol2Row(colNum):
        col_values = []
        for rowNum in range(1, maxRowSourceFile + 1): # +1 to get the last row            
            # get column 1 first then continue
            value = sourceFile[source_sheet].cell(row=rowNum, column=colNum).value
            col_values.append(value)
        for i in range(len(col_values)):
            sourceFile[target_sheet].cell(row=colNum, column=i+1).value=col_values[i]        
        
    # get the maxes for rows and columns
    maxColSourceFile = sourceFile[source_sheet].max_column
    maxRowSourceFile = sourceFile[source_sheet].max_row
    # loop through the columns and pass them to copyCol2Row(colnr)
    for colnr in range(1, maxColSourceFile + 1): # +1 to get the last col
        copyCol2Row(colnr)
        
    # openpyxl does not have a file.close() open files remain open until your program is finished    
    sourceFile.save(savepath)
    print('Data copied from sheet', source_sheet, 'to the sheet', target_sheet, 'transposed and saved as', savepath)
    print('All done!')