How to select the whole sheet in xlwings especially include all charts

Here is the question I asked before:

How to select the whole sheet in xlwings

I am using xlwings. I want to copy the whole wb1.sheets(1) and paste to wb.sheets(1) A4 cell. Currently I have to set a very large cell Z100000. Is there any generic way to select the whole sheet rather than the unsafe region A1:Z10000?

import xlwings as xw
path = r'C:\Users\Desktop\test.xlsx'
app=xw.App(visible=False,add_book=False)
wb1 = app.books.open(path)
wb = app.books.add()
wb1.sheets(1).range('A1:Z10000').copy(wb.sheets(1).range('A4'))

And someone gave the solution:

import xlwings as xw

path = r"test.xlsx"

wb = xw.Book(path)
ws = wb.sheets[0]

ws.used_range.select()

But the problem is that used_range ignores charts, pictures, and shapes, how could I improve it to include all things.

Solution

I think there are two options:

Option 1

The first option is to copy the sheet across from the old workbook to the new workbook and then to insert some rows and columns to position the start of the worksheet to where you want it. So let's say you wanted to paste the contents to cell B4 (I'm choosing B4 instead of A4 to show columns as well)

I would:

copy the worksheet across;
insert 3 rows up the top;
insert a column to the left.

This has the effect of (a) copying all data, charts and shapes across, and (b) getting the sheet content to start at cell B4 (the charts and shapes will shift across by default).

In my mind this is the easiest solution and is what I would try unless there is a clear reason why this approach would not work.

As an example:

import xlwings as xl

FromBook = xl.Book(path+'FromBook.xlsx')
ToBook = xl.Book()

FromBook.sheets(1).copy(ToBook.sheets(1))

# Move so start cell is 'B4'
ToBook.sheets(1).range('1:3').insert(shift='down', copy_origin='format_from_left_or_above')
ToBook.sheets(1).range('A:A').insert(shift='right', copy_origin='format_from_left_or_above')

Option 2

If you want to explicitly find the used area in a sheet that contains charts and shapes, as you have noted in your question, used_range won't include charts or shapes and there is no equivalent that does. In this case, you have to do this manually by looping through the shapes in the sheet and finding the location (in pixels) of the right-most shape and the bottom-most shape and then find the cell that is slight below and to the right of this.

Below is a working example that:

finds the position (in pixels) of the bottom-right corner of a region that contains all shapes;
finds the position (in pixels) of the bottom-right corner of data;
takes the maximum of these to get the outer-most range of the sheet (in pixels);
where there are shapes beyond the data range, finds the bottom-right cell that is just beyond the outer-most shape (by starting from the end of the data range and calculating the width of cells as it moves right and the height of cells as it moves down)
copies the expanded range to cell B4

If doing this regularly you would want to tidy this up by moving code to functions and checking if the sheet has shapes (no point doing this if it doesn't have shapes). I will leave this to you, as I would just do option 1 which is easier...

FromBook = xl.Book(path+'FromBook.xlsx')
FromSheet = FromBook.sheets['Sheet1']

ToBook = xl.Book()


## 1. Find right and bottom most position of shapes (which includes charts)

shapesRightPos = 0
shapesBottomPos = 0

for shp in FromSheet.shapes:
    RightPos = shp.left + shp.width
    BottomPos = shp.top + shp.height
    shapesRightPos = max(shapesRightPos, RightPos)
    shapesBottomPos = max(shapesBottomPos, BottomPos)


## 2. Find right and bottom most position of data

cellsRightPos = FromSheet.used_range.last_cell.left + FromSheet.used_range.last_cell.width
cellsBottomPos = FromSheet.used_range.last_cell.top + FromSheet.used_range.last_cell.height


## 3. Find right and bottom most position of worksheet

sheetRightPos = max(cellsRightPos, shapesRightPos)
sheetBottomPos = max(cellsBottomPos, shapesBottomPos)


## 4. If shapes are beyound data range, find where the last cell would be

maxSearch = 1000

if sheetRightPos > cellsRightPos:
    for i in range(0,maxSearch):
        cellRightPos = FromSheet.used_range.last_cell.offset(0,i).left + FromSheet.used_range.last_cell.offset(0,i).width
        if cellRightPos > sheetRightPos:
            sheetEndCell = FromSheet.used_range.last_cell.offset(0,i).address
            break

if sheetBottomPos > cellsBottomPos:
    for i in range(0,maxSearch):
        cellBottomPos = FromSheet.range(sheetEndCell).offset(i,0).top +  FromSheet.range(sheetEndCell).offset(i,0).height
        if cellBottomPos > sheetBottomPos:
            sheetEndCell = FromSheet.range(sheetEndCell).offset(i,0).address
            break

## 5. Copy and paste to 'B4'

FromSheet.range('A1:'+sheetEndCell).copy(ToBook.sheets(1).range('B4'))