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;
}

GridView Export into excel & Generate XML Dataset Values

DAL - Data Access Layer

static SqlConnection GetConnectionObject()
{
SqlConnection con = new SqlConnection();
string connectionstring;
try
{
connectionstring = ConfigurationManager.ConnectionStrings["logictest"].ConnectionString; ;
con.ConnectionString = connectionstring;

return con;
}
catch (Exception ex)
{
throw ex;
}

}

public static DataSet ExecuteSQLReturnDataSet(string strSQL)
{
DataSet ds = new DataSet("DataSet");
SqlConnection con = GetConnectionObject();
SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
try
{
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
da.Dispose();
ds.Dispose();
}
}

GridView Export into excel:
Method to GeneratetheReport:

public void ReportGeneration(DataSet dset, string filename)
{

GridView gv = new GridView();
gv.DataSource = dset;
gv.DataBind(); //Read the grid value however the report format may be excel/pdf
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.OutputStream.Write(new byte[] { 0xef, 0xbb, 0xbf }, 0, 3);
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + filename + "");
HttpContext.Current.Response.ContentType = "application/ms-excel";
StringWriter oStringWriter = new StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
gv.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Write(oStringWriter.ToString());
HttpContext.Current.Response.End();
HttpContext.Current.Response.Flush();
HttpContext.Current.ApplicationInstance.CompleteRequest();
}

On btnclic/load event call the below method

void BindGridView()
{
DataSet ds = new DataSet();
ds = ExecuteSQLReturnDataSet("select * from tablename");
grd.DataSource = ds;
grd.DataBind();
if (ds.Tables[0].Rows.Count > 0)
{
ReportGeneration(ds, "Report.xls");
}
}
Generate XML Dataset Values:
public void XMLGenarate()
{
string XMLFileName = "C:/Projects/XMLGenerate" + DateTime.Now.ToString("dd-mm-yyyy") +""; // can set it in web.config
DataSet ds = new DataSet();
ds = ExecuteSQLReturnDataSet("select * from tableName"); // can pass it as a parameter of(string) / from Data Access Layer which has coding standard.
FileStream fs = null;
if (ds.Tables[0].Rows.Count > 0)
{
fs = new FileStream(XMLFileName + ".xml", FileMode.OpenOrCreate, FileAccess.Write);
ds.WriteXml(fs);
fs.Close();
}

}

on btnclick/load event call the above method.

Pagination Custamization

Pagination Custamization:

1).aspx/.ascx Design View







2).aspx.cs/.ascx.cs


private int intCurrentselectPage;

#region CurrentselectPage
public int CurrentSelectPage
{
get
{
return intCurrentselectPage;
}
set
{
intCurrentselectPage = value;
}
}
#endregion


protected void grdNewsList_DataBound(object sender, EventArgs e)
{
if (grdNewsList.Rows.Count > 0)
{
GridViewRow gNavFooter = grdNewsList.BottomPagerRow;
LinkButton lbP = gNavFooter.FindControl("lbPrev") as LinkButton;
LinkButton lbN = gNavFooter.FindControl("lbNext") as LinkButton;
try
{
if (grdNewsList.PageIndex == 0)
{
lbP.Enabled = false;
}

int iStartIndex = 0;
int iCurrentPage = CurrentSelectPage + 1;

int iCalc = (iCurrentPage / 5) + ((iCurrentPage % 5) >= 1 ? 1 : (iCurrentPage % 5));

if (iCalc > 1)
{
iStartIndex = (((iCalc * 5) - 5) + 1);
}
else
{
iStartIndex = 1;
}

for (int i = 1; i <= 5; i++)
{
LinkButton lb = gNavFooter.FindControl("lb" + i.ToString()) as LinkButton;

lb.Text = iStartIndex.ToString();
lb.CommandArgument = iStartIndex.ToString();
if (iStartIndex > grdNewsList.PageCount)
{
lb.Visible = false;
}
if (iStartIndex == grdNewsList.PageIndex + 1)
{
lb.Enabled = false;
}
iStartIndex++;
}

if (grdNewsList.PageIndex == grdNewsList.PageCount - 1)
{
lbN.Enabled = false;
}
}
catch (Exception ex)
{
throw (ex);
}
}
}


protected void grdNewsList_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
CurrentSelectPage = e.NewPageIndex;
grdNewsList.PageIndex = e.NewPageIndex;
DisplayFromListPublishedNews(); // Bind the Method again.
}