用MVC导入导出
导入导出对于刚做的人一脸懵逼,但是明白思路之后就感觉非常容易,我也是研究了好久,才总算做了出来,放在这里给大家分享一下
一.先看下导出
视图脚本
<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>
视图html代码
@using (Html.BeginForm("DataIn", "Home", FormMethod.Post)) { <table> <tr> <td>@Html.Hidden("strGUID")</td> <td><input type="submit" value="导出" onclick="selectExcel()" /></td> </tr> </table> }
控制器代码
public ActionResult DataTableToExcel(string strGUID) { string[] GUID = Request.Form["strGUID"].Split(','); //这个是读取要导出的列表，逻辑要自己写的 DataTable dt = new DataTable(); dt.Columns.Add("序号"); 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(); User model = db.user.Find(id); dt.Rows.Add(model.Uid,model.Uname); } } //= 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"); } <!--如果报格式乱码错误 把设置编码和附件格式下的代码换成下面代码--> curContext.Response.Charset = "GB2312"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); curContext.Response.ContentEncoding = Encoding.Default;//设置输出流为简体中文 curContext.Response.ContentType = "application/vnd.ms-excel"; Response.Write("<meta http-equiv=\"content-type\" content=\"application/vnd.ms-excel; charset=utf-8\"/>");//加上这句话string类型就不乱码了
二,从Excel导入数据库
视图代码
@using (Html.BeginForm("Show", "Home", FormMethod.Post, new { enctype = "multipart/form-data" })) { <p> 选择文件：<input id="FileUpload" type="file" name="files" style="width: 250px; height: 24px; background: White" class="easyui-validatebox" /> </p> <p> <input id="btnImport" type="submit" value="导入" style="width: 60px; height: 28px;" /> </p> }
控制器代码
public ActionResult Show(HttpPostedFileBase filebase)
{
HttpPostedFileBase file=Request.Files["files"];
string FileName;
string savePath;
if (file == null||file.ContentLength<=0)
{
ViewBag.error = "文件不能为空";
return View();
}
else
{
string filename= Path.GetFileName(file.FileName);
int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
return View();
}
if (filesize >= Maxsize)
{
ViewBag.error = "上传文件超过4M，不能上传";
return View();
}
string path = AppDomain.CurrentDomain.BaseDirectory + "Excel/";
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
}
//string result = string.Empty;
string strConn;
strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + savePath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
DataSet myDataSet = new DataSet();
try
{
myCommand.Fill(myDataSet, "ExcelInfo");
}
catch (Exception ex)
{
ViewBag.error = ex.Message;
return View();
}
DataTable table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
//引用事务机制，出错时，事物回滚
using (TransactionScope transaction = new TransactionScope())
{
for (int i = 0; i < table.Rows.Count; i++)
{
////获取地区名称
//string _areaName = table.Rows[i][0].ToString();
////判断地区是否存在
//if (!_areaRepository.CheckAreaExist(_areaName))
//{
// ViewBag.error = "导入的文件中：" + _areaName + "地区不存在，请先添加该地区";
// return View();
//}
//else
//{
// Station station = new Station();
// station.AreaID = _areaRepository.GetIdByAreaName(_areaName).AreaID;
// station.StationName = table.Rows[i][1].ToString();
// station.TerminaAddress = table.Rows[i][2].ToString();
// station.CapacityGrade = table.Rows[i][3].ToString();
// station.OilEngineCapacity = decimal.Parse(table.Rows[i][4].ToString());
// _stationRepository.AddStation(station);
//}
User model = new User();
model.Uname = table.Rows[i][0].ToString();
db.user.Add(model);
db.SaveChanges();
}
transaction.Complete();
}
ViewBag.error = "导入成功";
System.Threading.Thread.Sleep(2000);
return Content("<script>alert('数据导入成功！');location.href='/Home/Index'</script>");
}
三:注意,
导出中的事务机制需要引用 using System.Transactions;
若是没有添加这个.dll组件可以去程序集中添加引用
