Excel导入导出（Demo）
=====================================================
Excel导入思路：
将一个Excel文件的数据转化成一个DateTabel 然后批量添加到数据库
将数据导入Excel思路：
将数据库信息查询到转化成Datatable然后批量添加到Excel中
//喜欢的可以关注一下我的博客http://www.cnblogs.com/lizong000/
================================================================================================
在DAL里面的数据处理
=======================================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace ExcelDal
{
public class ExcelDals
{
/// <summary>
/// 将Excel数据添加到dataTable中
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable ExcelTable(string path)
{
string strconn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strconn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sql = "select * from [Sheet1$]";
OleDbCommand com = new OleDbCommand(sql, conn);
OleDbDataAdapter ada = new OleDbDataAdapter(com);
DataTable dt = new DataTable();
ada.Fill(dt);
conn.Close();
return dt;
}
/// <summary>
/// 将数据导入Excel
/// </summary>
/// <param name="path"></param>
/// <param name="StuList"></param>
/// <returns></returns>
public static int ExcelToAdd(string path, List<MODELS.StrModel> StuList)
{
string strcon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strcon);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string createStr = "create table [Sheet6]([学号] int,[姓名] Text,[语文成绩] Text,[数学成绩] int)";
OleDbCommand cmd = new OleDbCommand(createStr, conn);
cmd.ExecuteNonQuery();
conn.Close();
int ids = 0;
foreach (MODELS.StrModel item in StuList)
{
AddExcel(item, path);
ids++;
}
return ids;
}
/// <summary>
/// 添加到Excel
/// </summary>
/// <param name="StuList"></param>
private static void AddExcel(MODELS.StrModel StuList,string path)
{
string sql = "insert into [Sheet6$]([学号],[姓名],[语文成绩],[数学成绩]) values(" + StuList.StuID + ",'" + StuList.StuName + "','" + StuList.StuCMark + "','" + StuList.StuMonthMark + "')";
string strcon = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strcon);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
OleDbCommand com = new OleDbCommand(sql, conn);
com.ExecuteNonQuery();
conn.Close();
}
}
}
======================================================================================================
在控制器中的操作代码，主要是调用Excel帮助类
===================================================
using ExcelDal;
using System.IO;
using System.Data;
//将数据库数据导入Excel
public ActionResult Down()
{
StuDal dal = new StuDal();
List<MODELS.StrModel> StuList = StuDal.StuList(); //将数据库中的数据拿出来
int exelId = ExcelDals.ExcelToAdd("C:/Users/ASUS/Desktop/学生信息二.xlsx", StuList); //调用ExceDal的方法将数据放入到Excel中
if (exelId > 0)
{
return Content("<script>alert('导出成功');location.href='/Home/index'");
}
else
{
return Content("<script>alert('导出失败');location.href='/Home/index'");
}
}
/// <summary>
/// 导入数据库
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
[HttpPost]
public ActionResult Up(HttpPostedFileBase file)
{
string name = Server.MapPath("/Excel/" + Path.GetFileName(file.FileName)); //
file.SaveAs(name);
ExcelDals bll = new ExcelDals();
ExcelDal.StuDal stuBll = new StuDal();
DataTable Dex = bll.ExcelTable(name); //将Excel数据转换为Datatable
if (stuBll.AddExcelToSql(Dex) > 0)
{
return Content("<script>alert('导入成功');location.href='/Home/index'</script>");
}
else
{
return Content("<script>alert('导入失败');location.href='/Home/index'</script>");
}
}
================================================================================================
Html页面的代码
======================================================================================
<script src="~/Scripts/jquery-1.8.2.min.js"></script>
<script>
function DC() {
location.href = "/Home/Down";
}
</script>
</head>
<body>
<form action="/Home/Up" method="post" enctype="multipart/form-data">
<input id="File1" type="file" name="file"/><input type="submit" value="导入"/>
</form>
<input type="button" value="导出" onclick="DC()" />
<div>
<table>
<tr>
<td>
学号
</td>
<td>
学生姓名
</td>
<td>
语文成绩
</td>
<td>
数学成绩
</td>
</tr>
@{
foreach (var i in ViewBag.StuList)
{
<tr>
<td>
@i.StuID
</td>
<td>
@i.StuName
</td>
<td>
@i.StuCMark
</td>
<td>
@i.StuMonthMark
</td>
</tr>
}
}
</table>
</div>
</body>
</html>
