private void LoadTemplate_Click(object sender, RoutedEventArgs e)
{
var filePath = string.Empty;
OpenFileDialog openFileDialog = new OpenFileDialog();
if (_migrationContainer.Destination != null)
{
openFileDialog.InitialDirectory = _migrationContainer.Destination.FileDirectory;
}
else
{
openFileDialog.InitialDirectory = "C:\\";
}
openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsm|All files (*.*)|*.*";
openFileDialog.FilterIndex = 2;
openFileDialog.RestoreDirectory = true;
Nullable <bool> result = openFileDialog.ShowDialog();
if (result == true)
{
filePath = openFileDialog.FileName;
AsposeExcel Template = new AsposeExcel(filePath);
LoadFromTemplate(Template);
}
else
{
var msg = new MessageView("Please choose some template file");
}
}
public SettingsView(MigrationContainer migration)
{
InitializeComponent();
_migrationContainer = migration;
try
{
if (_migrationContainer.Destination != null)
{
cmbExcelDestination.ItemsSource = _migrationContainer.Destination.ListOfWorksheets();
InitSourceExcel();
}
if (_migrationContainer.Source != null)
{
cmbExcelSource.ItemsSource = _migrationContainer.Source.ListOfWorksheets();
InitDestinationExcel();
}
}
catch
{
var msg = new MessageView("Could not load worksheets");
msg.Show();
}
}
private void MergeDestinationWithSource()
{
int columnparserscount = columnParsers.Count;
if (IsKeyChecked())
{
var keycolumn = from el in columnParsers
where el.IsKey == true
select el;
if (keycolumn.Count() != 1)
{
var msg = new MessageView("You cannot specify more than one key!");
msg.Show();
}
else
{
var col = keycolumn.FirstOrDefault();
Dictionary <string, int> sourceKeys = new Dictionary <string, int>();
Dictionary <string, int> destinationKeys = new Dictionary <string, int>();
sourceKeys = LoadColumn(_migration.Source, col.SourceColumnIndex);
destinationKeys = LoadColumn(_migration.Destination, col.DestinationColumnIndex);
SortedDictionary <string, int> sortedSourceKeys = new SortedDictionary <string, int>(sourceKeys);
//SortedDictionary<string, int> sortedDestinationKeys = new SortedDictionary<string, int>(destinationKeys);
int ifNotFoundKeyRow = LastEmptyRow(_migration.Destination);
bool permission = true;
int row = 0;
int columncaptionsindex = _migration.Destination.ColumnCaptionRow;
foreach (var cl in sortedSourceKeys)
{
if (cl.Key != string.Empty)
{
if (destinationKeys.ContainsKey(cl.Key))
{
row = destinationKeys[cl.Key] + columncaptionsindex;
}
else
{
row = ifNotFoundKeyRow++;
}
FromSource2DestinationByRow(row,
cl,
sourceKeys,
permission);
}
}
_migration.Destination.Save();
_migration.Source.Save();
}
}
}
private void LoadFromTemplate(AsposeExcel template)
{
string sourcefullpath = template.ReadCell(0, 1);
string destinationfullpath = template.ReadCell(1, 1);
string sourceworksheet = template.ReadCell(0, 2);
string destinationworksheet = template.ReadCell(1, 2);
if (File.Exists(sourcefullpath) && File.Exists(destinationfullpath))
{
_migrationContainer.Source = new AsposeExcel(sourcefullpath);
InitSourceExcel();
_migrationContainer.Destination = new AsposeExcel(destinationfullpath);
InitDestinationExcel();
if (_migrationContainer.Source.ChangeWorksheet(sourceworksheet) && _migrationContainer.Destination.ChangeWorksheet(destinationworksheet))
{
_migrationContainer.TemplateAttached = true;
for (int crow = 3; crow < template.RowsCount; crow++)
{
ColumnParser col = new ColumnParser();
col.SourceColumnName = template.ReadCell(crow, 0);
col.SourceColumnIndex = Convert.ToInt16(template.ReadCell(crow, 1));
col.DestinationColumnName = template.ReadCell(crow, 2);
col.DestinationColumnIndex = Convert.ToInt16(template.ReadCell(crow, 3));
col.IsKey = Convert.ToBoolean(template.ReadCell(crow, 4));
col.LookupMatch = Convert.ToBoolean(template.ReadCell(crow, 5));
_migrationContainer.ColumnParsers.Add(col);
}
if (_migrationContainer.ColumnParsers.Count() > 0)
{
var msg = new MessageView("Template has been loaded");
msg.Show();
}
else
{
var msg = new MessageView("Cannot find any column relations");
msg.Show();
}
}
else
{
var msg = new MessageView("Cannot find worksheets");
msg.Show();
}
}
else
{
var msg = new MessageView("Cannot find files. Put them in the same directory as written in excel file");
msg.Show();
}
}
private void CmbExcelSource_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
if (_migrationContainer.Source.ChangeWorksheet(cmbExcelSource.SelectedItem.ToString()))
{
txtColumnSourceCount.Text = _migrationContainer.Source.ColumnsCount.ToString();
txtRowSourceCount.Text = (_migrationContainer.Source.RowsCount - _migrationContainer.Source.ColumnCaptionRow).ToString();
txtSimililarityPercent.Text = Similarity();
}
else
{
var msg = new MessageView("Source worksheet cannot be loaded");
}
}
public ComparisonView(MigrationContainer migration)
{
InitializeComponent();
_migration = migration;
MergeActions.Add("Overwrite");
MergeActions.Add("Merge By Key");
MergeActions.Add("Merge With Text Match");
MergeActions.Add("Merge And Check Relation");
MergeActions.Add("Add 2 Worksheet");
cmbMergeOption.ItemsSource = MergeActions;
if (_migration.Source != null && _migration.Destination != null)
{
LoadComparisonView();
}
else
{
var msg = new MessageView("Navision Excel files should be loaded!");
msg.Show();
}
}
private void BtnMerge_Click(object sender, RoutedEventArgs e)
{
if (columnParsers != null)
{
if (_migration.Destination != null && _migration.Source != null)
{
if (cmbMergeOption.Text == "Overwrite")
{
OverwriteDestinationBySource();
}
if (cmbMergeOption.Text == "Merge By Key")
{
MergeDestinationWithSource();
}
if (cmbMergeOption.Text == "Merge And Check Relation")
{
MergeAndCheckRelations();
}
if (cmbMergeOption.Text == "Merge With Text Match")
{
MergeCheckTextMatch();
}
if (cmbMergeOption.Text == "Add 2 Worksheet")
{
Add2Worksheet();
}
}
else
{
var msg = new MessageView("Please load some excels to merge them");
msg.Show();
}
}
}
private void MergeCheckTextMatch()
{
if (IsSecondKeyChecked() && IsKeyChecked())
{
var keycolumn = from el in columnParsers
where el.IsKey == true
select el;
var matchcolumn = from el in columnParsers
where el.LookupMatch == true
select el;
if (keycolumn.Count() != 1 || matchcolumn.Count() != 1)
{
var msg = new MessageView("You cannot specify more than one first key!");
msg.Show();
}
else
{
var keycol = keycolumn.FirstOrDefault();
var matchcol = matchcolumn.FirstOrDefault();
Dictionary <string, int> sourceKeys = new Dictionary <string, int>();
Dictionary <string, int> destinationKeys = new Dictionary <string, int>();
sourceKeys = LoadColumn(_migration.Source, keycol.SourceColumnIndex);
destinationKeys = LoadColumn(_migration.Destination, keycol.DestinationColumnIndex);
SortedDictionary <string, int> sortedSourceKeys = new SortedDictionary <string, int>(sourceKeys);
Dictionary <string, int> sourceNames = new Dictionary <string, int>();
Dictionary <string, int> destinationNames = new Dictionary <string, int>();
sourceNames = LoadColumn(_migration.Source, matchcol.SourceColumnIndex);
destinationNames = LoadColumn(_migration.Destination, matchcol.DestinationColumnIndex);
int ifNotFoundKeyRow = LastEmptyRow(_migration.Destination);
bool permission = true;
int row = 0;
int columncaptionsindex = _migration.Destination.ColumnCaptionRow;
foreach (var cl in sortedSourceKeys)
{
if (cl.Key != string.Empty)
{
if (destinationKeys.ContainsKey(cl.Key))
{
row = destinationKeys[cl.Key] + columncaptionsindex;
int rowsource = sourceKeys[cl.Key] + columncaptionsindex;
decimal percent = Levenstein.Percent(
_migration.Source.ReadCell(rowsource, matchcol.SourceColumnIndex),
_migration.Destination.ReadCell(row, matchcol.DestinationColumnIndex)
);
if (percent < 80)
{
var elements = from el in destinationNames
where Levenstein.NettoDistance(cl.Key, el.Key) <= 3 && el.Key != string.Empty
select el;
if (elements.Count() > 0)
{
KeyValuePair <string, int> result = new KeyValuePair <string, int>();
var comp = new SimilarNames(elements, result);
comp.ShowDialog();
}
}
}
else
{
row = ifNotFoundKeyRow++;
}
FromSource2DestinationWithRelationByRow(row,
cl,
sourceKeys,
permission);
}
}
_migration.Destination.Save();
_migration.Source.Save();
}
}
}
