How to write table to different sheet in openpyxl

[rsearing]

I tried using Pandas but am getting errors that I don't understand quite yet when trying to import the file. I am able, however, to import the file easily using openpyxl.

I have a very large sheet that has header data. It also contains a table that I'll need to transpose up towards the top and then the main table starts on a row (39). I am able to import the sheet and then run:

sheet_obj = wb_obj.active
sheet_obj.delete_rows(1,39)

I want to be able to write the new object to a new sheet (I called "test") in the same work book so that I can test what I'm deleting. (Eventually, I will be exporting this to MySQL, but wanting to see the contents of the table as sort of a debugging effort. I am unable to figure out how to write the sheet_obj to the other sheet. I'm sure this is simple....

Two questions:
1) How to write a sheet object to a NEW sheet
2) Is there a simple way to transpose the object? (I saw that pandas has a wb.T method - does openpyxl have something similar?

THANK YOU SO much! I'm very new to python and learning all of this on the fly.

Sincerely,
Rob

[Larz60+]

this should help: https://stackoverflow.com/a/68800310

[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!')