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:
        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('>,)', '')

        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)
           = copy.copy(
                    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') = 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.

Concluding remarks

After reading this article, there are more points of knowledge to share with you oh, slowly find yourself, just link below.

Columns of Recommended Concern

👨‍👩‍👦‍👦 Machine Learning: Share the Explanation of Machine Learning Projects and Common Models
👨‍👩‍👦‍👦 Data analysis: Share data analysis of real-world projects and collation of common skills

Future Content Review

💚 Learn Python full set of code [Ultra Detail] Get started with Python, core grammar, data structures, Python advances [to the person who wants to learn Python well]
❤️ Learn the pandas full set of codes [Ultra Detail] Data viewing, input and output, selection, integration, cleaning, conversion, remodeling, mathematical and statistical methods, sorting
💙 Learn pandas full set of codes [Ultra Detail] Boxed operations, group aggregation, time series, data visualization
💜 Learn the NumPy code suite Basic operations, data types, array operations, replication and attempts, indexes, slicing and iteration, shape operations, general functions, linear algebra

Pay attention to me and learn more about it!

CSDN@Report, I also have a good study today

Topics: Python Data Analysis pandas openpyxl