C# sort excel worksheet

[epots9]

I'm still learning C# so forgive me if this question seems "noobish".

I've created an excel template using C#, what it does is connects to our ERP system and gets the specific values wanted. All this is done through an actions pane control. The data comes through fine and appears where and how i want it, but thats not the problem.

There are 7 columns that get filled (rows vary based on results from db query). One department needs the file to be sorted by job # (column A1) then work order # (column B1)...while the other department needs it by shipping date (column F1) then work order # (column B1). I'm sure after i figure out one way it would be easy enough to figure out the sort for the other.

Since all my code is in the actions pane control, to access the worksheet i do:

Globals.Sheet1.X
 

I've tried searching but haven't been able to find anything helpful. If anyone can help me get this sorting to work that would be greatly appreciated.

Thanks in advance.

[epots9]

i reocrded a macro in excel to create the following:

Cells.Select
    ActiveWorkbook.Worksheets("Report").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("F2:F57") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("B2:B57") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Report").Sort
        .SetRange Range("A1:G57")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 

i just need assistance converting it to C#.

[joedeene]

...i just need assistance converting it to C#.

Take a look at this helpful site that insertAlias has previously pointed out. It is a great resource on the comparison between Visual Basic.net and C#.net. Here's the link: http://www.harding.edu/fmccown/vbnet...omparison.html

joedeene

[SioSio]

This is a sample that searches for the "Report" sheet of "C:\\sample.xlsx" and sorts the range (A1:E7) using the B column as the sort key.

First, add a reference from GAC. : Microsoft Office.Interop.Excel.

Click button 1 on the form to start.

using Excel = Microsoft.Office.Interop.Excel;

        // Method that returns the index of the specified worksheet name
        private int getSheetIndex(string sheetName, Excel.Sheets shs)
        {
            int i = 0;
            foreach (Excel.Worksheet sh in shs)
            {
                if (sheetName == sh.Name)
                {
                     return i + 1;
                }
                i += 1;
            }
            return 0;
        }
 
        void Button1Click(object sender, EventArgs e)
        {
            Excel.Application oXls; // Excel Object
            oXls = new Excel.Application();
            Excel.Workbook oWBook; // workbook Object
 
            string excelName = "C:\\sample.xlsx";
 
            oXls = new Excel.Application();
            oXls.Visible = true; // Show Excel window for confirmation
 
            // Open Excel file
            oWBook = (Excel.Workbook)(oXls.Workbooks.Open(
                excelName,  // Excel file name
                Type.Missing, // （Can be omitted）UpdateLinks (0 / 1 / 2 / 3)
                Type.Missing, // （Can be omitted）ReadOnly (True / False )
                Type.Missing, // （Can be omitted）Format
                // 1:Tab / 2:Comma (,) / 3:Space / 4:Semicolon (;)
                // 5:Non / 6:Characters specified by Parameter Delimiter
                Type.Missing, // （Can be omitted）Password
                Type.Missing, // （Can be omitted）WriteResPassword
                Type.Missing, // （Can be omitted）IgnoreReadOnlyRecommended
                Type.Missing, // （Can be omitted）Origin
                Type.Missing, // （Can be omitted）Delimiter
                Type.Missing, // （Can be omitted）Editable
                Type.Missing, // （Can be omitted）Notify
                Type.Missing, // （Can be omitted）Converter
                Type.Missing, // （Can be omitted）AddToMru
                Type.Missing, // （Can be omitted）Local
                Type.Missing  // （Can be omitted）CorruptLoad
            ));
            // Get a Worksheet object from the given worksheet name
            string sheetName = "Report";
            Excel.Worksheet oSheet; // Worksheet Object
            oSheet = (Excel.Worksheet)oWBook.Sheets[
                getSheetIndex(sheetName, oWBook.Sheets)];
 
            // Set sort properties          
            oSheet.Sort.SetRange(oSheet.Range["A1", "E7"]);
            oSheet.Sort.Header = Excel.XlYesNoGuess.xlYes;
            oSheet.Sort.SortFields.Add(oSheet.Range["B1", "B7"], Excel.XlSortOn.xlSortOnValues,
                Excel.XlSortOrder.xlAscending);
 
            // Sort worksheet
            oSheet.Sort.Apply();
:
:
:
 
                        oSheet=null;
                        oWBook=null;
                        oXls=null;
        }