private List <string> getColumnNames(Excel e)
{
List <List <string> > listaKolone = e.ReadRange(4, 4, "A", exl.getColumnName(exl.ws.UsedRange.Columns.Count));
for (int i = 0; i < listaKolone[0].Count; i++)
{
if (listaKolone[0][i] == "")
{
if (opisKolona != null)
{
if (opisKolona[i] != "")
{
listaKolone[0][i] = opisKolona[i];
}
else
{
if (posebnaImena != null)
{
listaKolone[0][i] = posebnaImena[i];
}
}
}
}
}
return(listaKolone[0]);
}
private List <string> getSpecialNames(Excel e)
{
List <List <string> > listaImena = e.ReadRange(5, 5, "A", exl.getColumnName(exl.ws.UsedRange.Columns.Count));
return(listaImena[0]);
}
private List <string> getColumnDescription(Excel e)
{
List <List <string> > listaOpisa = e.ReadRange(3, 3, "A", exl.getColumnName(exl.ws.UsedRange.Columns.Count));
return(listaOpisa[0]);
}
private void btnUsporedi_Click(object sender, EventArgs e)
{
listBox2MDB.Items.Clear();
listBoxExcel.Items.Clear();
OutputTextbox.Clear();
textBox1.Clear();
textBox2.Clear();
string endcol = exl.getColumnName(exl.ws.UsedRange.Columns.Count);
dat = exl.ReadRange(6, exl.getMaxRowNumber(), "A", endcol);
usedColumn = Enumerable.Repeat(false, imenaKolona.Count).ToList();
emptyColumn = Enumerable.Repeat(false, imenaKolona.Count).ToList();
results = new Dictionary <string, Dictionary <string, double> >();
// Nadji novi partNo na kojem nisi bio
int i;
for (i = 1; i < listaExcel.Count; i++)
{
if (bio[i])
{
continue;
}
bio[i] = true;
break;
}
// obradi mdb
string path = m.getMDBpath(listaExcel[i]);
if (path == null)
{
return;
}
listaMdb = m.getMDBdata(listaExcel[i]);
m.CreateTable("mapping", listaExcel[i]);
int PNordinal = listaMdb.Columns["reference"].Ordinal;
string connectString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + m.folderPath + Path.DirectorySeparatorChar + path;
cn = new OleDbConnection(connectString);
MDBpathLabel.Text = m.folderPath + Path.DirectorySeparatorChar + path;
string dir = Path.GetDirectoryName(MDBpathLabel.Text) + Path.DirectorySeparatorChar;
TextWriter tw = null;
if (!File.Exists(dir + "mapping.log"))
{
File.Create(dir + "mapping.log").Dispose();
tw = new StreamWriter(dir + "mapping.log");
}
else if (File.Exists(dir + "mapping.log"))
{
tw = new StreamWriter(dir + "mapping.log", true);
}
string sep = "";
for (int x = 0; x < 30; x++)
{
sep += '-';
}
tw.WriteLine(sep);
tw.WriteLine("Unique 100% matches: ");
tw.WriteLine(sep);
for (int k = 0; k < listaMdb.Columns.Count; ++k)
{
int count = exl.countColumns("A", exl.getColumnName(exl.ws.UsedRange.Columns.Count));
List <int> candCol = Enumerable.Range(0, count).ToList();
List <int> noOfMatches = Enumerable.Repeat(0, count).ToList();
foreach (var item in listaMdb.AsEnumerable())
{
var pn = (string)item.ItemArray[PNordinal];
if (listaExcel.Contains(pn))
{
bio[listaExcel.FindIndex(a => a == pn)] = true;
index = dat.FindIndex(drow => drow[0] == pn);
xmlData = dat[index];
string atr = item.ItemArray[k].ToString();
double num;
//if (candCol.Count == 0) break;
if (double.TryParse(atr, out num))
{
double num2;
for (int j = 0; j < xmlData.Count; j++)
{
if (double.TryParse(xmlData[j], out num2))
{
if (num != num2)
{
candCol.Remove(j);
}
else
{
noOfMatches[j]++;
}
}
else
{
candCol.Remove(j);
}
}
}
else if (atr != "")
{
double num2;
for (int j = 0; j < xmlData.Count; j++)
{
if (!double.TryParse(xmlData[j], out num2) && xmlData[j] != "")
{
RegexOptions options = RegexOptions.None;
Regex regex = new Regex("[ ]{2,}", options);
// ukloni visestruke razmake i razmake na pocetku i kraju
atr = regex.Replace(atr, " ");
atr = atr.Trim();
// ukloni visestruke razmake i razmake na pocetku i kraju
xmlData[j] = regex.Replace(xmlData[j], " ");
xmlData[j] = xmlData[j].Trim();
if (!string.Equals(atr, xmlData[j], StringComparison.CurrentCultureIgnoreCase))
{
candCol.Remove(j);
}
else
{
noOfMatches[j]++;
}
}
else
{
candCol.Remove(j);
}
}
}
else
{
continue;
}
}
else if (!listaExcel.Contains(pn))
{
m.DeletePartNumber(cn, pn, MDBpathLabel.Text);
listaMdb.Rows.Remove(item);
continue;
}
}
//foreach (var cand in candCol) text += cand + ",";
if (candCol.Count == 1)
{
try
{
cn.Open();
}
catch (InvalidOperationException except) { }
string upit = "SELECT COUNT(mdb) FROM mapping WHERE mdb = '" + listaMdb.Columns[k].ColumnName + "'";
OleDbCommand cmd = new OleDbCommand(upit, cn);
int exists = (int)cmd.ExecuteScalar();
if (exists == 0)
{
m.AddExtraMapping(imenaKolona[candCol[0]],
listaMdb.Columns[k].ColumnName,
opisKolona[candCol[0]],
cn);
}
//MessageBox.Show("Mapirao " + imenaKolona[candCol[0]]+" => "+" "+listaMdb.Columns[k]);
OutputTextbox.Text += "MDB: " + listaMdb.Columns[k] + " => " + "Excel: " + imenaKolona[candCol[0]] + "\r\n";
tw.WriteLine("MDB: " + listaMdb.Columns[k] + " => " + "Excel: " + imenaKolona[candCol[0]]);
usedColumn[candCol[0]] = true;
continue;
}
// SUMNJIVO mapiranje
listBox2MDB.Items.Add(listaMdb.Columns[k].ColumnName);
string text = listaMdb.Columns[k].ColumnName + " => ";
foreach (var cand in candCol)
{
text += imenaKolona[cand] + ", ";
}
int cnt = listaMdb.Rows.Count;
text += "\n";
string mdbCol = listaMdb.Columns[k].ColumnName;
try
{
results.Add(mdbCol, new Dictionary <string, double>());
}
catch { }
for (int j = 0; j < count; j++)
{
if (Math.Round(((double)noOfMatches[j] / cnt), 3) > 0.0)
{
try
{
results[mdbCol].Add(imenaKolona[j], (double)noOfMatches[j] / cnt);
}
catch { }
text += imenaKolona[j] + ": " + Math.Round(((double)noOfMatches[j] / cnt), 3) + "\n";
}
}
text += listaMdb.Rows.Count;
//MessageBox.Show(text);
}
for (int z = 0; z < imenaKolona.Count; z++)
{
if (usedColumn[z] == false)
{
bool empty = true;
foreach (var row in listaMdb.AsEnumerable())
{
var pn = (string)row.ItemArray[PNordinal];
index = dat.FindIndex(drow => drow[0] == pn);
List <string> xmlData = dat[index];
if (xmlData[z] != "")
{
empty = false;
break;
}
}
if (!empty)
{
listBoxExcel.Items.Add(imenaKolona[z]);
}
else
{
emptyColumn[z] = true;
}
}
}
tw.WriteLine(sep);
tw.WriteLine("User mapping: ");
tw.WriteLine(sep);
tw.Close();
refreshStatusLabel();
}
