Range.Copy(Range) - 1 Issue and 1 Question

Aspose.Cells Product Family
1

Hello, I am quite confused with Range.Copy(). First, I understand that there are two major versions of the method in the APIs. Version 1 copies a selected range that is not entirely row or column based. Version 2 copies a selected range that is row or column based. So I will refer to the different APIs by Version 1 and Version 2 in this posting.

Issues
1. I cannot get Version 2 of this API to work. Specifically, what I try to do is create a Range, and copy it to other Ranges that I create. This causes my CPU utilization to jump to 100%, until I kill the w3wp process. Obviously something is wrong here. I have traced through my debugger, and have indeed confirmed that on a call to your API your method doesn’t return, and CPU utilization goes to 100%.

Questions
1. What exactly is Version 1 of Range.Copy() supposed to be guaranteed to copy? I have noticed that merged cells for example don’t get copied. What by your definition therefore do you not intend to support for copying purposes? This will help me better understand what I should or shouldn’t put into a template, and when to contact you for support purposes.

Thanks
Shan Plourde

2

Hi Shan,

I optimized the method in the latest hotfix. Please download it and have a try.

3

Hi Laurence, the 100% CPU utilisation issue is resolved. Thank you very much for the timely update.

The only remaining item in my post is the question below:

1. What exactly is Version 1 of Range.Copy() supposed to be guaranteed to copy? I have noticed that merged cells for example don’t get copied. What by your definition therefore do you not intend to support for copying purposes? This will help me better understand what I should or shouldn’t put into a template, and when to contact you for support purposes.

You see, when I use Cells.CreateRange(int, int, bool) to create 2 ranges, then copy one range to the other, I am assuming that all formatting should be copied 100% intact. For example, I have merged cells, certain columns in the worksheet are set to a certain width, and so on.

So my question really is what do you supposed to support for copying ranges that were created with Cells.CreateRange(int, int, bool)? If you are supposed to support also copying the cell widths, and merged cells, then this is definitely not the case - it is not happening.

Let me know if this question is clear or not.

Thanks
Shan Plourde

4

Hi Shan,

Because the created range may contains part of a merged cell, to avoid conflicts, I didn’t add support to merged cells. I will check this issue and will add this feature to Aspose.Excel. So in future release, if you create a range with a full merged cell, it will be copied. If it’s a partial merged cell, it will not be copied.

And now copying column width is also not supported. I will check this issue and add it if possible.

Now I am also work on some other features, so you have to wait for about one week for this feature.

5

Hi Laurence,
I understand your thoughts about this. I would recommend that you design your basic Excel manipulation APIs after how Microsoft Excel itself supports basic tasks through its user interface. For example, model your copying of entire rows or columns after how Excel actually does it.

This will make the API more clear for new developers building applications with it - they will be able to use Microsoft Excel to see how a copy and paste behaves, and can then expect that that would be the same case with your APIs - where that is possible. It will also help make your libraries more clear as there are many subtleties of your APIs that are not documented such as this. It therefore becomes a matter of trial and error or guessing at times how something should behave - not a big deal because it’s not tremendously time consuming, but things to consider anyways.

Thanks again,
Shan Plourde

6

Hi Shan,

Thanks for your suggestion. That’s very helpful.

Actually, we design our APIs after how MS Excel does it. We will shape APIs to make it consistent. If there is variance, we will add it to our documentation.

7

@Aspose.ExcelCustomer,
Please note that Aspose.Excel is discontinued now and no more active development is done for it in Aspose. Another advanced product Aspose.Cells has replaced it which supports all the features in Aspose.Excel as well as contains different latest features available in MS Excel. We can work with ranges using this new product in different ways. For example we can copy range data with style as demonstrated in the following sample code:

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


// Instantiate a new Workbook.
Workbook workbook = new Workbook();

// Get the first Worksheet Cells.
Cells cells = workbook.Worksheets[0].Cells;

// Fill some sample data into the cells.
for (int i = 0; i < 50; i++)
{
    for (int j = 0; j < 10; j++)
    {
        cells[i, j].PutValue(i.ToString() + "," + j.ToString());
    }
}

// Create a range (A1:D3).
Range range = cells.CreateRange("A1", "D3");

// Create a style object.
Style style;
style = workbook.CreateStyle();
// Specify the font attribute.
style.Font.Name = "Calibri";
// Specify the shading color.
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
// Specify the border attributes.
style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.TopBorder].Color = Color.Blue;
style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.BottomBorder].Color = Color.Blue;
style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.LeftBorder].Color = Color.Blue;
style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
style.Borders[BorderType.RightBorder].Color = Color.Blue;
// Create the styleflag object.
StyleFlag flag1 = new StyleFlag();
// Implement font attribute
flag1.FontName = true;
// Implement the shading / fill color.
flag1.CellShading = true;
// Implment border attributes.
flag1.Borders = true;
// Set the Range style.
range.ApplyStyle(style, flag1);

// Create a second range (C10:F12).
Range range2 = cells.CreateRange("C10", "F12");

// Copy the range data with formatting.
range2.Copy(range);

dataDir = dataDir + "CopyRange.out.xlsx";
// Save the excel file.
workbook.Save(dataDir);

Refer to the following articles for more information on ranges:
Create Access and Copy Named Ranges
Format and Modify Named Ranges
Setting Formula for Named Range
Copy Range Style Only

Give a try to this new product by downloading it from the following link:
Aspose.Cells for .NET (Latest Version)

Here is a runnable solution which contains hundreds of examples to demonstrate different features of Aspose.Cells.