DataToExcelText.cs 16 KB

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