21

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--
  • 25,431
  • 8
  • 61
  • 93
KaliMa
  • 1,970
  • 6
  • 26
  • 51
  • 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

5 Answers5

65

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))

wb1.Close(SaveChanges=False)
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
  • 8,003
  • 5
  • 26
  • 52
  • 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
  • 7
    @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
  • still looking for solution, that will work in Linux. As i can see 2nd and 3rd solutions works in Windows only, while 1st solution will not copy a whole sheets, that important for example for embedded images. – Vik Ermolenko Dec 06 '22 at 11:06
  • I am trying to implement solution 2 for my local xlsx file. When I close the file via wb1.Close(SaveChanges=True), I am getting an error from ms-excel: file is allready in use, try another time. I am only able to save it, after I have closed my python ide. Any suggestions? – Leonhard Geisler Aug 25 '23 at 13:23
8

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,280
  • 12
  • 28
  • 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
  • 4
    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
  • 24,970
  • 5
  • 40
  • 69
Josh Herzberg
  • 318
  • 1
  • 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
  • 21
  • 3
0

Solution 1 is the only approach that works in Linux and thus is suited for server automatization. Openpyxl, however, only preserves the formatting for cells that contained data in the template.

Thus, openpyxl needs some tweaking in the template: Fill the template with spaces (' ') for all cells that might get populated later on. Doing this for millions of rows will explode Excel, but dealing with data variations of thousands of rows works fairly well. And ' ' are for most purposes invisible and cause no issues, if less rows populated. Other dummy data in contrast might.

UCCH
  • 1
  • 1