By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,142 Members | 790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,142 IT Pros & Developers. It's quick & easy.

C# sort excel worksheet

epots9
Expert 100+
P: 1,351
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:

Expand|Select|Wrap|Line Numbers
  1. Globals.Sheet1.X
  2.  
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.
Nov 10 '08 #1
Share this Question
Share on Google+
3 Replies

epots9
Expert 100+
P: 1,351
i reocrded a macro in excel to create the following:

Expand|Select|Wrap|Line Numbers
  1. Cells.Select
  2.     ActiveWorkbook.Worksheets("Report").Sort.SortFields.Clear
  3.     ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("F2:F57") _
  4.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  5.     ActiveWorkbook.Worksheets("Report").Sort.SortFields.Add Key:=Range("B2:B57") _
  6.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  7.     With ActiveWorkbook.Worksheets("Report").Sort
  8.         .SetRange Range("A1:G57")
  9.         .Header = xlYes
  10.         .MatchCase = False
  11.         .Orientation = xlTopToBottom
  12.         .SortMethod = xlPinYin
  13.         .Apply
  14.     End With
  15.  
i just need assistance converting it to C#.
Nov 10 '08 #2

joedeene
100+
P: 583
...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
Nov 10 '08 #3

100+
P: 161
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.
Expand|Select|Wrap|Line Numbers
  1. using Excel = Microsoft.Office.Interop.Excel;
Expand|Select|Wrap|Line Numbers
  1.         // Method that returns the index of the specified worksheet name
  2.         private int getSheetIndex(string sheetName, Excel.Sheets shs)
  3.         {
  4.             int i = 0;
  5.             foreach (Excel.Worksheet sh in shs)
  6.             {
  7.                 if (sheetName == sh.Name)
  8.                 {
  9.                      return i + 1;
  10.                 }
  11.                 i += 1;
  12.             }
  13.             return 0;
  14.         }
  15.  
  16.         void Button1Click(object sender, EventArgs e)
  17.         {
  18.             Excel.Application oXls; // Excel Object
  19.             oXls = new Excel.Application();
  20.             Excel.Workbook oWBook; // workbook Object
  21.  
  22.             string excelName = "C:\\sample.xlsx";
  23.  
  24.             oXls = new Excel.Application();
  25.             oXls.Visible = true; // Show Excel window for confirmation
  26.  
  27.             // Open Excel file
  28.             oWBook = (Excel.Workbook)(oXls.Workbooks.Open(
  29.                 excelName,  // Excel file name
  30.                 Type.Missing, // (Can be omitted)UpdateLinks (0 / 1 / 2 / 3)
  31.                 Type.Missing, // (Can be omitted)ReadOnly (True / False )
  32.                 Type.Missing, // (Can be omitted)Format
  33.                 // 1:Tab / 2:Comma (,) / 3:Space / 4:Semicolon (;)
  34.                 // 5:Non / 6:Characters specified by Parameter Delimiter
  35.                 Type.Missing, // (Can be omitted)Password
  36.                 Type.Missing, // (Can be omitted)WriteResPassword
  37.                 Type.Missing, // (Can be omitted)IgnoreReadOnlyRecommended
  38.                 Type.Missing, // (Can be omitted)Origin
  39.                 Type.Missing, // (Can be omitted)Delimiter
  40.                 Type.Missing, // (Can be omitted)Editable
  41.                 Type.Missing, // (Can be omitted)Notify
  42.                 Type.Missing, // (Can be omitted)Converter
  43.                 Type.Missing, // (Can be omitted)AddToMru
  44.                 Type.Missing, // (Can be omitted)Local
  45.                 Type.Missing  // (Can be omitted)CorruptLoad
  46.             ));
  47.             // Get a Worksheet object from the given worksheet name
  48.             string sheetName = "Report";
  49.             Excel.Worksheet oSheet; // Worksheet Object
  50.             oSheet = (Excel.Worksheet)oWBook.Sheets[
  51.                 getSheetIndex(sheetName, oWBook.Sheets)];
  52.  
  53.             // Set sort properties          
  54.             oSheet.Sort.SetRange(oSheet.Range["A1", "E7"]);
  55.             oSheet.Sort.Header = Excel.XlYesNoGuess.xlYes;
  56.             oSheet.Sort.SortFields.Add(oSheet.Range["B1", "B7"], Excel.XlSortOn.xlSortOnValues,
  57.                 Excel.XlSortOrder.xlAscending);
  58.  
  59.             // Sort worksheet
  60.             oSheet.Sort.Apply();
  61. :
  62. :
  63. :
  64.  
  65.                         oSheet=null;
  66.                         oWBook=null;
  67.                         oXls=null;
  68.         }
Jul 8 '20 #4

Post your reply

Sign in to post your reply or Sign up for a free account.