none
How to sort Column Data of Excel usinf C#

    Question

  • Hi

    How to sort the entire row of excel based on the selected column using C#.

    I have an excel sheet containing data in diffrent columns(Say Column A,Column B, Column C), Based on Column A, I want to sort the data present in that Entire row.(i.e Based on column entire row shoulh swap)

    Column A    Column B  Column C

     Name Age Address
    Paul 20 USA
    John 22 California
    Grep 45 Cannada
    Swen 67 Aus

    After Sorting Column A the result should be like,

    Column A    Column B  Column C

     Name Age Address
    Grep 45 Cannada
    John 22 California
    Paul 45 USA

    Swen

    Regards

    ewas

    67

    Aus

    Monday, January 13, 2014 9:08 AM

Answers

  • You can use DataSet/DataTable for reading excel file results and do the sorting.

    For better support try posting in http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Monday, January 13, 2014 9:13 AM
  • Also you could check this MSDN document:

    How to: Programmatically Sort Data in Worksheets


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 14, 2014 2:18 AM
    Moderator
  • Hello,

        This solution for you,
       
    private void SortExcel()
    {
        //Set up
        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;
        Excel.Range oLastAACell;
        Excel.Range oFirstACell;
    
        //Start Excel and get Application object.
        oXL = new Excel.Application();
        oXL.Visible = true;
    
        //Get a new workbook.;
        oWB = (Excel._Workbook)(oXL.Workbooks.Open(@"C:\Book.Xlsx"));
    
        //Get Sheet Object
        oSheet = (Excel.Worksheet)oWB.Worksheets["Sheet1"];
    
        //Get complete last Row in Sheet (Not last used just last)     
        int intRows = oSheet.Rows.Count;
    
        //Get the last cell in Column AA
        oLastAACell = (Excel.Range)oSheet.Cells[intRows, 27];
    
        //Move courser up to the last cell in AA that is not blank
        oLastAACell = oLastAACell.End[Excel.XlDirection.xlUp];
    
        //Get First Cell of Data (A2)
        oFirstACell = (Excel.Range)oSheet.Cells[2, 1];
    
        //Get Entire Range of Data
        oRng = (Excel.Range)oSheet.Range[oFirstACell, oLastAACell];
    
        //Sort the range based on First Columns And 6th (in this case A and F)
        oRng.Sort(oRng.Columns[1, Type.Missing],Excel.XlSortOrder.xlAscending, // the first sort key Column 1 for Range
                  oRng.Columns[6, Type.Missing],Type.Missing, Excel.XlSortOrder.xlAscending,// second sort key Column 6 of the range
                  Type.Missing, Excel.XlSortOrder.xlAscending,  // third sort key nothing, but it wants one
                  Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, 
                  Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,   
                  Excel.XlSortDataOption.xlSortNormal,
                  Excel.XlSortDataOption.xlSortNormal, 
                  Excel.XlSortDataOption.xlSortNormal);
        }
    -----------------------------------------
     Mark as answer if it helps to you
     Free .NET Barcode Generator & Scanner supporting over 40 kinds of 1D & 2D symbologies.
    Tuesday, January 14, 2014 2:40 AM
  • Hi, you could refer the code below to have a try,

    Workbook workbook = new Workbook(); 
    workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MiscDataTable.xls");
    Worksheet worksheet = workbook.Worksheets[0];
    workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending);
    workbook.DataSorter.SortColumns.Add(3, OrderBy.Ascending);
    workbook.SaveToFile("Sample.xls");
    

    code from

    http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide/How-to-Sort-Excel-Data.html

    hope it helps.

    Tuesday, January 14, 2014 3:07 AM
  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    namespace Forum
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
          
              
               
            }

            private void button1_Click(object sender, EventArgs e)
            {

            }
            private void ExcelSort()
            {
                Excel.Application _app = new Excel.Application();
                _app.Visible = true;
                _app.Workbooks.Open(@"D:\test.xlsx"); // Menntion your excel work book name
                Excel.Workbook _wb = _app.ActiveWorkbook;
                Excel.Worksheet _ws = _wb.ActiveSheet; // Mention your worksheet name
                Excel.Range _sortBy = _ws.get_Range("A2", "A5");
                Excel.Range _sortRange = _ws.get_Range("A1", "C5");

                _ws.Sort.SortFields.Clear();
                _ws.Sort.SetRange(_sortRange);
                _ws.Sort.SortFields.Add(_sortBy, 0, SortOrder.Ascending);
                _ws.Sort.Header = Excel.XlYesNoGuess.xlYes;
                _ws.Sort.MatchCase = false;
                _ws.Sort.Orientation = Excel.XlSortOrientation.xlSortColumns;
                _ws.Sort.SortMethod = Excel.XlSortMethod.xlPinYin;
                _ws.Sort.Apply();
            }
        }
    }

    • Edited by thiru.mg Tuesday, January 14, 2014 6:04 AM
    • Marked as answer by CaillenModerator Tuesday, January 21, 2014 5:59 AM
    Tuesday, January 14, 2014 4:27 AM

