protected void outPut_Click(object sender, EventArgs e) { DB db = new DB(); SqlConnection conn = db.DBconn(); String str = "student_ID"; if (DropDownList1.SelectedItem.Text == "学生") { str = "student_ID"; } else if (DropDownList1.SelectedItem.Text == "教师") { str = "teacher_ID"; } else if (DropDownList1.SelectedItem.Text == "应用方向") { str = "direction"; } SqlDataAdapter da = new SqlDataAdapter("SELECT student_ID as '学生学号',name as '学生姓名', class as '学生班级', teacher_ID as '教工号',teacher_name as '教师姓名',paperid as '论文编号',title as '论文题目', direction as '题目性质', introduce as '简介', priority as '志愿次序',grade as '年级' FROM themeSelectedResult ORDER BY " + str + ", priority", conn); DataSet ds = new DataSet(); da.Fill(ds, "table1"); System.Data.DataTable dt = ds.Tables["table1"]; //获取数据集 DataView dv = ds.Tables["table1"].DefaultView; string str1 = this.TextBox1.Text; string str2 = this.TextBox2.Text; if (str1 == "") { Response.Write("<script>alert('导出的Excel表文件的名称不能为空。')</script>"); return; } if (str2 == "") { Response.Write("<script>alert('该表的标题不能为空。')</script>"); return; } SQLtoExcel se=new SQLtoExcel(); String filepath = Server.MapPath("相关文件/" + "" + str1 + ".xls"); se.OutputExcel(dv, str1, str2, filepath); //在服务器上产生excel的方法,dv为要输出到Excel的数据,str1为文件名称 ,str2为标题名称 ,path是路径 string path = Server.MapPath("相关文件/" + str1 + ".xls"); System.IO.FileInfo file = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); }
protected void Button1_Click(object sender, EventArgs e) { //SqlConnection conn = dacon.getcon(); //SqlDataAdapter da = new SqlDataAdapter("SELECT id as '序号',month as '统计月份', count1 as '单次统计数', count2 as '批量统计数',Cost as '应付金额',pay as '实际支付',remain as '未结算',OpTime as '更新日期' FROM userFees where ClientName='" + Session["username"] + "' ORDER BY id", conn); //DataSet ds = new DataSet(); //da.Fill(ds, "table1"); //if(ds.Tables[0].Rows.Count<1) //{ // Response.Write("<script>alert('您的费用记录为空,暂时不能下载。')</script>"); // return; //} //System.Data.DataTable dt = ds.Tables["table1"]; //获取数据集 //DataView dv = ds.Tables["table1"].DefaultView; if (this.rate.Rows.Count <= 0) { Response.Write("<script>alert('您当前表格的记录为空,暂时不能下载。请重新搜索')</script>"); return; } if (Session["username"] == null) { Response.Write("<script>alert('用户名失效,请重新登录')</script>"); return; } //Gridview转datable DataTable dt = new DataTable(); dt.Columns.Add("序号", typeof(string)); dt.Columns.Add("统计月份", typeof(string)); dt.Columns.Add("用户名", typeof(string)); dt.Columns.Add("单次统计数", typeof(int)); dt.Columns.Add("批量统计数", typeof(int)); dt.Columns.Add("应付金额", typeof(float)); dt.Columns.Add("实际支付", typeof(float)); dt.Columns.Add("未结算", typeof(float)); dt.Columns.Add("更新日期", typeof(string)); for (int i = 0; i < this.rate.Rows.Count; i++) { //dt.Rows.Add(rate.Rows[i]); DataRow newrow = dt.NewRow(); for (int j = 0; j < rate.Columns.Count; j++) newrow[j] = rate.Rows[i].Cells[j].Text.Trim(); dt.Rows.Add(newrow); } DataView dv = dt.DefaultView; string str1 = "用户" + Session["username"].ToString() + DateTime.Now.ToString("D") + "的账单"; string str2 = "用户" + Session["username"].ToString() + "的账单统计"; if (str1 == "") { Response.Write("<script>alert('导出的Excel表文件的名称不能为空。')</script>"); return; } if (str2 == "") { Response.Write("<script>alert('该表的标题不能为空。')</script>"); return; } try { SQLtoExcel se = new SQLtoExcel(); String filepath = Server.MapPath("用户报表/" + "" + str1 + ".xls"); se.OutputExcel(dv, str1, str2, filepath); //在服务器上产生excel的方法,dv为要输出到Excel的数据,str1为文件名称 ,str2为标题名称 ,path是路径 string path = Server.MapPath("用户报表/" + str1 + ".xls"); System.IO.FileInfo file = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); } catch (Exception e1) { Response.Write("<script>alert('生成excel过程出现错误,错误原因:" + e1.Message + "。')</script>"); return; } }
protected void outPut_Click(object sender, EventArgs e) { DB db = new DB(); SqlConnection conn = db.DBconn(); String str = "student_ID"; if (DropDownList1.SelectedItem.Text == "学生") { str = "student_ID"; } else if (DropDownList1.SelectedItem.Text == "教师") { str = "teacher_ID"; } else if (DropDownList1.SelectedItem.Text == "应用方向") { str = "direction"; } SqlDataAdapter da = new SqlDataAdapter("SELECT student_ID as '学生学号',name as '学生姓名', class as '学生班级', teacher_ID as '教工号',teacher_name as '教师姓名',paperid as '论文编号',title as '论文题目', direction as '题目性质', introduce as '简介', priority as '志愿次序',grade as '年级' FROM themeSelectedResult ORDER BY "+ str + ", priority", conn); DataSet ds = new DataSet(); da.Fill(ds, "table1"); System.Data.DataTable dt = ds.Tables["table1"]; //获取数据集 DataView dv = ds.Tables["table1"].DefaultView; string str1 = this.TextBox1.Text; string str2 = this.TextBox2.Text; if (str1 == "") { Response.Write("<script>alert('导出的Excel表文件的名称不能为空。')</script>"); return; } if (str2 == "") { Response.Write("<script>alert('该表的标题不能为空。')</script>"); return; } SQLtoExcel se = new SQLtoExcel(); String filepath = Server.MapPath("相关文件/" + "" + str1 + ".xls"); se.OutputExcel(dv, str1, str2, filepath); //在服务器上产生excel的方法,dv为要输出到Excel的数据,str1为文件名称 ,str2为标题名称 ,path是路径 string path = Server.MapPath("相关文件/" + str1 + ".xls"); System.IO.FileInfo file = new System.IO.FileInfo(path); Response.Clear(); Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息,为"文件下载/另存为"对话框指定默认文件名 Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name)); // 添加头信息,指定文件大小,让浏览器能够显示下载进度 Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流,必须被下载 Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 Response.WriteFile(file.FullName); // 停止页面的执行 Response.End(); }