Python sorts the sheet s in the table [openpyxl] work skill sorting series

Good morning, everyone. My surname is Wu. If you think the article is OK, you can also call me Miss Wu. Welcome to the world of data analysis and study with me!

Interested friends can pay attention to me Data analysis column , there are many high-quality articles to share with you.

The first method

from openpyxl import load_workbook

workbook = load_workbook('excel.xlsx')
workbook._sheets = sorted(workbook._sheets)

You can try it online. It's useless for me to try it.

The second method

This method is developed by myself. The main idea is to copy each sheet in the original table into another new table according to the order we want, so as to achieve the purpose of sorting, but it will also produce a new table.

def copy_sheet(wb, wb2, sheetnames):
	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
        
        # Scale
        sheet2.views.sheetView[0].zoomScale = sheet.views.sheetView[0].zoomScale

        # Start processing the merged cells in the form of "(< cellrange A1: A4 >,), replace (< cellrange and >,) 'and find the merged cells
        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)

Where sheetnames is the sheet order list you want.

Disadvantages: if there are too many sheet s or too much content, the running time will be too long.

Concluding remarks

After reading this article, there are more knowledge points to share with you. Take your time to find ha, which is the link below.


Recommended columns

👨‍👩‍👦‍👦 Machine learning: sharing machine learning practical projects and explanation of common models
👨‍👩‍👦‍👦 Data analysis: share data analysis, practical projects and common skills

Review of previous contents

💚 Learn a full set of Python code [super detailed] Introduction to python, core syntax, data structure, advanced Python [to you who want to learn Python well]
❤️ Learn the full set of pandas code [super detailed] data viewing, input and output, selection, integration, cleaning, conversion, remodeling, mathematical and statistical methods and sorting
💙 Learn the full set of pandas code [super detailed] box operation, grouping aggregation, time series and data visualization
💜 Learn the basic operation, data type, array operation, copy and attempt, index, slice and iteration, shape operation, general function and linear algebra of NumPy full set of code [super detail]


Pay attention to me and learn more about it!

CSDN@Report, I also have to study hard today

Keywords: Python Data Analysis openpyxl

Added by acroporas on Fri, 11 Feb 2022 21:15:21 +0200