Copy a Named Range from one workbook to another

[jamieMacabacus]

Howdy!
I have a named range A in workbook X. I want to copy all the data (values and format) of range A into workbook Y. I have tried to use PostWorksheetCellsRangesCopyRequest but I keep getting errors. My current flow is:

• upload a new file (workbook X, which contains range A)
• create a new workbook Y in aspose storage
• call PostWorksheetCellsRangesCopyRequest (this is the step that is failing).

Is there some precursor step that I am missing? Like, do I need to create a new range B in workbook Y before I can copy over range A?

Perhaps my greatest issue is that I am not entirely sure what the RangeOperate parameter should look like. The example in github didn’t seem to work for me. I can see the various components (operate: str, source: Range, target: Range, paste_options: PastOptions) but I am not clear what these components should look like. Some questions off the top of my head would be:

• What are the valid flags for operate and what does each flag mean? In github I see ‘copy’ as a possible option, but I assume there are others?
• Looking at the Range type, I don’t see any options for specifying which workbook the range is in. Only worksheet. Additionally, what is the ‘refers_to’ type? What should go there?
• I’m pretty lost entirely when it comes to PasteOptions. I can see the components (only_visible_cells, paste_type, skip_blanks, transpose), and I can guess at their meanings, but I can’t seem to find any documentation on what these flags mean/do.

Sorry if any of these questions are completely clueless lol. I sincerely appreciate any guidance y’all could provide.

[Professionalize.Discourse]

@jamieMacabacus

To copy a named range from one worksheet to another using Aspose.Cells, you can utilize the PostWorksheetCellsRangesCopyRequest method. Here’s a detailed guide to help you through the process, including the necessary parameters and their meanings.

Steps to Copy a Named Range

1. Upload the Source Workbook: Ensure that your source workbook (Workbook X) is uploaded to the Aspose storage.

2. Create the Target Workbook: You need to create a new workbook (Workbook Y) in Aspose storage where you will copy the data.

3. Define the Source and Target Ranges:

   • You need to specify the source range (the named range you want to copy) and the target range (where you want to paste the data in the new workbook).
   • The Range object should include properties like FirstRow, RowCount, FirstColumn, and ColumnCount.

4. Set the RangeOperate Parameter:

   • The RangeCopyRequest object requires the Operate parameter, which can be set to "copydata" to copy both values and formats.
   • The Source and Target properties should be set to the respective Range objects you defined earlier.

5. Make the API Call:

   • Use the PostWorksheetCellsRangesCopyRequest method to execute the copy operation.

Example Code

Here’s a sample code snippet to illustrate the process:

// Initialize the Cells API
CellsApi cellsApi = new CellsApi("your-client-id", "your-client-secret");
string remoteFolder = "TestData/In";
string sourceWorkbookName = "WorkbookX.xlsx"; // Your source workbook
string targetWorkbookName = "WorkbookY.xlsx"; // Your target workbook

// Upload the source workbook
this.UploadFile(sourceWorkbookName, remoteFolder + "/" + sourceWorkbookName, "");

// Define the source range (named range A)
var sourceRange = new Range()
{
    FirstRow = 0, // Adjust based on your named range
    FirstColumn = 0,
    RowCount = 10, // Adjust based on your named range
    ColumnCount = 5 // Adjust based on your named range
};

// Define the target range (where to copy in Workbook Y)
var targetRange = new Range()
{
    FirstRow = 0, // Adjust as needed
    FirstColumn = 0,
    RowCount = 10, // Same as source
    ColumnCount = 5 // Same as source
};

// Create the range copy request
var rangeOperate = new RangeCopyRequest()
{
    Operate = "copydata",
    Source = sourceRange,
    Target = targetRange
};

// Create the request to copy the range
var request = new PostWorksheetCellsRangesCopyRequest(
    name: targetWorkbookName,
    sheetName: "Sheet1", // Specify the sheet name in Workbook Y
    rangeOperate: rangeOperate,
    folder: remoteFolder,
    storageName: ""
);

// Execute the copy operation
this.CellsApi.PostWorksheetCellsRangesCopy(request);

Important Parameters Explained

• Operate: The valid options include "copydata" for copying both values and formats. Other operations may include "copy" or "move", depending on your needs.
• Source and Target Ranges: These should specify the exact rows and columns you want to copy from and to. The refers_to property is typically used to define the named range in the source workbook.
• PasteOptions: This includes options like only_visible_cells, paste_type, skip_blanks, and transpose. You can refer to the API documentation for detailed descriptions of these options.

Troubleshooting

If you encounter errors, ensure that:

• The source workbook is correctly uploaded and accessible.
• The specified ranges are valid and do not exceed the dimensions of the worksheets.
• You have the correct permissions to access and modify the workbooks in Aspose storage.

Feel free to ask if you have more questions or need further clarification on any specific part of the process!

[jamieMacabacus]

Howdy!
I am still a bit confused.

