How to run through excelfile and group rows

try this

 

 

string cell1, cell2, cell3, cell4, cell5;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"E:\Book1.xlsx");
Excel.Worksheet xlWorksheet = xlWorkbook.Sheets[1]; // assume it is the first sheet
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
//int colCount = 3;
DataTable dt = new DataTable();
dt.Columns.Add("Company", typeof(string));
dt.Columns.Add("Year", typeof(string));
dt.Columns.Add("Month", typeof(string));
dt.Columns.Add("Day", typeof(string));
dt.Columns.Add("Count", typeof(string));
for (int i = 2; i <= rowCount; i++)
{
cell1 = ""; cell2 = ""; cell3 = ""; cell4 = ""; cell5="";
if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 1].Value2 != null)
cell1 = xlRange.Cells[i, 1].Value2.ToString();
if (xlRange.Cells[i, 2] != null && xlRange.Cells[i, 2].Value2 != null)
cell2 = xlRange.Cells[i, 2].Value2.ToString();
if (xlRange.Cells[i, 3] != null && xlRange.Cells[i, 3].Value2 != null)
cell3 = xlRange.Cells[i, 3].Value2.ToString();
if (xlRange.Cells[i, 4] != null && xlRange.Cells[i, 4].Value2 != null)
cell4 = xlRange.Cells[i, 4].Value2.ToString();
if (xlRange.Cells[i, 5] != null && xlRange.Cells[i, 5].Value2 != null)
cell5 = xlRange.Cells[i, 5].Value2.ToString();
if (cell1 != "" )
dt.Rows.Add(cell1, cell2, cell3, cell4, cell5);
}
dt.AcceptChanges();
DataView dv = dt.DefaultView;
dv.Sort = "Company asc";
dt = dv.ToTable();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
ds.WriteXml(@"E:\Book1.xml");
xlWorksheet.Cells.Clear();

for (var i = 0; i < dt.Columns.Count; i++)
{
xlWorksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
}

// rows
for (var i = 0; i < dt.Rows.Count; i++)
{

for (var j = 0; j < dt.Columns.Count; j++)
{
xlWorksheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
}

}

xlWorksheet.SaveAs(@"E:\Book1.xlsx");
GC.Collect();
GC.WaitForPendingFinalizers();

//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorksheet);

//close and release
xlWorkbook.Close();
Marshal.ReleaseComObject(xlWorkbook);

//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}