Populating Excel into DataTable

U need to refer the Interop.Excel dll to achieve this..!

// Declarations
ArrayList variableList = new ArrayList();
ArrayList variableAverageList = new ArrayList();
DataTable dt = null;

private void PopulateDataSetFromExcel()
{
if (OpenDlg.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass excelObj = new Excel.ApplicationClass();

Excel.Workbook workBook = excelObj.Workbooks._Open(OpenDlg.FileName, Type.Missing, false, Type.Missing, "", "", true, Excel.XlPlatform.xlWindows, "\t", true, false, Type.Missing, true);
Excel.Sheets sheets = workBook.Worksheets;
// getting the respective WorkSheet
Excel.Worksheet workSheet = (Excel.Worksheet)sheets.get_Item(1);
string nwName = string.Empty;

int rowCount = 1;
bool isDataRow = false;
do
{
Excel.Range range = workSheet.get_Range("A" + rowCount.ToString(), "S" + rowCount.ToString());
System.Array excelRow = (System.Array)range.Cells.Value2;
if (IsStartingRow(excelRow) && !isDataRow)
{
// Adding Variables
foreach (object excelCell in excelRow)
{
if (null != excelCell && "Index" != excelCell.ToString() && "TOTAL INDEX" != excelCell.ToString())
{
variableList.Add(excelCell.ToString());
}
}
GenerateDataTable();
isDataRow = true;
}
else if (isDataRow)
{

// Adding datas
if (null != excelRow.GetValue(1, 1))
{
//string ss = excelRow.GetValue(1, 1).ToString();
InsertDataRowIntoTable(excelRow);
}
else
{
DataRow dataRow = dt.NewRow();
int count = 0;
// Adding Variables
foreach (object excelCell in excelRow)
{
if (null != excelCell)
{
variableAverageList.Add(excelCell.ToString());

dataRow[string.Format("var{0}Value",count)] = excelCell;

count++;
}
}
dt.Rows.Add(dataRow);
dt.AcceptChanges();
nwName = null;
}
}


rowCount++;
} while (null != nwName);

System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelObj);
workBook = null;
excelObj = null;
}

}


// To find whether its a starting row
private bool IsStartingRow(Array excelRow)
{
foreach (object excelCell in excelRow)
{
if (excelCell != null && (excelCell.ToString() == "Live Rtg" || excelCell.ToString() == "% Target Comp" ||
excelCell.ToString() == "Income 75k+" || excelCell.ToString() == "BG: Trendsetters"
|| excelCell.ToString() == "XXXX"))
{
return true;
}
}
return false;
}

// Generating DataTable
private void GenerateDataTable()
{
dt = new DataTable("DataTable1");
dt.Columns.Add("ProgramName", typeof(string));
dt.Columns.Add("Network", typeof(string));
for(int cnt=0; cnt < variableList.Count; cnt++)
{
dt.Columns.Add(string.Format("var{0}Value", cnt), typeof(string));
dt.Columns.Add(string.Format("var{0}Index", cnt), typeof(string));
}

}

// Inserting DataRow into the DataTable
private void InsertDataRowIntoTable(Array excelRow)
{
DataRow dataRow = dt.NewRow();
int columnCount = 0;
foreach (object excelCell in excelRow)
{
if (null != excelCell && columnCount < dt.Columns.Count)
{
dataRow[dt.Columns[columnCount].ColumnName] = excelCell;
columnCount++;
}

}
dt.Rows.Add(dataRow);
dt.AcceptChanges();
}

Confirm box while deleting a row from a GridView

paste the following code in ur delete button. It will work

OnClientClick="return confirm('Do you really want to delete this row?');"