gridview 数据导入到excel

[tshf198556]

我现在用到将gridview 数据导入到excel ，再从服务器下载，我第一次接触，不太会，希望各位教教，最好能给实例代码，给的好的给高分，谢谢了

[samhua2008]

在页面上放一个按钮名字改为Btn_ExportToExcel
protected void Btn_ExportToExcel_Click(object sender, EventArgs e)
{
ExportToExcel();//调用ExportToExcel（）函数
}
自定义函数 ExportToExcel
public void ExportToExcel()
{
string style = @"<style> .text { mso-number-format:\@; } </script> ";
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
Response.Charset = "gb2312";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

GridView1.AllowPaging = false;
BindData();
GridView1.RenderControl(htmlWrite);
Response.Write(style);
Response.Write(stringWrite.ToString());
Response.End();
GridView1.AllowPaging = true;
BindData();
}
以上可以导出分页的数据。

[lgaimin]

我有代码。可惜我现在在家里。

[samhua2008]

绝对可以使用，我是刚测试过哦，给分。有什么问题在联系。

[tshf198556]

你这个不还不太符合我的要求 我要取出数据的表的字段很多，而我Gridview中只显示了部分重要的 ，现在我是要导出数据库表中的所有数据哦，不好意思没说清楚 还有就是gridview中后面两项是“详细情况”和“删除” 这两列我不想让它出现在导出的表中

[tshf198556]

那回家了记得给我吧 谢谢 嘿嘿

[malin2670372]

姑娘，那就是根据 查询到得Table 或你所说的数据源去导出了.
网上很多资料的..

[angellan]

此方法接收一个DataTable,你把你要导出的数据，查询出来放在DataTable里，调用这个方法就行了
private void SaveToExcel(DataTable objTable, string name)
{
int CountR = objTable.Rows.Count;//行数
int CountC = objTable.Columns.Count;//列数
Response.Clear();
Response.Buffer = true;

//设置Http的头信息,编码格式
Response.AppendHeader("Content-Disposition", "attachment;filename=" + name);
Response.ContentType = "application/ms-excel";


//设置编码
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//写表头
for (int i = 0; i < CountC; i++)
{
Response.Write(objTable.Columns[i].ColumnName + "\t");
}
Response.Write("\n");
//写表内容
for (int RowNo = 0; RowNo <= CountR - 1; RowNo++)
{
string RowContent = "";
for (int CloumnNo = 0; CloumnNo <= CountC - 1; CloumnNo++)
{
RowContent += Convert.ToString(objTable.Rows[RowNo][CloumnNo]) + "\t";
}
RowContent += "\n";
Response.Write(RowContent);
}
Response.Flush();
}

[lengjianyang]

如果你想导出符合条件的数据，不一定要从gridview 导出，你可以直接调用数据集，取出你需要的字段就可以。

[lengjianyang]

这个是给文件起名字，并填充数据，是从ExGridView1读取数据
Random r = new Random();
int ii = r.Next(100, 999);
string fileName = "地域对比统计" + System.DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string path = Server.MapPath(".");
string savepath = Path.Combine(path, fileName);
string sql = "create table sheet1(地市 varchar(20),注销用户数 varchar(4),新增用户数 varchar(10),在网用户数 varchar(12),活跃用户数 varchar(12))";



OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savepath + ";Extended Properties=Excel 8.0");

OleDbCommand cmd = cn.CreateCommand();
cn.Open();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
for (int i = 0; i < ExGridView1.Rows.Count; i++)
{

string sqlstr = "insert into sheet1 values('" + TrimStr(this.ExGridView1.Rows[i].Cells[0].Text) + "','" + TrimStr(this.ExGridView1.Rows[i].Cells[1].Text) + "','" + TrimStr(this.ExGridView1.Rows[i].Cells[2].Text) + "','" + TrimStr(this.ExGridView1.Rows[i].Cells[3].Text) + "','" + TrimStr(this.ExGridView1.Rows[i].Cells[4].Text) + "')";

cmd.CommandText = sqlstr;
cmd.ExecuteNonQuery();
}
cn.Close();

if (ResponseFile(Request, Response, fileName, savepath, 10240))
{
if (File.Exists(savepath))
File.Delete(savepath);
}

[lengjianyang]

这个是使用数据集来将数据打印到excel文档中的
try
{
// Conn.Open();

SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = comm;
DataTable dt = new DataTable();
sda.Fill(dt);
OleDbConnection cn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savepath + ";Extended Properties=Excel 8.0");

OleDbCommand cmd = cn.CreateCommand();
cn.Open();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
for (int i = 0; i < dt.Rows.Count; i++)
{

string sqlstr = "insert into sheet1 values('" + TrimStr(dt.Rows[i][5].ToString()) + "','" + TrimStr(dt.Rows[i][3].ToString()) + "','" + TrimStr(dt.Rows[i][2].ToString()) + "','" + GetShopName(dt.Rows[i][7].ToString()) + "')";

cmd.CommandText = sqlstr;
cmd.ExecuteNonQuery();
}
cn.Close();
}
catch (Exception ex)
{

JScript.Alert(ex.Message);
}
finally
{
Conn.Close();
}
}

