Quickest excel interop way to copy a big range, sort it and remove unnecessay columns

Question

I have a c# code that produces a very big array that I write in an excel worksheet. Then I create another worksheet inside the same workbook with the same big array, and I sort the latter :

using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    class Program
    {
        static object[,] returnDummyVariant()
        {
            object[,] res = new object[4,3];

            res[0, 0] = "Surname";
            res[0, 1] = "Height";
            res[0, 2] = "Age";

            res[1, 0] = "Julian";
            res[1, 1] = "185";
            res[1, 2] = "39";

            res[2, 0] = "Mark";
            res[2, 1] = "173";
            res[2, 2] = "63";

            res[3, 0] = "Patrick";
            res[3, 1] = "193";
            res[3, 2] = "23";

            return res;
        }

        private static Excel.Range WriteTopLeft(object[,] variant, Excel.Range topLeft)
        {
            if (variant == null) return null;

            int nL = variant.GetLength(0);
            int nC = variant.GetLength(1);

            if (nL * nC == 0) return null;

            var worksheet = topLeft.Worksheet;
            var writeRange = worksheet.Range[topLeft, topLeft.Offset[nL - 1, nC - 1]];

            writeRange.Value2 = variant;

            return writeRange;
        }

        static void Main(string[] args)
        {
            Application excel = new Application();

            string path = @"paht\to\file\Book1.xlsx";

            Workbook wb = excel.Workbooks.Open(path);

            var wsData = (Excel.Worksheet)wb.Worksheets.Item[1];
            wsData.Name = "Data";

            var variant = returnDummyVariant();
            var rData = WriteTopLeft(variant, wsData.Cells[1, 1]);

            int rows = rData.Rows.Count;
            int columns = rData.Columns.Count;

            var ws = (Excel.Worksheet)wb.Worksheets.Add();
            ws.Name = "Sorted Data";
            rData = WriteTopLeft(variant, ws.Cells[1, 1]);

            rData.Sort(rData.Columns[2, Type.Missing], Excel.XlSortOrder.xlDescending,
                Type.Missing, Excel.XlSortOrder.xlAscending, Excel.XlSortOrder.xlAscending,
                Type.Missing, Excel.XlSortOrder.xlAscending,
                Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing,
                Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,
                Excel.XlSortDataOption.xlSortNormal,
                Excel.XlSortDataOption.xlSortNormal,
                Excel.XlSortDataOption.xlSortNormal);

            wb.Save();
            wb.Close();
        }
    }
}

The .Sort bit triggers an exception : System.Runtime.InteropServices.COMException: 'Reference isn't valid.' and I cannot find why.

I tried several corrections here and there without any success.

Answer 1

Firstly, as good practice, you should catch the exception thrown & find any additional information - which will be the HRESULT for this exception.

In this case though, the information obtained is not particularly helpful - Error code 0x800a03ec, which is used for multiple faults.

Looking at the documentation for the Sort method, I found your 4th parameter is incorrect - it should only be used when sorting PivotTable reports & should otherwise be passed as Type.Missing.

rData.Sort(rData.Columns[2, Type.Missing], Excel.XlSortOrder.xlDescending,
            Type.Missing, Type.Missing,  Excel.XlSortOrder.xlAscending,
            Type.Missing, Excel.XlSortOrder.xlAscending,
            Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing,
            Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,
            Excel.XlSortDataOption.xlSortNormal,
            Excel.XlSortDataOption.xlSortNormal,
            Excel.XlSortDataOption.xlSortNormal);

You could also simplify the method call by using named parameters, it will then use appropriate default values for the ones you omit:

        rData.Sort(rData.Columns[2, Type.Missing],
             XlSortOrder.xlDescending, 
             Header: XlYesNoGuess.xlGuess, 
             Orientation: XlSortOrientation.xlSortColumns);

See also https://learn.microsoft.com/en-us/visualstudio/vsto/how-to-programmatically-sort-data-in-worksheets?view=vs-2019

Also, you should quit the application - excel.Quit(); at the end of your application. By default, an instance of Excel is opened up but not displayed - if you do not quit the application then it will remain open until you shut your PC down - try running your application several times & then open up TaskManager & you will see an equal number of Excel processes.

If your application is relatively small like your code above, then you should not have any issue regarding tidying up the Excel COM objects, if your code is more complex & you use many objects (Ranges, Worksheets etc are all COM objects) then you should ensure they are released from memory by use of Marshal.ReleaseComObject & setting the reference to null - calling GC.Collect twice is also recommended by some people - lots of discussion here : How do I properly clean up Excel interop objects?