//Read excel data by looping each record
public static List <WorkItemWithIteration> ReadDataFromExcel(string excelFilePath)
{
List <WorkItemWithIteration> workItemIteration = new List <WorkItemWithIteration>();
try
{
Microsoft.Office.Interop.Excel.Application appExl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = appExl.Workbooks.Open(excelFilePath);
Microsoft.Office.Interop.Excel._Worksheet NwSheet = workbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range ShtRange = NwSheet.UsedRange;
int rowCount = ShtRange.Rows.Count;
int colCount = ShtRange.Columns.Count;
int rowindex = 0;
DataTable dt = new DataTable();
for (int i = 5; i <= rowCount; i++)
{
if (ShtRange.Cells[i, 1] != null && ShtRange.Cells[i, 1].Value2 != null)
{
if (ShtRange.Cells[i, 1].Value2.ToString() == "Row Labels")
{
rowindex = i;
}
}
if (i > rowindex)
{
for (int j = 2; j <= colCount - 1; j++)
{
if (ShtRange.Cells[i, j] != null && ShtRange.Cells[i, j].Value2 != null)
{
WorkItemWithIteration wrkitem = new WorkItemWithIteration();
if (ShtRange.Cells[i, 1] != null && ShtRange.Cells[i, 1].Value2 != null)
{
wrkitem.UserName = ShtRange.Cells[i, 1].Value2.ToString();
}
if (ShtRange.Cells[rowindex, 1] != null && ShtRange.Cells[rowindex, 1].Value2 != null)
{
wrkitem.IterationPath = ShtRange.Cells[rowindex, j].Value2.ToString();
}
if (ShtRange.Cells[i, j] != null && ShtRange.Cells[i, j].Value2 != null)
{
wrkitem.Value = Convert.ToDouble(ShtRange.Cells[i, j].Value2.ToString() == "" ? 0 : ShtRange.Cells[i, j].Value2.ToString());
workItemIteration.Add(wrkitem);
}
}
}
}
}
}
catch (Exception ex)
{
WriteFileToDisk("ReadDataFromExcel", ex.Message + Environment.NewLine + ex.StackTrace);
}
return(workItemIteration);
}
//Read data by looping DataTable record
public static List <WorkItemWithIteration> ReadDataFromDataTable(DataTable dt)
{
List <WorkItemWithIteration> workItemIteration = new List <WorkItemWithIteration>();
try
{
int rowindex = 0;
for (int i = 5; i < dt.Rows.Count; i++)
{
if (dt.Rows[i][1].ToString() == "Row Labels")
{
rowindex = i;
}
if (i > rowindex)
{
for (int j = 0; j < dt.Columns.Count - 1; j++)
{
WorkItemWithIteration wrkitem = new WorkItemWithIteration();
if (dt.Rows[i][0] != null)
{
wrkitem.UserName = dt.Rows[i][0].ToString();
}
if (dt.Rows[rowindex][0] != null)
{
wrkitem.IterationPath = dt.Rows[rowindex][j].ToString();
}
if (dt.Rows[i][j] != null)
{
wrkitem.Value = Convert.ToDouble(dt.Rows[i][j].ToString() == "" ? 0 : dt.Rows[i][j]);
workItemIteration.Add(wrkitem);
}
}
}
}
}
catch (Exception ex)
{
WriteFileToDisk("ReadDataFromDataTable", ex.Message + Environment.NewLine + ex.StackTrace);
}
return(workItemIteration);
}