How to copy over an Excel sheet to another workbook in Python
Solution 1
A Python-only solution using the openpyxl
package. Only data values will be copied.
import openpyxl as xl
path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'
wb1 = xl.load_workbook(filename=path1)
ws1 = wb1.worksheets[0]
wb2 = xl.load_workbook(filename=path2)
ws2 = wb2.create_sheet(ws1.title)
for row in ws1:
for cell in row:
ws2[cell.coordinate].value = cell.value
wb2.save(path2)
Solution 2
A solution that uses the pywin32
package to delegate the copying operation to an Excel application. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows machine that has MS Excel installed.
from win32com.client import Dispatch
path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'
xl = Dispatch("Excel.Application")
xl.Visible = True # You can remove this line if you don't want the Excel application to be visible
wb1 = xl.Workbooks.Open(Filename=path1)
wb2 = xl.Workbooks.Open(Filename=path2)
ws1 = wb1.Worksheets(1)
ws1.Copy(Before=wb2.Worksheets(1))
wb2.Close(SaveChanges=True)
xl.Quit()
Solution 3
A solution that uses the xlwings
package to delegate the copying operation to an Excel application. Xlwings is in essence a smart wrapper around (most, though not all) pywin32
/appscript
excel API functions. Data values, formatting and everything else in the sheet is copied. Note: this solution will work only on a Windows or Mac machine that has MS Excel installed.
import xlwings as xw
path1 = 'C:\\Users\\Xukrao\\Desktop\\workbook1.xlsx'
path2 = 'C:\\Users\\Xukrao\\Desktop\\workbook2.xlsx'
wb1 = xw.Book(path1)
wb2 = xw.Book(path2)
ws1 = wb1.sheets(1)
ws1.api.Copy(Before=wb2.sheets(1).api)
wb2.save()
wb2.app.quit()
Copy excel sheet from one worksheet to another in Python
In the end I did this:
def copyExcelSheet(sheetName):
read_from = load_workbook(item)
#open(destination, 'wb').write(open(source, 'rb').read())
read_sheet = read_from.active
write_to = load_workbook("Master file.xlsx")
write_sheet = write_to[sheetName]
for row in read_sheet.rows:
for cell in row:
new_cell = write_sheet.cell(row=cell.row, column=cell.column,
value= cell.value)
write_sheet.column_dimensions[get_column_letter(cell.column)].width = read_sheet.column_dimensions[get_column_letter(cell.column)].width
if cell.has_style:
new_cell.font = copy(cell.font)
new_cell.border = copy(cell.border)
new_cell.fill = copy(cell.fill)
new_cell.number_format = copy(cell.number_format)
new_cell.protection = copy(cell.protection)
new_cell.alignment = copy(cell.alignment)
write_sheet.merge_cells('C8:G8')
write_sheet.merge_cells('K8:P8')
write_sheet.merge_cells('R8:S8')
write_sheet.add_table(newTable("table1","C10:G76","TableStyleLight8"))
write_sheet.add_table(newTable("table2","K10:P59","TableStyleLight9"))
write_to.save('Master file.xlsx')
read_from.close
With this to check if the sheet already exists:
#checks if sheet already exists and updates sheet if it does.
def checkExists(sheetName):
book = load_workbook("Master file.xlsx") # open an Excel file and return a workbook
if sheetName in book.sheetnames:
print ("Removing sheet",sheetName)
del book[sheetName]
else:
print ("No sheet ",sheetName," found, will create sheet")
book.create_sheet(sheetName)
book.save('Master file.xlsx')
with this to create new tables:
def newTable(tableName,ref,styleName):
tableName = tableName + ''.join(random.choices(string.ascii_uppercase + string.digits + string.ascii_lowercase, k=15))
tab = Table(displayName=tableName, ref=ref)
# Add a default style with striped rows and banded columns
tab.tableStyleInfo = TableStyleInfo(name=styleName, showFirstColumn=False,showLastColumn=False, showRowStripes=True, showColumnStripes=True)
return tab
How to copy data from One Excel sheet to another sheet for same Workbook Using Python?
You'll need to transform data as you need and store it another sheet :
#Columns in student.xlsx : Name,Gender,DOB,Maths,Physics,Chemistry,English,Biology,Economics,History,Civics
import pandas as pd
pd.read_excel(open('student.xlsx', 'rb'),
sheet_name='Sheet1')
af = df.melt(id_vars="Name","Gender","DOB"],var_name="Subject",value_name="Marks")
result = af.sort_values(by="Name")
result.to_excel('student.xlsx,sheet_name='Sheet2')
Related Topics
Most Pythonic Way to Interleave Two Strings
How to Write Data into CSV Format as String (Not File)
Accessing a Value in a Tuple That Is in a List
String Formatting: Columns in Line
Cython: "Fatal Error: Numpy/Arrayobject.H: No Such File or Directory"
Extract Email Sub-Strings from Large Document
How to Read File with Space Separated Values in Pandas
Save Results to CSV File with Python
Image Segmentation Based on Edge Pixel Map
Detecting Mouse Clicks in Windows Using Python
How to Set the Default Color Cycle for All Subplots with Matplotlib
Sort a Pandas Dataframe Series by Month Name
Using Multiple Python Engines (32Bit/64Bit and 2.7/3.5)
How Can Pyspark Be Called in Debug Mode
Executing Command Line Programs from Within Python
Using Win32Com with Multithreading
Sql-Like Window Functions in Pandas: Row Numbering in Python Pandas Dataframe