static bool checkPattern(MS_excel excel, DataRow dr, string[] patterns, int indexOf, int[] columns, int i)
{
Regex regex;
bool match = false;
if (excel.ds.Tables[0].Rows.IndexOf(dr) > indexOf && (!(string.IsNullOrEmpty(dr[0].ToString())) || !(string.IsNullOrEmpty(dr[1].ToString()))))
{
foreach (string pat in patterns)
{
if (columns[i] == -1)
{
break;
}
regex = new Regex(pat);
if (regex.IsMatch(dr[columns[i]].ToString()))
{
match = true;
}
}
}
if (match)
{
return(true);
}
else
{
return(false);
}
}
static void Main(string[] args)
{
/*
* COLUMNS contains ID of columns
* columns[0] = Codes
* columns[1] = Description
* columns[2] = Qty
* columns[3] = Price
* columns[4] = Discount
* columns[5] = TotalValue of row
*/
////directory to the folder with files
string directory = @"C:\test\Sonepar2\";
////Creating list of files to be read
var dir = new DirectoryInfo(directory);
FileInfo[] fi = dir.GetFiles("*.xls");
foreach (FileInfo file in fi)
{
string pathToFile = String.Concat(directory, file);
bool sonepar = false;
if (file.Name.ToString().Contains("sonepar"))
{
sonepar = true;
}
try
{
MS_excel ex = new MS_excel(@pathToFile, "Sheet1", false);
//Adding values -1 to the columns array
int indexOf = 0;
int[] columns = new int[ex.ds.Tables[0].Columns.Count];
for (int i = 0; i < ex.ds.Tables[0].Columns.Count - 1; i++)
{
columns[i] = -1;
}
Console.WriteLine(pathToFile);
findColumnsByName(ex, ref columns, ref indexOf);
displayRows(ex, columns, indexOf, file.ToString(), sonepar);
}
catch (Exception e)
{
Console.WriteLine(e);
continue;
}
}
//string[] pattCode = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Code'", "Pattern");
//MS_excel excel = new MS_excel(@"C:\[OCR] hotFolders\sebastian2\done\TEst\0000242004_kod w opisie.xls", "Sheet1", false);
//foreach (DataRow dr in excel.ds.Tables[0].Rows)
//{
// string pattern = "(" + "[a-zA-Z0-9]{2}-[a-zA-Z0-9]{4}-[a-zA-Z0-9]{3}" + ")";
// string test = "PX-0024-OXI SIROS E27 IP23 100W";
// Match mtch = Regex.Match(test, pattern);
// //Console.WriteLine(pattern);
// if (mtch.Success) Console.WriteLine(mtch.Groups[1].Value);
//}
}
//Special value = true should be used for SONEPAR's invoices
static void displayRows(MS_excel excel, int[] columns, int indexOf, string file, bool sonepar = false)
{
Row r;
var rows = new List <Row>();
string docName = file;
bool matchCode, matchDescription, matchQuantity, matchPrice, matchDiscount, matchTotalValue;
matchCode = false;
matchDescription = false;
matchQuantity = false;
matchPrice = false;
matchDiscount = false;
matchTotalValue = false;
string[] pattCode = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Code'", "Pattern");
string[] pattDescription = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Description'", "Pattern");
string[] pattQuantity = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Quantity'", "Pattern");
string[] pattPrice = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Price'", "Pattern");
string[] pattDiscount = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'Discount'", "Pattern");
string[] pattTotalValue = getDataFromSQL("SELECT * FROM Patterns WHERE ColumnName = 'TotalValue'", "Pattern");
foreach (DataRow dr in excel.ds.Tables[0].Rows)
{
for (int i = 0; i < columns.Length; i++)
{
switch (i)
{
case 0:
matchCode = checkPattern(excel, dr, pattCode, indexOf, columns, i);
break;
case 1:
matchDescription = checkPattern(excel, dr, pattDescription, indexOf, columns, i);
break;
case 2:
matchQuantity = checkPattern(excel, dr, pattQuantity, indexOf, columns, i);
break;
case 3:
matchPrice = checkPattern(excel, dr, pattPrice, indexOf, columns, i);
break;
case 4:
matchDiscount = checkPattern(excel, dr, pattDiscount, indexOf, columns, i);
break;
case 5:
matchTotalValue = checkPattern(excel, dr, pattTotalValue, indexOf, columns, i);
break;
default:
break;
}
}
if (sonepar && !String.IsNullOrEmpty(dr[columns[2]].ToString()) && !String.IsNullOrEmpty(dr[columns[3]].ToString()) && excel.ds.Tables[0].Rows.IndexOf(dr) > indexOf)
{
string match = "S/Ref.: ";
bool found = false;
int counter = 1;
string row;
r = new Row();
if (Regex.IsMatch(dr[columns[1]].ToString(), match))
{
row = dr[columns[1]].ToString();
r.Code = getItemCode(row);
}
else
{
string rowQuantity = excel.ds.Tables[0].Rows[excel.ds.Tables[0].Rows.IndexOf(dr) + counter][columns[2]].ToString();
string rowPrice = excel.ds.Tables[0].Rows[excel.ds.Tables[0].Rows.IndexOf(dr) + counter][columns[3]].ToString();
while (!found && String.IsNullOrEmpty(rowQuantity.ToString()) && String.IsNullOrEmpty(rowPrice.ToString()))
{
row = excel.ds.Tables[0].Rows[excel.ds.Tables[0].Rows.IndexOf(dr) + counter][columns[1]].ToString();
if (Regex.IsMatch(row.ToString(), match))
{
r.Code = getItemCode(row);
found = true;
}
else
{
counter++;
}
}
}
try
{
if (columns[1] != -1)
{
r.Description = dr[columns[1]].ToString();
}
else
{
r.Description = "";
}
if (columns[2] != -1)
{
r.Quantity = Convert.ToDouble(dr[columns[2]].ToString().Replace(",", "."));
}
if (columns[3] != -1 && matchPrice)
{
r.Price = toDouble(dr[columns[3]].ToString());
}
if (columns[4] != -1)
{
r.Discount = dr[columns[4]].ToString();
}
else
{
r.Discount = "";
}
if (columns[5] != -1 && matchTotalValue)
{
r.TotalValue = toDouble(dr[columns[5]].ToString());
}
rows.Add(r);
}
catch { continue; }
}
else if ((matchPrice && matchQuantity) || (matchQuantity && matchTotalValue))
{
r = new Row();
try
{
if (columns[0] != -1 && !String.IsNullOrEmpty(dr[columns[0]].ToString()))
{
r.Code = dr[columns[0]].ToString();
}
else
{
r.Code = "";
}
if (columns[1] != -1)
{
r.Description = dr[columns[1]].ToString();
}
if (columns[2] != -1 && matchQuantity)
{
r.Quantity = Convert.ToDouble(dr[columns[2]].ToString().Replace(",", "."));
}
if (columns[3] != -1 && matchPrice)
{
r.Price = toDouble(dr[columns[3]].ToString());
}
if (columns[4] != -1)
{
r.Discount = dr[columns[4]].ToString();
}
else
{
r.Discount = "";
}
if (columns[5] != -1 && matchTotalValue)
{
r.TotalValue = toDouble(dr[columns[5]].ToString());
}
rows.Add(r);
}
catch { continue; }
}
matchCode = false;
matchDescription = false;
matchQuantity = false;
matchPrice = false;
matchDiscount = false;
matchTotalValue = false;
}
insertDataToSQL(rows, docName);
}
static void findColumnsByName(MS_excel excel, ref int[] columns, ref int indexOf)
{
string[] dictionary = { "" };
bool found = false;
bool indexOfBegining = false;
string[] columnNames = { "Code", "Description", "Quantity", "Price", "Discount", "TotalValue" };
for (int i = columns[0]; i < columns.Length; i++)
{
found = false;
switch (i)
{
case 0:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'Code'", "Term");
break;
case 1:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'Description'", "Term");
break;
case 2:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'Quantity'", "Term");
break;
case 3:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'Price'", "Term");
break;
case 4:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'Discount'", "Term");
break;
case 5:
dictionary = getDataFromSQL("SELECT * FROM Dictionary WHERE ColumnName = 'TotalValue'", "Term");
break;
default:
Array.Clear(dictionary, 0, dictionary.Length);
break;
}
foreach (DataRow dr in excel.ds.Tables[0].Rows)
{
for (int k = 0; k < excel.ds.Tables[0].Columns.Count; k++)
{
foreach (string term in dictionary)
{
if (!found && dr[k].Equals(term))
{
if (!indexOfBegining)
{
indexOf = indexOf = excel.ds.Tables[0].Rows.IndexOf(dr);
indexOfBegining = true;
}
Console.WriteLine("Found column " + columnNames[i] + " on position {0}", k); //Do usuniecia potem
columns[i] = k;
found = true;
}
else
{
continue;
}
}
}
}
}
}