将数据导出到excel表中
代码
控制器里面
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MvcApplication1.Models;
namespace MvcApplication1.Controllers
{
public class HomeController : Controller
{
//
// GET: /Home/
UEntity db = new UEntity();
public ActionResult Index()
{
List<User1> list = db.user1.ToList();
return View(list);
}
public ActionResult DataTableToExcel(string strGUID)
{
string[] GUID = Request.Form["strGUID"].Split(',');
//这个是读取要导出的列表,逻辑要自己写的
DataTable dt = new DataTable();
dt.Columns.Add("序号");
dt.Columns.Add("姓名");//这些应该就是在excel里显示的第一行标题
dt.Columns.Add("密码");
foreach (var aa in GUID)
{
if (aa != null && aa != "")
{
int id = Convert.ToInt32(aa);
//List<User> list = db.user.Where(p => p.Uid == id).ToList();
User1 model = db.user1.Find(id);//这个应该就是去业务层、持久层查询的方法
dt.Rows.Add(model.Uid, model.Uname,model.Upwd);//这些应该就是对应着标题展示的数据
}
}
//= SQLServerDAL.DSalesOrders.SalesOrders_GetListExcel(strGUID).Tables[0];下面这些基本不用看,把上面这些调试好,具体怎么调,我不知道你们这该怎么写了,哈哈哈哈哈哈,关了啊。
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
string filename = DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + "_"
+ DateTime.Now.Hour + "_" + DateTime.Now.Minute;
byte[] str = null;
if (dt != null)
{
// 设置编码和附件格式
curContext.Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
curContext.Response.ContentType = "application/vnd.ms-excel";
//System.Text.Encoding.UTF8;
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dt.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
// 返回客户端
str = System.Text.Encoding.UTF8.GetBytes(strWriter.ToString());
}
return File(str, "attachment;filename=" + filename + ".xls");//这个应该是生成的excel的文件名称
}
}
}
Index页面
@model List<MvcApplication1.Models.User1>
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<script type="text/javascript" language="javascript">
function selectExcel() {
var GUID = document.getElementsByName("check");
var temp = "";
var strGUID = "";
for (var i = 0; i < GUID.length; i++) {
if (GUID[i].checked) {
temp += GUID[i].value + ",";
}
}
strGUID = temp
//alert(strGUID);//测试取到的值是否正确
alert(strGUID);
//$("#GUID").val(strGUID);//将多选的值赋给Id为strGUID的隐藏域
document.getElementById("strGUID").value = strGUID;
}
</script>
</head>
<body>
<div>
@using (Html.BeginForm("DataTableToExcel", "Home", FormMethod.Post))
{
<table>
<tr>
<td>@Html.Hidden("strGUID")</td>
<td><input type="submit" value="导出" onclick="selectExcel()" /></td>
</tr>
</table>
}
@foreach (var item in Model)
{
<table>
<tr>
<td><input value="@item.Uid" name="check" type="checkbox" /></td>
<td>@item.Uid</td>
<td>@item.Uname</td>
<td>@item.Upwd</td>
</tr>
</table>
}
</div>
</body>
</html>
效果图