Ability to copy worksheets from different workbooks

Issue #325 duplicate
Vinod Kandasamy created an issue

As mentioned in #323, copying worksheets from different workbooks is not supported.

Can this ability be added in a future release?

Comments (10)

  1. CharlieC

    Well, currently add_sheet is essentially an internal method used when creating sheets. I guess that it could be changed to support what you need but I don't see that happening unless you submit a pull request.

  2. Vinod Kandasamy reporter

    Here's the add_sheet method:

    def add_sheet(self, worksheet, index=None):
        """Add an existing worksheet (at an optional index)."""
        if not isinstance(worksheet, self._worksheet_class):
            raise TypeError("The parameter you have given is not of the type '%s'" % self._worksheet_class.__name__)
    
        if index is None:
            self.worksheets.append(worksheet)
        else:
            self.worksheets.insert(index, worksheet)
    

    It looks like a simple insert / append operation to the list self.worksheets. If inserting worksheets from a different workbook is not supported, or if it results in 'unreadable content', I think the changes needed to make this work could be quite involved.

    I don't think I have the expertise or the time to delve into code to figure out the 'unreadable content' error in #323, but can we keep this bug open so that someone else or I may be able to submit a pull request at some point in the future? This bug currently seems to be closed as a duplicate of #323.

  3. CharlieC

    Yes, it should probably be a private method that is used by create_sheet. Otherwise it gives a false impression of what's possible.

    The bug is staying closed because it is essentially continuing the discussion of 323. And open source works best by people scratching their own itches. Copying the worksheet involves copying all the cells, styles, charts, drawings, etc. to the new workbook. Most of this might be possible with the standard copy module.

    In the meantime we might raise an exception when trying to do what you did so that you find out straightaway that it doesn't work and not when you try and open the file.

  4. Vinod Kandasamy reporter

    I could have been wording it incorrectly all this while. The reason I used the word 'copy' is because it resembles the copy worksheet action in the Excel GUI. What I actually intended to do, was not to create independent copies of a worksheet in memory, but to reference the same worksheet in two different workbooks, and save each workbook independently so that two copies of the same worksheet exist in the file system. I'd be okay with even moving a worksheet from one workbook to another (i.e., deleting all references to the worksheet from the former workbook).

    That shouldn't require copying all cells, styles, etc., right? Given that I get the 'unreadable content' error right now, would you have any suggestions on what parts of the code I should look at modifying to do this operation the right way?

  5. CharlieC

    Referencing the same worksheet in different workbooks would be even harder. Workbooks are top of the object tree in our world. There is provision for external references but our reference support is currently extremely limited and fragile. The specification doesn't really help either. Not that the specification helps much anywhere. Copying is probably easier to do.

    The unreadable content error isn't much use I'm afraid. The reports that Excel generates are often misleading. The Office Open SDK tool is much useful for this sort of thing. I suspect the structure of the file is slightly garbled. Excel complains and just ignores what it doesn't understand.

  6. Eric Gazoni

    @charlie_x regarding the possible confusion about these methods (even I have trouble sometimes between add_sheet and create_sheet), I think we could clean up a bit the public methods available in workbook and worksheet. These come from the early days of the project and should go away.

  7. Tim Rausch

    It's not supported but you can use win32com from Python

    from win32com.client import DispatchEx
    excel = DispatchEx('Excel.Application')
    wbP=excel.Workbooks.Open(r'C:\Temp\Junk\Temp.xlsx')
    wbG=excel.Workbooks.Open(r'C:\Temp\Junk\Temp2.xlsx')
    wbG.Worksheets('TAA2').Copy(Before=wbP.Worksheets("TAA"))
    wbP.SaveAs(r'C:\Temp\Junk\Temp.xlsx')
    excel.Quit()
    del excel # ensure Excel process ends
    
  8. CharlieC

    @trausch69 Thanks but win32com isn't always available. In any case I'd now recommend xlwings over win32com

  9. Log in to comment