您的当前位置:首页正文

在Web窗体将GridView数据导出到Excel表

2020-01-28 来源:步旅网
 protected void ImageButton2_Click(object sender,

ImageClickEventArgs e) {

History hotline = new History();

hotline.StartTime = txtJGStartTime.Text.Trim(); hotline.EndTime = txtJGEndTime.Text.Trim(); hotline.FSeatNo = DdlJGzx.SelectedItem.Value; hotline.Fmemo = DDLJieGuo.SelectedItem.Value; DataSet

ds

=

hotline.EndTime,

hotline.QueryJGBaoBiao(hotline.StartTime,

hotline.FSeatNo, hotline.Fmemo); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中

// History hotline = new History(); //创建Scores对象

// DataSet ds = hotline.QueryScore(); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中 DataTable DT = ds.Tables[0];

//生成将要存放结果的Excel文件的名称 string

NewFileName

=

DateTime.Now.ToString(\"yyyyMMddHHmmss\") + \".xls\";

//转换为物理路径 NewFileName NewFileName);

//根据模板正式生成该Excel文件

File.Copy(Server.MapPath(\"~//Module01.xls\"), NewFileName, true);

//建立指向该Excel文件的数据库连接 string

strConn

Source=\"

= +

=

Server.MapPath(\"Temp/\"

+

\"Provider=Microsoft.Jet.OLEDB.4.0;Data

NewFileName + \";Extended Properties='Excel 8.0;'\"; OleDbConnection OleDbConnection(strConn);

//打开连接,为操作该文件做准备 Conn.Open();

OleDbCommand Cmd = new OleDbCommand(\"\", Conn);

foreach (DataRow DR in DT.Rows) {

string XSqlString = \"insert into [Sheet1$]\"; XSqlString += \"([编号],[日期],[号码],[坐席号],[营销结果],[录音文件]) values(\";

Conn

=

new

XSqlString += \"'\" + DR[\"fId\"] + \"',\"; XSqlString += \"'\" + DR[\"fDate\"] + \"',\"; XSqlString += \"'\" + DR[\"fCalled\"] + \"',\"; XSqlString += \"'\" + DR[\"fSeatNo\"] + \"',\"; XSqlString += \"'\" + DR[\"fmemo\"] + \"',\"; XSqlString += \"'\" + DR[\"fRecordFile\"] + \"')\"; Cmd.CommandText = XSqlString; Cmd.ExecuteNonQuery(); }

//操作结束,关闭连接 Conn.Close();

//打开要下载的文件,并把该文件存放在FileStream中

System.IO.FileStream

System.IO.File.OpenRead(NewFileName);

//文件传送的剩余字节数:初始值为文件的总大小 long Length = Reader.Length;

Response.Buffer = false;

Response.AddHeader(\"Connection\", \"Keep-Alive\"); Response.ContentType = \"application/octet-stream\";

Reader

=

Response.AddHeader(\"Content-Disposition\", \"attachment; filename=\" + Server.UrlEncode(\"电话Q吧报表.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);

//往缓冲区读入数据

0,

//

//存放欲发送

Response.OutputStream.Write(Buffer, ByteToRead); //把缓冲区的数据写入客户端浏览器 Response.Flush();

//立即写入客户端

Length -= ByteToRead;

//剩余字节数减少

} else {

//客户端浏览器已经断开,阻止继续循环 Length = -1; } }

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

protected void btnJieGuo_Click(object sender, EventArgs e) { try{

History hotline = new History();

hotline.StartTime = txtJGStartTime.Text.Trim();

hotline.EndTime = txtJGEndTime.Text.Trim(); hotline.FSeatNo = DdlJGzx.SelectedItem.Value; hotline.Fmemo = DDLJieGuo.SelectedItem.Value; DataSet

ds

=

hotline.EndTime,

hotline.QueryJGBaoBiao(hotline.StartTime,

hotline.FSeatNo, hotline.Fmemo); //调用QueryScore方法查询成绩并将查询结果放到DataSet数据集中

GVJieGuo.DataSource = ds; //为GridView控件指名数据源 GVJieGuo.DataBind(); }

catch (Exception) {

Response.Write(\"\");

} }

因篇幅问题不能全部显示,请点此查看更多更全内容