private void CheckPropertiesConfig(PropertyInfo[] properties, Type sheetType)
{
foreach (ICell headerCell in sheet.GetRow(0).Cells)
{
string columnName = headerCell.StringCellValue;
if (!columnName.StartsWith(ignoreColumnHeaderPrefix))
{
bool isFind = false;
foreach (PropertyInfo p in properties)
{
if (p.Name == columnName)
{
isFind = true;
break;
}
}
if (!isFind)
{
string err = string.Format("Excel config error: column [{0}] in [{1}] is wrong",
columnName, sheetType.ToString());
ExcelQueryUtility.Assert(false, err);
}
}
}
}
/// <summary>
/// Retrieves all sheet names.
/// </summary>
public string[] GetSheetNames()
{
List <string> sheetList = new List <string>();
if (this.workbook != null)
{
int numSheets = this.workbook.NumberOfSheets;
for (int i = 0; i < numSheets; i++)
{
sheetList.Add(this.workbook.GetSheetName(i));
}
}
else
{
ExcelQueryUtility.LogError("Workbook is null. Did you forget to import excel file first?");
}
return((sheetList.Count > 0) ? sheetList.ToArray() : null);
}
private bool CheckCellValueLegal(PropertyInfo propertyInfo, object cellValue)
{
bool result = true;
string name = propertyInfo.Name;
//Note: we don't allow a row with id = 0
if (name.ToLower() == "id")
{
int intValue = 0;
int.TryParse(cellValue.ToString(), out intValue);
if (intValue == 0)
{
string s = string.Format("Excel config error: in [{0}] sheet, there is a cell with {1} = 0", sheet.SheetName, name);
ExcelQueryUtility.LogError(s);
result = false;
}
}
return(result);
}
/// <summary>
/// Constructor.
/// </summary>
public ExcelQuery(string path, string sheetName = "")
{
try
{
this.filepath = path;
using (FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
string extension = GetSuffix(path);
if (extension == "xls")
{
workbook = new HSSFWorkbook(fileStream);
}
else if (extension == "xlsx")
{
#if UNITY_EDITOR_OSX
throw new Exception("xlsx is not supported on OSX.");
#else
workbook = new XSSFWorkbook(fileStream);
#endif
}
else
{
throw new Exception("Wrong file.");
}
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
}
}
}
catch (Exception e)
{
ExcelQueryUtility.LogError(e.Message);
}
}
public IList Deserialize(Type type, int start = 1)
{
PropertyInfo[] properties = type.GetProperties();
//check if column name config is correct
CheckPropertiesConfig(properties, type);
Type listType = typeof(List <>).MakeGenericType(new Type[] { type });
IList result = (IList)Activator.CreateInstance(listType);
int current = 0;
IRow row0 = sheet.GetRow(0);
foreach (IRow row in sheet)
{
if (current < start)
{
current++; // skip header column.
continue;
}
var item = Activator.CreateInstance(type);
bool isCellValueLegal = true;
for (var i = 0; i < row0.Cells.Count; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
PropertyInfo property = GetPropertyInfo(properties, i);
if (property != null && property.CanWrite)
{
try
{
var value = ConvertFrom(cell, property.PropertyType);
if (property.PropertyType.IsArray)
{
//NOTE: enum array type is not supported. (Does it really needed?)
const char DELIMETER = ',';
string str = value as string;
// remove whitespace between each of element
str = new string(str.ToCharArray()
.Where(ch => !Char.IsWhiteSpace(ch))
.ToArray());
// remove ',', if it is found at the end.
char[] charToTrim = { ',', ' ' };
str = str.TrimEnd(charToTrim);
// split by ','
object[] temp = str.Split(DELIMETER);
Array array = (Array)Activator.CreateInstance(property.PropertyType, temp.Length);
for (int j = 0; j < array.Length; j++)
{
object o = Convert.ChangeType(temp[j], property.PropertyType.GetElementType());
array.SetValue(o, j);
}
property.SetValue(item, array, null);
}
else
{
if (i == 0 && property.PropertyType.Equals(typeof(string)))
{
string v = value as string;
if (string.IsNullOrEmpty(v))
{
isCellValueLegal = false;
}
}
property.SetValue(item, value, null);
}
if (isCellValueLegal)
{
isCellValueLegal = CheckCellValueLegal(property, value);
}
if (!isCellValueLegal)
{
break;
}
}
catch (Exception e)
{
string pos = string.Format("Row[{0}], Cell[{1}]", (current + 1).ToString(), GetHeaderColumnName(i));
ExcelQueryUtility.LogError(string.Format("Excel File {0} Deserialize Exception: {1} at {2}", this.filepath, e.Message, pos));
}
}
}
}
if (isCellValueLegal)
{
result.Add(item);
}
current++;
}
return(result);
}
