private void FormSQLInsert(Sheet sheet, int row, int col)
{
List <string> fld = new List <string>();
List <string> data = new List <string>();
string nameTable = sheet.Cells[row - 1][col + 1].ToString();
int k = row;
int j = col;
while (!string.IsNullOrWhiteSpace(sheet.Cells[k][j].ToString()))
{
string name = sheet.Cells[k][j + 1];
string cont = null;
if (name.IndexOf("<") != -1)
{
cont = name.Substring(name.IndexOf("<") + 1);
cont = cont.Trim('>');
name = name.Substring(0, name.IndexOf("<"));
}
fld.Add(name);
data.Add(cont);
k++;
}
k = row;
j = 0;
while (!string.IsNullOrWhiteSpace(sheet.Cells[k][j].ToString()))
{
string name = sheet.Cells[k][j + 1];
int index = fld.IndexOf(name);
if (index != -1)
{
data[index] = name;
}
k++;
}
string sql = $"INSERT INTO [{nameTable}] (";
for (int i = 0; i < fld.Count; i++)
{
sql += $"[{fld[i]}],";
}
sql = sql.Trim(',') + $") IN '{Settings.Default.PathBaseED2}'";
sql += " SELECT ";
for (int i = 0; i < data.Count; i++)
{
sql += ((data[i] != null) ? $"[{ data[i]}]" : "null") + ",";
}
if (string.IsNullOrWhiteSpace(sheet.Cells[row - 1][col + 3]))
{
sql = sql.Trim(',') + $" FROM [{sheet.Cells[row - 1][1]}]";
}
else
{
string name = sheet.Cells[row - 1][col + 3];
sql = sql.Trim(',') + $" FROM {name}";
}
SQLInsert.Add(sql);
}
