Gridview控件中的EXCEL导出功能（From Gridview OR Dataset）

刚刚做了一个Excel导出功能,以前收集了N多方法。可是从来没有试过，大部分都是看看是那么回事就好了，

今天做的时候才发现每个方法似乎都或多或少的存在一些问题。

一下是我最终的解决方案，貌似还不是那么完美，但是已经满足基本需求了。

1.Export Excel  From  Gridview

 

public static void ExportToExcel(System.Web.UI.Control control, string ExcelFileName)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear(); 
            context.Response.Buffer = true; 
            context.Response.Charset = "GB2312";
            context.Response.ContentEncoding = System.Text.Encoding.UTF7;
            context.Response.AppendHeader("Content-Disposition","attachment;filename=" + HttpUtility.UrlEncode(ExcelFileName,System.Text.Encoding.UTF8) + ".xls"); 
            context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            context.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
            System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true);
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); 
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            control.RenderControl(oHtmlTextWriter); 
            string charset ="<meta http-equiv='Content-Type' content='text/html; charset=gb2312'>";
            context.Response.Write(charset+oStringWriter.ToString());
            context.Response.End();
        }

 

缺点：当Gridview使用分页功能时，该方法只能导出当前页的数据。

        为此我们在调用时先将Gridview的allowpaging属性设置为false

 

 protected void BtChange1_Clink(object sender, EventArgs e)
        {
            this.GVmaterial.AllowPaging = false;
            this.GVmaterial.AllowSorting = false;
            DataBind1();
            Common.ExportToExcel(this.GVmaterial, this.TabOptionItem1.Tab_Name.ToString());
            this.GVmaterial.AllowSorting = true;
            this.GVmaterial.AllowPaging = true;
         
        }

 

补充:allowpaging方法属性设为false之后databind（）方法之前若执行合并单元格方法

则再导出的数据在Excel中依然是可以合并单元格的

 

public static void GroupRows(GridView GridView1, int cellNum)
        {
            
            int i = 0, rowSpanNum = 1;
            while (i < GridView1.Rows.Count - 1)
            {
                GridViewRow gvr = GridView1.Rows[i];

                for (++i; i < GridView1.Rows.Count; i++)
                {
                    GridViewRow gvrNext = GridView1.Rows[i];
                    if (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text)
                    {
                        gvrNext.Cells[cellNum].Visible = false;//不然会把其他的挤走，造成行突出
                        rowSpanNum++;
                    }
                    else
                    {
                        gvr.Cells[cellNum].RowSpan = rowSpanNum;
                        rowSpanNum = 1;
                        break;
                    }

                    if (i == GridView1.Rows.Count - 1)
                    {
                        gvr.Cells[cellNum].RowSpan = rowSpanNum;
                    }
                }
            }
        }

 

 

 

2.Export Excel  From  Dataset

 

 public static void ExportDsToXls( string fileName, DataSet ds)
        {
            HttpContext context = HttpContext.Current;
            context.Response.Clear();
            context.Response.Buffer = true;
            context.Response.Charset = "GB2312";
            //context.Response.Charset = "UTF-8";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
            context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
            context.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
            //context.EnableViewState = false;
            context.Response.Write(ExportTable(ds));
            context.Response.End();
        }

 public static string ExportTable(DataSet ds)
        {
            string data = "";
            foreach (DataTable tb in ds.Tables)
            {
                //data += tb.TableName + "\n";
                data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
                //写出列名
                data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
                foreach (DataColumn column in tb.Columns)
                {
                    data += "<td>" + column.ColumnName + "</td>";
                }
                data += "</tr>";
                //写出数据
                foreach (DataRow row in tb.Rows)
                {
                    data += "<tr>";
                    foreach (DataColumn column in tb.Columns)
                    {
                        //if (column.ColumnName.Equals("证件编号") || column.ColumnName.Equals("报名编号"))
                        //    data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
                        //else
                            data += "<td>" + row[column].ToString() + "</td>";
                    }
                    data += "</tr>";
                }
                data += "</table>";
            }
            return data;
        }

缺点：不能实现单元格合并功能

 

 

 protected void BtChange1_Clink(object sender, EventArgs e)
        {
Common.ExportDsToXls(this.TabOptionItem1.Tab_Name.ToString(),ds1);
        }