Excel File Import to Gridview (HTML Format/normal)

Excel file directly read and fill the value into Gridview.
below code read the file from application physicalpath/upload control posted file.

DataSet ds = new DataSet();

void BindtheGrid()
{
string path = "C\\Projects\\LogicTest\\Report.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + path + "Extended Properties='Excel 8.0;IMEX=1;'";
//You must use the $ after the object you reference in the spreadsheet
//OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Report$] ", strConn);
OleDbDataAdapter myCommand = new OleDbDataAdapter();
DataTable dt;
try
{
myCommand.Fill(ds, "Report");
dt = ds.Tables["Report"];
}
catch (Exception ex)
{
//when the excel file could not read ie file is not in format
/* HttpPostedFile httpUploadedCSV = uploadgroup.PostedFile;
StreamReader sr = new StreamReader(httpUploadedCSV.InputStream); if the file is posed by file control*/

string filename = Server.MapPath("Report.xls"); // it is reading directly from the server location
StreamReader sr = default(StreamReader);
sr = File.OpenText(filename);


string s = sr.ReadToEnd();

DataSet ds = ConvertHTMLTablesToDataSet(s);
//dt = ds.Tables[0];
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
Some time imported file may be HTML format it would have been stored. needs to read and get the proper dataset/datatable:

protected DataSet ConvertHTMLTablesToDataSet(string HTML)
{
//' Declarations

try
{



MatchCollection Tables = Regex.Matches(HTML, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);

// ' Get a match for all the tables in the HTML

foreach (Match table in Tables)
{


// ' Reset the current row counter and the header flag
iCurrentRow = 0;
HeadersExist = false;

//' Add a new table to the DataSet
dt = new DataTable();

//' Create the relevant amount of columns for this table (use the headers if they exist, otherwise use default names)

if (table.Value.Contains(" {
// ' Set the HeadersExist flag
HeadersExist = true;

//' Get a match for all the rows in the table
MatchCollection Headers = Regex.Matches(table.Value, HeaderExpression, (RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase));

// ' Loop through each header element
foreach (Match Header in Headers)
{
dt.Columns.Add(Header.Groups[1].ToString());
}
}
else
{
for (int iColumns = 1; iColumns < Regex.Matches(Regex.Matches(Regex.Matches(table.Value, TableExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).ToString(), RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).ToString(), ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase).Count; iColumns++)
{
dt.Columns.Add("Column " + iColumns);
}
}

//' Get a match for all the rows in the table
MatchCollection Rows = Regex.Matches(table.Value, RowExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);

//' Loop through each row element
foreach (Match Row in Rows)
{


// ' Only loop through the row if it isn't a header row

if (!(iCurrentRow == 0 & HeadersExist == true))
{

// ' Create a new row and reset the current column counter
dr = dt.NewRow();
iCurrentColumn = 0;

// ' Get a match for all the columns in the row
MatchCollection Columns = Regex.Matches(Row.Value, ColumnExpression, RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.IgnoreCase);

// ' Loop through each column element
foreach (Match Column in Columns)
{
if (Column.Groups[1].ToString().Trim() == " ")
{
dr[iCurrentColumn] = "";
}
else
{
dr[iCurrentColumn] = Column.Groups[1].ToString().Trim();
}
// ' Increase the current column
iCurrentColumn += 1;
}

// ' Add the DataRow to the DataTable
dt.Rows.Add(dr);

}

// ' Increase the current row counter
iCurrentRow += 1;
}


//' Add the DataTable to the DataSet
ds.Tables.Add(dt);

}

}
catch (Exception e)
{
//lblerr.Text = "File is not in the Expected Format. Save the File as 'Excel 97-2003 Workbook' Format, then try Import.";
if (ds.Tables.Count == 0)
{
DataTable dt = new DataTable();
ds.Tables.Add(dt);
}
}
return ds;
}