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:

[code=c#]
Globals.Sheet1. X
[/code]

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:

[code=VB]
Cells.Select
ActiveWorkbook. Worksheets("Rep ort").Sort.Sort Fields.Clear
ActiveWorkbook. Worksheets("Rep ort").Sort.Sort Fields.Add Key:=Range("F2: F57") _
, SortOn:=xlSortO nValues, Order:=xlAscend ing, DataOption:=xlS ortNormal
ActiveWorkbook. Worksheets("Rep ort").Sort.Sort Fields.Add Key:=Range("B2: B57") _
, SortOn:=xlSortO nValues, Order:=xlAscend ing, DataOption:=xlS ortNormal
With ActiveWorkbook. Worksheets("Rep ort").Sort
.SetRange Range("A1:G57")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
[/code]

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.xls x" 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;
		}