All replies

  • You can use DataSet/DataTable for reading excel file results and do the sorting.

    For better support try posting in http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    Monday, January 13, 2014 9:13 AM
  • Also you could check this MSDN document:

    How to: Programmatically Sort Data in Worksheets


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 14, 2014 2:18 AM
    Moderator
  • Hello,

        This solution for you,
       
    private void SortExcel()
    {
        //Set up
        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;
        Excel.Range oRng;
        Excel.Range oLastAACell;
        Excel.Range oFirstACell;
    
        //Start Excel and get Application object.
        oXL = new Excel.Application();
        oXL.Visible = true;
    
        //Get a new workbook.;
        oWB = (Excel._Workbook)(oXL.Workbooks.Open(@"C:\Book.Xlsx"));
    
        //Get Sheet Object
        oSheet = (Excel.Worksheet)oWB.Worksheets["Sheet1"];
    
        //Get complete last Row in Sheet (Not last used just last)     
        int intRows = oSheet.Rows.Count;
    
        //Get the last cell in Column AA
        oLastAACell = (Excel.Range)oSheet.Cells[intRows, 27];
    
        //Move courser up to the last cell in AA that is not blank
        oLastAACell = oLastAACell.End[Excel.XlDirection.xlUp];
    
        //Get First Cell of Data (A2)
        oFirstACell = (Excel.Range)oSheet.Cells[2, 1];
    
        //Get Entire Range of Data
        oRng = (Excel.Range)oSheet.Range[oFirstACell, oLastAACell];
    
        //Sort the range based on First Columns And 6th (in this case A and F)
        oRng.Sort(oRng.Columns[1, Type.Missing],Excel.XlSortOrder.xlAscending, // the first sort key Column 1 for Range
                  oRng.Columns[6, Type.Missing],Type.Missing, Excel.XlSortOrder.xlAscending,// second sort key Column 6 of the range
                  Type.Missing, Excel.XlSortOrder.xlAscending,  // third sort key nothing, but it wants one
                  Excel.XlYesNoGuess.xlGuess, Type.Missing, Type.Missing, 
                  Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin,   
                  Excel.XlSortDataOption.xlSortNormal,
                  Excel.XlSortDataOption.xlSortNormal, 
                  Excel.XlSortDataOption.xlSortNormal);
        }
    -----------------------------------------
     Mark as answer if it helps to you
     Free .NET Barcode Generator & Scanner supporting over 40 kinds of 1D & 2D symbologies.
    Tuesday, January 14, 2014 2:40 AM
  • Hi, you could refer the code below to have a try,

    Workbook workbook = new Workbook(); 
    workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MiscDataTable.xls");
    Worksheet worksheet = workbook.Worksheets[0];
    workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending);
    workbook.DataSorter.SortColumns.Add(3, OrderBy.Ascending);
    workbook.SaveToFile("Sample.xls");
    

    code from

    http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide/How-to-Sort-Excel-Data.html

    hope it helps.

    Tuesday, January 14, 2014 3:07 AM
  • Hi, you could refer the code below to have a try,

    Workbook workbook = new Workbook(); 
    workbook.LoadFromFile(@"..\..\..\..\..\..\Data\MiscDataTable.xls");
    Worksheet worksheet = workbook.Worksheets[0];
    workbook.DataSorter.SortColumns.Add(2, OrderBy.Ascending);
    workbook.DataSorter.SortColumns.Add(3, OrderBy.Ascending);
    workbook.SaveToFile("Sample.xls");

    code from

    http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide/How-to-Sort-Excel-Data.html

    hope it helps.

    Hi, please refer the code, it's useful.

    Workbook workbook = new Workbook(); workbook.LoadFromFile(@"..\..\sort.xlsx"); workbook.DataSorter.SortColumns.Add(1, OrderBy.Ascending);

    workbook.DataSorter.Sort(workbook.Worksheets[0].Range["B1:B5"]);

    workbook.SaveToFile(@"..\..\result.xlsx",ExcelVersion.Version2010);



    Tuesday, January 14, 2014 3:49 AM
  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using Excel = Microsoft.Office.Interop.Excel;
    namespace Forum
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
          
              
               
            }

            private void button1_Click(object sender, EventArgs e)
            {

            }
            private void ExcelSort()
            {
                Excel.Application _app = new Excel.Application();
                _app.Visible = true;
                _app.Workbooks.Open(@"D:\test.xlsx"); // Menntion your excel work book name
                Excel.Workbook _wb = _app.ActiveWorkbook;
                Excel.Worksheet _ws = _wb.ActiveSheet; // Mention your worksheet name
                Excel.Range _sortBy = _ws.get_Range("A2", "A5");
                Excel.Range _sortRange = _ws.get_Range("A1", "C5");

                _ws.Sort.SortFields.Clear();
                _ws.Sort.SetRange(_sortRange);
                _ws.Sort.SortFields.Add(_sortBy, 0, SortOrder.Ascending);
                _ws.Sort.Header = Excel.XlYesNoGuess.xlYes;
                _ws.Sort.MatchCase = false;
                _ws.Sort.Orientation = Excel.XlSortOrientation.xlSortColumns;
                _ws.Sort.SortMethod = Excel.XlSortMethod.xlPinYin;
                _ws.Sort.Apply();
            }
        }
    }

    • Edited by thiru.mg Tuesday, January 14, 2014 6:04 AM
    • Marked as answer by CaillenModerator Tuesday, January 21, 2014 5:59 AM
    Tuesday, January 14, 2014 4:27 AM
  • I really Appreciate for the help,

    Do i need to add any refrence for the above because i am getting compiler errors

    regards

    ewas

    Tuesday, January 14, 2014 4:49 AM
  • Microsoft.Office.Interop.Excel


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, January 14, 2014 5:35 AM
    Moderator