• In the source and target ranges you don’t specify which workbook or worksheet the ranges refer to. I see that you specify targetWorkbook in the PostWorksheetCellsRangesCopyRequest , but it doesn’t look like you are referencing the sourceworkbook anywhere after the upload. How do I tell aspose that I want to copy range A in wookbook X to a new range in workbook Y?
• Also, I’m still a bit confused about the flow. Do I need to create a new named range in workbook Y before I copy over range A? Or can I just copy over range A directly to the new workbook?
• Can you link me to the page in the documentation that explains the different flags for the “operate” parameter? I have not been able to find it.
  – Edit: I have been able to find the different options: copydata, copystyle, copyto, copyvalue. But I am not sure what each option means.
• You mentioned that “refers_to” property refers to the name of the named_range in the source workbook. Does this mean I can use this property instead of specifying the cols and rows?

[xuejianzhang]

@jamieMacabacus Hello, we currently only support range copying within the same sheet. We do not yet support copying data from one workbook to another. We will upgrade the version to support this feature next week.
If you have any new requirements during use, you can also propose them. As long as we can achieve them, we can develop new APIs according to your needs.

[xuejianzhang]

@jamieMacabacus
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSCLOUD-13082

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

[xuejianzhang]

@jamieMacabacus If you just want to save part of the content in workbook x as a new workbook y, you can use GetWorksheetWithFormatRequest to achieve this

CellsApi cellsApi = new CellsApi("your-client-id", "your-client-secret");
UploadFileRequest uploadFileRequest = new UploadFileRequest();

uploadFileRequest.path = "Book1.xlsx";
uploadFileRequest.storageName = "";
uploadFileRequest.UploadFiles = new Dictionary<string, Stream>() { { "Book1.xlsx", File.OpenRead("D:\\cells.cloud.family\\cells.cloud\\src\\testdata\\Cells\\Book1.xlsx") } };
cellsApi.UploadFile(uploadFileRequest);

GetWorksheetWithFormatRequest getWorksheetWithFormatRequest = new GetWorksheetWithFormatRequest();
getWorksheetWithFormatRequest.format = "xlsx";
getWorksheetWithFormatRequest.sheetName = "Sheet1";
getWorksheetWithFormatRequest.name = "Book1.xlsx";
getWorksheetWithFormatRequest.area = "I5:K6";
using (Stream stream =  cellsApi.GetWorksheetWithFormat(getWorksheetWithFormatRequest))
{
    using (Stream downloadFile = File.OpenWrite("C:\\TestData\\cells.cloud\\download\\ExportRangeData.xlsx"))
    {
        stream.CopyTo(downloadFile);
        stream.Close();
        downloadFile.Close();
    }
}

[xuejianzhang]

@jamieMacabacus We have resolved this issue in the latest version. The corresponding API function is PostWorksheetCellsRangesCopy

ReadyFile(Book1Xlsx);
ReadyFile(MyDocumentXlsx);
//set cell value
var url = BuildUrl(string.Format("{0}/worksheets/Sheet1/cells/b2?folder={1}&value=rangecopytest&type=string", Book1Xlsx, TempFolderPath));
string bodyParam = null;
Dictionary<string, string> headParams = null;
Dictionary<string, object> formParams = null;
var response = ApiInvoker.InvokeApiAsync<dynamic>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.AreEqual(HttpStatusCode.OK.ToString(), (string)response.Status.Value);
// copy to
dynamic data = new ExpandoObject();
data.Operate = "CopyTo";
data.Source = new ExpandoObject();
data.Source.FirstRow = 4;
data.Source.FirstColumn = 8;
data.Source.RowCount = 2;
data.Source.ColumnCount = 3;
data.Source.Worksheet = "Sheet1";
data.Target = new ExpandoObject();
data.Target.FirstRow = 4;
data.Target.FirstColumn = 8;
data.Target.RowCount = 2;
data.Target.ColumnCount = 3;
data.Target.Worksheet = "Sheet1";
data.TargetWorkbook = "CellsTests/myDocument.xlsx";
bodyParam = JsonConvert.SerializeObject(data);
url = BuildUrl(string.Format("{0}/worksheets/Sheet1/ranges/copy?folder={1}", Book1Xlsx, TempFolderPath));
response = ApiInvoker.InvokeApiAsync<dynamic>(url, "POST", bodyParam, headParams, formParams).Result;
Assert.AreEqual(HttpStatusCode.OK.ToString(), (string)response.Status.Value);
Download(TempFolderPath + "/" + MyDocumentXlsx, "TestPostWorksheetCellsRangesCopyToOtherWorkbook.xlsx");

Is the skd package you use for net? We will provide the corresponding sdk package.

[jamieMacabacus]

Hi!
Thank you so much for your help, I really appreciate it. I am using the Python SDK

[jamieMacabacus]

Howdy!
I tried the code you provided but I am still getting errors. Here is my code:

output_folder = "output"

source_file = "source.xlsx"
request = UploadFileRequest(
    upload_files=[source_file],
    path=f"{output_folder}/{source_file}",
    storage_name=STORAGE,
)
cells_api.upload_file(request)

