469,300 Members | 2,278 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,300 developers. It's quick & easy.

C# sort excel worksheet

epots9
1,351 Expert 1GB
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
3 8733
epots9
1,351 Expert 1GB
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
583 512MB
...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
SioSio
258 256MB
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.

Similar topics

13 posts views Thread by Allison Bailey | last post: by
1 post views Thread by c duden | last post: by
1 post views Thread by mail2atulmehta | last post: by
reply views Thread by dgoel | last post: by
8 posts views Thread by Sam | last post: by
1 post views Thread by Randall Arnold | last post: by
6 posts views Thread by gumbystation | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.