public static void Validate(HeaderNames names)
{
var properties = names.GetType().GetProperties();
foreach (var property in properties)
{
var value = property.GetValue(names, new object[] { });
if (value == null || string.IsNullOrEmpty(value.ToString().Trim()) == true)
{
throw new JsonHeaderNameEmptyException(property.Name);
}
}
}
private void ValidateHeaders(DataTable dataTable, HeaderNames names)
{
var properties = names.GetType().GetProperties();
foreach (var property in properties)
{
var name = property.GetValue(names, new object[] { }).ToString();
var findName = dataTable.Rows[0].ItemArray.FirstOrDefault(i => i.Equals(name) == true);
if (findName == null)
{
throw new ColumnMissingException(name);
}
}
}
public IEnumerable <Inventory> ReadExcelToTable(string path)
{
//Connection String
string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;';";
using (OleDbConnection conn = new OleDbConnection(connstring))
{
conn.Open();
//Get All Sheets Name
DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
//Get the First Sheet Name
string firstSheetName = "ראשי$";
//Query String
string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);
OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
DataSet set = new DataSet();
ada.Fill(set);
List <Inventory> inventories = new List <EuroImport.Inventory>();
HeaderNames headersNamesFromJson = LoadExcelColumnNames.Load(headerNameFile);
ValidateHeaders(set.Tables[0], headersNamesFromJson);
FixColumnHeaders(set.Tables[0]);
for (int index = 1; index < set.Tables[0].Rows.Count; index++)
{
inventories.Add(new Inventory
{
SKU = set.Tables[0].Rows[index][headersNamesFromJson.SKU].ToString(),
GENDER = set.Tables[0].Rows[index][headersNamesFromJson.Gender].ToString(),
CATEGORY = set.Tables[0].Rows[index][headersNamesFromJson.Category].ToString(),
SUB_CATEGORY = set.Tables[0].Rows[index][headersNamesFromJson.SubCategory].ToString(),
BRAND = set.Tables[0].Rows[index][headersNamesFromJson.Brand].ToString(),
MODEL = set.Tables[0].Rows[index][headersNamesFromJson.Model].ToString(),
MODEL_NAME = set.Tables[0].Rows[index][headersNamesFromJson.ModelName].ToString(),
COLOR = set.Tables[0].Rows[index][headersNamesFromJson.Color].ToString(),
SIZE = set.Tables[0].Rows[index][headersNamesFromJson.Size].ToString(),
REGULAR_PRICE = set.Tables[0].Rows[index][headersNamesFromJson.RegularPrice].ToString(),
SALE_PRICE = set.Tables[0].Rows[index][headersNamesFromJson.SalePrice].ToString(),
STOCK = set.Tables[0].Rows[index][headersNamesFromJson.Stock].ToString(),
REMARKS = set.Tables[0].Rows[index][headersNamesFromJson.Remarks].ToString(),
ADDITION_CATEGORY = set.Tables[0].Rows[index][headersNamesFromJson.AdditionCategory].ToString(),
LOCATION = set.Tables[0].Rows[index][headersNamesFromJson.Location].ToString()
});
}
return(inventories.Where(c => string.IsNullOrEmpty(c.MODEL) == false ||
string.IsNullOrEmpty(c.COLOR) == false ||
string.IsNullOrEmpty(c.SIZE) == false));
}
}