Two methods for Python to copy all sheet s in a table to another table [openpyxl / pandas] Practical Work Skills Sorting Series

Posted by ThinkGeekness on Mon, 07 Feb 2022 18:46:52 +0100

Good morning, everyone. My name is Wu. If you think the article is well written, you can also call me Mr. Wu. Welcome to join me in the world of data analysis and learning!

Interested friends can follow me Data Analysis Column There are many good articles to share with you.

Front End Time encountered a problem while working - how to copy all sheet s in one table to another because they were sorted before openpyxl collation I've already said how to copy a sheet from a table into another sheet in this blog post, so this time I naturally came up with the idea.

Here I enclose this code:

def copy_sheet(wb, wb2):
    sheetnames = wb.sheetnames
    for sheetname in sheetnames:
        print(sheetname)
        sheet = wb[sheetname]
        sheet2 = wb2.create_sheet(sheetname)

        # tab color
        sheet2.sheet_properties.tabColor = sheet.sheet_properties.tabColor

        wm = list(sheet.merged_cells)
        if len(wm) > 0:
            for i in range(0, len(wm)):
                cell2 = str(wm[i]).replace('(<CellRange ', '').replace('>,)', '')
                sheet2.merge_cells(cell2)

        for i, row in enumerate(sheet.iter_rows()):
            sheet2.row_dimensions[i+1].height = sheet.row_dimensions[i+1].height
            for j, cell in enumerate(row):
                sheet2.column_dimensions[get_column_letter(j+1)].width = sheet.column_dimensions[get_column_letter(j+1)].width
                sheet2.cell(row=i + 1, column=j + 1, value=cell.value)

                # format cell
                source_cell = sheet.cell(i+1, j+1)
                target_cell = sheet2.cell(i+1, j+1)
                target_cell.fill = copy.copy(source_cell.fill)
                if source_cell.has_style:
                    target_cell._style = copy.copy(source_cell._style)
                    target_cell.font = copy.copy(source_cell.font)
                    target_cell.border = copy.copy(source_cell.border)
                    target_cell.fill = copy.copy(source_cell.fill)
                    target_cell.number_format = copy.copy(source_cell.number_format)
                    target_cell.protection = copy.copy(source_cell.protection)
                    target_cell.alignment = copy.copy(source_cell.alignment)

I personally feel that this code meets the needs of the title in the most extreme situations, but it also has a big drawback - it takes too long.

If there are only one or two sheets in a table, or if there is very little content in each sheet, this method will naturally work fine. But the problem is that in a real work scene, where are there so simple requirements (or why do you have to write a piece of code for such simple requirements). So this method can only provide you with an idea reference, but it is not recommended for you to solve practical problems.

So I thought for a long time and finally found a practical way.

raw_excel = 'excel_raw.xlsx'
workbook = load_workbook(raw_excel)
writer = pd.ExcelWriter(tmp_file, engine='openpyxl')
writer.book = workbook

This is a really time-consuming scenario compared to the previous one, but it works mostly in scenarios where pandas are used together, and it works well for most of our data direction practitioners.