protected void ExcelImportWizard_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
if (!CustomValidatorValidateMapping.IsValid)
{
return;
}
string appendMode = ViewState["AppendMode"].ToString();
Dictionary <string, string> columnMapping = new Dictionary <string, string>();
foreach (GridViewRow row in dgvColumns.Rows)
{
string excelColumn = row.Cells[0].Text;
string dbColumn = (row.Cells[1].Controls[1] as DropDownList).SelectedValue;
if (dbColumn.Equals(ExcelUtils.IGNORE_COLUMN_VALUE, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
columnMapping.Add(dbColumn, excelColumn);
}
MemoryStream stream = new MemoryStream(ViewState["Stream"] as byte[]);
DataTable dt;
using (ExcelPackage spreadSheetDocument = new ExcelPackage(stream))
{
dt = ExcelUtils.GetDataTableFromExcel(spreadSheetDocument, true);
}
using (InteractiveKeyEntities context = new InteractiveKeyEntities())
{
if (appendMode == "Overwrite")
{
// remove existing species and their character states
foreach (var sp in context.Species)
{
var states = sp.CharacterStates.ToArray();
foreach (var state in states)
{
sp.CharacterStates.Remove(state);
}
context.Species.Remove(sp);
}
context.SaveChanges();
}
string excelSpeciesColName = ExcelUtils.GetExcelColumnName(FriendlyColumnNames.SpeciesName, columnMapping);
foreach (DataRow dataRow in dt.Rows)
{
string speciesName = ExcelUtils.GetExcelRowValueForColumn(dataRow, excelSpeciesColName).ToString().Trim();
Species sp = context.Species.SingleOrDefault(i => i.SpeciesName == speciesName);
if (sp == null)
{
sp = context.Species.Create();
sp.AssignNewSpeciesId(context);
sp.SpeciesName = speciesName;
context.Species.Add(sp);
}
foreach (DataColumn col in dataRow.Table.Columns)
{
string colName = col.ColumnName;
if (colName != columnMapping[FriendlyColumnNames.SpeciesName.ToUpper()]) //not species column
{
Character cha = context.Characters.SingleOrDefault(i => i.CharacterCode == colName);
if (cha == null)
{
//excel contains non character code columns -- Error
throw new Exception(colName + " is not a valid Character Code. Please correct this and re-import.");
}
else
{
string stateCode = dataRow[colName].ToString().Trim();
if (!string.IsNullOrEmpty(stateCode) && stateCode != "-")
{
CharacterState st = context.CharacterStates.SingleOrDefault(i => i.CharacterID == cha.CharacterID && i.CharacterStateCode == stateCode);
if (st == null)
{
string errMsg = string.Format("{0} is not a valid Character State for the character: {1} for {2}. Please correct this and re-import.", stateCode, cha.CharacterCode, speciesName);
throw new Exception(errMsg);
}
else
{
sp.CharacterStates.Add(st);
}
}
}
}
}
context.SaveChanges();
}
context.SaveChanges();
}
}