server/CSserver/DataTransfer/tongji/DataToExcelText.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using NPOI.HSSF.UserModel;
using StackExchange.Redis;
using CSharpUtil;


using Newtonsoft.Json.Linq;
//using System.Diagnostics;

namespace DataTransfer.tongji
{
    class DataToExcelText
    {
        public static void excelText(string memKey, int retain, int zoneid, float day)
        {
            //获取redis种的数据
            var mem = Redis.Ins.GetDatabase(0);

            HashEntry[] ctxList = mem.HashGetAll(memKey);
            int length = ctxList.Length;
            if (length <= 0)
            {
                return;
            }

            Dictionary<string, string> levelDic = new Dictionary<string, string>();
            Dictionary<string, string> taskDic = new Dictionary<string, string>();
            Dictionary<string, string> gateDic = new Dictionary<string, string>();

            foreach (var item in ctxList)
            {
                string[] sList = item.ToString().Split(':');
                string type = sList[0];
                string num = sList[1];

                string[] ctx = type.Split('-');
                string name = ctx[0];
                //string id = ctx[1];            

                if (name == "level")
                {
                    levelDic[type] = num;
                }
                else if (name == "task")
                {
                    taskDic[type] = num;
                }
                else if (name == "gateId")
                {
                    gateDic[type] = num;
                }
            }

            Dictionary<string, string> dic = levelDic.Union(taskDic).ToDictionary(k => k.Key, v => v.Value);
            Dictionary<string, string> newdic = dic.Union(gateDic).ToDictionary(k => k.Key, v => v.Value);

            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建工作表            
            var sheet = workbook.CreateSheet("信息表");
            var row = sheet.CreateRow(0);
            //创建单元格
            var cellid = row.CreateCell(0);
            cellid.SetCellValue("指标类型");
            var cellname = row.CreateCell(1);
            cellname.SetCellValue("指标id");
            var cellpwd = row.CreateCell(2);
            cellpwd.SetCellValue("指标累计值");

            int x = 1;
            foreach (KeyValuePair<string, string> kv in newdic)
            {
                string[] sList = kv.Key.Split('-');
                string name = sList[0];
                string id = sList[1];

                int num = int.Parse(kv.Value);

                var rowi = sheet.CreateRow(x);
                var cname = rowi.CreateCell(0);
                cname.SetCellValue(name);

                var cid = rowi.CreateCell(1);
                cid.SetCellValue(id);

                var cnum = rowi.CreateCell(2);
                cnum.SetCellValue(num);

                x += 1;
            }

            string txtName = "流失玩家指标累计信息表_" + "zone" + zoneid + "_" + retain + "lose_" + day;

            FileStream file = new FileStream(@"F:\excel\" + txtName + ".xls", FileMode.CreateNew, FileAccess.Write);
            workbook.Write(file);
            file.Dispose();
        }

        /*
        /// <summary>
        /// 指标累计值数据转为execl表
        /// </summary>
        public static void TargetStatistics()
        {
            var mem = Redis.Ins.GetDatabase(0);

            int zoneid = 1;
            HashEntry[] ctxList = mem.HashGetAll(MemKey_Statistics.TargetStatistics(zoneid));
            int length = ctxList.Length;
            if (length <= 0)
            {
                return;
            }


            Dictionary<string, Dictionary<string, int>> dic = new Dictionary<string, Dictionary<string, int>>();

            foreach (var item in ctxList)
            {
                string[] sList = item.ToString().Split(':');
                string type = sList[0];
                int num = int.Parse(sList[1]);

                string[] clist = type.Split('-');               
                string nType = targetType(clist[0]);
                //Console.WriteLine("nType-----.." + nType);
                if (dic.ContainsKey(nType))
                {
                    //Console.WriteLine("type-----..." + type);
                    dic[nType].Add(type, num);
                }
                else
                {
                    Dictionary<string, int> itemDic = new Dictionary<string, int>();
                    itemDic.Add(type, num);
                    dic[nType] = itemDic;
                    //Console.WriteLine("type-----..." + type);
                }
            }

            HSSFWorkbook workbook = new HSSFWorkbook();
                       
            foreach (KeyValuePair<string, Dictionary<string,int>> kv in dic)
            {
                Dictionary<string, int> dict =  kv.Value;
                Console.WriteLine("kv--key-----........"+kv.Key);

                string tableName = "指标统计表_" + kv.Key;
                //创建工作表            
                var sheet = workbook.CreateSheet(tableName);
                var row = sheet.CreateRow(0);
                //创建单元格
                var cellid = row.CreateCell(0);
                cellid.SetCellValue("指标描述信息");
                var cellname = row.CreateCell(1);
                cellname.SetCellValue("指标类型");
                var cellpwd = row.CreateCell(2);
                cellpwd.SetCellValue("指标累计值");
                int x = 1;
                foreach (KeyValuePair<string,int>kt in dict)
                {
                    string type = kt.Key;
                    int num = kt.Value;                   
                    string desc = TargetDesc(type);

                    //Console.WriteLine("ktttt--type-----........" + type);
                    //Console.WriteLine("ktttt--name-----........" + desc);
                
                    var rowi = sheet.CreateRow(x);
                    var cdesc = rowi.CreateCell(0);
                    cdesc.SetCellValue(desc);

                    var cType = rowi.CreateCell(1);
                    cType.SetCellValue(type);

                    var cnum = rowi.CreateCell(2);
                    cnum.SetCellValue(num);

                    x += 1;
                }

            }

            TimeSpan ts = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1);
            float curday = MathF.Floor((long)ts.TotalSeconds / 24 / 60 / 60);

            string txtName = "指标累计信息表_" + "zone" + zoneid+"-day-"+curday;

            FileStream file = new FileStream(@"F:\excel\" + txtName + ".xls", FileMode.CreateNew, FileAccess.Write);
            workbook.Write(file);
            file.Dispose();

            Console.WriteLine("指标累计execl完成........");
        }

        public static string TargetDesc(string type)
        {
            string[] sList = type.Split('-');
            var mem = Redis.Ins.GetDatabase(0);
            string desc = "";
            switch (sList[0])
            {
                case "registerUserNum":
                    desc = "注册人数累计";
                    break;
                case "comNewGuideUserNum":
                    desc = "完成新手引导人数累计";
                    break;
                case "unlockbuidId":                  
                    string ctx = mem.HashGet(MemKey_Game.Build(),sList[1]);
                    JObject ctxDic = JObject.Parse(ctx);
                    string s = ctxDic["name"].ToString();
                    desc = "累计解锁建筑-" + s+"的人数";
                    break;
                case "mainTaskId_ComUserNum"://主线任务id--完成人数
                    desc = "完成主线任务id-" + sList[1] + "的人数";

                    break;

                case "shopTaskId_ComUserNum":
                    desc = "完成悬赏任务id-" + sList[1] + "的人数";

                    break;
                case "shopTaskId_BuyUserNum":
                    desc = "购买悬赏任务id-" + sList[1] + "的人数";

                    break;
                case "collegeTaskId_ComUserNum":
                    desc = "完成学院任务id-" + sList[1] + "的人数";

                    break;
                case "collegeCouseId_UnlockUserNum":
                    desc = "解锁学院课程id-" + sList[1] + "的人数";

                    break;

                case "dailyTaskId_ComUserNum":
                    desc = "完成日常任务id-" + sList[1] + "的人数";

                    break;
                case "storyGateId_ComUserNum":
                    desc = "完成剧情关卡id-" + sList[1] + "的人数";
                    break;

                case "storyGateId_BattleNum":
                    desc = "剧情关卡id-" + sList[1] + "的挑战次数/通关次数";
                    break;

                case "HuanLingShiGateId_ComUserNum":
                    desc = "完成副本唤灵师关卡id-" + sList[1] + "的人数";
                    break;
                case "HuanLingShiGateId_BattleNum":
                    desc = "完成副本唤灵师关卡id-" + sList[1] + "的挑战次数/通关次数";
                    break;
                case "WeaponGateId_ComUserNum":
                    desc = "完成副本武器关卡id-" + sList[1] + "的人数";
                    break;
                case "WeaponGateId_BattleNum":
                    desc = "完成副本武器关卡id-" + sList[1] + "的挑战次数/通关次数";
                    break;

                case "YanLingGateId_ComUserNum":
                    desc = "完成副本言灵关卡id-" + sList[1] + "的人数";
                    break;
                case "YanLingGateId_BattleNum":
                    desc = "完成副本言灵关卡id-" + sList[1] + "的挑战次数/通关次数";
                    break;
                case "userlevel":
                    desc = "玩家等级为" + sList[1] + "的人数";
                    break;
                case "newUserLotteryDraw_one":
                    desc = "新手池抽卡单次抽奖的访问频率";
                    break;
                case "newUserLotteryDraw_ten":
                    desc = "新手池抽卡十连抽的访问频率";
                    break;
                case "yanLingLotteryDraw_one":
                    desc = "言灵活动祈愿奖池单次抽奖的访问频率";
                    break;
                case "yanLingLotteryDraw_ten":
                    desc = "言灵活动祈愿奖池十连抽的访问频率";
                    break;
                case "weaponLotteryDraw_one":
                    desc = "武器活动祈愿奖池单次抽奖的访问频率";
                    break;
                case "weaponLotteryDraw_ten":
                    desc = "武器活动祈愿奖池十连抽的访问频率";
                    break;

                case "commonLotteryDraw_one":
                    desc = "常驻祈愿奖池单次抽奖的访问频率";
                    break;
                case "commonLotteryDraw_ten":
                    desc = "常驻祈愿奖池十连抽的访问频率";
                    break;

                case "HuanLingShiLevel_UserNum":
                    desc = "唤灵师"+sList[1]+"等级为"+sList[2]+"的人数";
                    break;
                case "HuanLingShiId_UserNum":
                    desc = "拥有唤灵师"+ sList[1]+"的人数";
                    break;

                case "YanLingId_UserNum":
                    desc = "拥有言灵" + sList[1] + "的人数";
                    break;
                case "YanLingIdLevel_UserNum":
                    desc = "言灵" + sList[1] + "等级为" + sList[2] + "的人数";
                    break;
                case "WeaponId_UserNum":
                    desc = "拥有武器" + sList[1] + "的人数";
                    break;
                case "WeaponIdLevel_UserNum":
                    desc = "武器" + sList[1] + "等级为" + sList[2] + "的人数";
                    break;
              
            }


            return desc;
        }

        public static string targetType(string t)
        {
            string type = "";
            switch (t)
            {
                case "registerUserNum":
                    type = "registerUserNum";
                    break;
                case "comNewGuideUserNum":
                    type = "comNewGuideUserNum";
                    break;
                case "unlockbuidId":
                    type = "unlockbuidId";
                    break;
                case "mainTaskId_ComUserNum"://主线任务id--完成人数
                    type = "mainTask";

                    break;

                case "shopTaskId_ComUserNum":
                    type = "shopTask";

                    break;
                case "shopTaskId_BuyUserNum":
                    type = "shopTask";

                    break;
                case "collegeTaskId_ComUserNum":
                    type = "collegeTask";
                    break;
                case "collegeCouseId_UnlockUserNum":
                    type = "collegeTask";

                    break;

                case "dailyTaskId_ComUserNum":
                    type = "dailyTask";

                    break;
                case "storyGateId_ComUserNum":
                    type = "storyGate";
                    break;

                case "storyGateId_BattleNum":
                    type = "storyGate";
                    break;

                case "HuanLingShiGateId_ComUserNum":
                    type = "HuanLingShiGate";
                    break;
                case "HuanLingShiGateId_BattleNum":
                    type = "HuanLingShiGate";
                    break;
                case "WeaponGateId_ComUserNum":
                    type = "WeaponGate";
                    break;
                case "WeaponGateId_BattleNum":
                    type = "WeaponGate";
                    break;

                case "YanLingGateId_ComUserNum":
                    type = "YanLingGate";
                    break;
                case "YanLingGateId_BattleNum":
                    type = "YanLingGate";
                    break;
                case "userlevel":
                    type = "userlevel";
                    break;
                case "newUserLotteryDraw_one":
                    type = "LotteryDraw";
                    break;
                case "newUserLotteryDraw_ten":
                    type = "LotteryDraw";
                    break;
                case "yanLingLotteryDraw_one":
                    type = "LotteryDraw";
                    break;
                case "yanLingLotteryDraw_ten":
                    type = "LotteryDraw";
                    break;
                case "weaponLotteryDraw_one":
                    type = "LotteryDraw";
                    break;
                case "weaponLotteryDraw_ten":
                    type = "LotteryDraw";
                    break;

                case "commonLotteryDraw_one":
                    type = "LotteryDraw";
                    break;
                case "commonLotteryDraw_ten":
                    type = "LotteryDraw";
                    break;

                case "HuanLingShiLevel_UserNum":
                    type = "HuanLingShi";
                    break;
                case "HuanLingShiId_UserNum":
                    type = "HuanLingShi";
                    break;

                case "YanLingId_UserNum":
                    type = "YanLing";
                    break;
                case "YanLingIdLevel_UserNum":
                    type = "YanLing";
                    break;
                case "WeaponId_UserNum":
                    type = "Weapon";
                    break;
                case "WeaponIdLevel_UserNum":
                    type = "Weapon"; 
                    break;

            }

            return type;
        }
        */
    }

    public class User
    {
        /// <summary>
        /// 索引
        /// </summary>
        public int Id { get; set; }

        public string Name { get; set; }

        public string pwd { get; set; }

        public string type { get; set; }
    }
}