public static DataTable FindExcelSheetTable(this DataSet dataset, string tableIdentityOrName)
{
var jsDeserializerForOptions = new JavaScriptSerializer();
foreach (DataTable currentTable in dataset.Tables)
{
var tableOptionsObject = new ExcelTableOptions();
#region Exclude table
if (currentTable.TableName.ToLower().EndsWith(".x"))
{
continue;
}
#endregion
var currentTableName = currentTable.TableName;
#region Read table options
if (currentTableName.Contains("{") &&
currentTableName.Contains("}"))
{
var tableOptionsText = currentTableName.Substring(currentTableName.IndexOf("{"));
//In table options JSON text, We use ^ instead of : and here we replace it
tableOptionsText = tableOptionsText.Replace("^", ":");
tableOptionsObject = jsDeserializerForOptions.Deserialize <ExcelTableOptions>(tableOptionsText);
currentTableName = currentTableName.Substring(0, currentTableName.IndexOf("{"));
tableOptionsObject.Id = currentTableName;
}
else
{
tableOptionsObject.Id = currentTable.TableName;
}
#endregion
if (tableOptionsObject.Id == tableIdentityOrName)
{
return(currentTable);
}
}
return(null);
}
//Import full database from excel file
//If sheet ends with .x that sheet will not be imported
public List <string> BootstrapDatabaseFromExcelSheet(string filePath, ExcelVersion version)
{
var jsDeserializerForOptions = new JavaScriptSerializer();
TruncateFullDatabase();
List <string> listOfProcessedTables = new List <string>();
var datasetFromExcel = new ExcelReader().GetDataSet(version, filePath, true);
if (this.OnExcelDataSetRead != null)
{
this.OnExcelDataSetRead(datasetFromExcel);
}
var dataTableImporter = new DataImport(this.ConnectionString);
foreach (DataTable currentTable in datasetFromExcel.Tables)
{
var tableOptionsObject = new ExcelTableOptions();
#region Exclude table
if (currentTable.TableName.ToLower().EndsWith(".x"))
{
continue;
}
#endregion
var currentTableName = currentTable.TableName;
#region Read table options
if (currentTableName.Contains("{") &&
currentTableName.Contains("}"))
{
var tableOptionsText = currentTableName.Substring(currentTableName.IndexOf("{"));
//In table options JSON text, We use ^ instead of : and here we replace it
tableOptionsText = tableOptionsText.Replace("^", ":");
tableOptionsObject = jsDeserializerForOptions.Deserialize <ExcelTableOptions>(tableOptionsText);
currentTableName = currentTableName.Substring(0, currentTableName.IndexOf("{"));
tableOptionsObject.Id = currentTableName;
}
else
{
tableOptionsObject.Id = currentTable.TableName;
}
#endregion
listOfProcessedTables.Add(currentTable.TableName);
//event Pre Column Options Work
if (this.OnExcelPreColumnOptionsWork != null)
{
this.OnExcelPreColumnOptionsWork(currentTable, tableOptionsObject);
}
foreach (DataColumn currentColumn in currentTable.Columns)
{
#region Column driven by options
if (currentColumn.ColumnName.Contains("{") &&
currentColumn.ColumnName.Contains("}"))
{
var columnOptionsText = currentColumn.ColumnName.Substring(currentColumn.ColumnName.IndexOf("{"));
var columnOptionsObject = jsDeserializerForOptions.Deserialize <ExcelColumnOptions>(columnOptionsText);
var columnNameB4Options = currentColumn.ColumnName.Substring(0, currentColumn.ColumnName.IndexOf("{"));
#region Column data Type conversion In case of Type option present in column options
if (columnOptionsObject.Type.IsNotNullOrEmpty())
{
var targetDotNetType = Type.GetType(columnOptionsObject.Type);
foreach (DataRow row4DataTypeChange in currentTable.Rows)
{
var typeConverter4ValueInColumn = new ManualConvert(row4DataTypeChange[currentColumn.ColumnName]);
try
{
//set value
row4DataTypeChange[currentColumn.ColumnName] = typeConverter4ValueInColumn.ConvertType(columnOptionsObject.Type);
}
catch (Exception err)
{
//Set Db null
row4DataTypeChange[currentColumn.ColumnName] = DBNull.Value;
}
}
}
#endregion
#region Query for foreign keys or alternative values
if (columnOptionsObject.Query.IsNotNullOrEmpty())
{
var nonDistinctColumnValues = new List <string>();
foreach (DataRow row4NonDistinctValues in currentTable.Rows)
{
nonDistinctColumnValues.Add(row4NonDistinctValues[currentColumn.ColumnName].Text());
}
var distinctColumnValues = nonDistinctColumnValues.Distinct <string>().ToList <string>();
var sbDistinctColumValues = new StringBuilder();
var indexForeachOnDistinctValues = 0;
foreach (var distinctColumnValue in distinctColumnValues)
{
if (indexForeachOnDistinctValues > 0)
{
sbDistinctColumValues.Append(",");
}
sbDistinctColumValues.Append("'" + distinctColumnValue + "'");
indexForeachOnDistinctValues++;
}
var pairsTableFromDB = GetDataTable(columnOptionsObject.Query.Replace("@Values", sbDistinctColumValues.ToString()));
//Insert non existing in db
if (columnOptionsObject.Insert.IsNotNullOrEmpty())
{
var listNonExistingItems = new List <string>();
foreach (DataRow rowInExcel in currentTable.Rows)
{
var recordExists = false;
foreach (DataRow rowInDBToReplace in pairsTableFromDB.Rows)
{
if (rowInExcel[currentColumn.ColumnName].Text() == rowInDBToReplace[0].Text())//first column matched
{
recordExists = true;
break;
}
}
if (!recordExists)
{
listNonExistingItems.Add(rowInExcel[currentColumn.ColumnName].Text());
}
}
if (listNonExistingItems.Any())
{
listNonExistingItems = listNonExistingItems.Distinct <string>().ToList <string>();
foreach (var nonExistingValue in listNonExistingItems)
{
ExecuteQuery(columnOptionsObject.Insert, "@Value", nonExistingValue);
}
#region Fetch again
indexForeachOnDistinctValues = 0;
sbDistinctColumValues = new StringBuilder();
foreach (var distinctColumnValue in distinctColumnValues)
{
if (indexForeachOnDistinctValues > 0)
{
sbDistinctColumValues.Append(",");
}
sbDistinctColumValues.Append("'" + distinctColumnValue + "'");
indexForeachOnDistinctValues++;
}
pairsTableFromDB = GetDataTable(columnOptionsObject.Query.Replace("@Values", sbDistinctColumValues.ToString()));
#endregion
}
}
foreach (DataRow rowInExcel in currentTable.Rows)
{
foreach (DataRow rowInDBToReplace in pairsTableFromDB.Rows)
{
if (rowInExcel[currentColumn.ColumnName].Text() == rowInDBToReplace[0].Text()) //first column matched
{
rowInExcel[currentColumn.ColumnName] = rowInDBToReplace[1]; //second column replaces original value
break;
}
}
}
}
#endregion
//change column name if Options found for current column
currentColumn.ColumnName = columnNameB4Options;
}
#endregion
}
//event Pre import
if (this.OnPreTableImport != null)
{
this.OnPreTableImport(currentTable, tableOptionsObject, currentTableName);
}
currentTable.TableName = currentTableName;
dataTableImporter.FromDataTable(currentTable, true);
//Pre table import event
if (this.OnPostTableImport != null)
{
this.OnPostTableImport(currentTable, tableOptionsObject, currentTableName);
}
}
//Last event Post import
if (this.OnExcelFileImported != null)
{
this.OnExcelFileImported();
}
return(listOfProcessedTables);
}
