Exporting the gridview data into excel and word document.
Many times i see the question in forum for exporting the data. So today in this article we will discuss how can we export the data of gridview control into Microsoft excel sheet and Microsoft word document.
For this we will take a dropdownlist, butoons and grdiview control.On the basis of selected value of dropdown grdiview will be populated with data. below is the design page code:
<center id="center2" runat="server">
<fieldset id="Fieldset1" style="text-align: center; width: 950px; height: auto; background-color: whiteSmoke;
border-color: Gray;" runat="server">
<table id="table1" runat="server" border="0" width="550px">
<tr>
<td colspan="5">
<asp:Label ID="lblStatus" Text="Patient Status" runat="server" CssClass="FontColor"></asp:Label>
</td>
<td style="padding-right: 75px;">
<asp:DropDownList ID="ddlStatus" runat="server">
<asp:ListItem Text="All" Value=""></asp:ListItem>
<asp:ListItem Text="Active" Value="1"></asp:ListItem>
<asp:ListItem Text="In-Active" Value="0"></asp:ListItem>
</asp:DropDownList>
</td>
<td>
<asp:Label ID="lblDoctor" Text="Doctor" runat="server" CssClass="FontColor"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlDoctor" runat="server">
</asp:DropDownList>
</td>
<td>
<asp:Button ID="btngenerate" runat="server" Text="Generate Report" OnClick="btngenerate_Click" />
</td>
</tr>
</table>
</fieldset>
<asp:GridView ID="grdOutPatient" runat="server" AutoGenerateColumns="false" AllowPaging="true"
GridLines="Both" DataKeyNames="Id" PageSize="5" OnPageIndexChanging="grdOutPatient_PageIndexChanging">
<RowStyle HorizontalAlign="Center" />
<HeaderStyle BackColor="#C1CDCD" />
<AlternatingRowStyle BackColor="Control" />
<Columns>
<asp:BoundField HeaderText="Name" DataField="Name" HeaderStyle-Width="80px" />
<asp:BoundField HeaderText="Date Of Birth" DataFormatString="{0:MM/dd/yyyy}" DataField="DOB"
HeaderStyle-Width="90px" />
<asp:BoundField HeaderText="Gender" DataField="Sex" HeaderStyle-Width="100px" />
<asp:BoundField HeaderText="Address" DataField="Address" HeaderStyle-Width="140px" />
<asp:BoundField HeaderText="Blood Group" DataField="BloodGroup" HeaderStyle-Width="50px" />
<asp:BoundField HeaderText="Doctor Concerned" DataField="Ref_DoctorName" HeaderStyle-Width="50px" />
<%-- <asp:TemplateField HeaderStyle-Width="50px" HeaderText="LoggedIn" data>
<ItemTemplate>
<asp:Label ID="lblLoggedIn" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>--%>
</Columns>
</asp:GridView>
<asp:Button ID="btnexporttoexcel" runat="server" Text="Export to Excel" Visible="false"
OnClick="btnexporttoexcel_Click" />
<asp:Button ID="btnexporttoword" runat="server" Text="Export to Word" Visible="false"
OnClick="btnexporttoword_Click" />
</center>
on the basis of the dropdown values we will get the value from table and bind it to the grid.
and then we will export the content of the gridview to excel and word.
Below is the .cs page code:
string query;
protected void Page_Load(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(Session["UserId"].ToString()))
{
// CheckRole();
if (!IsPostBack)
{
loaddoctordropdown();
}
}
}
private void loaddoctordropdown()
{
string connection = ConfigurationManager.ConnectionStrings["HospitalConnectionString"].ToString();
SqlConnection con = new SqlConnection(connection);
con.Open();
DataSet ds = new DataSet();
SqlDataAdapter adpt = new SqlDataAdapter("select Name from Doctor where IsActive = '1'", con);
adpt.Fill(ds, "temp1");
for (int i = 0; i < ds.Tables["temp1"].Rows.Count; i++)
{
ddlDoctor.Items.Add(Convert.ToString(ds.Tables["temp1"].Rows[i]["Name"].ToString()));
}
this.ddlDoctor.Items.Insert(0, new ListItem("All", string.Empty));
}
protected void grdOutPatient_RowDataBound(object sender, GridViewRowEventArgs e)
{
}
protected void btnexporttoexcel_Click(object sender, EventArgs e)
{
HtmlForm form = new HtmlForm();
string attachment = "attachment; filename=OutPatient.xls";
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";
StringWriter stw = new StringWriter();
HtmlTextWriter htextw = new HtmlTextWriter(stw);
grdOutPatient.AllowPaging = false;
grdOutPatient.GridLines = GridLines.Both;
grdOutPatient.Width = Unit.Percentage(98.0);
grdOutPatient.Style.Add("margin-left", "20px");
grdOutPatient.DataSource = ViewState["OutPatient"];
grdOutPatient.DataBind();
//grdusers.HeaderRow.Style.Add("background-color", "#C1CDCD");
for (int i = 0; i < grdOutPatient.HeaderRow.Cells.Count; i++)
{
grdOutPatient.HeaderRow.Cells[i].Style.Add("background-color", "#C1CDCD");
}
form.Controls.Add(grdOutPatient);
this.Controls.Add(form);
form.RenderControl(htextw);
Response.Write(stw.ToString());
Response.End();
}
protected void btnexporttoword_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition","attachment;filename=OutPatient.doc");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-word ";
StringWriter sw= new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
grdOutPatient.AllowPaging = false;
grdOutPatient.DataBind();
grdOutPatient.RenderControl(hw);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void grdOutPatient_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.grdOutPatient.PageIndex = e.NewPageIndex;
if (ViewState["Users"] != null)
{
grdOutPatient.DataSource = (DataSet)ViewState["OutPatient"];
grdOutPatient.DataBind();
}
}
protected void btngenerate_Click(object sender, EventArgs e)
{
string ddlvalue = ddlStatus.SelectedValue != string.Empty ? ddlStatus.SelectedValue : null;
string ddldoctor = ddlDoctor.SelectedValue.ToString() != string.Empty ? ddlDoctor.SelectedValue : null;
string connection = ConfigurationManager.ConnectionStrings["HospitalConnectionString"].ToString();
SqlConnection con = new SqlConnection(connection);
con.Open();
if (ddlvalue != null && ddldoctor != null)
{
query = "select * from Patient Where Ref_Doctor = '" + ddldoctor + "' and IsActive = '" + ddlvalue + "' and PatientType = 'OP'";
}
else if (ddlvalue == null && ddldoctor != null)
{
query = "select * from Patient Where Ref_Doctor = '" + ddldoctor + "'and PatientType = 'OP'";
}
else if (ddlvalue != null && ddldoctor == null)
query = "select * from Patient Where IsActive = '" + ddlvalue + "'and PatientType = 'OP'";
else
query = "select * from Patient Where PatientType = 'OP'";
SqlDataAdapter da = new SqlDataAdapter(query, con);
DataSet ds = new DataSet();
da.Fill(ds, "temp1");
if (ds.Tables[0].Rows.Count > 0)
{
grdOutPatient.DataSource = ds;
ViewState["OutPatient"] = ds;
grdOutPatient.Visible = true;
btnexport.Visible = true;
lblMessage.Visible = false;
grdOutPatient.DataBind();
}
else
{
string s = "<table cellspacing=0 cellpadding=2 borderColor=#999999 class='maintable contentcellleft' border=1 width=100%><tr><td align=center><font color='red'><b>No record found.</b></font></td></tr></table>";
grdOutPatient.Visible = false;
lblMessage.Visible = true;
btnexport.Visible = false;
lblMessage.Text = s;
lblMessage.Style.Add("fontcolor", "Red");
}
}
and below is the table structure:
CREATE TABLE [dbo].[Doctor](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Sex] [varchar](50) NULL,
[Specialist] [varchar](max) NULL,
[Consultation_Day] [varchar](50) NULL,
[Consultation_Time] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Contact_Number] [varchar](50) NULL,
[Emergency_Number] [varchar](50) NULL,
[Consultation_Fee] [int] NULL,
[Total_Patients] [int] NULL,
[Date] [datetime] NULL,
[IsActive] [bit] NULL,
CONSTRAINT [PK_Doctor] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO