DataToExcelText.cs 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. using System.IO;
  7. using NPOI.HSSF.UserModel;
  8. using StackExchange.Redis;
  9. using CSharpUtil;
  10. using Newtonsoft.Json.Linq;
  11. //using System.Diagnostics;
  12. namespace DataTransfer.tongji
  13. {
  14. class DataToExcelText
  15. {
  16. public static void excelText(string memKey, int retain, int zoneid, float day)
  17. {
  18. //获取redis种的数据
  19. var mem = Redis.Ins.GetDatabase(0);
  20. HashEntry[] ctxList = mem.HashGetAll(memKey);
  21. int length = ctxList.Length;
  22. if (length <= 0)
  23. {
  24. return;
  25. }
  26. Dictionary<string, string> levelDic = new Dictionary<string, string>();
  27. Dictionary<string, string> taskDic = new Dictionary<string, string>();
  28. Dictionary<string, string> gateDic = new Dictionary<string, string>();
  29. foreach (var item in ctxList)
  30. {
  31. string[] sList = item.ToString().Split(':');
  32. string type = sList[0];
  33. string num = sList[1];
  34. string[] ctx = type.Split('-');
  35. string name = ctx[0];
  36. //string id = ctx[1];
  37. if (name == "level")
  38. {
  39. levelDic[type] = num;
  40. }
  41. else if (name == "task")
  42. {
  43. taskDic[type] = num;
  44. }
  45. else if (name == "gateId")
  46. {
  47. gateDic[type] = num;
  48. }
  49. }
  50. Dictionary<string, string> dic = levelDic.Union(taskDic).ToDictionary(k => k.Key, v => v.Value);
  51. Dictionary<string, string> newdic = dic.Union(gateDic).ToDictionary(k => k.Key, v => v.Value);
  52. HSSFWorkbook workbook = new HSSFWorkbook();
  53. //创建工作表
  54. var sheet = workbook.CreateSheet("信息表");
  55. var row = sheet.CreateRow(0);
  56. //创建单元格
  57. var cellid = row.CreateCell(0);
  58. cellid.SetCellValue("指标类型");
  59. var cellname = row.CreateCell(1);
  60. cellname.SetCellValue("指标id");
  61. var cellpwd = row.CreateCell(2);
  62. cellpwd.SetCellValue("指标累计值");
  63. int x = 1;
  64. foreach (KeyValuePair<string, string> kv in newdic)
  65. {
  66. string[] sList = kv.Key.Split('-');
  67. string name = sList[0];
  68. string id = sList[1];
  69. int num = int.Parse(kv.Value);
  70. var rowi = sheet.CreateRow(x);
  71. var cname = rowi.CreateCell(0);
  72. cname.SetCellValue(name);
  73. var cid = rowi.CreateCell(1);
  74. cid.SetCellValue(id);
  75. var cnum = rowi.CreateCell(2);
  76. cnum.SetCellValue(num);
  77. x += 1;
  78. }
  79. string txtName = "流失玩家指标累计信息表_" + "zone" + zoneid + "_" + retain + "lose_" + day;
  80. FileStream file = new FileStream(@"F:\excel\" + txtName + ".xls", FileMode.CreateNew, FileAccess.Write);
  81. workbook.Write(file);
  82. file.Dispose();
  83. }
  84. /// <summary>
  85. /// 指标累计值数据转为execl表
  86. /// </summary>
  87. public static void TargetStatistics()
  88. {
  89. var mem = Redis.Ins.GetDatabase(0);
  90. int zoneid = 1;
  91. HashEntry[] ctxList = mem.HashGetAll(MemKey_Statistics.TargetStatistics(zoneid));
  92. int length = ctxList.Length;
  93. if (length <= 0)
  94. {
  95. return;
  96. }
  97. Dictionary<string, Dictionary<string, int>> dic = new Dictionary<string, Dictionary<string, int>>();
  98. foreach (var item in ctxList)
  99. {
  100. string[] sList = item.ToString().Split(':');
  101. string type = sList[0];
  102. int num = int.Parse(sList[1]);
  103. string[] cLict = type.Split('-');
  104. string nType = cLict[0];
  105. if (dic.ContainsKey(nType))
  106. {
  107. dic[type].Add(type, num);
  108. }
  109. else
  110. {
  111. Dictionary<string, int> itemDic = new Dictionary<string, int>();
  112. itemDic.Add(type, num);
  113. dic[type] = itemDic;
  114. }
  115. }
  116. HSSFWorkbook workbook = new HSSFWorkbook();
  117. //创建工作表
  118. var sheet = workbook.CreateSheet("指标统计表");
  119. var row = sheet.CreateRow(0);
  120. //创建单元格
  121. var cellid = row.CreateCell(0);
  122. cellid.SetCellValue("指标描述信息");
  123. var cellname = row.CreateCell(1);
  124. cellname.SetCellValue("指标类型");
  125. var cellpwd = row.CreateCell(2);
  126. cellpwd.SetCellValue("指标累计值");
  127. int x = 1;
  128. foreach (KeyValuePair<string, Dictionary<string,int>> kv in dic)
  129. {
  130. Dictionary<string, int> dict = kv.Value;
  131. foreach (KeyValuePair<string,int>kt in dict)
  132. {
  133. string type = kt.Key;
  134. int num = kt.Value;
  135. string desc = TargetDesc(type);
  136. var rowi = sheet.CreateRow(x);
  137. var cdesc = rowi.CreateCell(0);
  138. cdesc.SetCellValue(desc);
  139. var cType = rowi.CreateCell(1);
  140. cType.SetCellValue(type);
  141. var cnum = rowi.CreateCell(2);
  142. cnum.SetCellValue(num);
  143. x += 1;
  144. }
  145. }
  146. string txtName = "指标累计信息表_" + "zone" + zoneid;
  147. FileStream file = new FileStream(@"F:\excel\" + txtName + ".xls", FileMode.CreateNew, FileAccess.Write);
  148. workbook.Write(file);
  149. file.Dispose();
  150. }
  151. public static string TargetDesc(string type)
  152. {
  153. string[] sList = type.Split('-');
  154. var mem = Redis.Ins.GetDatabase(0);
  155. string desc = "";
  156. switch (sList[0])
  157. {
  158. case "registerUserNum":
  159. desc = "注册人数累计";
  160. break;
  161. case "comNewGuideUserNum":
  162. desc = "完成新手引导人数累计";
  163. break;
  164. case "unlockbuidId":
  165. string ctx = mem.HashGet(MemKey_Game.Build(),sList[1]);
  166. JObject ctxDic = JObject.Parse(ctx);
  167. string s = ctxDic["name"].ToString();
  168. desc = "累计解锁建筑-" + s+"的人数";
  169. break;
  170. case "mainTaskId_ComUserNum"://主线任务id--完成人数
  171. desc = "完成主线任务id-" + sList[1] + "的人数";
  172. break;
  173. case "shopTaskId_ComUserNum":
  174. desc = "完成悬赏任务id-" + sList[1] + "的人数";
  175. break;
  176. case "shopTaskId_BuyUserNum":
  177. desc = "购买悬赏任务id-" + sList[1] + "的人数";
  178. break;
  179. case "collegeTaskId_ComUserNum":
  180. desc = "完成学院任务id-" + sList[1] + "的人数";
  181. break;
  182. case "collegeCouseId_UnlockUserNum":
  183. desc = "解锁学院课程id-" + sList[1] + "的人数";
  184. break;
  185. case "dailyTaskId_ComUserNum":
  186. desc = "完成日常任务id-" + sList[1] + "的人数";
  187. break;
  188. case "storyGateId_ComUserNum":
  189. desc = "完成剧情关卡id-" + sList[1] + "的人数";
  190. break;
  191. case "storyGateId_BattleNum":
  192. desc = "剧情关卡id-" + sList[1] + "的挑战次数/通关次数";
  193. break;
  194. case "HuanLingShiGateId_ComUserNum":
  195. desc = "完成副本唤灵师关卡id-" + sList[1] + "的人数";
  196. break;
  197. case "HuanLingShiGateId_BattleNum":
  198. desc = "完成副本唤灵师关卡id-" + sList[1] + "的挑战次数/通关次数";
  199. break;
  200. case "WeaponGateId_ComUserNum":
  201. desc = "完成副本武器关卡id-" + sList[1] + "的人数";
  202. break;
  203. case "WeaponGateId_BattleNum":
  204. desc = "完成副本武器关卡id-" + sList[1] + "的挑战次数/通关次数";
  205. break;
  206. case "YanLingGateId_ComUserNum":
  207. desc = "完成副本言灵关卡id-" + sList[1] + "的人数";
  208. break;
  209. case "YanLingGateId_BattleNum":
  210. desc = "完成副本言灵关卡id-" + sList[1] + "的挑战次数/通关次数";
  211. break;
  212. case "userlevel":
  213. desc = "玩家等级为-" + sList[1] + "的人数";
  214. break;
  215. case "newUserLotteryDraw_one":
  216. desc = "新手池抽卡单次抽奖的访问频率";
  217. break;
  218. case "newUserLotteryDraw_ten":
  219. desc = "新手池抽卡十连抽的访问频率";
  220. break;
  221. case "yanLingLotteryDraw_one":
  222. desc = "言灵活动祈愿奖池单次抽奖的访问频率";
  223. break;
  224. case "yanLingLotteryDraw_ten":
  225. desc = "言灵活动祈愿奖池十连抽的访问频率";
  226. break;
  227. case "weaponLotteryDraw_one":
  228. desc = "武器活动祈愿奖池单次抽奖的访问频率";
  229. break;
  230. case "weaponLotteryDraw_ten":
  231. desc = "武器活动祈愿奖池十连抽的访问频率";
  232. break;
  233. case "commonLotteryDraw_one":
  234. desc = "常驻祈愿奖池单次抽奖的访问频率";
  235. break;
  236. case "commonLotteryDraw_ten":
  237. desc = "常驻祈愿奖池十连抽的访问频率";
  238. break;
  239. case "HuanLingShiLevel_UserNum":
  240. desc = "唤灵师"+sList[1]+"等级为"+sList[2]+"的人数";
  241. break;
  242. case "HuanLingShiId_UserNum":
  243. desc = "拥有唤灵师"+ sList[1]+"的人数";
  244. break;
  245. case "YanLingId_UserNum":
  246. desc = "拥有言灵" + sList[1] + "的人数";
  247. break;
  248. case "YanLingIdLevel_UserNum":
  249. desc = "言灵" + sList[1] + "等级为" + sList[2] + "的人数";
  250. break;
  251. case "WeaponId_UserNum":
  252. desc = "拥有武器" + sList[1] + "的人数";
  253. break;
  254. case "WeaponIdLevel_UserNum":
  255. desc = "武器" + sList[1] + "等级为" + sList[2] + "的人数";
  256. break;
  257. }
  258. return desc;
  259. }
  260. }
  261. public class User
  262. {
  263. /// <summary>
  264. /// 索引
  265. /// </summary>
  266. public int Id { get; set; }
  267. public string Name { get; set; }
  268. public string pwd { get; set; }
  269. public string type { get; set; }
  270. }
  271. }