Net导出Excel

using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using NuctechProject.DTO;
using NuctechProject.DTO.Bll;
using System.Data;
using System.IO;
using System.Web;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.OleDb;
using System.Collections.Generic;
using DataTable = System.Data.DataTable;

namespace NuctechProject.Layouts.Project
{
    public partial class EditTasks : LayoutsPageBase
    {
        string proUrl = Common.proUrl;
        string url = Common.rootUrl;
        private ProjectBll bll = new ProjectBll();
        private UserBLL userbll = new UserBLL();
        protected void Page_Load(object sender, EventArgs e)
        {

            if (!IsPostBack)
            {
                //这里是获取的
                this.hideID.Value = GetQueryString("proid");
                using (SPWeb spWeb = new SPSite(url).OpenWeb())
                {
                    SPList spLists = spWeb.GetList(url + "/Lists/ProjectList/AllItems.aspx");
                    if (!string.IsNullOrEmpty(GetQueryString("proid")))
                    {
                        SPListItem item = spLists.GetItemById(Convert.ToInt32(GetQueryString("proid")));
                        this.lblTitle.Text = item["ProjectName"].ToString();//显示项目名
                    }

                }
                bool isEdit = GetIsEditTemp();
                if (isEdit)
                {
                    divChangeTemp.Style.Add("display", "block");
                }
            }
            if (string.IsNullOrEmpty(GetQueryString("type")) == false)
            {
                Response.ContentType = "text/plain";
                SenderPost(Convert.ToInt32(GetQueryString("proid")));
                Response.End();
            }
        }

        public bool GetIsEditTemp()
        {
            bool isEdit = true;
            int proid = 0;
            int.TryParse(hideID.Value, out proid);
            if (proid != 0)
            {
                using (SPWeb spWeb = new SPSite(proUrl).OpenWeb())
                {
                    SPList spLists = spWeb.GetList(proUrl + "/Lists/" + proid + "/AllItems.aspx");
                    SPQuery spQuery = new SPQuery();
                    spQuery.RowLimit = 1;
                    spQuery.Query = @"<OrderBy>
                                      <FieldRef Name='Version' Ascending='False' />
                                   </OrderBy>";
                    SPListItemCollection itemColl = spLists.GetItems(spQuery);
                    if (itemColl.Count > 0)
                    {
                        SPListItem item = itemColl[0];
                        if (item["Version"] != null)
                        {
                            int ver = 0;
                            int.TryParse(item["Version"].ToString(), out ver);
                            if (ver > 0)
                            {
                                isEdit = false;
                            }
                        }
                    }
                }
            }
            return isEdit;
        }

        private string GetQueryString(string key)
        {
            if (this.Request.QueryString[key] != null)
            {
                return this.Request.QueryString[key].ToString();
            }
            else
            {
                return string.Empty;
            }
        }
      
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnToExcel_Click(object sender, EventArgs e)
        {
            DownTaskTempXlsx(proUrl, hideID.Value);
        }

