Insert Range above Table breaks the table

Aspose.Cells Product Family
1

If you insert (copy and paste) a range in excel, you get the option to shift down the content below. There is no such option for Cell.PutValue().
Instead i’m using InsertRange below the field, where i want to put the value.

If there is a Table below the cell where i want to insert the range, excel tells me this is not possible as it would break the table.
In Aspose, it just happens (and the table breaks).

I know, you aren’t ot supposed to insert a range above a table, but how can i check if this is allowed without looping over all the tables and comparing their area manually (which is expensive when you do this often).

Will this throw an exception in 23.3? What is the best way to prevent this?
I need to insert ranges before i write values, so i can shift down content, but i don’t want situations like this to break the entire excel file.

      Cells cells = workbook.Worksheets[0].Cells;

      // Add a table
      ListObjectCollection listObjects = workbook.Worksheets[0].ListObjects;
      listObjects.Add(20, 2, 25, 7, true);

      // Write values above the table, which would overwrite everything in the table
      int[] myValueArray = new int[] { 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89 };
      cells.ImportArray(myValueArray, 15, 3, true);

      // so let's insert a range first

      CellArea cellArea;
      cellArea.StartRow = 16;
      cellArea.EndRow = cellArea.StartRow + myValueArray.Count();
      cellArea.StartColumn = 3;
      cellArea.EndColumn = cellArea.StartColumn;

      // Result: Table is broken and excel file reports an error when opening it.
      cells.InsertRange(cellArea, ShiftType.Down);
2

@SimplyLiz
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): CELLSNET-52917

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.

3

@SimplyLiz
If the range can not be shifted in the next version 23.3, a warning will be thrown as MS Excel, you can select throw exception or cancel operation.

static void Main(string[] args)
        {
 var workbook = new Workbook();
 workbook.Settings.WarningCallback = new CustomWarningCallback();
....
}
 class CustomWarningCallback : IWarningCallback
        {
            internal CustomWarningCallback()
            {
               
            }
            public void Warning(WarningInfo warningInfo)
            {
                switch (warningInfo.WarningType)
                {
                    case WarningType.InvalidOperator:
                        //you can ingore error, or throw exception.
                        //if you return directly, the operation will be cancelled.
                        return;

                    default:
                        break;
                }
            }
        }
4

@SimplyLiz
In fact, we have to loop over all the tables to determine whether we can move the range down.
The following codes check whether the table could be shifted.

     internal static bool IsAllowedToShift(ListObject table,int startRow, int startColumn, int endRow, int endColumn)
            {
                if (startRow > table.EndRow)
                {
                    return true;
                }
                if (endColumn < table.StartColumn || table.EndColumn < startColumn)
                {
                    return true;
                }
                else if (startColumn <= table.StartColumn && endColumn >= table.EndColumn)
                {
                    return true;
                }
                return false;
            }
1 Like
5

Thank you, i’ve implemented two funktions to check if there will be a table beneath (so don’t insert lines) and another one so i know how many lines i can insert until i reach the table and i don’t overwrite anything.

I’m using LINQ though:

    bool isTableBelow(string cellName, int sheetIndex)
    {
      int row, col;
      CellsHelper.CellNameToIndex(cellName, out row, out col);

      return m_workbook.Worksheets[sheetIndex].ListObjects.Any(y => row < y.StartRow && (col <= y.EndColumn && col >= y.StartColumn));
    }
6

@SimplyLiz,

Thanks for sharing the sample. It seems it works for your needs. Feel free to write us back if you have further queries or issue.

7

The issues you have found earlier (filed as CELLSNET-52917) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

1 Like
8

Thank you, works as expected now

9

@SimplyLiz

Good to know your issue is sorted out. Feel free to contact with us if you have any requirements.

1 Like