/// <summary>
/// Import data from Excel file into SmartObject
/// </summary>
private void Import(FileProperty excelFile, string sheetName, string smartObject, string createMethodName, string headerRowSpaces, string transactionIDName,
string transactionIDValue)
{
// arrays to store the imported column names and also the SmartObject column names
// this will be used later to get a list of matching columns.
string[] dsColumnNames = null, soColumnNames = null;
// To store returned values from Excel file
DataTable dt;
// Read Data in excel file
try
{
dt = ReadExcelFile(excelFile, sheetName);
if (dt.Rows.Count == 0)
{
throw new ApplicationException(Resources.ExcelImportNoRowsImported);
}
if (dt.Columns.Count == 0)
{
throw new ApplicationException(Resources.ExcelImportNoColumnsFound);
}
// populate an array of column names
dsColumnNames = new string[dt.Columns.Count];
foreach (DataColumn col in dt.Columns)
{
if (!string.IsNullOrWhiteSpace(headerRowSpaces) && string.Compare(headerRowSpaces.Trim(), "remove", true) == 0)
{
col.ColumnName = col.ColumnName.Replace(" ", "");
}
else // by default replace spaces with underscores as SmartObject system names do that
{
col.ColumnName = col.ColumnName.Replace(" ", "_");
}
// just get rid of other (non-underscore or hyphen) punctuation which is also invalid
col.ColumnName = Regex.Replace(col.ColumnName, @"[\p{P}\p{S}-[-_]]", "");
dsColumnNames[col.Ordinal] = col.ColumnName.ToLower();
}
}
catch (Exception ex)
{
throw new Exception(Resources.ExcelImportUnabledToReadExcelFile, ex);
}
// If able to read data from Excel File, continue to bulk insert into SmartObject.
try
{
SOC.SmartObjectClientServer smoClientServer = this.ServiceBroker.K2Connection.GetConnection <SOC.SmartObjectClientServer>();
// get the list of columns from the SmartObject, call the Create method by default
using (smoClientServer.Connection)
{
SOC.SmartObject soImport = smoClientServer.GetSmartObject(smartObject);
// populate an array of column names
soColumnNames = new string[soImport.Properties.Count];
for (int i = 0; i < soImport.Properties.Count; i++)
{
soColumnNames[i] = soImport.Properties[i].Name.ToLower();
}
var arrMatchingCols = dsColumnNames.Join(soColumnNames, dscol => dscol, socol => socol, (dscol, socol) => socol).ToList();
if (arrMatchingCols.Count == 0)
{
throw new ApplicationException(Resources.ExcelImportNoMatchingColumnsInSmO);
}
// Bulk Insert into SmartObject
try
{
using (SOC.SmartObjectList inputList = new SOC.SmartObjectList())
{
// If the CreateMethodName is not specified, use the default value of "Create"
if (string.IsNullOrEmpty(createMethodName))
{
soImport.MethodToExecute = "Create";
}
else
{
soImport.MethodToExecute = createMethodName;
}
string sTransactionIDName = string.Empty;
if (!string.IsNullOrEmpty(transactionIDName))
{
sTransactionIDName = transactionIDName.ToLower();
}
string sTransactionIDValue = string.Empty;
if (!string.IsNullOrEmpty(transactionIDValue))
{
sTransactionIDValue = transactionIDValue;
}
if (arrMatchingCols.Contains(sTransactionIDName))
{
throw new ApplicationException(Resources.ExcelImportTransactionIDNameExist);
}
foreach (DataRow dr in dt.Rows)
{
SOC.SmartObject newSmartObject = soImport.Clone();
// loop through collection of matching fields and insert the values
foreach (string sColName in arrMatchingCols)
{
// for handling date and datetime types
DateTime tmpDate;
int nonblankCharCount = dr[sColName].ToString().Length; //It is plausible that there will valid cases of null/empty cells
// handle Date amd DateTime columns correctly. Convert Excel datetime format (double) to .NET DateTime type
if (nonblankCharCount > 0 && newSmartObject.Properties[sColName].Type == SOC.PropertyType.DateTime) // DateTime column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = String.Concat(tmpDate.ToShortDateString(), " ", tmpDate.ToShortTimeString());
}
else if (nonblankCharCount > 0 && newSmartObject.Properties[sColName].Type == SOC.PropertyType.Date) // Date column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = tmpDate.ToShortDateString();
}
else if (nonblankCharCount > 0 && newSmartObject.Properties[sColName].Type == SOC.PropertyType.Time) // Time column
{
tmpDate = DateTime.FromOADate(Convert.ToDouble(dr[sColName].ToString()));
newSmartObject.Properties[sColName].Value = tmpDate.ToShortTimeString();
}
else // not a Date or DateTime column
{
newSmartObject.Properties[sColName].Value = dr[sColName].ToString();
}
}
// Add the transaction ID value for identification purposes.
if (!string.IsNullOrEmpty(sTransactionIDName) && !string.IsNullOrEmpty(sTransactionIDValue))
{
// replace spaces with underscores as SmartObject system names do that
newSmartObject.Properties[sTransactionIDName.Replace(" ", "_")].Value = sTransactionIDValue;
}
inputList.SmartObjectsList.Add(newSmartObject);
}
smoClientServer.ExecuteBulkScalar(soImport, inputList);
}
// free objects
arrMatchingCols = null;
soImport = null;
}
catch (Exception ex)
{
throw new Exception(Resources.ExcelImportUnabledToInsertIntoSmO, ex);
}
}
}
catch (Exception ex)
{
throw new Exception(Resources.ExcelImportUnabledToConnectToK2Server, ex);
}
}
