導出Excel

修行的嘟嘟 2014-09-15

展開全文

public ActionResult ExportFeedBack(string beginTime, string endTime)
        {
            Paging page = new Paging { PageIndex = 1, PageSize = 100000000 };
            PagedList<GetCallCentreSearchForFeedBack_Result> list = CustomerBLL.Instance.GetFeedBack(beginTime, endTime, page);
            DataTable dt = ListToDataTable<GetCallCentreSearchForFeedBack_Result>(list);

            //開始處理導出EXCEL

            if (list.Count > 0)
            {
                string temp = string.Format("attachment;filename={0}", "FeedBackList.csv");
                Response.ClearHeaders();

                Response.AppendHeader("Content-disposition", temp);

                Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
                Response.ContentType = "application/vnd.ms-excel;charset=gb2312";
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                Response.Write(ExportCSV(ds));
                Response.End();
            }
            return View();
        }

/// <summary>
        /// 將泛類型集合List類轉換成DataTable
        /// </summary>
        /// <param name="list">泛類型集合</param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //檢查實體集合不能為空
            if (entitys == null || entitys.Count < 1)
            {
                throw new Exception("需轉換的集合為空");
            }
            //取出第一個實體的所有Propertie
            Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生產(chǎn)代碼中，應將生成的DataTable結構Cache起來，此處略
            DataTable dt = new DataTable();
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //將所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //檢查所有的的實體都為同一類型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要轉換的集合元素類型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }

        /// <summary>
        /// 導出Excel.
        /// </summary>
        /// <param name="ds">The ds.</param>
        /// <returns></returns>
        /// 創(chuàng)建者：王宇
        /// 創(chuàng)建日期：9/15/2014 11:42 AM
        /// 修改者：
        /// 修改時間：
        /// ------------------------------------
        public string ExportCSV(DataSet ds)
        {
            string data = "";
            //data = ds.DataSetName + "\n";

            foreach (DataTable tb in ds.Tables)
            {
                //data += tb.TableName + "\n"; //原來
                data += "問題反饋列表" + "\n";

                //寫出列名
                foreach (DataColumn column in tb.Columns)
                {
                    //data += column.ColumnName + ","; //原來
                    string columnName = GetColumnName(column.ColumnName);
                    if (!string.IsNullOrEmpty(columnName))
                    {
                        data += columnName + ",";
                    }
                }
                data += "\n";

                //寫出數(shù)據(jù)
                foreach (DataRow row in tb.Rows)
                {
                    foreach (DataColumn column in tb.Columns)
                    {
                        //data += row[column].ToString() + ","; //原來
                        string value = GetColumnValue(column.ToString(), row[column].ToString()).Replace("\n", " ");
                        if (value != "TMP")
                        {
                            data += value + ",";
                        }
                    }
                    data += "\n";
                }
                data += "\n";
            }

            return data;
        }

        /// <summary>
        /// 獲取列.
        /// </summary>
        /// <param name="columnName">列名稱.</param>
        /// <returns></returns>
        /// 創(chuàng)建者：王宇
        /// 創(chuàng)建日期：9/15/2014 11:42 AM
        /// 修改者：
        /// 修改時間：
        /// ------------------------------------
        private string GetColumnName(string columnName)
        {
            string tmp = string.Empty;
            switch (columnName)
            {
                case "EmailOrPhone":
                    tmp = "手機號/郵箱";
                    break;
                case "Source":
                    tmp = "模板";
                    break;
                case "Path":
                    tmp = "下單來源";
                    break;
                case "CreateDate":
                    tmp = "反饋時間";
                    break;
                case "Content":
                    tmp = "反饋內(nèi)容";
                    break;
                default:
                    break;
            }
            return tmp;
        }

        /// <summary>
        /// 獲取列值.
        /// </summary>
        /// <param name="columnName">Name of the column.</param>
        /// <param name="value">The value.</param>
        /// <returns></returns>
        /// 創(chuàng)建者：王宇
        /// 創(chuàng)建日期：9/15/2014 11:42 AM
        /// 修改者：
        /// 修改時間：
        /// ------------------------------------
        private string GetColumnValue(string columnName, string value)
        {
            string tmp = string.Empty;
            switch (columnName)
            {
                case "OrderPath":
                    tmp = "TMP";
                    break;
                case "Name":
                    tmp = "TMP";
                    break;
                default:
                    tmp = value;
                    break;
            }
            return tmp;
        }