1、环境：VS2013 SQL Server2012

2、数据库脚本： 

with CTE as    (     -->Begin 一个定位点成员      select Id,MenuName,ParentId,cast(MenuName as nvarchar(max)) as TE,OrderNum,                --ROW_NUMBER()over(order by getdate()) as OrderID                CAST( (CASE                 WHEN OrderNum < 10 THEN '00'+LTRIM(OrderNum)                ELSE '0'+LTRIM(OrderNum)                END) AS VARCHAR(100)) AS numstr         --最关键是上面这个字段，要获取排序字段，按字符串来排序。          --其中窗口函数必须要使用order by，但是不能用整型，那就用时间吧          from Main_Menu where ParentId = 0 -->End      union all     -->Begin一个递归成员      select Main_Menu.ID, Main_Menu.MenuName,Main_Menu.ParentId,cast(replicate(' ',len(CTE.TE))+'|_'+Main_Menu.MenuName as nvarchar(MAX)) as TE,Main_Menu.OrderNum,          --CTE.OrderNum*100+Main_Menu.OrderNum as OrderNum                --Main_Menu.OrderNum                CAST( (CTE.numstr+                CASE                 WHEN Main_Menu.OrderNum < 10 THEN '00'+LTRIM(Main_Menu.OrderNum)                ELSE '0'+LTRIM(Main_Menu.OrderNum)                END) AS VARCHAR(100) ) AS numstr         from Main_Menu inner join CTE             on Main_Menu.ParentId=CTE.Id    -->End     )     select * from CTE  order by LTRIM(numstr) --最后将这个整型数据转换为字符串型的进行排序

3、C#代码：

public class readexcel    {        #region Excel2003        /// 
        /// 将Excel文件中的数据读出到DataTable中(xls)        /// 
        ///         /// 
             public static DataTable ExcelToTableForXLS(string file)        {            DataTable dt = new DataTable();            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))            {                HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);                ISheet sheet = hssfworkbook.GetSheetAt(0);                //表头                IRow header = sheet.GetRow(sheet.FirstRowNum);                List
     
       columns = new List
      
       ();                for (int i = 0; i < header.LastCellNum; i++)                {                    object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);                    if (obj == null || obj.ToString() == string.Empty)                    {                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));                        //continue;                    }                    else                        dt.Columns.Add(new DataColumn(obj.ToString()));                    columns.Add(i);                }                //数据                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)                {                    DataRow dr = dt.NewRow();                    bool hasValue = false;                    foreach (int j in columns)                    {                        if (sheet.GetRow(i)==null)                        {                            continue;                        }                        dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);                        if (dr[j] != null && dr[j].ToString() != string.Empty)                        {                            hasValue = true;                        }                    }                    if (hasValue)                    {                        dt.Rows.Add(dr);                    }                }            }            return dt;        }        /// 
       
        /// 将DataTable数据导出到Excel文件中(xls)        /// 
        /// 
               /// 
               public static void TableToExcelForXLS(DataTable dt, string file)        {            HSSFWorkbook hssfworkbook = new HSSFWorkbook();            ISheet sheet = hssfworkbook.CreateSheet("Test");            //表头            IRow row = sheet.CreateRow(0);            for (int i = 0; i < dt.Columns.Count; i++)            {                ICell cell = row.CreateCell(i);                cell.SetCellValue(dt.Columns[i].ColumnName);            }            //数据            for (int i = 0; i < dt.Rows.Count; i++)            {                IRow row1 = sheet.CreateRow(i + 1);                for (int j = 0; j < dt.Columns.Count; j++)                {                    ICell cell = row1.CreateCell(j);                    cell.SetCellValue(dt.Rows[i][j].ToString());                }            }            //转为字节数组            MemoryStream stream = new MemoryStream();            hssfworkbook.Write(stream);            var buf = stream.ToArray();            //保存为Excel文件            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))            {                fs.Write(buf, 0, buf.Length);                fs.Flush();            }        }        /// 
       
        /// 获取单元格类型(xls)        /// 
        /// 
               /// 
               private static object GetValueTypeForXLS(HSSFCell cell)        {            if (cell == null)                return null;            switch (cell.CellType)            {                case CellType.Blank: //BLANK:                    return null;                case CellType.Boolean: //BOOLEAN:                    return cell.BooleanCellValue;                case CellType.Numeric: //NUMERIC:                    return cell.NumericCellValue;                case CellType.String: //STRING:                    return cell.StringCellValue;                case CellType.Error: //ERROR:                    return cell.ErrorCellValue;                case CellType.Formula: //FORMULA:                default:                    return "=" + cell.CellFormula;            }        }        #endregion    }