private void SaveAsSystemPhrases(ExcelImportEventArgs arg)
{
ExcelSheet excelSheet = arg.Result;
// Extract data from imported Excel sheet
List <LanguagePhraseDto> languagePhrases = new List <LanguagePhraseDto>();
for (int index = excelSheet.DataStartRowIndex; index < excelSheet.Rows.Count; index++)
{
ExcelSheetRow excelRow = excelSheet.Rows[index];
LanguagePhraseDto languagePhrase = new LanguagePhraseDto();
languagePhrases.Add(languagePhrase);
languagePhrase.PhraseKey = excelRow.GetValue(Col_Key);
languagePhrase.PhraseValue = excelRow.GetValue(Col_Value);
}
// Save batch
using (IUnitOfWork uow = UnitOfWorkFactory.Instance.Start(DataStoreResolver.CRMDataStoreKey))
{
LanguageFacade facade = new LanguageFacade(uow);
IFacadeUpdateResult <LanguagePhraseData> result = facade.SaveSystemPhrases(SelectedLanguageId, languagePhrases);
if (result.IsSuccessful)
{
arg.IsSuccessful = true;
arg.Message = string.Format("Batch save done. \\nTotal {0} rows.", languagePhrases.Count);
}
else
{
arg.IsSuccessful = false;
// Deal with Update result
ProcUpdateResult(result.ValidationResult, result.Exception);
}
}
}
private void PerformSave(ExcelImportEventArgs arg)
{
ExcelSheet excelSheet = arg.Result;
// Extract data from imported Excel sheet
List <ProductDto> products = new List <ProductDto>();
for (int index = excelSheet.DataStartRowIndex; index < excelSheet.Rows.Count; index++)
{
ExcelSheetRow excelRow = excelSheet.Rows[index];
ProductDto product = new ProductDto();
ExtractImportedSheetRow(product, excelRow);
product.SupplierId = SupplierId;
products.Add(product);
}
// Save batch
using (IUnitOfWork uow = UnitOfWorkFactory.Instance.Start(DataStoreResolver.CRMDataStoreKey))
{
ProductFacade facade = new ProductFacade(uow);
IFacadeUpdateResult <ProductData> result = facade.SaveProducts(products);
if (result.IsSuccessful)
{
arg.IsSuccessful = true;
arg.Message = string.Format("Batch save done. \\nTotal {0} rows.", products.Count);
}
else
{
arg.IsSuccessful = false;
// Deal with Update result
ProcUpdateResult(result.ValidationResult, result.Exception);
}
}
}
private DataTable GetSheetDataTable(ExcelSheet sheet)
{
DataTable table = new DataTable(sheet.Name);
table.Columns.Add(Col_Row);
table.Columns.Add(Col_Validation);
foreach (ImportFieldMapping mapping in MappingList)
{
table.Columns.Add(mapping.RequestField);
}
for (int index = sheet.DataStartRowIndex; index < sheet.Rows.Count; index++)
{
ExcelSheetRow row = sheet.Rows[index];
DataRow dataRow = table.NewRow();
table.Rows.Add(dataRow);
dataRow[Col_Row] = row.RowNo;
dataRow[Col_Validation] = row.ValidateResultString;
int colIndex = 0;
foreach (ExcelSheetColumn column in sheet.Columns)
{
if (column.IsMapped)
{
dataRow[column.TargetName] = row[colIndex].Value;
}
colIndex++;
}
}
return(table);
}
public ExcelSheet CreateDataPhraseSheet(Dictionary <string, string> phrases)
{
Dictionary <object, LanguageDto> languages = RetrieveLanguagesAndDataPhrases();
ExcelSheet sheet = new ExcelSheet();
// Create table schema
sheet.Columns.Add("PhraseKey");
sheet.Columns.Add("DefaultValue");
foreach (LanguageDto language in languages.Values)
{
sheet.Columns.Add(language.Name);
}
// Fill in row data
foreach (string key in phrases.Keys)
{
ExcelSheetRow row = sheet.NewRow();
sheet.Rows.Add(row);
row[0].Value = key;
row[1].Value = phrases[key];
int index = 2;
foreach (LanguageDto language in languages.Values)
{
string value = string.Empty;
if (language.DataPhrases.ContainsKey(key))
{
value = language.DataPhrases[key];
}
row[index].Value = value;
index++;
}
}
return(sheet);
}
private void ApplyMappingInfoAndValidate(ExcelSheet sheet)
{
// Map columns according to mapping list
foreach (ImportFieldMapping mapping in MappingList)
{
if (!string.IsNullOrEmpty(mapping.ColumnName))
{
ExcelSheetColumn column = sheet.Columns[mapping.ColumnName];
column.TargetName = mapping.RequestField;
column.Validator = mapping.Validator;
}
}
// Clear all row-level validate results
foreach (ExcelSheetRow row in sheet.Rows)
{
row.RowLevelValidateResult.Clear();
}
// Validate whole sheet
// Trigger SheetValidate event
ExcelSheetValidatingEventArgs arg = new ExcelSheetValidatingEventArgs(sheet);
if (ExcelSheetValidating != null)
{
ExcelSheetValidating(this, arg);
}
if (!arg.IsCancelled)
{
// Validate each row
for (int index = sheet.DataStartRowIndex; index < sheet.Rows.Count; index++)
{
ExcelSheetRow row = sheet.Rows[index];
// trigger RowValidate event
ExcelSheetRowValidatingEventArgs args = new ExcelSheetRowValidatingEventArgs(row);
if (ExcelSheetRowValidating != null)
{
ExcelSheetRowValidating(this, args);
}
if (!args.IsCancelled)
{
// Validate each cell in current row
foreach (ExcelSheetCell cell in row.Cells)
{
cell.Validate();
}
}
}
}
}
// Extract data field by field
private void ExtractImportedSheetRow(ProductDto product, ExcelSheetRow excelRow)
{
product.Name = excelRow.GetValue(Col_Name);
product.Description = excelRow.GetValue(Col_Description);
product.UnitPrice = Convert.ToDecimal(excelRow.GetValue(Col_UnitPrice));
product.UnitOfMeasure = excelRow.GetValue(Col_UnitOfMeasure);
product.Packaging = excelRow.GetValue(Col_Packaging);
string categoryId = excelRow.GetValue(Col_CategoryId);
if (categoryId != null && categoryId.TrimHasValue())
{
product.CategoryId = Convert.ToInt32(excelRow.GetValue(Col_CategoryId));
}
}
private bool IsNull(ExcelSheetRow row)
{
bool isNull = true;
foreach (ExcelSheetCell cell in row.Cells)
{
if (cell.Value != null && cell.Value.Length > 0)
{
isNull = false;
break;
}
}
return(isNull);
}
private ExcelSheet GetSheet(Worksheet workSheet)
{
// get the row count and column count of excel sheet
int rowCounts = workSheet.MaxRowIndex + 1;
int colCounts = workSheet.MaxColIndex + 1;
ExcelSheet sheet = new ExcelSheet(workSheet.Name);
// Create table schema according the excel sheet
for (int colIndex = 0; colIndex < colCounts; colIndex++)
{
string columnName = string.Format("F{0}", colIndex + 1);
sheet.Columns.Add(columnName);
}
// Read data from each cell, store them to table
for (int rowIndex = 0; rowIndex < rowCounts; rowIndex++)
{
ExcelSheetRow row = sheet.NewRow();
row.RowNo = rowIndex + 1;
// copy data for current row
for (int colIndex = 0; colIndex < colCounts; colIndex++)
{
Cell aCell = workSheet.Cells[rowIndex, colIndex];
if (aCell != null)
{
row[colIndex].Value = aCell.StringValue;
}
else
{
row[colIndex].Value = string.Empty;
}
}
bool isNull = IsNull(row);
// add row to sheet if it's not null row
if (!isNull)
{
sheet.Rows.Add(row);
}
}
return(sheet);
}
// Extract data field by field
private void ExtractImportedSheetRow(SupplierDto Supplier, ExcelSheetRow excelRow)
{
Supplier.Name = excelRow.GetValue(Col_Name);
Supplier.AddressLine1 = excelRow.GetValue(Col_Address);
Supplier.City = excelRow.GetValue(Col_City);
Supplier.Country = excelRow.GetValue(Col_Country);
Supplier.ZipCode = excelRow.GetValue(Col_ZipCode);
Supplier.ContactPerson = excelRow.GetValue(Col_ContactPerson);
Supplier.ContactPhone = excelRow.GetValue(Col_ContactPhone);
Supplier.ContactFax = excelRow.GetValue(Col_ContactFax);
Supplier.ContactEmail = excelRow.GetValue(Col_ContactEmail);
Supplier.Website = excelRow.GetValue(Col_Website);
string categoryId = excelRow.GetValue(Col_Category);
if (categoryId != null && categoryId.TrimHasValue())
{
Supplier.CategoryId = Convert.ToInt32(categoryId);
}
}
// Extract data field by field
private void ExtractImportedSheetRow(ContactDto contact, ExcelSheetRow excelRow)
{
contact.FullName = excelRow.GetValue(Col_FullName);
contact.FamilyName = excelRow.GetValue(Col_FamilyName);
}
