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

前台界面如下：

 

需要注意的：

posted @ 2017-06-10 23:16  shuai7boy  阅读(451)  评论(0)  编辑  收藏  举报