层级结构SPList导出到Excel

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Web;

namespace NuctechProject.Layouts.Settings
{
    public partial class ToExcel : LayoutsPageBase
    {
        private static readonly string _rootUrl = HttpContext .Current.Request.Url.Scheme + "://" +
                                                  HttpContext.Current.Request.Url.Host;

        private static readonly string weburl = _rootUrl + "/" ;
        private readonly string StrUrl = weburl + "newTemplate/"; //公告站点地址
        protected void Page_Load(object sender, EventArgs e)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate
            {
                using (var site = new SPSite(StrUrl))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        //构建DataTable，规则为第一个为任务名，最后一个为层级
                        var dt = new System.Data.DataTable();
                        dt.Columns.Add( "里程碑" , typeof( string));
                        dt.Columns.Add( "责任人" , typeof( string));
                        dt.Columns.Add( "任务状态" , typeof( string));
                        dt.Columns.Add( "ceng", typeof (string)); //不用更改，不显示，需放在最后
                        SPList spList = web.Lists["TastTemp" ];
                        dt = GetDataTable(0, spList, dt, 1);
                        DataTabletoExcel(dt, @"C:\Users\Administrator\Desktop\strTemplateFileName.xlsx" );
                    }
                }
                ;
            });
        }
        public static void DataTabletoExcel(System.Data. DataTable tmpDataTable, string strFileName)
        {
            if (tmpDataTable == null )
                return;
            int rowNum = tmpDataTable.Rows.Count;
            int columnNum = tmpDataTable.Columns.Count;
            int rowIndex = 1;
            int columnIndex = 0;
            int cengMax = 0;
            cengMax = Convert.ToInt32(tmpDataTable.Compute("max(ceng)" , ""));
            int cengValue = cengMax - 1;
            Microsoft.Office.Interop.Excel. Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            xlApp.DefaultFilePath = "";
            xlApp.DisplayAlerts = true;
            xlApp.SheetsInNewWorkbook = 1;
            Workbook xlBook = xlApp.Workbooks.Add(true );
            //导入列名
            for (int i = 0; i < (columnNum + cengValue); i++)
            {
                if (i == 1)
                {
                    xlApp.Cells[1, i] = tmpDataTable.Columns[i - 1].ColumnName;
                }
                if (i <= cengMax && i > 1)
                {
                    xlApp.Cells[1, i] = GetCapital(i - 1) + "级任务" ;
                }
                if (i > cengMax)
                {
                    xlApp.Cells[1, i] = tmpDataTable.Columns[(i - cengMax)].ColumnName;
                }
            }
            //将DataTable中的数据导入Excel中
            for (int i = 0; i < rowNum; i++)
            {
                rowIndex++;
                columnIndex = 0;
                for (int j = 0; j < (columnNum - 1); j++)
                {
                    if (j < 1)
                    {
                        int ceng = Convert .ToInt16(tmpDataTable.Rows[i][columnNum - 1].ToString());
                        xlApp.Cells[i + 2, ceng] = tmpDataTable.Rows[i][j].ToString();
                    }
                    if (j >= 1)
                    {
                        xlApp.Cells[i + 2, j + cengMax] = tmpDataTable.Rows[i][j].ToString();
                    }
                    columnIndex++;
                }
            }
            xlApp.get_Range((Microsoft.Office.Interop.Excel. Range)xlApp.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range )xlApp.Cells[1, Convert.ToInt16(columnNum + cengValue - 1)]).Interior.ColorIndex = 16;
            xlApp.get_Range((Microsoft.Office.Interop.Excel. Range)xlApp.Cells[1, 1], (Microsoft.Office.Interop.Excel.Range )xlApp.Cells[10000, 100]).EntireColumn.AutoFit();//列宽自适应
            xlBook.SaveCopyAs(strFileName);
        }

        /// <summary>
        /// 获取一个任务的子任务
        /// </summary>
        /// <param name="item"></param>
        /// <param name="parentKey"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetDataTable( int parentId, SPList list, System.Data.DataTable dt, int ceng)
        {
            SPQuery query = new SPQuery();
            query.Query = @"<Where>
                          <Eq>
                             <FieldRef Name='PID' />
                             <Value Type='Number'>" + parentId.ToString() + @"</Value>
                          </Eq>
                       </Where>" ;
            SPListItemCollection items = list.GetItems(query);
            foreach (SPListItem item in items)
            {
                string taskName = item["TaskName" ].ToString();
                string assignedTo = item["AssignedTo" ].ToString().Substring(item["AssignedTo"].ToString().LastIndexOf( "#") + 1);
                string status = item["Status" ].ToString();
                dt.Rows.Add(taskName, assignedTo, status, ceng.ToString());
                dt = GetDataTable(item.ID, list, dt, ceng + 1);
            }
            return dt;
        }
        public static string GetCapital( int number)
        {
            switch (number)
            {
                case 1:
                    return "一" ;
                case 2:
                    return "二" ;
                case 3:
                    return "三" ;
                case 4:
                    return "四" ;
                case 5:
                    return "五" ;
            }
            return "更多" ;
        }
    }


}