ASP.NET 数据导出至EXCEL

下面这些为程序的导出按钮点击事件后台处理方法

//导出按钮

        protectedvoid butPrint_Click(objectsender,EventArgs e)

        {

            DataTabledt = new DataTable("aaa");

            dt = Query();

            DataSetds = new DataSet();//数据集，当有多个数据表数据时，需循环输出

            ds.Tables.Add(dt);

            ExportDsToXls(this.Page, "序列号详细信息", ds, dt.Rows.Count); 

        }

 

 

        //查询数据

        privateDataTable Query()

        {

            DataTabledtrecord =GoodRecord.SelDepotRecordList(txtDepotID.Text,txtGoodID.Text,"toexcel");

 

            returndtrecord;

        }

 

        //根据数据拼接table

        public string GetExportString(DataSetds)

        {

            StringBuilderdata =new StringBuilder();//因为循环很多，用StringBuilder会快很多

 

            foreach (DataTable tbinds.Tables)//若多个数据表

                //data+= tb.TableName + "\n";

                data.Append("<table cellspacing=\"0\"cellpadding=\"5\" rules=\"all\"border=\"1\">");

                //写出列名

                data.Append("<tr style=\"font-weight: bold; white-space:nowrap;\">");

                foreach(DataColumn columnintb.Columns)

                {

                    data.Append("<td>");

                   data.Append(column.ColumnName);

                    data.Append("</td>");

                }

                data.Append("</tr>");

 

                //写出数据

                foreach(DataRow rowintb.Rows)

                {

                    data.Append("<tr>");

                    foreach(DataColumn columnintb.Columns)

                    {

                        data.Append("<td style=\"vnd.ms-excel.numberformat:@\">");

                       data.Append(row[column].ToString());

                        data.Append("</td>");

                    }

                    data.Append("</tr>");

                }

                data.Append("</table>");

            }

            returndata.ToString();

        }

        //导出为Excel

        public void ExportDsToXls(Pagepage, string fileName, DataSetds,int rowcount)

        {

 

            stringcoding = "UTF-8";

            if(rowcount < 3)

                coding = "GB2312";

 

 

            page.Response.Clear();

            page.Response.Buffer = true;

            page.Response.AddHeader("Content-Disposition","attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) +""+DateTime.Now.ToString("yyyyMMddHHmmss") +".xls");

            page.Response.Charset = coding;// "UTF-8";

            page.Response.ContentEncoding =System.Text.Encoding.GetEncoding(coding);//UTF-8

            page.Response.ContentType = "application/ms-excel";//设置输出为Excel文件            page.EnableViewState =false;

           page.Response.Write(GetExportString(ds));

            page.Response.End();

        }