首页 > 代码库 > 从excel文件中获取数据 转化成list集合 并去重

从excel文件中获取数据 转化成list集合 并去重

  static void Main(string[] args)
        {
            string filepath = @"E:\新房媒体-C端收款.xlsx";

            //IReadEntDataFromExcel iReadEntDataFromExcel = new  ReadEntDataFromExcelInfo();
            //List<String> result=iReadEntDataFromExcel.ReadEntDataFromExcel(filepath);

            DataTable data=http://www.mamicode.com/GetDataFromExcel(filepath);
            



            List<TradeFlow> list = new List<TradeFlow>();
            list = ConvertToModel(data);
            Dictionary<string, List<TradeFlow>> dictTradeFlow = new Dictionary<string, List<TradeFlow>>();
            List<TradeFlow> listTemp = new List<TradeFlow>();

            //TODO  1、先把SequenceID不为空的记录加载到dict里,同时记录SequenceID为空的到listTemp
            foreach (var item in list)
            {
                if (item.SequenceID != "")
                {
                    if (!dictTradeFlow.ContainsKey(item.TradeNo))
                    {
                        List<TradeFlow> li = new List<TradeFlow>();
                        li.Add(item);
                        dictTradeFlow.Add(item.TradeNo, li);
                    }
                    else
                    {
                        dictTradeFlow[item.TradeNo].Add(item);
                    }
                }
                else
                    listTemp.Add(item);

            }

            //TODO  2、遍历listTemp,与dict里面的tradeno相同的进行比较,加上其他比对条件,重复则不添

            foreach (var item in listTemp)
            {
                if (dictTradeFlow.ContainsKey(item.TradeNo))
                {
                    List<TradeFlow> li = dictTradeFlow[item.TradeNo];

                    bool bFlag = false;
                    foreach (var flow in li)
                    {
                        if (item.PayType == flow.PayType && item.Amount == flow.Amount)
                        {
                            bFlag = true;
                            break;
                        }
                    }
                    if (!bFlag)
                    {
                        dictTradeFlow[item.TradeNo].Add(item);
                    }
                }
                else
                {
                    dictTradeFlow.Add(item.TradeNo, new List<TradeFlow>() { item });
                }
            }
            list = new List<TradeFlow>();
            foreach (var item in dictTradeFlow)
            {
                list.AddRange(item.Value);
            }
            SortedDictionary<DateTime, List<TradeFlow>> sorteDictTradeFlow = new SortedDictionary<DateTime, List<TradeFlow>>();
            foreach (var item in list)
            {
                if (!sorteDictTradeFlow.ContainsKey(item.CreateTime))
                {
                    List<TradeFlow> li = new List<TradeFlow>();
                    li.Add(item);
                    sorteDictTradeFlow.Add(item.CreateTime, li);
                }
                else
                {
                    sorteDictTradeFlow[item.CreateTime].Add(item);
                }
            }
            list = new List<TradeFlow>();
            foreach (var item in sorteDictTradeFlow)
            {
                list.AddRange(item.Value);
            }
            list.Reverse();
        }

    

 