        protected void SenderPost(int proid)
        {
            //根据项目ID查找pid==0  xiangm
            string projectname = string.Empty;
            SPFieldUserValueCollection userValueCol = new SPFieldUserValueCollection();

            using (SPWeb spWeb = new SPSite(proUrl).OpenWeb())
            {
                SPList spLists = spWeb.GetList(proUrl + "/Lists/" + proid + "/AllItems.aspx");
                SPQuery spQuery = new SPQuery();
                spQuery.Query = @"<Where>
                                      <And>
                                         <And>
                                            <Eq>
                                               <FieldRef Name='PID' />
                                               <Value Type='Number'>0</Value>
                                            </Eq>
                                            <Eq>
                                               <FieldRef Name='VersionState' />
                                               <Value Type='Choice'>草稿</Value>
                                            </Eq>
                                         </And>
                                         <Eq>
                                            <FieldRef Name='Version' />
                                            <Value Type='Number'>" + bll.GetLastVasionNumber(Convert.ToInt32(hideID.Value)) + @"</Value>
                                         </Eq>
                                      </And>
                                   </Where>";

                SPListItemCollection items = spLists.GetItems(spQuery);
                SPFieldUserValueCollection user = new SPFieldUserValueCollection();
                foreach (SPListItem item in items)
                {
                    SPFieldUserValueCollection fuzeren = item["AssignedTo"] as SPFieldUserValueCollection;
                    if (fuzeren != null)
                    {
                        //一级节点的
                        user.AddRange(fuzeren);
                    }
                    #region 2

                    spQuery.Query = @"<Where>
                                      <And>
                                         <And>
                                            <Eq>
                                               <FieldRef Name='PID' />
                                               <Value Type='Number'>" + item["ID"].ToString() + @"</Value>
                                            </Eq>
                                            <Eq>
                                               <FieldRef Name='VersionState' />
                                               <Value Type='Choice'>草稿</Value>
                                            </Eq>
                                         </And>
                                         <Eq>
                                            <FieldRef Name='Version' />
                                            <Value Type='Number'>" + bll.GetLastVasionNumber(Convert.ToInt32(hideID.Value)) + @"</Value>
                                         </Eq>
                                      </And>
                                   </Where>";
                    SPListItemCollection childers = spLists.GetItems(spQuery);
                    foreach (SPListItem tx in childers)
                    {
                        SPFieldUserValueCollection childs = item["AssignedTo"] as SPFieldUserValueCollection;
                        if (childs != null)
                        {
                            //2级节点的
                            user.AddRange(childs);
                        }

                    }
                    #endregion

                }

                string title = "项目【" + this.lblTitle.Text + "】有更新";
                string body = "<a href=\"" + url + "/_layouts/15/Project/EditTasks.aspx?proid=" + proid + "\">点击查看变更信息。</a>";
                string openurl = url + "/_layouts/15/Project/EditTasks.aspx?proid=" + proid;
                Common.SendNotify(title, body, DateTime.Now, user, openurl);
                foreach (var tx in user)
                {
                    if (!string.IsNullOrEmpty(tx.User.Email))
                    {
                        Common.SendEmail(tx.User.Email, title, body);
                    }
                }

            }
        }

