DataSet导出到Excel

privatevoid load() {

        DataSet ds = getDataset();

        string FileName = "excel";

 

        //Web页面定义

        //System.Web.UI.Page mypage=new System.Web.UI.Page();

        HttpResponse resp;

        resp = HttpContext.Current.Response;

        resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

        resp.AppendHeader("Content-disposition", "attachment;filename=" + FileName + ".xls");

        resp.ContentType = "application/octet-stream";//默认

        //resp.ContentType = "application/x-xls";

 

 

        //变量定义

        string colHeaders = null;

        string Is_item = null;

 

        //显示格式定义////////////////

 

 

        //文件流操作定义

        //  FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write);

        //StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("GB2312"));

 

        StringWriter sfw = newStringWriter();

        //定义表对象与行对象，同时用DataSet对其值进行初始化

        System.Data.DataTable dt = ds.Tables[0];

        DataRow[] myRow = dt.Select();

        int i = 0;

        int cl = dt.Columns.Count;

 

        //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符

        for (i = 0; i < cl; i++)

        {

            //if(i==(cl-1))  //最后一列，加\n

            // colHeaders+=dt.Columns[i].Caption.ToString();

            //else

            colHeaders += dt.Columns[i].Caption.ToString() + "\t";

        }

        sfw.WriteLine(colHeaders);

        //sw.WriteLine(colHeaders);

 

        //逐行处理数据

 

 

 

        foreach (DataRow row in myRow)

        {

            //当前数据写入

            for (i = 0; i < cl; i++)

            {

                //if(i==(cl-1))

                //   Is_item+=row[i].ToString()+"\n";

                //else

                Is_item += row[i].ToString() + "\t";

            }

            sfw.WriteLine(Is_item);

            //sw.WriteLine(Is_item);

            Is_item = null;

        }

        resp.Write(sfw);

        //resp.Clear();

        resp.End();

    }

 

    privateDataSet getDataset() {

        string sql = "SELECT * FROM 成绩表";

 

        SqlConnection conn = newSqlConnection("server=.;database=DB;uid=sa;password=123;");

        conn.Open();

 

        SqlCommand cmd = newSqlCommand(sql, conn);

 

        DataSet ds = newDataSet();

 

        SqlDataAdapter ad = newSqlDataAdapter();

        ad.SelectCommand = cmd;

        ad.Fill(ds, "成绩表");

        return ds;

    }

 

}