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)
{
}