[xukaifu]

   private DataTable ReadGridView()
    {
       
        DataTable dt = new DataTable();
        DataRow dr;
        dt.Columns.Add(new DataColumn("序号", typeof(string)));
        dt.Columns.Add(new DataColumn("姓名", typeof(string)));
        dt.Columns.Add(new DataColumn("身 份 证", typeof(string)));
        dt.Columns.Add(new DataColumn("参加工作时间(年、月)", typeof(string)));
        dt.Columns.Add(new DataColumn("离退休时间(年、月)", typeof(string)));
        dt.Columns.Add(new DataColumn("实有工龄", typeof(int)));
        dt.Columns.Add(new DataColumn("现任职称", typeof(string)));
        dt.Columns.Add(new DataColumn("存量补贴标准(元/年)", typeof(decimal)));
        dt.Columns.Add(new DataColumn("存量补贴额(元)", typeof(decimal)));        
        dt.Columns.Add(new DataColumn("备注", typeof(string)));
        CheckBox chk = new CheckBox();
        List<string> obj = new List<string>();
        for (int i = 0; i < this.GridView1.Rows.Count; i++)
        {
             chk = this.GridView1.Rows[i].FindControl("chkSel") as CheckBox;
             if (chk.Checked)
             {
                 string strWh = "";
                 strWh = "id=" + chk.Text;
                 obj.Add(strWh);
             }
        }
        StringBuilder str =new StringBuilder();
        for (int i = 0; i < obj.Count; i++)
        {
            if (i == obj.Count - 1)
            {
                str.Append(obj[i]);
            }
            else
            {
                str.Append(obj[i]+" or ");
            }
           
        }
        whe = str.ToString();
        List<house.Model.employee> list = getEmpList(GetCount("employee"), 1, whe);
        for (int i = 0; i < list.Count; i++)
        {
            dr = dt.NewRow();
            dr[0] = list[i].eid;
            dr[1] = list[i].name;
            dr[2] =list[i].cardId.ToString();
            dr[3] = list[i].joinJoyTime;
            dr[4] = list[i].toRetireTime;
            dr[5] = list[i].WorkAge.ToString();
            if (list[i].incumbentHeadship != "")
                dr[6] = list[i].incumbentHeadship;
            else
            {
                dr[6] = "无";
            }
            dr[7] = list[i].subsidyCriterion.ToString();
            dr[8] = list[i].subsidyMoney.ToString();
            if (list[i].remark!= "")
                dr[9] = list[i].remark;
            else {
                dr[9] = "无";
            }
            dt.Rows.Add(dr);
        }
        return dt;
    }

动态得到一个datetable

 DataTable dt = ReadGridView();
        GridView ds = new GridView();
        ds.DataSource = dt;
        ds.DataBind();
        //生成将要存放结果的Excel文件的名称
        string NewFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        //转换为物理路径
        NewFileName = Server.MapPath("excel/" + NewFileName);
        //根据模板正式生成该Excel文件
        File.Copy(Server.MapPath("excel/mouldtest.xls"), NewFileName, true);
        //建立指向该Excel文件的数据库连接
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NewFileName + ";Extended Properties='Excel 8.0;'";
        OleDbConnection Conn = new OleDbConnection(strConn);
        //打开连接，为操作该文件做准备
        Conn.Open();
        OleDbCommand Cmd = new OleDbCommand("", Conn);

        foreach (GridViewRow dr in ds.Rows)
        {
            string XSqlString = "insert into [Sheet1$]";
            XSqlString += "([序号],[姓名],[身 份 证],[参加工作时间(年、月)],[离退休时间(年、月)],[实有工龄],[现任职称],[存量补贴标准(元/年)],[存量补贴额(元)],[备注]) values(";
            XSqlString += "'" + dr.Cells[0].Text + "',";
            XSqlString += "'" + dr.Cells[1].Text + "',";
            XSqlString += "'" + dr.Cells[2].Text + "',";
            XSqlString += "'" + dr.Cells[3].Text + "',";
            XSqlString += "'" + dr.Cells[4].Text + "',";
            XSqlString += "'" + dr.Cells[5].Text + "',";
            XSqlString += "'" + dr.Cells[6].Text + "',";
            XSqlString += "'" + dr.Cells[7].Text + "',";
            XSqlString += "'" + dr.Cells[8].Text + "',";
            XSqlString += "'" + dr.Cells[9].Text + "')";
            Cmd.CommandText = XSqlString;
            Cmd.ExecuteNonQuery();
        }

        //操作结束，关闭连接
        Conn.Close();
        //打开要下载的文件，并把该文件存放在FileStream中
        System.IO.FileStream Reader = System.IO.File.OpenRead(NewFileName);
        //文件传送的剩余字节数：初始值为文件的总大小
        long Length = Reader.Length;

        Response.Buffer = false;
        Response.AddHeader("Connection", "Keep-Alive");
        Response.ContentType = "application/octet-stream";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("存量.xls"));
        Response.AddHeader("Content-Length", Length.ToString());

        byte[] Buffer = new Byte[10000];		//存放欲发送数据的缓冲区
        int ByteToRead;											//每次实际读取的字节数

        while (Length > 0)
        {
            //剩余字节数不为零，继续传送
            if (Response.IsClientConnected)
            {
                //客户端浏览器还打开着，继续传送
                ByteToRead = Reader.Read(Buffer, 0, 10000);					//往缓冲区读入数据
                Response.OutputStream.Write(Buffer, 0, ByteToRead);	//把缓冲区的数据写入客户端浏览器
                Response.Flush();																		//立即写入客户端
                Length -= ByteToRead;																//剩余字节数减少
            }
            else
            {
                //客户端浏览器已经断开，阻止继续循环
                Length = -1;
            }
        }

        //关闭该文件
        Reader.Close();
        //删除该Excel文件
        File.Delete(NewFileName);

这是我的例子
你好好看一下
对你一定有用

[mengxj85]

Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", "attachment;filename=zhuangdingshengchanjihuabiao.xls");
                Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
                Response.Write("<table border='1'>");
                Response.Write("<tr>");
//使用Gridview的数据源datatable
//遍历datatable,重组新table
Response.Write("</tr>");
                Response.Write("</table>");
                Response.End();