18

I have a string with a sourcefile path and another string with a destfile path, both pointing to Excel workbooks.

I want to take the first sheet of the sourcefile and copy it as a new tab to the destfile (doesn't matter where in the destfile), then save it.

Couldn't find an easy way in xlrd or xlwt or xlutils to do this. Am I missing something?

M--
  • 20,766
  • 7
  • 52
  • 87
KaliMa
  • 1,870
  • 6
  • 20
  • 47
  • Two questions: (1) Does it suffice to have only data values copied, or do you also need to copy formatting of cells? (2) Do you need a Python-only solution, or can Python code that starts and controls an Excel application be used? – Xukrao Jun 16 '17 at 18:19
  • @Xukrao (1) Yes, values suffice (2) Python-only; I have a solution using Excel VBA already but I'd like to move it to Python so I don't have to use two processes, unless you have a Python solution that calls a specific subroutine of a specific .xlsm file? – KaliMa Jun 16 '17 at 18:23

4 Answers4

53

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()
Xukrao
  • 6,984
  • 4
  • 25
  • 50
  • How can I mention the specific sheet name of the excel file from which I want to copy in any of the above examples? – Pete Jul 14 '21 at 10:09
  • 3
    @Pete Solution 1: `ws1 = wb1["Name"]`, Solution 2: `ws1 = wb1.Worksheets("Name")`, Solution 3: `ws1 = wb1.sheets("Name")` – Xukrao Jul 15 '21 at 16:30
7

This might help if you're not opposed to using Pandas

import pandas as pd

#change xxx with the sheet name that includes the data
data = pd.read_excel(sourcefile, sheet_name="xxx")

#save it to the 'new_tab' in destfile
data.to_excel(destfile, sheet_name='new_tab')

Hope it helps

Bubble Bubble Bubble Gut
  • 3,152
  • 11
  • 27
  • I would prefer to stay within the confines of what I already have installed since it takes forever for me to be able to install new packages due to account restrictions – KaliMa Jun 16 '17 at 16:23
  • Tried this on a laptop that does have it installed, and this code doesn't work (read_excel isn't an attribute ) – KaliMa Jun 16 '17 at 17:04
  • 1
    @KaliMa I'm pretty sure it is an attribute, documentation [here](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html). I've been using it multiple times – Bubble Bubble Bubble Gut Jun 16 '17 at 17:08
  • whichever the case may be, that's the error it's throwing for me, and based on a Google search it does appear to be a common error, but none of the fixes seem to work and I don't want to make one problem into several here, is there a non-pandas solution? – KaliMa Jun 16 '17 at 17:15
  • This solution will work. Ding is trying to help you. Also, ```pandas``` uses ```xlrd``` and ```xlwt``` to do the excel IO. So you should be able to do what you're are asking using only those modules. – TrigonaMinima Jun 16 '17 at 17:46
  • @TrigonaMinima Where was I being rude? All I did was point out that this wasn't working for me and why. – KaliMa Jun 16 '17 at 18:00
  • Can I copy sheets with formulae from one workbook to another with this? – Athul Muralidharan Sep 16 '18 at 06:49
  • 3
    Excel sheet formats are missing when copy the excel sheet. How to keep the format also. – Vineesh TP Sep 02 '19 at 13:22
1

You could also try xlwings. 

import xlwings as xw
wb = xw.Book(r'C:\path\to\file.xlsx')
sht = wb.sheets['Sheet1']
new_wb = xw.Book(r'C:\new_path\to\file.xlsx')
new_wb.sheets['name'] = sht
Acorn
  • 23,483
  • 4
  • 35
  • 66
Josh Herzberg
  • 198
  • 13
0

Long battle and finally got the answer. From xlwings source code: https://github.com/xlwings/xlwings/pull/1216/files

source_sheet.range.copy(destination_sheet.range)

In other words:

wb.sheets['Sheet1'].range('A1:A6').copy(wb.sheets['Sheet2'].range('A1:A6'))
  • It works also from one workbook to another workbook.
  • A range of cells must be provided. Just ensure the range is big enough to cover the full worksheet.
  • The copy function copy/paste everything withing a range of cells (values, cell format, hyperlinks, cell type, ...)
Jordi
  • 11
  • 2