c# Sorting Rows in Excel using Microsoft Interop-CodePudding

I been trying to Sort a Range on basis of first Column (i.e dates). But when I run my code nothing seems to happen. There is no change in the file. Following is my code.

Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        String file = @"C:\Book1.xlsx";
        xlWorkBook = xlApp.Workbooks.Open(file);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
       
        Excel.Range rng = xlWorkSheet.get_Range("B16", "I38");

        rng.Sort(rng.Columns[1, Type.Missing], Excel.XlSortOrder.xlDescending,
                        Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,
                        Type.Missing, Excel.XlSortOrder.xlAscending,
                        Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing,
                        Excel.XlSortOrientation.xlSortColumns,
                        Excel.XlSortMethod.xlPinYin,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal);
        
       
        xlWorkBook.Close(true, misValue, misValue);

CodePudding user response：

Because you set null value to rngsort variable.

CodePudding user response：

In case if anyone is looking or a solution.

Excel.Application xlApp = new Excel.Application();
        # setup constants, workbook, sheet, etc.
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;
        String file = @"C:\Book1.xlsx";
        xlWorkBook = xlApp.Workbooks.Open(file);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        # identify the last row from the used range of the column of interest (column B in this case)
        Excel.Range UsedRange = xlWorkSheet.UsedRange.Columns["B:B"];
        int lastRow = UsedRange.Row   UsedRange.Rows.Count - 1;
        
        # get range of interest...B15:I<last row>
        Excel.Range rng = xlWorkSheet.get_Range("B15", "I"   lastRow);

        # finally, sort the rows of the range of interest according to the original criteria
        rng.Sort(rng.Rows, Excel.XlSortOrder.xlAscending,
                        Type.Missing, Type.Missing, Excel.XlSortOrder.xlAscending,
                        Type.Missing, Excel.XlSortOrder.xlAscending,
                        Excel.XlYesNoGuess.xlYes, Type.Missing, Type.Missing,
                        Excel.XlSortOrientation.xlSortColumns,
                        Excel.XlSortMethod.xlPinYin,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal,
                        Excel.XlSortDataOption.xlSortNormal);
        rng = null;