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

来源：互联网发布：python static method 编辑：程序博客网时间：2024/06/06 16:39

将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

前台界面如下：

需要注意的：

阅读全文

0 0