kayotin
kayotin

Reputation: 1

how to copy a excel sheet by xlrd,xlwt,xlutils with formatting?

I can do it by openpyxl, but it is slow. so I want to ask if is it OK to copy an Excel sheet by xlrd,xlwt,xlutils to a new workbook.

Need to include all formatting information, including merged cells, row numbers, column widths, borders, cell formatting

you can see my openpyxl code below:

from copy import copy
import openpyxl
from openpyxl.workbook import Workbook
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import get_column_letter
import pathlib


def copy_sheet(file, save_path):

    workbook = openpyxl.load_workbook(file)  # type:Workbook
    sheet_names = workbook.sheetnames

    new_wb = openpyxl.Workbook()
    for sheet_name in sheet_names:
        source_worksheet = workbook[sheet_name]  # type: Worksheet
        new_worksheet = new_wb.create_sheet(sheet_name)

        # copy styles
        new_worksheet.sheet_format = source_worksheet.sheet_format
        new_worksheet.sheet_properties = source_worksheet.sheet_properties
        new_worksheet.merged_cells = source_worksheet.merged_cells
        new_worksheet.page_margins = source_worksheet.page_margins
        new_worksheet.page_setup = source_worksheet.page_setup
        new_worksheet.print_options = source_worksheet.print_options

        # copy data to new Sheet
        for row in source_worksheet.iter_rows(min_row=1, max_row=source_worksheet.max_row,
                                              min_col=1, max_col=source_worksheet.max_column):
            for cell in row:
                new_cell = new_worksheet.cell(row=cell.row, column=cell.column)
                new_cell.value = cell.value
                new_cell.font = copy(cell.font)
                new_cell.fill = copy(cell.fill)
                new_cell.border = copy(cell.border)
                new_cell.alignment = copy(cell.alignment)
        # copy row height and width
        for i in range(1, source_worksheet.max_column + 1):
            new_worksheet.column_dimensions[openpyxl.utils.get_column_letter(i)].width = \
                source_worksheet.column_dimensions[openpyxl.utils.get_column_letter(i)].width
        for i in range(1, source_worksheet.max_row + 1):
            new_worksheet.row_dimensions[i].height = source_worksheet.row_dimensions[i].height
    new_name = pathlib.Path(file).name
    new_wb.save(f"{save_path}/copied_{new_name}")

My purpose is to sort all sheets of an xls file and then save it to a new workbook, which needs to keep the original formatting intact. The sorting is based on the Q12 cell of each sheet. I have solved the sorting problem, now the problem is how do I copy the sheet with the format

Upvotes: 0

Views: 298

Answers (0)

Related Questions