Reputation: 1
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