private DataTable excelToRevisionTable(OpenFileDialog ofd)
{
DataTable dataTable = readFromExcel(ofd.FileName);
DataTable copy = dataTable.Copy();
IEnumerable <DataColumn> columns = copy.Columns.Cast <DataColumn>();
EnumerableRowCollection <DataRow> rows = copy.AsEnumerable();
List <DataColumn> nullColumns = columns.Where(col => rows.All(r => r.IsNull(col) ||
String.CompareOrdinal(
(r.ToString()).Trim(),
string.Empty) == 0
)).ToList();
foreach (DataColumn colToRemove in nullColumns)
{
copy.Columns.Remove(colToRemove);
}
var searchTerm = new Regex(@"^[0-9]+\S.*?\s\S.*?");
var resData = new DataTable();
resData.Columns.Add("article");
resData.Columns.Add("price");
for (int rowNum = 0; rowNum < copy.Rows.Count; rowNum++)
{
for (int colNum = 0; colNum < copy.Columns.Count; colNum++)
{
object cell = copy.Rows[rowNum][colNum];
if (searchTerm.IsMatch(cell.ToString()))
{
DataRow ravi = resData.NewRow();
ravi["article"] = copy.Rows[rowNum][colNum];
if (copy.Rows[rowNum][colNum + 1] == DBNull.Value)
{
ravi["price"] = "";
}
else
{
ravi["price"] = copy.Rows[rowNum][colNum + 1];
}
resData.Rows.Add(ravi);
break;
}
}
}
return(resData);
}