我现在用到将gridview 数据导入到excel ,再从服务器下载,我第一次接触,不太会,希望各位教教,最好能给实例代码,给的好的给高分,谢谢了
解决方案 »
- asp .net(c#) 怎样用按钮控制datalist中itemTemplate项模板的显示
- VS2003,xml如何调用ajax实现下拉框三级联动?
- 正则表达式问题
- value.Tables为空或不是对象
- 求救~~如何动态添加"meta"元素
- 一个很迷惑的问题,我都不知道怎么形容错误了
- 我想自杀,如果再解决不了这个问题的话....
- 数据库用的是oracle,查询的时候日期比较该怎么写!
- 请教一个Session的一个问题,急!!~
- asp.net中怎么实现在listview的项中弹出层
- 做博客哪些地方适合使用AJAX问题?
- vs 2005自带的asp .net development server为什么不能处理microsoft.ui.web.webcontols命名空间下的控件(如treeview)
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();
}
以上可以导出分页的数据。
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();
网上很多资料的..
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();
}
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);
}
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();
}
}
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].re!= "")
dr[9] = list[i].re;
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);这是我的例子
你好好看一下
对你一定有用