target_file = "target.xlsx"
request = UploadFileRequest(
    upload_files=[target_file],
    path=f"{output_folder}/{target_file}",
    storage_name=STORAGE,
)
cells_api.upload_file(request)


request = GetNamedRangesRequest(
    name=source_file,
    folder=output_folder,
    storage_name=STORAGE,
)
response = cells_api.get_named_ranges(request)

r = response.container.get("ranges").range_list[0]
val = {
    "first_row": r.first_row,
    "first_column": r.first_column,
    "row_count": r.row_count,
    "column_count": r.column_count,
    "worksheet": r.worksheet,
    "name": r.name,
}

# Define source range in the source workbook
source_range = Range(
    first_row=val["first_row"],
    first_column=val["first_column"],
    row_count=val["row_count"],
    column_count=val["column_count"],
    worksheet=val["worksheet"],
)

# Define target range in the target workbook
target_range = Range(
    first_row=val["first_row"],
    first_column=val["first_column"],
    row_count=val["row_count"],
    column_count=val["column_count"],
    worksheet="Sheet1",
)

# Create range copy request
range_copy_request = RangeCopyRequest(
    operate="CopyTo",
    source=source_range,
    target=target_range,
    target_workbook=f"{output_folder}/{target_file}",  # Explicitly setting target workbook
)

# API request: Copy range from source workbook to target workbook
request = PostWorksheetCellsRangesCopyRequest(
    name=source_file,  # This is the document FROM which we copy
    sheet_name=val["worksheet"],  # Sheet name in the source workbook
    range_operate=range_copy_request,
    folder=output_folder,  # Folder where both files are stored
    storage_name=STORAGE,
)

response = cells_api.post_worksheet_cells_ranges_copy(request)

Here are the errors I am getting

asposecellscloud.rest.ApiException: (400)
Reason: Bad Request
HTTP response headers: HTTPHeaderDict({'date': 'Mon, 10 Feb 2025 21:18:08 GMT', 'content-type': 'application/json; charset=utf-8', 'content-length': '264', 'x-cluster': 'cloud-01', 'x-lb': 'TLR'})
HTTP response body: {"RequestId":"5a0dd704cfa0d176b35005c5f53840fe","Error":{"Code":"Fail","Message":"PostWorksheetCellsRanges fails. (Object reference not set to an instance of an object.)","Description":"Operation Failed. Fail Operation.","DateTime":"2025-02-10T21:18:08.8001773Z"}

I have confirmed that both the target and the source are being uploaded correctly. Additionally, the val dictionary has the correct values for the first range in the source workbook.

[wangtao]

@jamieMacabacus ,

We will refine the new SDK for Python based on the latest Cells Cloud API services. Please wait a day or two.

[jamieMacabacus]

Sounds good, thank you!

[wangtao]

@jamieMacabacus ,

Please upgrade the Aspose.Cells Cloud SDK for Python to version 25.1.1 and using the enclosed code for reference.

import os
import sys

from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

api = CellsApi(os.getenv(‘CellsCloudClientId’),os.getenv(‘CellsCloudClientSecret’),“v3.0”,os.getenv(‘CellsCloudApiBaseUrl’))
remote_folder = ‘TestData/In’
source_name = ‘Book1.xlsx’
target_name = ‘myDocument.xlsx’

mapFiles = {
source_name: source_name
}
request = UploadFileRequest( mapFiles, remote_folder + ‘/’ + source_name,storage_name= ‘’)
api.upload_file(request)
mapFiles = {
target_name: target_name
}
request = UploadFileRequest( mapFiles, remote_folder + ‘/’ + target_name,storage_name= ‘’)
api.upload_file(request)

rangeOperateSource = Range(column_count= 3 ,first_column= 8 ,first_row= 4 ,row_count= 2, worksheet=“Sheet1” )
rangeOperateTarget = Range(column_count= 3 ,first_column= 1,first_row= 1 ,row_count= 2 , worksheet=“Sheet4”)
rangeOperate = RangeCopyRequest(operate= ‘CopyTo’ ,source= rangeOperateSource ,target= rangeOperateTarget,target_workbook = remote_folder + ‘/’ + target_name )

postWorksheetCellsRangesCopyRequest = PostWorksheetCellsRangesCopyRequest( source_name, ‘Sheet1’, rangeOperate, folder= remote_folder,storage_name= ‘’)
api.post_worksheet_cells_ranges_copy(postWorksheetCellsRangesCopyRequest)

putActiveWorksheetRequest = PutActiveWorksheetRequest( target_name , ‘Sheet4’,folder= remote_folder,storage_name= ‘’)
api.put_active_worksheet(putActiveWorksheetRequest)

[jamieMacabacus]

Awesome, that worked. Thank you!

[wangtao]

@jamieMacabacus ,

Thank you for using our products. If we can meet any of your needs, we will integrate them for you as much as possible.