GridView


1. GridView to Excel

ตัวอย่างนี้จะได้ Format ของ GridView มาด้วย ถ้ามีรูปอยู่ใน GridView ต้องเป็น Full URL Path ถึงจะ Export รูปออกมาได้ เช่น http://xxx.com/test/pic.png ถ้าเป็น /test/pic.png แบบนี้รูปจะไม่มาด้วย

    protected void btnExport_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time. */
    }

แบบนี้ format ทีเป็น style จะไม่มาด้วย

    protected void btnExport_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = _contentType;
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        System.Web.UI.HtmlControls.HtmlForm frm = new System.Web.UI.HtmlControls.HtmlForm();
        frm.Attributes["runat"] = "server";
        frm.Controls.Add(GridView1);
        GridView1.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }

2. Copy / Paste
วิธีนี้ใช้ได้โดยครอบส่วนที่ต้องการ Copy โดยใช้ Tag div แล้วให้ user กดที่ button Copy แล้วนำไป Paste ที่ Excel

<input type="button" value="Copy" onclick="window.clipboardData.setData('Text', document.all['MainDiv'].innerHTML)" />

3. Export to CSV
โดยดึงข้อมูลจาก DataTable

    protected void btnExport_Click(object sender, EventArgs e)
    {
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("th-TH");
        DataTable dt = Session["ActualReceive"] as DataTable;
        Response.ClearContent();
        Response.AddHeader("Content-Disposition", "attachment;filename=ActualReceiveReport.csv");
        Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in dt.Columns)
        {
            Response.Write(tab + "\"" + dc.ColumnName + "\"");
            tab = ",";
        }
        Response.Write("\r\n");

        foreach (DataRow dr in dt.Rows)
        {
            tab = "";
            int i;
            for (i = 0; i < dt.Columns.Count; i++)
            {
                Response.Write(tab + "\"" + dr[i].ToString().Replace("\"","\"\"") + "\"");
                tab = ",";
            }
            Response.Write("\r\n");
        }
        Response.End();
    }

http://aspalliance.com/
http://www.aspsnippets.com/ มี export -> word, pdf ด้วย
http://mattberseth.com/
http://forums.asp.net/

Advertisements

We often want to have a gridview which selects items based on a dropdownlist. It’s trivial to do this in the VS2005 designer by simply first creating an SqlDataSource with a where clause, then with the sqldatasource wizard, assign the SelectedValue of the dropdownlist to the GridView.

The application running looks like this:

And the page source to do is simply this:

<%@ Page Language=”C#” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>

<script runat=”server”>
</script>

<html xmlns=”http://www.w3.org/1999/xhtml”>
<head runat=”server”>
    <title>Simple GridView With DropDownList Selection</title>
</head>
<body>
    <form id=”form1? runat=”server”>
        <div>
            <asp:DropDownList ID=”DropDownList1? runat=”server” AutoPostBack=”True”>
                <asp:ListItem>Ikura</asp:ListItem>
                <asp:ListItem>Konbu</asp:ListItem>
            </asp:DropDownList><br />
            <asp:SqlDataSource ID=”SqlDataSource1? runat=”server” ConnectionString=”&lt;%$ ConnectionStrings:NorthWindConnectionString %&gt;“
                SelectCommand=”SELECT [ProductName], [UnitsInStock], [UnitsOnOrder] FROM [Alphabetical List of Products] WHERE ([ProductName] = @ProductName2)”>
                <SelectParameters>
                    <asp:ControlParameter ControlID=”DropDownList1? Name=”ProductName2? PropertyName=”SelectedValue”
                        Type=”String” />
                </SelectParameters>
            </asp:SqlDataSource>
            <asp:GridView ID=”GridView1? runat=”server” AutoGenerateColumns=”False” DataSourceID=”SqlDataSource1?>
                 <Columns>
                    <asp:BoundField DataField=”ProductName” HeaderText=”ProductName” SortExpression=”ProductName” />
                    <asp:BoundField DataField=”UnitsInStock” HeaderText=”UnitsInStock” SortExpression=”UnitsInStock” />
                    <asp:BoundField DataField=”UnitsOnOrder” HeaderText=”UnitsOnOrder” SortExpression=”UnitsOnOrder” />
                </Columns>
            </asp:GridView>
        </div>
    </form>
</body>
</html>

However, what if you want to have a show all option in your DropDownList? My suggestion is to abandon (somewhat) the visual programming model (or at least that’s the only way I can think of doing it) and add the SelectParameters in the Page_Load event of the page. In addition, to get all the rows to show, I suggest adding the Sql Injection trick which is add an OR to the sql. You can that make the OR always return true, or always return false. I do this by simply passing a 1=@CompareInteger to the Sql that always gets evaluated. Then dynamically set the CompareInteger to 1 or 999. 1 makes all the rows return, 999 forces the evaluation of the ProductName=@ProductName part of the Where clause.

Here is what the application looks like running:

And here is the code that actually does the job.

<%@ Page Language="C#" %>

 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

    protected void Page_Load(object sender, EventArgs e)
    {

        if (!IsPostBack)
        {
            ControlParameter cp =
                new ControlParameter("ProductName",TypeCode.String,"DropDownList1","SelectedValue");
            SqlDataSource1.SelectParameters.Add(cp);

            Parameter p = new Parameter("CompareInteger", TypeCode.Int32);
            p.DefaultValue = "1";
            SqlDataSource1.SelectParameters.Add(p);
        }
    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (DropDownList1.SelectedValue.Equals("(All)"))
        {
            SqlDataSource1.SelectParameters[1].DefaultValue = "1";
        }
        else
        {
            SqlDataSource1.SelectParameters[1].DefaultValue = "999";  // not one
        }
    }

</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>DropDownList and GridView with Show All Option</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged">
            <asp:ListItem Value="(All)"></asp:ListItem>
            <asp:ListItem>Ikura</asp:ListItem>
            <asp: pan style="color: maroon;">ListItem>Konbu</asp:ListItem>
        </asp:DropDownList><br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
                <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock" SortExpression="UnitsInStock" />
            </Columns>
        </asp:GridView>
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthWindConnectionString %>" SelectCommand="SELECT [ProductName], [UnitsInStock] FROM [Alphabetical List of Products] WHERE ([ProductName] = @ProductName OR 1 = @CompareInteger) ORDER BY [ProductName]">
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Notice that autopostback is set on the dropdownlist as well as the select change event. That’s it! Give it a try.

http://peterkellner.net/2006/10/14/showallingridviewfromddl/