public override bool Load()
{
ghost targetData = target as ghost;
string path = targetData.SheetName;
if (!File.Exists(path))
{
return(false);
}
string sheet = targetData.WorksheetName;
ExcelQuery query = new ExcelQuery(path, sheet);
if (query != null && query.IsValid())
{
targetData.dataArray = query.Deserialize <ghostData>().ToArray();
EditorUtility.SetDirty(targetData);
AssetDatabase.SaveAssets();
return(true);
}
else
{
return(false);
}
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
Characters data = (Characters)AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(Characters));
if (data == null)
{
data = ScriptableObject.CreateInstance <Characters> ();
data.SheetName = filePath;
data.WorksheetName = sheetName;
AssetDatabase.CreateAsset((ScriptableObject)data, assetFilePath);
//data.hideFlags = HideFlags.NotEditable;
}
//data.dataArray = new ExcelQuery(filePath, sheetName).Deserialize<CharactersData>().ToArray();
//ScriptableObject obj = AssetDatabase.LoadAssetAtPath (assetFilePath, typeof(ScriptableObject)) as ScriptableObject;
//EditorUtility.SetDirty (obj);
ExcelQuery query = new ExcelQuery(filePath, sheetName);
if (query != null && query.IsValid())
{
data.dataArray = query.Deserialize <CharactersData>().ToArray();
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(assetFilePath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
}
public override bool Load()
{
string path = this.SheetName;
if (!File.Exists(path))
{
return(false);
}
ExcelQuery query = null;
query = new ExcelQuery(path, "UI");
if (query != null && query.IsValid())
{
ui = query.Deserialize <UI>();
}
else
{
return(false);
}
query = new ExcelQuery(path, "Item");
if (query != null && query.IsValid())
{
item = query.Deserialize <Item>();
}
else
{
return(false);
}
EditorUtility.SetDirty(this);
AssetDatabase.SaveAssets();
return(true);
}
public static List <CellType> bind(ExcelQuery ine)
{
return(ct);
// List<CellType> cellTypes= new List<CellType>();
// for( var i =0;i<machine.ColumnHeaderList.Count;i++)
// {
// cellTypes.Add(machine.ColumnHeaderList[i].type);
// }
// return cellTypes;
}
public void GetList(string[] vendors)
{
HashSet <string> UPCs = new HashSet <string>();
HashSet <string> ALUs = new HashSet <string>();
Dictionary <string, string> UOMs = new Dictionary <string, string>();
foreach (string vendor in vendors)
{
GetSheet(vendor).Subscribe(path =>
{
assetLoader.LoadAsset <ColumnMap>(vendor).Subscribe(map =>
{
ExcelQuery query = OpenSpreadSheet(path, vendor);
string err = string.Empty;
//Get 1st sheet
string[] sheets = query.GetSheetNames();
if (sheets == null || sheets.Length > 1)
{
Debug.LogError("Sheet missing or more than one sheet! Quitting");
return;
}
//use first sheet in our query, whatever it is named
query.GetNewSheet(sheets[0]);
int numOfRows = query.NumOfRows();
int len = query.GetLongestRowLength();
for (int row = 0; row < numOfRows; row++)
{
string[] cells = query.GetRow(row + 1, "(_._)", len);
//string upc = map.rules[upcColumn].rule.Process(cells);
//string alu = map.rules[aluColumn].rule.Process(cells);
//string uom = map.rules[uomColumn].rule.Process(cells);
string upc = map.rules[0].rule.Process(cells);
string alu = map.rules[0].rule.Process(cells);
string uom = map.rules[0].rule.Process(cells);
if (!UPCs.Contains(upc))
{
if (!ALUs.Contains(alu))
{
UPCs.Add(upc);
ALUs.Add(alu);
UOMs.Add(upc, uom);
}
}
}
});
});
}
}
/// <summary>
/// Import the specified excel file and prepare to set type of each cell.
/// </summary>
protected override void Import()
{
base.Import();
ExcelMachine machine = target as ExcelMachine;
string path = machine.excelFilePath;
string sheet = machine.WorkSheetName;
if (string.IsNullOrEmpty(path))
{
EditorUtility.DisplayDialog(
"Error",
"You should specify spreadsheet file first!",
"OK"
);
return;
}
if (!File.Exists(path))
{
EditorUtility.DisplayDialog(
"Error",
"File at " + path + " does not exist.",
"OK"
);
return;
}
if (machine.HasHeadColumn())
{
machine.HeaderColumnList.Clear();
}
var titles = new ExcelQuery(path, sheet).GetTitle();
if (titles != null)
{
foreach (string s in titles)
{
HeaderColumn header = new HeaderColumn();
header.name = s;
machine.HeaderColumnList.Add(header);
}
}
else
{
Debug.LogWarning("The WorkSheet [" + sheet + "] may be empty.");
}
EditorUtility.SetDirty(machine);
AssetDatabase.SaveAssets();
}
private static void CreateColumnMap(string sheetPath)
{
string assetPath = "Assets/Scripts/DataObjects";
if (!string.IsNullOrEmpty(sheetPath))
{
if (!File.Exists(sheetPath))
{
Debug.LogWarning(string.Format("Sheet Path {0} does not exist.", sheetPath));
return;
}
ExcelQuery q = new ExcelQuery(sheetPath);
q.GetNewSheet(q.GetSheetNames()[0]);
string err = string.Empty;
string[] titles = q.GetTitle(0, ref err);
if (err != string.Empty)
{
Debug.LogError(string.Format("Could not read sheet titles from first sheet {0} of {1}", q.GetSheetNames()[0], sheetPath));
return;
}
else
{
var bar = ScriptableObjectUtils.ObservableCreateAsset <ColumnMap>(Path.GetFileNameWithoutExtension(sheetPath) + "_ColumnMap", assetPath)
.Do(map =>
{
ScriptableObjectUtils.ObservableCreateAsset <RuleConstant>("WriteNothing", assetPath)
.Subscribe(defaultRule =>
{
int len = titles.Length;
for (int i = 0; i < len; i++)
{
map.rules.Add(new RuleInfo()
{
description = string.Format("{0} - {1}", SheetUtils.GetColumnLettersFromIndex(i + 1), titles[i]),
rule = defaultRule
});
}
});
map.header.AddRange(titles);
});
bar.Subscribe();
}
}
}
ExcelQuery OpenSpreadSheet(string filePath, string pref)
{
if (string.IsNullOrEmpty(filePath))
{
return(null);
}
ExcelQuery query = new ExcelQuery(filePath);
if (query != null)
{
PlayerPrefs.SetString(pref, filePath);
Debug.Log("opened sheet: " + filePath);
return(query);
}
return(null);
}
SheetType LoadMachineExcelAsset <SheetType, DataType>(string excelName, string sheetName)
where SheetType : new()
{
string totalDir = Path.Combine(Application.dataPath, "Excels/Machine/");
string path = totalDir + excelName + ".xls";
Debug.Assert(File.Exists(path));
ExcelQuery query = new ExcelQuery(path, sheetName);
SheetType result = new SheetType();
Type type = result.GetType();
System.Reflection.PropertyInfo info = type.GetProperty("DataArray");
DataType[] array = query.Deserialize <DataType>().ToArray();
info.SetValue(result, array, null);
return(result);
}
private static void ImportButton(SerializedProperty list, int index)
{
if (!(list.serializedObject.targetObject is ItemList))
{
Debug.LogError("Can't import, this is not an itemlist!");
return;
}
if (GUILayout.Button(importButtonContent, EditorStyles.miniButtonRight, miniButtonWidth))
{
//if this is a map...
if (list.GetArrayElementAtIndex(index).objectReferenceValue is ColumnMap)
{
//get sheet
ExcelQuery sheet = GetSheet();
if (sheet == null)
{
return;
}
ItemList itemList = (ItemList)list.serializedObject.targetObject;
itemList.OnEnable();
//import sheet
string[][] import = ImportSheet(sheet, (ColumnMap)list.GetArrayElementAtIndex(index).objectReferenceValue, itemList.ALU_Column.Index.value, itemList.UPC_Column.Index.value, itemList);
ProcessImport(import, itemList);
itemList.OnEnable();
//CleanItems(list);
}
else
{
Debug.LogError("Element is not a ColumnMap!");
}
}
}
int Compare_Rows_Skip_Mismatched_UPC_If_ItemIDs_Equal(ExcelQuery query, int row1, int row2, int rowlen, int uid_index, HashSet <int> skips)
{
string[] vals1 = query.GetRow(row1, "(_._)", rowlen);
string[] vals2 = query.GetRow(row2, "(_._)", rowlen);
bool item_equal = false;
if (vals1[uid_index] != vals2[uid_index])
{
item_equal = true;
}
if (item_equal) //if the item ids are equal, we ignore any mismatches in the UPC column because we know QB will only see one of these rows
{
return(CompareRows(vals1, vals2, skips));
}
else
{
return(CompareRows(vals1, vals2));
}
}
/// <summary>
/// Import the specified excel file and prepare to set type of each cell.
/// </summary>
protected override void Import(bool reimport = false)
{
base.Import(reimport);
ExcelMachine machine = target as ExcelMachine;
string path = machine.excelFilePath;
string sheet = machine.WorkSheetName;
if (string.IsNullOrEmpty(path))
{
EditorUtility.DisplayDialog(
"Error",
"You should specify spreadsheet file first!",
"OK"
);
return;
}
if (!File.Exists(path))
{
EditorUtility.DisplayDialog(
"Error",
"File at " + path + " does not exist.",
"OK"
);
return;
}
var titles = new ExcelQuery(path, sheet).GetTitle();
List <string> titleList = titles.ToList();
if (machine.HasHeadColumn() && reimport == false)
{
var headerDic = machine.HeaderColumnList.ToDictionary(header => header.name);
// collect non changed header columns
var exist = from t in titleList
where headerDic.ContainsKey(t) == true
select new HeaderColumn {
name = t, type = headerDic[t].type, OrderNO = headerDic[t].OrderNO
};
// collect newly added or changed header columns
var changed = from t in titleList
where headerDic.ContainsKey(t) == false
select new HeaderColumn {
name = t, type = CellType.Undefined, OrderNO = titleList.IndexOf(t)
};
// merge two
var merged = exist.Union(changed).OrderBy(x => x.OrderNO);
machine.HeaderColumnList.Clear();
machine.HeaderColumnList = merged.ToList();
}
else
{
machine.HeaderColumnList.Clear();
if (titles != null)
{
int i = 0;
foreach (string s in titles)
{
machine.HeaderColumnList.Add(new HeaderColumn {
name = s, type = CellType.Undefined, OrderNO = i
});
i++;
}
}
else
{
Debug.LogWarning("The WorkSheet [" + sheet + "] may be empty.");
}
}
EditorUtility.SetDirty(machine);
AssetDatabase.SaveAssets();
}
private static string[][] ImportSheet(ExcelQuery q, ColumnMap map, int aluIndex, int upcIndex, ItemList itemList)
{
HashSet <string> seenALUs = new HashSet <string>();
HashSet <string> seenUPCs = new HashSet <string>();
InventoryItem item;
Debug.Log("Import sheet");
q.GetNewSheet(q.GetSheetNames()[0]);
string err = string.Empty;
string[] titles = q.GetTitle(0, ref err);
if (titles == null && err != string.Empty)
{
Debug.LogError(string.Format("Could not read sheet titles from first sheet {0} - err msg: {1}", q.GetSheetNames()[0], err));
return(null);
}
//NO--This is all wrong. Should be checking that the map matches the itemlist when it is imported
//if (titles.Length != map.header.Count)
//{
// Debug.LogError(string.Format("Map heading count does not match imported sheet"));
// return null;
//}
//int index = 0;
//foreach (string heading in titles)
//{
// if (!heading.Equals(map.header[index], StringComparison.OrdinalIgnoreCase))
// {
// Debug.LogError(string.Format("Map heading {0} does not match heading ({1})in imported sheet at index {2}", map.header[index], heading, index));
// return null;
// }
// index++;
//}
int rows = q.NumOfRows();
int cols = map.header.Count;
int titleRow = q.TitleRow;
int len = q.GetLongestRowLength();
List <string[]> temp2 = new List <string[]>();
string[] inputVals;
List <string> row = new List <string>();
for (int x = titleRow + 1; x < rows - titleRow; x++)
{
inputVals = q.GetRow <string>(x, string.Empty, len);
if (inputVals == null)
{
continue;
}
string alu = map.rules[aluIndex].rule.Process(inputVals);
string upc = map.rules[upcIndex].rule.Process(inputVals);
item = itemList.GetALU(alu);
if (item != null && item.DefaultUnitIsCase)
{
inputVals[map.UOM.Index.GetVal()] = map.CaseVal.GetVal();
}
//if importOnce is set, skip any we've already seens
if (!map.importOnce || (!seenALUs.Contains(alu)) && (!seenUPCs.Contains(upc)))
{
row.Clear();
for (int y = 0; y < cols; y++)
{
row.Add(map.rules[y].rule.Process(inputVals));
}
if (!string.IsNullOrEmpty(alu))
{
seenALUs.Add(row[aluIndex]);
}
else
{
Debug.LogError(string.Format("No ALU found for row {0}", x));
continue;
}
if (!string.IsNullOrEmpty(upc))
{
seenUPCs.Add(row[upcIndex]);
}
temp2.Add(row.ToArray());
}
}
return(temp2.ToArray());
}
List <Dictionary <string, int> > GetALUSets(ExcelQuery query, bool compareRows, List <string> searchTerms, List <string> excludes)
{
List <Dictionary <string, int> > set = new List <Dictionary <string, int> >();
List <List <string> > list = new List <List <string> >();
string err = string.Empty;
//Get 1st sheet
string[] sheets = query.GetSheetNames();
if (sheets == null || sheets.Length > 1)
{
Debug.LogError("Sheet missing or more than one sheet! Quitting");
return(null);
}
//use first sheet in our query, whatever it is named
query.GetNewSheet(sheets[0]);
//Get titles
string[] titles = query.GetTitle(0, ref err);
if (titles == null)
{
Debug.Log("No titles found! Quitting");
return(null);
}
int len = titles.Length;
int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0];
if (uid_col < 0)
{
Debug.LogError(string.Format("couldn't find UID column"));
return(null);
}
else
{
Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col)));
}
HashSet <int> skips = new HashSet <int>(SheetUtils.FindMatchIndexesInStringArr(titles, UPC_ColumnNames.ToArray()));
//search titles for "UPC" or "Barcode"
for (int col = 0; col < len; col++)
{
foreach (string heading in searchTerms)
{
if (titles[col].Contains(heading, StringComparison.OrdinalIgnoreCase))
{
bool exclude = false;
foreach (string exclusion in excludes)
{
if (titles[col].Contains(exclusion, StringComparison.OrdinalIgnoreCase))
{
exclude = true;
}
}
if (exclude)
{
continue;
}
Dictionary <string, int> column = new Dictionary <string, int>();
set.Add(column);
List <string> sublist = new List <string>();
list.Add(sublist);
//add all values for that column to dictionary with row as value
string[] vals = query.GetColumn <string>(col, "(_._)");
for (int row = 0; row < vals.Length; row++)
{
string val = vals[row];
if (val == "(_._)")
{
continue; //skip our null sentinel
}
sublist.Add(val);
if (column.ContainsKey(val))
{
if (compareRows)
{
LogResult(string.Format("found {0} misses between row {1} and row {2}", Compare_Rows_Skip_Mismatched_UPC_If_ItemIDs_Equal(query, row + 1, column[val] + 1, titles.Length, uid_col, skips),
column[val] + 1, row + 1));
}
else
{
LogResult(string.Format("Current row:{0} Subset for column:{1} already contains val:{2} at row:{3}", row + 2, query.GetHeaderColumnName(col), val, column[val] + 2));
}
}
else
{
column.Add(val, row);
}
}
}
}
}
aluColumns.Add(list);
return(set);
}
void ColumnCheck(ExcelQuery query, string heading)
{
string err = string.Empty;
//use first sheet in our query, whatever it is named
query.GetNewSheet(query.GetSheetNames()[0]);
//Get titles
string[] titles = query.GetTitle(0, ref err);
if (titles == null)
{
Debug.Log("No titles found! Quitting");
return;
}
int len = titles.Length;
int uid_col = SheetUtils.FindMatchIndexesInStringArr(titles, UID_ColumnNames.ToArray())[0];
if (uid_col < 0)
{
Debug.LogError(string.Format("couldn't find UID column"));
return;
}
else
{
Debug.Log(string.Format("Found uid_column at {0} which is header {1}", uid_col, query.GetHeaderColumnName(uid_col)));
}
for (int col = 0; col < len; col++)
{
if (titles[col].Equals(heading, StringComparison.OrdinalIgnoreCase))
{
Dictionary <long, int> column = new Dictionary <long, int>();
//add all values for that column to dictionary with row as value
long[] vals = query.GetColumn <long>(col, -1);
for (int row = 0; row < vals.Length; row++)
{
long val = vals[row];
if (val == -1)
{
continue; //skip our null sentinel
}
if (column.ContainsKey(val))
{
string[] duplicate = query.GetRow(row + 1, "(_._)", len);
string[] original = query.GetRow(column[val] + 1, "(_._)", len);
int result = CompareRows(duplicate, original);
if (result < 3 && original[uid_col] != duplicate[uid_col])
{
LogResult(string.Format("Problem! found almost duplicate upc and row (misses:{0}) with non-identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col]));
continue;
}
if (result > 2 && original[uid_col] == duplicate[uid_col])
{
LogResult(string.Format("Problem! found duplicate upc with non-duplicate rows (misses:{0}) but with identical itemids: row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col]));
continue;
}
if (result < 3 && result > 0)
{
LogResult(string.Format("found almost duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1));
continue;
}
if (result < 1)
{
LogResult(string.Format("found duplicate upc/itemid (misses:{0}) we expect item:{1} at row:{2} will not be added because item:{3} exists at row:{4}", result, duplicate[uid_col], row + 1, original[uid_col], column[val] + 1));
continue;
}
if (result > 2)
{
LogResult(string.Format("found non-duplicate items with the same UPC: {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col]));
continue;
}
LogResult(string.Format("somehow managed to miss a case: found {0} misses between row {1} (item:{3}) and row {2} (item:{4})", result, column[val] + 1, row + 1, original[uid_col], duplicate[uid_col]));
}
else
{
column.Add(val, row);
}
}
}
}
return;
}
public static List <bool> bind2(ExcelQuery ine)
{
return(iarr);
}
/// <summary>
/// Import the specified excel file and prepare to set type of each cell.
/// </summary>
protected override void Import(bool reimport = false)
{
base.Import(reimport);
ExcelMachine machine = target as ExcelMachine;
string path = machine.excelFilePath;
string sheet = machine.WorkSheetName;
if (string.IsNullOrEmpty(path))
{
EditorUtility.DisplayDialog(
"Error",
"You should specify spreadsheet file first!",
"OK"
);
return;
}
if (!File.Exists(path))
{
EditorUtility.DisplayDialog(
"Error",
"File at " + path + " does not exist.",
"OK"
);
return;
}
var titles = new ExcelQuery(path, sheet).GetTitle();
List<string> titleList = titles.ToList();
if (machine.HasHeadColumn() && reimport == false)
{
var headerDic = machine.HeaderColumnList.ToDictionary(header => header.name);
int i = 0;
// collect non changed header columns
var exist = from t in titleList
where headerDic.ContainsKey(t) == true
select new HeaderColumn { name = t, type = headerDic[t].type, OrderNO = headerDic[t].OrderNO };
// collect newly added or changed header columns
var changed = from t in titleList
where headerDic.ContainsKey(t) == false
select new HeaderColumn { name = t, type = CellType.Undefined, OrderNO = titleList.IndexOf(t) };
// merge two
var merged = exist.Union(changed).OrderBy(x => x.OrderNO);
machine.HeaderColumnList.Clear();
machine.HeaderColumnList = merged.ToList();
}
else
{
machine.HeaderColumnList.Clear();
if (titles != null)
{
int i = 0;
foreach (string s in titles)
{
machine.HeaderColumnList.Add(new HeaderColumn { name = s, type = CellType.Undefined, OrderNO = i});
i++;
}
}
else
{
Debug.LogWarning("The WorkSheet [" + sheet + "] may be empty.");
}
}
EditorUtility.SetDirty(machine);
AssetDatabase.SaveAssets();
}
private void GetColumnHeadings(ItemList list)
{
if (list.Headings.Count == 0 && list.listName != string.Empty)
{
if (GUILayout.Button("Get Column Headings"))
{
string sheetPath = UtilScript.GetPath("InventoryItem");
if (!string.IsNullOrEmpty(sheetPath))
{
if (!File.Exists(sheetPath))
{
Debug.LogWarning(string.Format("Sheet Path {0} does not exist.", sheetPath));
return;
}
string guid = AssetDatabase.CreateFolder("Assets", list.listName);
string path = AssetDatabase.GUIDToAssetPath(guid);
guid = AssetDatabase.CreateFolder(path, "values");
string subPath = AssetDatabase.GUIDToAssetPath(guid);
ExcelQuery q = new ExcelQuery(sheetPath);
q.GetNewSheet(q.GetSheetNames()[0]);
string err = string.Empty;
string[] titles = q.GetTitle(0, ref err);
if (err != string.Empty)
{
Debug.LogError(string.Format("Could not read sheet titles from first sheet {0} of {1}", q.GetSheetNames()[0], sheetPath));
return;
}
else
{
int len = titles.Length;
for (int i = 0; i < len; i++)
{
StringDataValue name = Instantiate(list.NameValuePrefab);
name.name = "HeadingName_" + titles[i];
AssetDatabase.CreateAsset(name, subPath + "/" + name.name + ".asset");
IntDataValue index = Instantiate(list.IndexValuePrefab);
index.name = "HeadingIndex_" + titles[i];
AssetDatabase.CreateAsset(index, subPath + "/" + index.name + ".asset");
ColumnHeading newHeading = Instantiate(list.ColumnHeadingPrefab);
newHeading.name = "ColumnHeading_" + titles[i];
AssetDatabase.CreateAsset(newHeading, path + "/" + newHeading.name + ".asset");
newHeading.Name = name;
newHeading.Index = index;
newHeading.Name.value = titles[i];
newHeading.Index.value = i;
EditorUtility.SetDirty(newHeading);
EditorUtility.SetDirty(name);
EditorUtility.SetDirty(index);
list.Headings.Add(newHeading);
}
}
}
AssetDatabase.SaveAssets();
AssetDatabase.Refresh();
}
}
}
