How to Copy a Worksheet in Excel using Python Program

#python #excel #programming #tutorial

When you need to create a worksheet that is similar to another one in structure or format, you can directly copy the latter. This method can help you save a significant amount of time by avoiding repetitive work and minimizing input errors. This article will demonstrate how to use Python programs to copy worksheets in Excel files.

Tool

Visual Studio Code
Spire.XLS for Python

This is an Excel library supporting processing Excel document easily on Python platforms, such as creating Excel, reading Excel, converting Excel to image.

Installation

You can install Spire.XLS for Python in Visual Studio Code by performing the following steps:

First, make sure that you have download and install Python.
Open VS Code, click “Extensions”, search for “Python” and then install it.
Click “Explorer” > “NO FOLRDER OPENED” > “Open Folder”.
Choose a desired folder.
Add a “.py” file to this folder and name it whatever you like.
Click “Terminal” > “New Terminal”.
Last, input the following commands

pip install Spire.XLS-for-Python
pip install plum-dispatch==1.7.4

Sample Code

Copy a Worksheet in the Same Workbook

If you need to copy a worksheet in the same workbook, please refer to the following code.In this code, you can add a new worksheet to the workbook with Workbook.Worksheets.Add() method and copy the content from the original sheet to the new one with Worksheet.CopyFrom(Worksheet worksheet) and Workbook.CopyTheme (Workbook srcWorkbook) methods.

from spire.xls import *
from spire.common import *

#Initialize an instance of the Workbook class
workbook = Workbook()
#Load an Excel workbook
workbook.LoadFromFile("Sample 1.xlsx")

#Get the first worksheet
sourceSheet = workbook.Worksheets[0]

sheetName = sourceSheet.Name + "Copy"

#Add a new worksheet with a specific name to the workbook
destSheet = workbook.Worksheets.Add(sheetName)

#Copy the first worksheet to the newly added worksheet
destSheet.CopyFrom(sourceSheet)

#Save the result workbook to another file
workbook.SaveToFile("Result 1.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Copy a Worksheet to Another Workbook

Likewise, you are also allowed to copy a worksheet from a workbook to another one using the same methods. You just need to create a new workbook and clear the default sheets in it before copying.

from spire.xls import *
from spire.common import *

#Initialize an instance of the Workbook class
sourceWorkbook = Workbook()

#Load the source Excel workbook
sourceWorkbook.LoadFromFile("Sample 1.xlsx")

#Get the first worksheet of the source workbook
sourceSheet = sourceWorkbook.Worksheets[0]

#Get the name of the first worksheet
sheetName = sourceSheet.Name + "_Copy"

#Initialize an instance of the Workbook class
destWorkbook = Workbook()

 #Load the destination Excel workbook
destWorkbook.LoadFromFile("Sample 2.xlsx")

#Add a new worksheet with a specific name to the destination workbook
destSheet = destWorkbook.Worksheets.Add(sheetName)

#Copy the first worksheet of the source workbook to the new worksheet of the destination workbook
destSheet.CopyFrom(sourceSheet)

#Copy the theme from the source workbook to the destination workbook
destWorkbook.CopyTheme(sourceWorkbook)

#Save the destination workbook to another file
destWorkbook.SaveToFile("Result 2.xlsx", ExcelVersion.Version2013)
sourceWorkbook.Dispose()
destWorkbook.Dispose()

DEV Community