DataToExcelText.cs 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461
  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[] clist = type.Split('-');
  104. string nType = targetType(clist[0]);
  105. //Console.WriteLine("nType-----.." + nType);
  106. if (dic.ContainsKey(nType))
  107. {
  108. //Console.WriteLine("type-----..." + type);
  109. dic[nType].Add(type, num);
  110. }
  111. else
  112. {
  113. Dictionary<string, int> itemDic = new Dictionary<string, int>();
  114. itemDic.Add(type, num);
  115. dic[nType] = itemDic;
  116. //Console.WriteLine("type-----..." + type);
  117. }
  118. }
  119. HSSFWorkbook workbook = new HSSFWorkbook();
  120. foreach (KeyValuePair<string, Dictionary<string,int>> kv in dic)
  121. {
  122. Dictionary<string, int> dict = kv.Value;
  123. Console.WriteLine("kv--key-----........"+kv.Key);
  124. string tableName = "指标统计表_" + kv.Key;
  125. //创建工作表
  126. var sheet = workbook.CreateSheet(tableName);
  127. var row = sheet.CreateRow(0);
  128. //创建单元格
  129. var cellid = row.CreateCell(0);
  130. cellid.SetCellValue("指标描述信息");
  131. var cellname = row.CreateCell(1);
  132. cellname.SetCellValue("指标类型");
  133. var cellpwd = row.CreateCell(2);
  134. cellpwd.SetCellValue("指标累计值");
  135. int x = 1;
  136. foreach (KeyValuePair<string,int>kt in dict)
  137. {
  138. string type = kt.Key;
  139. int num = kt.Value;
  140. string desc = TargetDesc(type);
  141. //Console.WriteLine("ktttt--type-----........" + type);
  142. //Console.WriteLine("ktttt--name-----........" + desc);
  143. var rowi = sheet.CreateRow(x);
  144. var cdesc = rowi.CreateCell(0);
  145. cdesc.SetCellValue(desc);
  146. var cType = rowi.CreateCell(1);
  147. cType.SetCellValue(type);
  148. var cnum = rowi.CreateCell(2);
  149. cnum.SetCellValue(num);
  150. x += 1;
  151. }
  152. }
  153. TimeSpan ts = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1);
  154. float curday = MathF.Floor((long)ts.TotalSeconds / 24 / 60 / 60);
  155. string txtName = "指标累计信息表_" + "zone" + zoneid+"-day-"+curday;
  156. FileStream file = new FileStream(@"F:\excel\" + txtName + ".xls", FileMode.CreateNew, FileAccess.Write);
  157. workbook.Write(file);
  158. file.Dispose();
  159. Console.WriteLine("指标累计execl完成........");
  160. }
  161. public static string TargetDesc(string type)
  162. {
  163. string[] sList = type.Split('-');
  164. var mem = Redis.Ins.GetDatabase(0);
  165. string desc = "";
  166. switch (sList[0])
  167. {
  168. case "registerUserNum":
  169. desc = "注册人数累计";
  170. break;
  171. case "comNewGuideUserNum":
  172. desc = "完成新手引导人数累计";
  173. break;
  174. case "unlockbuidId":
  175. string ctx = mem.HashGet(MemKey_Game.Build(),sList[1]);
  176. JObject ctxDic = JObject.Parse(ctx);
  177. string s = ctxDic["name"].ToString();
  178. desc = "累计解锁建筑-" + s+"的人数";
  179. break;
  180. case "mainTaskId_ComUserNum"://主线任务id--完成人数
  181. desc = "完成主线任务id-" + sList[1] + "的人数";
  182. break;
  183. case "shopTaskId_ComUserNum":
  184. desc = "完成悬赏任务id-" + sList[1] + "的人数";
  185. break;
  186. case "shopTaskId_BuyUserNum":
  187. desc = "购买悬赏任务id-" + sList[1] + "的人数";
  188. break;
  189. case "collegeTaskId_ComUserNum":
  190. desc = "完成学院任务id-" + sList[1] + "的人数";
  191. break;
  192. case "collegeCouseId_UnlockUserNum":
  193. desc = "解锁学院课程id-" + sList[1] + "的人数";
  194. break;
  195. case "dailyTaskId_ComUserNum":
  196. desc = "完成日常任务id-" + sList[1] + "的人数";
  197. break;
  198. case "storyGateId_ComUserNum":
  199. desc = "完成剧情关卡id-" + sList[1] + "的人数";
  200. break;
  201. case "storyGateId_BattleNum":
  202. desc = "剧情关卡id-" + sList[1] + "的挑战次数/通关次数";
  203. break;
  204. case "HuanLingShiGateId_ComUserNum":
  205. desc = "完成副本唤灵师关卡id-" + sList[1] + "的人数";
  206. break;
  207. case "HuanLingShiGateId_BattleNum":
  208. desc = "完成副本唤灵师关卡id-" + sList[1] + "的挑战次数/通关次数";
  209. break;
  210. case "WeaponGateId_ComUserNum":
  211. desc = "完成副本武器关卡id-" + sList[1] + "的人数";
  212. break;
  213. case "WeaponGateId_BattleNum":
  214. desc = "完成副本武器关卡id-" + sList[1] + "的挑战次数/通关次数";
  215. break;
  216. case "YanLingGateId_ComUserNum":
  217. desc = "完成副本言灵关卡id-" + sList[1] + "的人数";
  218. break;
  219. case "YanLingGateId_BattleNum":
  220. desc = "完成副本言灵关卡id-" + sList[1] + "的挑战次数/通关次数";
  221. break;
  222. case "userlevel":
  223. desc = "玩家等级为" + sList[1] + "的人数";
  224. break;
  225. case "newUserLotteryDraw_one":
  226. desc = "新手池抽卡单次抽奖的访问频率";
  227. break;
  228. case "newUserLotteryDraw_ten":
  229. desc = "新手池抽卡十连抽的访问频率";
  230. break;
  231. case "yanLingLotteryDraw_one":
  232. desc = "言灵活动祈愿奖池单次抽奖的访问频率";
  233. break;
  234. case "yanLingLotteryDraw_ten":
  235. desc = "言灵活动祈愿奖池十连抽的访问频率";
  236. break;
  237. case "weaponLotteryDraw_one":
  238. desc = "武器活动祈愿奖池单次抽奖的访问频率";
  239. break;
  240. case "weaponLotteryDraw_ten":
  241. desc = "武器活动祈愿奖池十连抽的访问频率";
  242. break;
  243. case "commonLotteryDraw_one":
  244. desc = "常驻祈愿奖池单次抽奖的访问频率";
  245. break;
  246. case "commonLotteryDraw_ten":
  247. desc = "常驻祈愿奖池十连抽的访问频率";
  248. break;
  249. case "HuanLingShiLevel_UserNum":
  250. desc = "唤灵师"+sList[1]+"等级为"+sList[2]+"的人数";
  251. break;
  252. case "HuanLingShiId_UserNum":
  253. desc = "拥有唤灵师"+ sList[1]+"的人数";
  254. break;
  255. case "YanLingId_UserNum":
  256. desc = "拥有言灵" + sList[1] + "的人数";
  257. break;
  258. case "YanLingIdLevel_UserNum":
  259. desc = "言灵" + sList[1] + "等级为" + sList[2] + "的人数";
  260. break;
  261. case "WeaponId_UserNum":
  262. desc = "拥有武器" + sList[1] + "的人数";
  263. break;
  264. case "WeaponIdLevel_UserNum":
  265. desc = "武器" + sList[1] + "等级为" + sList[2] + "的人数";
  266. break;
  267. }
  268. return desc;
  269. }
  270. public static string targetType(string t)
  271. {
  272. string type = "";
  273. switch (t)
  274. {
  275. case "registerUserNum":
  276. type = "registerUserNum";
  277. break;
  278. case "comNewGuideUserNum":
  279. type = "comNewGuideUserNum";
  280. break;
  281. case "unlockbuidId":
  282. type = "unlockbuidId";
  283. break;
  284. case "mainTaskId_ComUserNum"://主线任务id--完成人数
  285. type = "mainTask";
  286. break;
  287. case "shopTaskId_ComUserNum":
  288. type = "shopTask";
  289. break;
  290. case "shopTaskId_BuyUserNum":
  291. type = "shopTask";
  292. break;
  293. case "collegeTaskId_ComUserNum":
  294. type = "collegeTask";
  295. break;
  296. case "collegeCouseId_UnlockUserNum":
  297. type = "collegeTask";
  298. break;
  299. case "dailyTaskId_ComUserNum":
  300. type = "dailyTask";
  301. break;
  302. case "storyGateId_ComUserNum":
  303. type = "storyGate";
  304. break;
  305. case "storyGateId_BattleNum":
  306. type = "storyGate";
  307. break;
  308. case "HuanLingShiGateId_ComUserNum":
  309. type = "HuanLingShiGate";
  310. break;
  311. case "HuanLingShiGateId_BattleNum":
  312. type = "HuanLingShiGate";
  313. break;
  314. case "WeaponGateId_ComUserNum":
  315. type = "WeaponGate";
  316. break;
  317. case "WeaponGateId_BattleNum":
  318. type = "WeaponGate";
  319. break;
  320. case "YanLingGateId_ComUserNum":
  321. type = "YanLingGate";
  322. break;
  323. case "YanLingGateId_BattleNum":
  324. type = "YanLingGate";
  325. break;
  326. case "userlevel":
  327. type = "userlevel";
  328. break;
  329. case "newUserLotteryDraw_one":
  330. type = "LotteryDraw";
  331. break;
  332. case "newUserLotteryDraw_ten":
  333. type = "LotteryDraw";
  334. break;
  335. case "yanLingLotteryDraw_one":
  336. type = "LotteryDraw";
  337. break;
  338. case "yanLingLotteryDraw_ten":
  339. type = "LotteryDraw";
  340. break;
  341. case "weaponLotteryDraw_one":
  342. type = "LotteryDraw";
  343. break;
  344. case "weaponLotteryDraw_ten":
  345. type = "LotteryDraw";
  346. break;
  347. case "commonLotteryDraw_one":
  348. type = "LotteryDraw";
  349. break;
  350. case "commonLotteryDraw_ten":
  351. type = "LotteryDraw";
  352. break;
  353. case "HuanLingShiLevel_UserNum":
  354. type = "HuanLingShi";
  355. break;
  356. case "HuanLingShiId_UserNum":
  357. type = "HuanLingShi";
  358. break;
  359. case "YanLingId_UserNum":
  360. type = "YanLing";
  361. break;
  362. case "YanLingIdLevel_UserNum":
  363. type = "YanLing";
  364. break;
  365. case "WeaponId_UserNum":
  366. type = "Weapon";
  367. break;
  368. case "WeaponIdLevel_UserNum":
  369. type = "Weapon";
  370. break;
  371. }
  372. return type;
  373. }
  374. }
  375. public class User
  376. {
  377. /// <summary>
  378. /// 索引
  379. /// </summary>
  380. public int Id { get; set; }
  381. public string Name { get; set; }
  382. public string pwd { get; set; }
  383. public string type { get; set; }
  384. }
  385. }