Hi I want to validate datable and if there is an any error in row of data table then i want add Error Msg in Error Column "Excel D: column contain Invalid data". in that row which consist error in row. and if any error occured i want to download excel with error in row.
Following is the code
fileLocation = ConfigurationManager.AppSettings["excelFilePath"].ToString() + fileName;
DataSet ExcelDataSet = null;
try
{
ExcelDataSet = objUl.GetDataTableFromExcel(fileLocation);
}
catch (Exception ex)
{
return RedirectToRoute(new { Controller = "Error", Action = "NotFound" });
}
public DataSet GetDataTableFromExcel(string path, bool hasHeader = true)
{
DataSet dataSet = null;
string ErrorMsg = string.Empty;
try
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = System.IO.File.OpenRead(path))
{
pck.Load(stream);
<pre>var ws = pck.Workbook.Worksheets.FirstOrDefault();
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
DataColumn ErrorCol = new DataColumn("Error", typeof(string));
tbl.Columns.Add(ErrorCol);
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
bool RowIsEmpty = true;
var wsRow = ws.Cells[rowNum, 1, rowNum, 10];
DataRow row = tbl.Rows.Add();
for (int colNum = 1; colNum <= tbl.Columns.Count - 1; colNum++)
object temp = ws.Cells[rowNum, colNum].Value;
if (temp != null && !string.IsNullOrEmpty(temp.ToString()))
{
ErrorMsg = validateLengthColumn(temp.ToString().Trim(), colNum);
if (ErrorMsg != "")
{
row[11 - 1] = ErrorMsg.ToString();
}
if (colNum == 3 || colNum == 4)
{
try
{
if (temp.ToString().IndexOf("AM") > 0 || temp.ToString().IndexOf("PM") > 0)
{
row[colNum - 1] = (temp.ToString().Trim());
}
else
{
row[colNum - 1] = temp.ToString().Trim();
}
}
catch (Exception ex)
{
row[colNum - 1] = "";
}
}
else
{
row[colNum - 1] = temp.ToString().Trim();
}
RowIsEmpty = false;
}
else
{
if (colNum == 1 || colNum == 2)
{
RowIsEmpty = true;
}
}
}
if (RowIsEmpty == true)
{
break;
}
}
if (tbl.Rows.Count > 0)
{
dataSet = new DataSet();
dataSet.Tables.Add(tbl);
}
}
}
{
public string validateLengthColumn(string Value, int col)
{
string Msg = string.Empty;
int outvalue;
if (Value.Length > 100 && col == 1)
{
Msg = col + " Contain Invalid Length";
}
if (Value.Length > 100 && col == 2)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (Value.Length > 50 && col == 3)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (Value.Length > 50 && col == 4)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (Value.Length > 30 && col == 5)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (Value.Length > 100 && col == 6)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (col == 7)
{
if (Value.Length > 30)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (int.TryParse(Value, out outvalue))
{
}
else
{
Msg = "Column " + col + " Contain Invalid Datatype IT should number";
}
}
if (Value.Length > 50 && col == 9)
{
Msg = "Column " + col + " Contain Invalid Length OR Format";
}
if (Value.Length > 10 && col == 10)
{
Msg = col + " Invalid Length";
}
return Msg;
}
DataRow[] result = dataTable.Select("Error like '%Invalid Length%' ");
if (result.Length > 0)
{
ExportToExcel(dataTable, theFileName);
}
Explain you what i am trying to do
I am taking data from Excel and passing data to following function
ExcelDataSet = objUl.GetDataTableFromExcel(fileLocation);
in above function taking data and converting it to datatable , in this function i added extra column if any error occure
DataColumn ErrorCol = new DataColumn("Error", typeof(string));
tbl.Columns.Add(ErrorCol);
in same function i validating data using following function
ErrorMsg = validateLengthColumn(temp.ToString().Trim(), colNum);
if any error ooucred it added in Error column
after all done datable fill
last i am validation if datable "Error" column contain any value if "yes" then i pass datatable to export data
DataRow[] result = dataTable.Select("Error like '%Invalid Length%' ");
if (result.Length > 0)
{
ExportToExcel(dataTable, theFileName);
}
Problem Is using this code try to procced 40,000 record it's take lot a time
if there is any code that make above process fast
What I have tried:
i try many thing datable.compute() method and many article in google but not get successd