首页 > 代码库 > 导出EXCEL

导出EXCEL

 /// <summary>
        /// ModelList 转换为DataTable
        /// </summary>
        /// <typeparam name="TModel">model实体</typeparam>
        /// <typeparam name="TField">字段实体</typeparam>
        ///  <typeparam name="TView">字段实体</typeparam>
        /// <param name="modelList">要转换的Model列表</param>
        /// <param name="fieldList">要转换的表头实体列表</param>
        /// <param name="titleName">表头中列名--TField表中列头显示名称的字段</param>
        /// <param name="fieldName">表头中列名--TField表中列头 名称的字段</param> 
        /// <returns></returns>
        public static DataTable ConvertListToDataTable<TModel, TField>(List<TModel> modelList, List<TField> fieldList = null, string titleName = "", string fieldName = "")
        {
            #region 判空处理
            

            if (null != fieldList && fieldList.Count > 0
                && (string.IsNullOrEmpty(titleName) ||
                 string.IsNullOrWhiteSpace(titleName) ||
                 string.IsNullOrWhiteSpace(fieldName) ||
                 string.IsNullOrEmpty(fieldName)))
            {
                return null;
            }
            #endregion

            var table = new DataTable();
            DataColumn column;
            DataRow row;

            #region 有表头数据的时候,需要处理表头数据,而且处理表数据的时候需要根据传入的表头数据来获取相关表数据
            if (null != fieldList && fieldList.Count > 0)
            {
                var titlePropertyList = typeof(TField).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).ToList();

                var titleProperty = titlePropertyList.Find(_ => _.Name == titleName);
                var fieldProperty = titlePropertyList.Find(_ => _.Name == fieldName);

                if (null == titleProperty || null == fieldProperty)
                {
                    return null;
                }
                #region 追加表头

                fieldList.ForEach(_ =>
                {
                    var title = titleProperty.GetValue(_, null);

                    var fieldNameValues = fieldProperty.GetValue(_, null);

                    column = new DataColumn(title.ToString(), titleProperty.PropertyType);
                    table.Columns.Add(column);

                });


                #endregion

                #region 表数据
                int count = 0;
                if (null != modelList && modelList.Count > 0)
                {
                    modelList.ForEach(model =>
                    {
                        row = table.NewRow();
                        fieldList.ForEach(_ =>
                        {
                            var fieldNameValues = fieldProperty.GetValue(_, null);
                            var title = titleProperty.GetValue(_, null);
                            row[title.ToString()] = GetFiledValue<TModel>(model, null != fieldNameValues ? fieldNameValues.ToString() : null);
                            count++;
                        });
                        table.Rows.Add(row);
                    });

                }
                #endregion

                return table;
            }
            #endregion

            #region 没有表头实体的时候,直接处理model列表
            var propertyList = typeof(TModel).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance).ToList();

            modelList.ForEach(model =>
            {
                if (null != model)
                {
                    row = table.NewRow();

                    propertyList.ForEach(property =>
                    {
                        string name = property.Name;
                        if (table.Columns[name] == null)
                        {
                            column = new DataColumn(name, property.PropertyType);
                            table.Columns.Add(column);
                        }

                        row[name] = property.GetValue(model, null);
                    });

                    table.Rows.Add(row);
                }
            });

            return table;
            #endregion
        }

        /// <summary>
        /// 根据filed名称获取数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="model"></param>
        /// <param name="filedName"></param>
        /// <param name="extraFiled"></param>
        /// <param name="hyevent">会议实体</param>
        /// <returns></returns>
        public static string GetFiledValue<T>(T model, string filedName)
        {
            if (ReflectionHelper.GetFields(typeof(T)).Contains(filedName))
            {
                
                var propertyHead = model.GetType().GetProperty(filedName);
                if (filedName.Contains("CreateTime"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        long dt = (long)o;
                        if (dt > 0)
                        {
                            return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt));
                        }
                        return "";
                    }
                    else
                        return "";
                }
                else if (filedName.Contains("Time")&&filedName!= "LogisticsTime")
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        long dt = (long)o;
                        if (dt > 0)
                        {
                            return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt));
                        }
                        return "";
                    }
                    else
                        return "";
                }
                if (filedName.Contains("Date"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        long dt = (long)o;
                        return CommonHelper.GetTrueDate(Ex.FromUnixTime(dt));
                    }
                    else
                        return "";
                }
                if (filedName.Contains("Link"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                         
                        return CommonHelper.GetTrueURL(o.ToString());
                    }
                    else
                        return "";
                }
                if (propertyHead.PropertyType.FullName.Contains("System.DateTime"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        DateTime dt = (DateTime)o;
                        return dt.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    else
                        return "";
                }
                else if (propertyHead.PropertyType.FullName.Contains("System.Boolean"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        if (filedName.Equals("IsEnable"))
                        {
                            return (bool)o == true ? "启用" : "禁用";
                        }

                        return (bool)o == true ? "" : "";
                    }
                    else
                        return "";
                }
                else if (propertyHead.PropertyType.FullName.Contains("System.Collections.Generic.List"))
                {
                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false)
                    {
                        if (propertyHead.PropertyType.FullName.Contains("System.String"))
                        {
                            var list = (List<String>)o;
                            return string.Join(",", list);
                        }
                        else if (propertyHead.PropertyType.FullName.Contains("System.Int64"))
                        {
                            var list = (List<long>)o;
                            return string.Join(",", list);
                        }
                        else if (propertyHead.PropertyType.FullName.Contains("Int32"))
                        {
                            var list = (List<int>)o;
                            return string.Join(",", list);
                        }
                        else
                        {
                            return string.Join(",", o);
                        }

                    }
                    else
                        return "";
                }
                else if (propertyHead.PropertyType.FullName.Contains("Enum"))
                {

                    object o = propertyHead.GetValue(model);
                    if (o != null && string.IsNullOrWhiteSpace(o.ToString()) == false && !o.ToString().Equals("0"))
                    {
                        var enumvalue =http://www.mamicode.com/ (System.Enum)o;

                        return CommonHelper.GetDescription(enumvalue);
                    }
                    else
                    {
                        if (filedName.Equals("CompleteStatus"))
                        {
                            return "未完成";
                        }
                        return "";
                    }
                }
                else
                    return Convert.ToString(propertyHead.GetValue(model));
            }

            return "";
           
        }

var dt = ExportHelper.ConvertListToDataTable(serviceList, fieldList, "DisplayName", "FieldName");
json = JsonConvert.SerializeObject(dt);

 

var data = http://www.mamicode.com/JsonConvert.DeserializeObject>(json);
var excel = BuildExcel(data);




///导出设置   

public byte[] BuildExcel(List<JObject> data) { using (ExcelPackage package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("sheet1"); var colums = data[0].Properties().ToList(); for (int j = 1; j <= colums.Count; j++) { worksheet.Cells[1, j].Value = http://www.mamicode.com/colums[j - 1].Name; } string cellName = string.Empty; for (int i = 1; i <= data.Count; i++) { var row = data[i - 1]; for (int j = 1; j <= colums.Count; j++) { cellName = row[colums[1].Name].ToString(); worksheet.Cells[i + 1, j].Value = http://www.mamicode.com/row[colums[j - 1].Name].ToString(); } } var num = data.Count+2; worksheet.Cells[num, 1].Style.ShrinkToFit = false; int total = 0; if (!string.IsNullOrWhiteSpace(cellName)) { total = (num > 2 ? num - 2 : 0); } worksheet.Cells[num,1].Value = http://www.mamicode.com/"总计 "+ total + " 条记录"; return package.GetAsByteArray(); } }

 

 

 

导出EXCEL