Excel—— [导入到数据库] or 将数据 [导入到Excel]

将Excel导入到数据库实现如下：

前台代码:

@model IEnumerable<Model.Student>
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script src="~/js/jquery.min.js"></script>
    <script>
        function ExcInput()
        {
            location.href = "/Home/ExcInput";
        }
</script>
</head>
<body>
    <div>
        <form action="/Home/Execl" method="post" enctype="multipart/form-data">
            <input type="file" name="Exc" />
            <input type="submit" value="导入" />
        </form>

        <input type="submit" value="导出" onclick="ExcInput()"/>
        <table id="table">
            <tr>
                <th>编号</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>性别</th>
            </tr>
            @foreach (var item in Model)
            {
<tr>
                    <td>@item.id</td>
                    <td>@item.name</td>
                    <td>@item.age</td>
                    <td>@item.sex</td>
                </tr>
            }
</table>
    </div>
</body>
</html>

View Code

后台代码：

 /// <summary>
/// 初始化页面
/// </summary>
/// <returns></returns>
public ActionResult Index()
        {
            DAL.StudentDal dal = new DAL.StudentDal();
            List<Student> ls = dal.GetStudentList();
return View(ls);           
        }
/// <summary>
/// Excel上传部分
/// 导入   Import
/// </summary>
/// <param name="Exc"></param>
/// <returns></returns>
        [HttpPost]
public ActionResult Execl(HttpPostedFileBase Exc)
        {
#region /// 上传部分

//如果当前的网站目录为E:\wwwroot   应用程序虚拟目录为E:\wwwroot\company 浏览的页面路径为E:\wwwroot\company\news\show.asp
//在show.asp页面中使用
//Server.MapPath("./")   返回路径为：E:\wwwroot\company\news
//Server.MapPath("/")    返回路径为：E:\wwwroot
//Server.MapPath("../")   返回路径为：E:\wwwroot\company
//Server.MapPath("~/")   返回路径为：E:\wwwroot\company

string strfileName = Server.MapPath("/Word/"); //存储文件的地方

if (!Directory.Exists(strfileName)) //判断文件路径是否存在
            {
                Directory.CreateDirectory(strfileName);
            }
string fName = Path.GetFileName(Exc.FileName); //获取文件名
            Exc.SaveAs(strfileName + fName);

#endregion

#region /// Execl导入部分

//execl文件读取
            ExcelDAL exc = new ExcelDAL();
            DataTable dt = exc.ExcelToDS(strfileName + fName);

//把读取的数据导入到数据库
            DAL.StudentDal dal = new DAL.StudentDal();
foreach (DataRow dr in dt.Rows)
            {
                Student student = new Student();
                student.id = Convert.ToInt32(dr[0]);
                student.name = dr[1].ToString();
                student.sex = dr[2].ToString();
                student.age = Convert.ToInt32(dr[3]);
                dal.Add(student);
            }

#endregion

            List<Student> ls = dal.GetStudentList();//查询出所有数据

return View("Index", ls);
        }

View Code

Excel导入导出帮助类 ExcelDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using Model;
using System.Data.OleDb;

namespace DAL
{
/// <summary>
/// 
/// </summary>
public class ExcelDAL
    {
/// <summary>
/// 将excel中的数据取出，填充到dataset中
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
public DataTable ExcelToDS(string path)
        {
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
            DataSet ds = new DataSet();
            oda.Fill(ds);
return ds.Tables[0];
        }

/// <summary>
/// 将单条数据插入到excel中
/// </summary>
/// <param name="path"></param>
/// <param name="e"></param>
/// <returns></returns>
public int ExcelToAdd(string path, Student student)
        {
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
string SQL = "INSERT INTO [Sheet2$] ([编号],[姓名],[性别],[年龄]) VALUES(" + student.id + ",'" + student.name + "','" + student.sex + "'," + student.age + ")";
            OleDbCommand cmd = new OleDbCommand(SQL, conn);
int i = cmd.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();
return i;
        }

/// <summary>
/// 通过循环将list中的数据插入到excel中
/// </summary>
/// <param name="path">excel的路径</param>
/// <param name="studentList">数据集合</param>
/// <returns></returns>
public int ExcelToAdd(string path, List<Student> studentList)
        {
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";

            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([编号] INT,[姓名] Text,[性别] Text,[年龄] int)", conn);
            cmd.ExecuteNonQuery();

foreach (Student e in studentList)
            {
string SQL = "INSERT INTO [Sheet1$] ([编号],[姓名],[性别],[年龄]) VALUES(" + e.id + ",'" + e.name + "','" + e.sex + "'," + e.age + ")";
                cmd = new OleDbCommand(SQL, conn);
int i = cmd.ExecuteNonQuery();
            }
            conn.Close();
            conn.Dispose();
return 1;
        }
    }
}

View Code

将数据库内容导出到Excel实现：

后台代码：

  /// <summary>
/// 导出  export
/// </summary>
/// <returns></returns>
public ActionResult ExcInput()
        {
#region /// 查询部分

            DAL.StudentDal dal = new DAL.StudentDal();
            List<Student> ls = dal.GetStudentList();

#endregion

            ExcelDAL exc = new ExcelDAL();

            exc.ExcelToAdd("D:/studentDemo.xls", ls);
return View();
        }

View Code

前台界面如下：

Excel—— [导入到数据库] or 将数据 [导入到Excel]

需要注意的：

Excel—— [导入到数据库] or 将数据 [导入到Excel]

秒客网

Excel—— [导入到数据库] or 将数据 [导入到Excel]

相关文章