        public void DownTaskTempXlsx(string webPath, string listName)
        {
            string projectName = string.Empty;
            SPSecurity.RunWithElevatedPrivileges(delegate
            {
                using (SPWeb spWeb = new SPSite(url).OpenWeb())
                {
                    SPList projectList = spWeb.GetList(url + "/Lists/ProjectList/AllItems.aspx");
                    SPQuery projectQuery = new SPQuery();
                    projectQuery.Query = @"<Where>
                                              <Eq>
                                                 <FieldRef Name='ID' />
                                                 <Value Type='Counter'>" + listName + @"</Value>
                                              </Eq>
                                           </Where>";
                    SPListItemCollection projectItemColl = projectList.GetItems(projectQuery);
                    if (projectItemColl.Count > 0)
                    {
                        SPListItem projectItem = projectItemColl[0];
                        if (projectItem["ProjectName"] != null)
                            projectName = projectItem["ProjectName"].ToString();
                    }
                }
                using (SPWeb web = new SPSite(webPath).OpenWeb())
                {
                    string userNameStr = System.Web.HttpContext.Current.User.Identity.Name;
                    string userName = userNameStr.Substring(userNameStr.LastIndexOf(@"\") + 1);
                    string strDateName = DateTime.Now.ToString("hhmmss");
                    //构建DataTable，规则为第一个为任务名，最后一个为层级
                    var dt = new DataTable();
                    dt.Columns.Add("里程碑", typeof(string));
                    dt.Columns.Add("文件编号", typeof(string));
                    dt.Columns.Add("责任人", typeof(string));
                    dt.Columns.Add("计划开始时间", typeof(string));
                    dt.Columns.Add("计划结束时间", typeof(string));
                    dt.Columns.Add("实际开始时间", typeof(string));
                    dt.Columns.Add("实际结束时间", typeof(string));
                    dt.Columns.Add("输出文件", typeof(string));
                    dt.Columns.Add("任务状态", typeof(string));
                    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[listName];
                    int lastVasion = bll.GetLastVasionNumber(int.Parse(listName));
                    dt = GetDataTable(0, spList, dt, 1, lastVasion);
                    if (dt != null)
                    {
                        if (dt.Rows.Count > 0)
                        {
                            //string templatePath = Server.MapPath("Upfiles\\" + userName + strDateName + ".xlsx");
                            string folderPath = Server.MapPath("~/_layouts/15/ToExcel/" + userName + "/");
                            string templatePath = Server.MapPath("~/_layouts/15/ToExcel/" + userName + "/" + userName + strDateName + ".xlsx");
                            if (Directory.Exists(folderPath))     //根目录
                            {
                                //判断上传目录是否存在（删除遗留文件夹）
                                Directory.Delete(folderPath, true);
                            }
                            if (!Directory.Exists(folderPath))
                            {
                                Directory.CreateDirectory(folderPath);
                            }
                            #region 删除以username为开头的文件
                            //string path = Server.MapPath("Upfiles\\");
                            DirectoryInfo directory = new DirectoryInfo(folderPath);
                            FileInfo[] fileInfoArray = directory.GetFiles();
                            foreach (FileInfo file in fileInfoArray)
                            {
                                if (file.Name.Contains(userName))
                                {
                                    file.Delete();
                                }
                            }
                            #endregion

                            DataTabletoExcel(dt, templatePath, projectName, lastVasion);
                            Context.Response.ContentType = "application/ms-excel";
                            Context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(projectName + "进度计划v" + lastVasion + ".xlsx", Encoding.UTF8));
                            Response.BinaryWrite(File.ReadAllBytes(templatePath));
                            Context.Response.End();
                            // 删除副本
                            File.Delete(templatePath);
                        }
                        else
                        {
                            Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('列表为空不能导出到Excel');</script>");
                        }
                    }
                    else
                    {
                        Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('列表为空不能导出到Excel');</script>");
                    }
                }
            });
        }

        public static void DataTabletoExcel(DataTable tmpDataTable, string strFileName, string projectName, int lastVasion)
        {
            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.ApplicationClass MyExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            MyExcel.Visible = false;//excel是否可见
            MyExcel.DisplayAlerts = false;//屏蔽一些弹出窗口
            Microsoft.Office.Interop.Excel.Workbooks MyWorkBooks = MyExcel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook MyWorkBook = MyWorkBooks.Add(System.Type.Missing);
            Microsoft.Office.Interop.Excel.Worksheet MyWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)MyWorkBook.Worksheets[1];

            //ar xlApp = new Application();
            //xlApp.DefaultFilePath = "";
            //xlApp.DisplayAlerts = true;
            //xlApp.SheetsInNewWorkbook = 1;
            //Workbook xlBook = xlApp.Workbooks.Add(true);

            MyWorkSheet.Cells[1, 1] = projectName + " 进度计划";
            //导入列名
            for (int i = 0; i < (columnNum + cengValue); i++)
            {
                if (i == 1)
                {
                    MyWorkSheet.Cells[2, i] = tmpDataTable.Columns[i - 1].ColumnName;
                }
                if (i <= cengMax && i > 1)
                {
                    MyWorkSheet.Cells[2, i] = GetCapital(i - 1) + "级任务";
                }
                if (i > cengMax)
                {
                    MyWorkSheet.Cells[2, 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());
                        MyWorkSheet.Cells[i + 3, ceng] = tmpDataTable.Rows[i][j].ToString();
                    }
                    if (j >= 1)
                    {
                        MyWorkSheet.Cells[i + 3, j + cengMax] = tmpDataTable.Rows[i][j].ToString();
                    }
                    columnIndex++;
                }
            }

            MyExcel.Application.DisplayAlerts = false;
            Range RangeTitle = MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[1, 1], (Range)MyWorkSheet.Cells[1, Convert.ToInt16(columnNum + cengValue - 1)]);
            RangeTitle.Merge(false);
            RangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
            RangeTitle.Interior.ColorIndex = 34;
            MyExcel.Application.DisplayAlerts = true;