饮用方法和model:

 

 /// <summary>
        /// 通过AsposeCells得到DataTable
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static DataTable GetDataFromExcel(string filepath)
        {
            Workbook workbook = new Workbook();
            //workbook.Open("C:\\test.xlsx");
            workbook.Open(filepath);
            Cells cells = workbook.Worksheets[0].Cells;

            for (int i = 0; i < cells.MaxDataRow + 1; i++)
            {
                for (int j = 0; j < cells.MaxDataColumn + 2; j++)
                {
                    string s = cells[i, j].StringValue.Trim();
                    //一行行的读取数据,插入数据库的代码也可以在这里写
                }
            }
            //System.Data.DataTable dataTable1 = cells.ExportDataTable(1, 0, cells.MaxDataRow, cells.MaxColumn);//noneTitle  里边的数字 1  0 可以修改
            System.Data.DataTable dataTable2 = cells.ExportDataTable(0, 0, cells.MaxDataRow, cells.MaxColumn, true);//showTitle
            //return dataTable1;
            return dataTable2;
        }

       /// <summary>
       /// datatable 转化为list集合
       /// </summary>
       /// <param name="dt"></param>
       /// <returns></returns>
        public static List<TradeFlow> ConvertToModel(DataTable dt)
        {

            List<TradeFlow> ts = new List<TradeFlow>();// 定义集合
            Type type = typeof(TradeFlow); // 获得此模型的类型
            string tempName = "";
            foreach (DataRow dr in dt.Rows)
            {
                TradeFlow t = new TradeFlow();
                PropertyInfo[] propertys = t.GetType().GetProperties();// 获得此模型的公共属性
                foreach (PropertyInfo pi in propertys)
                {
                    tempName = pi.Name;
                    // 检查DataTable是否包含此列
                    if (dt.Columns.Contains(tempName))
                    {
                        // 判断此属性是否有Setter
                        if (!pi.CanWrite) continue;
                        object value = http://www.mamicode.com/dr[tempName];
                        if (value != DBNull.Value)
                            pi.SetValue(t, value, null);
                    }
                }
                ts.Add(t);
            }
            return ts;
        }
        public class TradeFlow
        {
            #region 扩展字段
            /// <summary>
            /// 充值流水号
            /// </summary>
            public string SequenceID { get; set; }
            #endregion

            /// <summary>
            /// 编号
            /// </summary>
            public string ID { get; set; }


            /// <summary>
            ///我方编号 用户编号/或商户编号
            /// </summary>
            public string OwnerID { get; set; }

            /// <summary>
            ///对方编号 用户编号/或商户编号
            /// </summary>
            public string Opposite { get; set; }

            /// <summary>
            /// 交易编号
            /// </summary>
            public string TradeNo { get; set; }


            /// <summary>
            /// 外部交易编号
            /// </summary>
            public string OutTradeNo { get; set; }


            /// <summary>
            /// 交易类型(1=在线支付,2=充值、3=交易退款、4=批量代发到账户、5=批量代发到银行卡、6=收费、7=提现、8=充值撤销、9=代扣)
            /// </summary>
            public int PayType { get; set; }

            /// <summary>
            /// 交易类型
            /// <para>2=充值</para>
            ///<para>10000=新房服务、10003=新房_新房帮竞标</para>
            ///<para>20000=二手房服务、20001=二手房_租房房源置顶、20002=二手房_租房帮APP置顶、20003=二手房_二手房房源置顶,20005=二手房_租房付佣金</para>
            ///<para>30000=家居服务、30001=家居_家居抢购、30002=家居_装修订单,30003=家居_装修帮APP装修订单</para>
            ///<para>40000=研究院服务、</para>
            ///<para>90000=搜房服务</para>
            ///</summary>
            public int TradeType { get; set; }

            /// <summary>
            /// 流水类型(1=收入,2=支出)
            /// </summary>
            public int FlowType { get; set; }


            /// <summary>
            /// 交易金额
            /// </summary>
            public decimal Amount { get; set; }

            /// <summary>
            /// 收入金额
            /// </summary>
            public decimal Income { get; set; }

            /// <summary>
            /// 支出金额
            /// </summary>
            public decimal PayOut { get; set; }


            /// <summary>
            /// 交易后金额
            /// </summary>
            public decimal AfterBalance { get; set; }

            /// <summary>
            /// 交易标题
            /// </summary>
            public string Title { get; set; }


            /// <summary>
            /// 交易描述
            /// </summary>
            public string Subject { get; set; }


            /// <summary>
            /// 交易时间
            /// </summary>
            public DateTime CreateTime { get; set; }

            /// <summary>
            /// 交易渠道(BALANCE、ALIPAY、YTPAY、AUTHPAY,BFBPAY等)
            /// </summary>
            public string TradeChannel { get; set; }

            /// <summary>
            /// 来源
            /// </summary>
            public string Origin { get; set; }

            /// <summary>
            /// 交易城市
            /// </summary>
            public string TradeCity { get; set; }

            /// <summary>
            /// 备注
            /// </summary>
            public string Description { get; set; }

            /// <summary>
            /// 币种名称
            /// </summary>
            public string CurrencyName { get; set; }

            #region /********Consume扩展********/
            /// <summary>
            /// 订单使用账户余额金额
            /// </summary>
            public decimal BalanceQuantity { get; set; }

            /// <summary>
            /// 订单使用第三方金额
            /// </summary>
            public decimal RestQuantity { get; set; }

            #endregion
        }

 

从excel文件中获取数据 转化成list集合 并去重