InsertRange method doesn't copies the data?


#1

Hi,

Can you run the below code and see why the aspose not able to copy the data when i use the insert range method. My requirement is to cut paste certain rows/columns with data.
I can copy the row using CopyRow() method, but that doesn’t have options to shift the rows down like InsertRange(). Please review and give us a solution as soon as you can.

         public void TestAsposeCode(string filePathToSave)
                 {
                     var workbook = new Workbook();
                     var worksheet = workbook.Worksheets[0];
                     ExcelRange.ExcelWorksheet = worksheet;
                     for (int i = 1; i <= 16; i++)
                     {
                         ExcelRange.SetCellRange(i, 1).SetValue(string.Format("{0}{1}", NonHoldingConstants.TemplateType, "OneRowColRange- '" + i + "' and 1 "));
                     }
         
                     ShiftTotalRows(false, worksheet);
         
                     workbook.Save(filePathToSave);
                 }
         
         private void ShiftTotalRows(bool isTotalRequired, Worksheet excelWorksheet)
                 {
                     var totalRowIndexes = new List<int>() { 11, 12 };// { 12, 13 };// In PARis, initial startingRow/Column will be decremented by 1.
                     var startingRow = 1;//2;
                     if (!isTotalRequired)
                     {
                         int count = 0;
                         foreach (int totalRowIndex in totalRowIndexes)
                         {
                          
                            Range copyRange = AsposeCellsHelper.GetAsposeCellRange(totalRowIndex, 1, 1, 1, excelWorksheet).EntireRow;
                            Range insertRange = AsposeCellsHelper.GetAsposeCellRange(startingRow, 1, 1, 1, excelWorksheet).EntireRow;
         
                             CellArea areaToMoveRows = new CellArea() { StartRow = startingRow, EndRow = startingRow };
                             excelWorksheet.Cells.InsertRange(areaToMoveRows, 1, ShiftType.Down, false);
                             excelWorksheet.Cells.DeleteRow(totalRowIndex + 1);// +1 is required since index will be changed after adding the above row
                             count++;
                         }
                     }
                }

public static class AsposeCellsHelper
    {            
        public static Range GetAsposeCellRange(int startingRow, int startingColumn, int totalRows, int totalColumns, Worksheet sheet)
        {
            if (sheet != null)
            {
                Tuple<int, int> asposeRowColumn = ReAdjustRowColumnIndexForAspose(startingRow, startingColumn);

                if (asposeRowColumn != null)
                {
                    sheet.SelectRange(asposeRowColumn.Item1, asposeRowColumn.Item2, totalRows, totalColumns, true);
                    Range selectedRange = ExcelRange.GetSelectedRange();
                    if (selectedRange != null)
                        return selectedRange;
                }
            }

            return null;
        }

        private static Tuple<int, int> ReAdjustRowColumnIndexForAspose(int startingRow, int startingColumn)
        {
          
            startingRow = startingRow - 1;
            startingColumn = startingColumn - 1;
            if (startingRow >= 0 && startingColumn >= 0)
            {
                return Tuple.Create<int, int>(startingRow, startingColumn);
            }

            return null;
        }
    }

#2

@PrathapSV,

Thanks for the sample code segment and details.

I evaluated your sample code a bit and found missing references for a few objects/classes (e.g. ExcelRange, NonHoldingConstants, etc.) which cannot be resolved as we are not sure on how you are specifying cell ranges. Kindly create a sample console application (runnable), zip the project and post us here to show the issue, we will check it soon.


#3

Hi,
I have attached the sample runnable project(.AsposeSupport.zip (166.9 KB)
), please review and provide a solution as soon as possible.

Please check the issue with both columns and rows, when we try to cut paste, it doesn’t copies the data for both rows and columns. The method which has the logic to cut paste the rows/columns is ShiftTotalRows and ShiftTotalColumn in **ExcelOpsAspose.cs file.

Please note i have removed the aspose dll in the attached project for size issues


#4

Also, Please note my main requirement is to just “Cut and Paste the Rows or Columns”, something like
MoveRows(CopiedRow,DestinationRow,ShiftCellsDown).

Unfortunately, am not able to find anything similar and straight forward in your documentation.


#5

@PrathapSV,

Thanks for the sample project.

I did try to evaluate your provided sample project. I am afraid, your project seems a bit lengthy and complex where you used your logic to accomplish your task. It would take time to understand your scenario precisely. Earlier we requested for a simple example (with a few lines of code) that would save our time and consequently we could reproduce the issue and figure it out soon. I did try your solution and found it generated the file (attached) which looks to me Ok. What’s wrong with the file (attached)? Please either simplify your sample and re-attach it here to show the issue. Alternatively, elaborate the issue with more details, attach some screenshots (by highlighting the issue) and your expected file (you may manually create/update the file in MS Excel with steps details). This will help us to evaluate your issue thoroughly and then we can try to figure it out (we can log a ticket for it).
files1.zip (7.1 KB)


#6

Forget about the whole issue, Can you just let me know how i can achieve
Cut and Paste of the Rows or Columns programmatically ”? using your Aspose library?

It was so simple in Microsoft’s Interop’s just one line(insertRange.Insert(XlInsertShiftDirection.xlShiftDown, copyRange.Cut()); ) of code and not sure why you are not able to understand this simple requirement???


#7

@PrathapSV,

We have thoroughly analyzed the issue. I am afraid currently we have no such direct API for Cut-Paste operation. We are working on such kind of feature and hopefully it can be published soon, may be in this month or in the next month. The following tickets are logged for the purpose:

  1. CELLSNET-46657 - Support inserting cut cells. - this ticket is derived from below ticket
  2. CELLSNET-46643 - Named ranges are not copied while copying a column.

Moreover, because we are still in the process of developing this feature, but if you desperately want to have on urgent basis, we may consider to publish this feature for evaluation purpose even sooner than when it should/will be published.

Alternatively, I think, currently, you have to copy the row/column to the destination place and then delete the original one. Using the following approach might not work for all the case too.
Insert a blank range using InsertRange method (specifying your desired shift option) for your specified area and then create two ranges (one source and destination). Now copy the source range to destination range using Range.Copy() method. But we found when using MS Excel’s Cut and Paste feature, other references to the cut range will be updated to the new ones too. But using this approach might not work this way.


#8

Thanks for the update, I already tried copy paste and then deleting the row, approach and it seems to meet our requirements as of now without any issues.

But i believe we have some other modules where we might need this cut-paste option with updating all the references properly like MS interop.

Please keep us posted when you release that cut-paste feature.


#9

@PrathapSV,

Sure, we will keep you posted with updates on the Cut/Paste feature.


#10

@PrathapSV,

This is to inform you that we have supported the feature (logged earlier as “CELLSNET-46657”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.


#11

@PrathapSV,

Please try our latest version/fix: Aspose.Cells for .NET v19.3.6 (attached)
Your issue should be fixed in it. See the sample code for your reference:
e.g
Sample code:

    Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets[0];

                ws.Cells[0, 2].Value = 1;
                ws.Cells[1, 2].Value = 2;
                ws.Cells[2, 2].Value = 3;
                ws.Cells[2, 3].Value = 4;
                ws.Cells.CreateRange(0, 2, 3, 1).Name = "NamedRange";

            Range cut = ws.Cells.CreateRange("C:C");
            ws.Cells.InsertCutCells(cut, 0, 1, ShiftType.Right);

Let us know your feedback.
Aspose.Cells19.3.6 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.3.6 For .Net4.0.Zip (4.9 MB)


#12

The issues you have found earlier (filed as CELLSNET-46657) have been fixed in Aspose.Cells for .NET v19.4. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi