public Entity_sheet1 GenerateData([FilePath] string filePath) { string exportPath = filePath.Replace(Path.GetExtension(filePath), ".asset"); Entity_sheet1 data = (Entity_sheet1)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_sheet1)); if (data == null) { data = ScriptableObject.CreateInstance <Entity_sheet1> (); AssetDatabase.CreateAsset((ScriptableObject)data, exportPath); // data.hideFlags = HideFlags.NotEditable; data.name = Path.GetFileNameWithoutExtension(filePath); } if (data.sheets.Count > 0 && data.sheets[0].list.Count > 0) { Debug.Log($"{exportPath} existed"); return(data); } data.sheets.Clear(); using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { IWorkbook book = null; if (Path.GetExtension(filePath) == ".xls") { book = new HSSFWorkbook(stream); } else { book = new XSSFWorkbook(stream); } for (int k = 0; k < book.NumberOfSheets; k++) { ISheet sheet = book.GetSheetAt(k); // } // foreach(string sheetName in sheetNames) { // ISheet sheet = book.GetSheet(sheetName); if (sheet == null) { // Debug.LogError("[QuestData] sheet not found:" + sheetName); continue; } Entity_sheet1.Sheet s = new Entity_sheet1.Sheet(); s.name = sheet.SheetName; for (int i = 1; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); ICell cell = null; Entity_sheet1.Param p = new Entity_sheet1.Param(); cell = row.GetCell(0); p.IP = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(1); p.LicenseHash = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(2); p.MachineId = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(3); p.Version = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(4); p.Location = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(5); p.Userid = (cell == null ? 0.0 : cell.NumericCellValue); cell = row.GetCell(6); p.count = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(7); p.SerialNumber = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(8); p.LicenseType = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(9); p.ShortVer = (cell == null ? "" : cell.StringCellValue); s.list.Add(p); } data.sheets.Add(s); } } ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject; EditorUtility.SetDirty(obj); Debug.Log($"Generate intermediate data: {exportPath}"); return(data); }
public void ExpandGroupBySearchingBasedOnUserID(Entity_sheet1 input, Entity_sheet1_Extra output) { List <Entity_sheet1.Param> list = input.sheets[0].list.Clone().ToList(); List <Param_Extra> retVal = new List <Param_Extra>(); List <Entity_sheet1.Param> currentGroup = new List <Entity_sheet1.Param>(); currentGroup.Add(list[0]); list.RemoveAt(0); int groupNumber = 1; int groupCount; int countUniqueMachineID; int countUniqueUserID; int countLicenseF; int countLicenseH; while (list.Count > 0) { // if still in the same group base on IP check if (currentGroup[currentGroup.Count - 1].IP.Equals(list[0].IP)) { currentGroup.Add(list[0]); list.RemoveAt(0); } else // this row belongs to another group based on IP check, so now let process the previous group { // base on UserID, find matched rows in the full list to amend to this group for (int i = 0; i < currentGroup.Count; i++) { Entity_sheet1.Param param = currentGroup[i]; List <Entity_sheet1.Param> temp = list.FindAll(x => x.Userid.Equals(param.Userid)).ToList(); for (int j = 0; j < temp.Count; j++) { list.Remove(temp[j]); } currentGroup.AddRange(temp); } // statistic about this group groupCount = currentGroup.Count; countUniqueMachineID = currentGroup.Select(x => x.MachineId).Distinct().Count(); countUniqueUserID = currentGroup.Select(x => x.Userid).Distinct().Count(); countLicenseF = currentGroup.GroupBy(x => x.MachineId) .Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList() .FindAll(x => x.licenseType.Equals("F")).Count; countLicenseH = currentGroup.GroupBy(x => x.MachineId) .Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList() .FindAll(x => x.licenseType.Equals("H")).Count; // let group by ip then sort by ip count within this group currentGroup = currentGroup.GroupBy(x => x.IP).OrderByDescending(g => g.Count()) .SelectMany(g => g).ToList(); // finish current group for (int i = 0; i < currentGroup.Count; i++) { Param_Extra pe = new Param_Extra(currentGroup[i]); pe.group = groupNumber; pe.rows = groupCount; pe.machines = countUniqueMachineID; pe.users = countUniqueUserID; pe.F = countLicenseF; pe.H = countLicenseH; retVal.Add(pe); } currentGroup.Clear(); // next group if (list.Count == 0) { break; } currentGroup.Add(list[0]); list.RemoveAt(0); ++groupNumber; } } if (currentGroup.Count > 0) { // statistic about this group groupCount = currentGroup.Count; countUniqueMachineID = currentGroup.Select(x => x.MachineId).Distinct().Count(); countUniqueUserID = currentGroup.Select(x => x.Userid).Distinct().Count(); countLicenseF = currentGroup.GroupBy(x => x.MachineId) .Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList() .FindAll(x => x.licenseType.Equals("F")).Count; countLicenseH = currentGroup.GroupBy(x => x.MachineId) .Select(g => new { machineId = g.Key, licenseType = g.FirstOrDefault().LicenseType }).ToList() .FindAll(x => x.licenseType.Equals("H")).Count; // finish current group for (int i = 0; i < currentGroup.Count; i++) { Param_Extra pe = new Param_Extra(currentGroup[i]); pe.group = groupNumber; pe.rows = groupCount; pe.machines = countUniqueMachineID; pe.users = countUniqueUserID; pe.F = countLicenseF; pe.H = countLicenseH; retVal.Add(pe); } currentGroup.Clear(); } output.list = retVal; }
public Param_Extra(Entity_sheet1.Param param) : base(param) { }