c# - 从excel中排序列表

Question

我正在尝试从我制作的 excel 表中获取不同的信息，并按顺序对它们进行排序，从电压开始，然后是马力，然后是型号。到目前为止，我编写的代码将从 Excel 文件中提取所有信息，但我不知道如何按三个不同的类别对其进行排序。到目前为止，这是我的代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace Model_Sort
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnSortExcel_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open("C:\\Users\\NMC2376\\OneDrive - Nidec\\E07 Files (2)\\E07 Files\\E07801237.xlsx");
            //Excel.Worksheet xlWorkSheet = xlWorkBook.ActiveSheet;
            //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //Excel.Range rng = (Excel.Range)(xlWorkSheet.get_Range(xlWorkSheet.Cells[3, 3], xlWorkSheet.Cells[5, 5]));
            Excel.Worksheet xlWS = xlWorkBook.Worksheets.get_Item(1);
            Excel.Range xlMMCol = xlWS.get_Range("B4", "B117");
            Excel.Range xlCICol = xlWS.get_Range("C4", "C117");
            Excel.Range xlSVCol = xlWS.get_Range("D4", "D117");
            Excel.Range xlVCol = xlWS.get_Range("E4", "E117");
            Excel.Range xlHPCol = xlWS.get_Range("F4", "F117");
            Excel.Range xlSCol = xlWS.get_Range("G4", "G117");
            Excel.Range xlCUSCol = xlWS.get_Range("H4", "H117");

            try
            {

                //object misValue = System.Reflection.Missing.Value;
                //var cellvalue = (string)(xlWorkSheet.Cells[1, 1] as Excel.Range).Value;
                listBox1.Items.Clear();
                object cellValues = xlVCol.Value;
                object[,] cellValues2 = xlCICol.Value;
                object[,] cellValues3 = xlSVCol.Value;
                object[,] cellValues4 = xlMMCol.Value;
                object[,] cellValues5 = xlHPCol.Value;
                object[,] cellValues6 = xlSCol.Value;
                object[,] cellValues7 = xlCUSCol.Value;
                int i = 1;
                foreach (object item in (Array)cellValues)
                {
                    listBox1.Items.Add(Convert.ToString( "Voltage:  " + item + ",    HP:  " + Convert.ToString(cellValues5[i,1]) + ", \t Customer: " + Convert.ToString(cellValues7[i,1])));
                    listBox1.Items.Add(Convert.ToString("Model: " +Convert.ToString(cellValues4[i,1]) + "\t \t Control ID:   " + Convert.ToString(cellValues2[i,1])+ "000000"));
                    listBox1.Items.Add(Convert.ToString("Map:   " + Convert.ToString(cellValues3[i,1])));
                    listBox1.Items.Add(Convert.ToString(""));
                    i++;
                }


                //MessageBox.Show(cellValues);
            }
            catch(Exception ex)
            {
                MessageBox.Show("Well Shucks");
            }
            finally
            {
                xlWorkBook.Close();
                xlApp.Quit();
                releaseObject(xlCICol);
                releaseObject(xlMMCol);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);
            }



        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}

Answer 1

使用 C#/Interop，这是一个如何按多个字段排序的示例。我不知道哪一列是型号，所以我猜是 B:

Excel.Worksheet sheet = xlWorkBook.Sheets[1];

sheet.Sort.SortFields.Clear();
sheet.Sort.SortFields.Add(sheet.Range["E4:E117"], Excel.XlSortOn.xlSortOnValues,
    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);
sheet.Sort.SortFields.Add(sheet.Range["F4:F117"], Excel.XlSortOn.xlSortOnValues,
    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);
sheet.Sort.SortFields.Add(sheet.Range["B4:B117"], Excel.XlSortOn.xlSortOnValues,
    Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal);
sheet.Sort.SetRange(sheet.Range["A4:H117"]);
sheet.Sort.Apply();