public static void CreateScriptableObjectFromExcelFile <T>(string excelPath, string assetPath) where T : ScriptableObject
{
// Dummy scriptable object
T obj = ScriptableObject.CreateInstance <T>();
obj.name = typeof(T).ToString();
AssetDatabase.CreateAsset(obj, assetPath);
Debug.Log("Parsing " + excelPath + " into " + assetPath);
// Using reflection find all the fields in our main container class
// These will be our sheets
FieldInfo[] sheetFields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);
if (sheetFields.Length == 0)
{
Debug.LogError("Could not find any fields in class " + typeof(T).ToString());
}
foreach (var sheetFieldInfo in sheetFields)
{
Debug.Log("Sheet name: " + sheetFieldInfo.ToString());
object[] attrs = sheetFieldInfo.GetCustomAttributes(true);
int found = 0;
if (attrs.Length == 0)
{
Debug.LogError("Could not find any attributes in class " + typeof(T).ToString());
}
foreach (var att in attrs)
{
// Depending on the attributes, we might have to use reflection to work out the column names
// We use reflection to work out the required datatype
ExcelSheetAttribute e = att as ExcelSheetAttribute;
if (e != null)
{
found += 1;
Debug.Log(sheetFieldInfo.ToString());
Debug.Log(sheetFieldInfo.GetType().ToString());
// Get the type of the list
Debug.Log(sheetFieldInfo.FieldType.GetGenericArguments().Length);
Type listType = sheetFieldInfo.FieldType.GetGenericArguments()[0];
Debug.Log(listType.ToString());
// Use reflection to call the correct generic method
MethodInfo method = typeof(ExcelParser).GetMethod("CreateListFromSheet");
MethodInfo generic = method.MakeGenericMethod(listType);
sheetFieldInfo.SetValue(obj, generic.Invoke(null, new object[] { obj, excelPath, e.sheetName }));
}
}
if (found == 0)
{
Debug.LogError("Could not find any ExcelSheet attributes in class " + typeof(T).ToString());
}
}
AssetDatabase.SaveAssets();
}
private static void ImplementExcelSheetOptions(Worksheet sheet, ExcelSheetAttribute sheetAttribute, int currentRow, int currentColumn)
{
//freeze all the header cells if requested.
if (sheetAttribute.FreezeHeaders)
{
sheet.FreezePanes(currentRow + 1, 0, 1, 0);
}
if (sheetAttribute.AutoFilter)
{
sheet.AutoFilter.SetRange(currentRow, sheetAttribute.StartColumn, currentColumn - 1);
}
}
public ExcelSheetInfoCollection GetExcelSheetInfo(Type itemType, object data)
{
if (itemType.Name.StartsWith("Dictionary"))
{
return(null);
}
var sheets = FormatterUtils.GetMemberNames(itemType);
var properties = GetSerialisablePropertyInfo(itemType);
var sheetCollection = new ExcelSheetInfoCollection();
foreach (var sheet in sheets)
{
var prop = properties.FirstOrDefault(p => p.Name == sheet);
ExcelSheetAttribute attr = FormatterUtils.GetAttribute <ExcelSheetAttribute>(prop);
if (prop == null || attr == null)
{
continue;
}
var sheetInfo = new ExcelSheetInfo()
{
SheetType = prop.PropertyType,
SheetName = sheet,
ExcelSheetAttribute = attr,
PropertyName = prop.Name,
SheetObject = FormatterUtils.GetFieldOrPropertyValue(data, prop.Name)
};
if (prop.PropertyType.Name.StartsWith("List") && sheetInfo.SheetObject != null)
{
sheetInfo.SheetType = FormatterUtils.GetEnumerableItemType(sheetInfo.SheetObject.GetType());
}
sheetCollection.Add(sheetInfo);
}
return(sheetCollection);
}
private static void PopulateAttributeBased(Worksheet sheet, IEnumerable items, Type type, ExcelSheetAttribute sheetAttribute)
{
var columnList = type.GetProperties()
.Where(x => x.GetCustomAttribute <ExcelColumnAttribute>() != null)
.Select(x => new PropertyAttributeItem(x, x.GetCustomAttribute <ExcelColumnAttribute>()))
.OrderBy(x => x.Attribute.Order)
.ThenBy(x => x.Property.Name)
.ToList();
var currentRow = sheetAttribute.StartRow;
var currentColumn = sheetAttribute.StartColumn;
if (sheetAttribute.Headers)
{
foreach (var column in columnList)
{
var cell = sheet.Cells[currentRow, currentColumn++];
cell.PutValue(column.Attribute.HeaderName ?? column.Property.Name);
SetHeaderStyle(cell);
}
ImplementExcelSheetOptions(sheet, sheetAttribute, currentRow, currentColumn);
currentRow++;
currentColumn = sheetAttribute.StartColumn;
}
foreach (var dataRow in items)
{
for (int i = 0; i < columnList.Count; i++)
{
var column = columnList[i];
var value = column.Property.GetValue(dataRow);
var cell = sheet.Cells[currentRow, currentColumn++];
if (column.Attribute.Formula)
{
if (column.Property.PropertyType != typeof(string))
{
throw new ExcelColumnFormulaMismatchException(type, column.Property.Name);
}
cell.Formula = (string)value;
continue;
}
cell.PutValue(value);
//apply custom formatting.
if (!String.IsNullOrEmpty(column.Attribute.Format))
{
var style = cell.GetStyle();
style.Custom = column.Attribute.Format;
cell.SetStyle(style);
}
}
currentRow++;
currentColumn = sheetAttribute.StartColumn;
}
//check if there are any auto-fit columns. Mostly to keep the currentColumn reset code template.
if (columnList.Any(x => x.Attribute.AutoFit))
{
foreach (var column in columnList)
{
if (column.Attribute.AutoFit)
{
sheet.AutoFitColumn(currentColumn);
}
currentColumn++;
}
currentColumn = sheetAttribute.StartColumn;
}
}
private static void PopulateSimple(Worksheet sheet, IEnumerable items, Type type, ExcelSheetAttribute sheetAttribute)
{
var propertyList = type.GetProperties().OrderBy(x => x.Name).ToList();
if (propertyList.Any(x => x.GetCustomAttribute <ExcelColumnAttribute>() != null))
{
throw new ApplicationException("ExcelColumnAttribute should not be used when IncludeAllProperties of ExcelSheetAttribute is true.");
}
var currentRow = sheetAttribute.StartRow;
var currentColumn = sheetAttribute.StartColumn;
if (sheetAttribute.Headers)
{
foreach (var property in propertyList)
{
var cell = sheet.Cells[currentRow, currentColumn++];
cell.PutValue(property.Name);
SetHeaderStyle(cell);
}
ImplementExcelSheetOptions(sheet, sheetAttribute, currentRow, currentColumn);
currentRow++;
currentColumn = sheetAttribute.StartColumn;
}
foreach (var dataRow in items)
{
for (int i = 0; i < propertyList.Count; i++)
{
var value = propertyList[i].GetValue(dataRow);
var cell = sheet.Cells[currentRow, currentColumn++];
cell.PutValue(value);
}
currentRow++;
currentColumn = sheetAttribute.StartColumn;
}
for (int i = 0; i < propertyList.Count; i++)
{
sheet.AutoFitColumn(currentColumn++);
}
currentColumn = sheetAttribute.StartColumn;
}