            Range Range1 = MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[1, 1], (Range)MyWorkSheet.Cells[rowNum + 2, Convert.ToInt16(columnNum + cengValue - 1)]);
            Range1.Font.Name = "微软雅黑";
            Range1.EntireColumn.AutoFit();
            Range1.BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, null);
            Range1.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;//块内竖线
            Range1.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;//块内横线

            //MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[1, 1],(Range)MyWorkSheet.Cells[1, Convert.ToInt16(columnNum + cengValue - 1)]).Interior.ColorIndex = 16;
            //MyWorkSheet.get_Range((Range)MyWorkSheet.Cells[1, 1], (Range)MyWorkSheet.Cells[10000, 100]).EntireColumn.AutoFit(); //列宽自适应
            SPSecurity.RunWithElevatedPrivileges(delegate { MyWorkBook.SaveCopyAs(strFileName); });
        }

        /// <summary>
        /// 获取一个任务的子任务
        /// </summary>
        /// <param name="item"></param>
        /// <param name="parentKey"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(int parentId, SPList list, DataTable dt, int ceng, int lastVasion)
        {
            var query = new SPQuery();
            query.Query = @"<Where>
                                <And>
                                     <And>
                                        <Eq>
                                         <FieldRef Name='PID' />
                                         <Value Type='Number'>" + parentId.ToString() + @"</Value>
                                        </Eq>
                                        <Eq>
                                            <FieldRef Name='VersionState' />
                                            <Value Type='Choice'>草稿</Value>
                                        </Eq>
                                    </And>
                                <Eq>
                                    <FieldRef Name='Version' />
                                    <Value Type='Number'>" + lastVasion + @"</Value>
                                </Eq>
                            </And>
                       </Where>";
            SPListItemCollection items = list.GetItems(query);
            foreach (SPListItem item in items)
            {
                string taskName = "";
                if (item["TaskName"] != null)
                {
                    taskName = item["TaskName"].ToString();
                }
                string fileID = "";
                if (item["FileID"] != null)
                {
                    fileID = item["FileID"].ToString();
                }
                string outputFile = "";
                if (item["OutputFile"] != null)
                {
                    outputFile = item["OutputFile"].ToString();
                }
                string closeUser = "";
                if (item["CloseUser"] != null)
                {
                    closeUser = item["CloseUser"].ToString().Substring(item["CloseUser"].ToString().LastIndexOf("#") + 1);
                }
                string body = "";
                if (item["Body"] != null)
                {
                    body = item["Body"].ToString();
                }
                string startDate = "";
                if (item["StartDate"] != null)
                {
                    startDate = Convert.ToDateTime(item["StartDate"]).ToShortDateString();
                }
                string closeDate = "";
                if (item["CloseDate"] != null)
                {
                    closeDate = Convert.ToDateTime(item["CloseDate"]).ToShortDateString();
                }
                string actuaStartDate = "";
                if (item["ActuaStartDate"] != null)
                {
                    actuaStartDate = Convert.ToDateTime(item["ActuaStartDate"]).ToShortDateString();
                }
                string actuaDueDate = "";
                if (item["ActuaDueDate"] != null)
                {
                    actuaDueDate = Convert.ToDateTime(item["ActuaDueDate"]).ToShortDateString();
                }
                string dueDate = "";
                if (item["DueDate"] != null)
                {
                    dueDate = Convert.ToDateTime(item["DueDate"]).ToShortDateString();
                }
                string assignedTo = "";
                if (item["AssignedTo"] != null)
                {
                    assignedTo = item["AssignedTo"].ToString().Substring(item["AssignedTo"].ToString().LastIndexOf("#") + 1);
                }
                string status = "";
                if (item["Status"] != null)
                {
                    status = item["Status"].ToString();
                }
                dt.Rows.Add(taskName, fileID, assignedTo, startDate, dueDate, actuaStartDate, actuaDueDate, outputFile, status, closeUser, closeDate, body, ceng.ToString());
                dt = GetDataTable(item.ID, list, dt, ceng + 1, lastVasion);
            }
            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 "更多";
        }

    }
}