public void CorrectGroupNameAndDescriptionBetweenSheets(Entity_sheet_Modified other, float matchingThreshold = 0.3f, bool applyCorrection = false) { StringBuilder log = new StringBuilder(); List <Group> namedGroups1 = groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList(); List <Group> namedGroups2 = other.groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList(); for (int i = 0; i < namedGroups1.Count; i++) { for (int j = 0; j < namedGroups2.Count; j++) { Group g1 = namedGroups1[i]; Group g2 = namedGroups2[j]; if (g1.Compare(g2, name, other.name, log, matchingThreshold) > matchingThreshold) { if (applyCorrection) { Debug.Log(log); Group groupToChoose = g1.description.Length > g2.description.Length ? g1 : g2; g1.name = groupToChoose.name; g2.name = groupToChoose.name; g1.description = groupToChoose.description; g2.description = groupToChoose.description; } else { Debug.Log(log); } } } } }
public void ExportSheetModified(Entity_sheet_Modified input, [FolderPath(AbsolutePath = true)] string path) { StringBuilder sb = new StringBuilder(); sb.AppendLine("Description,Combined Group,Group,Data Rows Count,Count Unique UserID,Count Unique MachineID,Count License F," + "Count License H,IP,LicenseHash,MachineId,Version,Location,Userid,count,SerialNumber,LicenseType," + "ShortVer"); List <ParamModified> list = input.sheets[0].list; foreach (ParamModified param in list) { sb.AppendLine(param.ToString()); } string finalPath = path.EndsWith($"{Path.DirectorySeparatorChar}") ? path : $"{path}{Path.DirectorySeparatorChar}"; string finalFileName = $"{finalPath}{input.name}.csv"; using (StreamWriter file = new StreamWriter(finalFileName)) { file.WriteLine(sb.ToString()); // "sb" is the StringBuilder } Debug.Log($"Final output: {finalFileName}"); }
public Entity_sheet_Modified CreateSheetModifed(Entity_sheet1_Extra extra, [FolderPath] string input) { string exportPath = input.Replace(Path.GetFileName(input), $"{extra.name}.asset"); Entity_sheet_Modified data = (Entity_sheet_Modified)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_sheet_Modified)); if (data == null) { data = ScriptableObject.CreateInstance <Entity_sheet_Modified>(); AssetDatabase.CreateAsset((ScriptableObject)data, exportPath); // data.hideFlags = HideFlags.NotEditable; data.name = Path.GetFileNameWithoutExtension(exportPath); } if (data.sheets.Count > 0 && data.sheets[0].list.Count > 0) { Debug.Log($"{exportPath} will be overriden"); } data.sheets.Clear(); Entity_sheet_Modified.SheetModified sheet = new Entity_sheet_Modified.SheetModified(); sheet.name = extra.name; data.sheets.Add(sheet); for (int i = 0; i < extra.list.Count; i++) { ParamModified param = new ParamModified(extra.list[i]); data.sheets[0].list.Add(param); } ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject; EditorUtility.SetDirty(obj); return(data); }
public void AutomateAllStepsAbove([FilePath] string filePath, CountryAndReference filterCountry, [FolderPath(AbsolutePath = true)] string outputFolder) { DateTime now = DateTime.Now; Entity_sheet1 data = GenerateData(filePath); Entity_sheet1 dataFiltered = ScriptableObject.CreateInstance <Entity_sheet1> (); dataFiltered.sheets = new List <Entity_sheet1.Sheet>(); dataFiltered.sheets.Add(new Entity_sheet1.Sheet()); dataFiltered.name = data.name; FilterLocation(filterCountry.country, data, dataFiltered); SortByIP(dataFiltered, false); GroupByIPThenSortByGroupCount(dataFiltered, false); Entity_sheet1_Extra dataExtra = ScriptableObject.CreateInstance <Entity_sheet1_Extra> (); dataExtra.name = $"{filterCountry.country}_{dataFiltered.name}"; ExpandGroupBySearchingBasedOnUserID(dataFiltered, dataExtra); SortTheGroupsByMachineCount(dataExtra); Entity_sheet_Modified dataModified = CreateSheetModifed(dataExtra, filePath); dataModified.output_GroupToCSV = dataModified.output_GroupStatisticToCSV = outputFolder; // dataModified.referenceData = filterCountry.referenceData; dataModified.AutomateAllStep(); if (filterCountry.matchWeeks != null) { if (!filterCountry.matchWeeks.weeks.Contains(dataModified)) { filterCountry.matchWeeks.weeks.Add(dataModified); } filterCountry.matchWeeks.referenceData = filterCountry.referenceData; filterCountry.matchWeeks.CompareAndMatchWeeks(); filterCountry.matchWeeks.ExportData(); } else { dataModified.ExportGroupToCSV(); dataModified.ExportGroupStatisticToCSV(); } // ExportSheetExtra(dataExtra, outputFolder); // ExportSheetModified(dataModified, outputFolder); TimeSpan ts = DateTime.Now.Subtract(now); Debug.Log($"Took: {ts.ToString()}"); }
public int MatchingNamedGroupsBetweenSheets(/*Entity_sheet_Modified other,*/ float matchingThreshold = 0.2f, bool applyLabeling = false) { Entity_sheet_Modified other = referenceData; if (other == this) { return(0); } List <Group> namedGroups1 = groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList(); List <Group> namedGroups2 = other.groups.Where(x => !string.IsNullOrEmpty(x.name) || !string.IsNullOrEmpty(x.description)).ToList(); CrossCheck(namedGroups1, this.name, other.groups, other.name, matchingThreshold, applyLabeling); CrossCheck(namedGroups2, other.name, groups, this.name, matchingThreshold, applyLabeling); MergeDuplicatedGroupsByName(); return(other.MergeDuplicatedGroupsByName()); }
public Entity_sheet_Modified GenerateDataThatHasDescription([FilePath] string filePath) { string exportPath = filePath.Replace(Path.GetExtension(filePath), ".asset"); Entity_sheet_Modified data = (Entity_sheet_Modified)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_sheet_Modified)); if (data == null) { data = ScriptableObject.CreateInstance <Entity_sheet_Modified>(); 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} will be overriden"); } 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 j = 0; j < book.NumberOfSheets; j++) { ISheet sheet = book.GetSheetAt(j); if (sheet == null) { Debug.LogError("[QuestData] sheet not found:"); continue; } Entity_sheet_Modified.SheetModified s = new Entity_sheet_Modified.SheetModified(); s.name = sheet.SheetName; for (int i = 1; i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); ICell cell = null; ParamModified p = new ParamModified(); int k = 0; cell = row.GetCell(k); p.Description = (cell == null ? "" : cell.StringCellValue.Trim()); cell = row.GetCell(++k); p.combinedGroup = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.group = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.rows = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.users = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.machines = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.F = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.H = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.IP = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.LicenseHash = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.MachineId = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.Version = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.Location = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.Userid = (cell == null ? 0.0 : cell.NumericCellValue); cell = row.GetCell(++k); p.count = (int)(cell == null ? 0 : cell.NumericCellValue); cell = row.GetCell(++k); p.SerialNumber = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); p.LicenseType = (cell == null ? "" : cell.StringCellValue); cell = row.GetCell(++k); 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); }