C#学习笔记---通过Excel导入数据库

一帆在线

2024-04-29 帮助1人

1、编写一个能够读取Excel的通用数据访问类OleDbHelper

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

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace DAL
{
    /// <summary>
    /// 访问Access数据库的通用类
    /// </summary>
    public class OleDbHelper
    {
        //适合Excel2003
        //private static string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0";
        //创建连接字符串(适合于EXCEL2007以后的版本)
        private static string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";

        /// <summary>
        /// 执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //将异常信息写入日志...

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //将异常信息写入日志...

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行一个结果集的查询
        /// </summary>
        /// <param name="sql">查询的SQL语句</param>
        /// <returns>返回一个SqlDataReader对象</returns>
        public static OleDbDataReader GetReader(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //将异常信息写入日志...

                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }

        }
        /// <summary>
        /// 执行返回数据集的查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            //创建数据适配器对象
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds); //使用数据适配器填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                //将异常信息写入日志...
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行返回数据集的查询(将指定路径的Excel导入到连接字符串中)
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, string path)
        {
            OleDbConnection conn = new OleDbConnection(string.Format(connString, path));
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            //创建数据适配器对象
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds); //使用数据适配器填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                //将异常信息写入日志...
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

2、编写ImportDataFromExcel类，添加查询Excel数据表的方法（要求数据封装成对象）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Sockets;
using System.Text;
using System.Threading.Tasks;

using Models;
using System.Data;


namespace DAL
{
    /// <summary>
    /// 从Excel中导入数据
    /// </summary>
    public class ImportDataFromExcel
    {
        /// <summary>
        /// 将选择的Excel数据表查询后封装成对象集合
        /// </summary>
        /// <param name="path">Excel文件的路径</param>
        /// <returns></returns>
        public List<Student> GetStudentByExcel(string path)
        {
            List<Student>list=new List<Student>();
            string sql = "select * from [Student$]";
            DataSet ds = OleDbHelper.GetDataSet(sql, path);
            DataTable dt = ds.Tables[0];
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new Student()
                {
                    StudentName = row["姓名"].ToString(),
                    Gender = row["性别"].ToString(),
                    Birthday = Convert.ToDateTime(row["出生日期"].ToString()),
                    StudentIdNo = row["身份证号"].ToString(),
                    PhoneNumber = row["电话号码"].ToString(),
                    StudentAddress = row["家庭住址"].ToString(),
                    ClassId = Convert.ToInt32(row["班级编号"].ToString()),
                    Age = Convert.ToInt32(row["年龄"].ToString()),
                    CardNo = row["考勤卡号"].ToString()
                });
            }
            return list;
        }
    }
}

3、在DataGridView中展示已经封装好的数据列表

        private void btnChoseExcel_Click(object sender, EventArgs e)
        {
            //打开文件
            OpenFileDialog openFile = new OpenFileDialog();
            DialogResult result = openFile.ShowDialog();
            if (result == DialogResult.OK)
            {
                string path = openFile.FileName;
                this.stuList = objImportDataFromExcel.GetStudentByExcel(path);
                //显示数据
                //this.dgvStudentList.DataSource = null;
                this.dgvStudentList.DataSource= stuList;
            }
        }

4、在SQLHelper类中编写同时“插入多条SQL语句的事务”方法

 /// <summary>
        /// 通过事务提交多条SQL语句
        /// </summary>
        /// <param name="sqlList">SQL语句集合</param>
        /// <returns></returns>
        public static bool UpdateByTran(List<string> sqlList)
        {
            SqlConnection conn=new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction(); //开启事务
                foreach (string itemSql in sqlList)
                {
                    cmd.CommandText = itemSql;
                    cmd.ExecuteNonQuery();
                }

                cmd.Transaction.Commit(); //提示事务（真正保存到数据库）
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback(); //回滚事务
                }
                
                throw new Exception("调用事务方法public static bool UpdateByTran(List<string> sqlList)出错："   ex.Message);
            }
            finally
            {
                if (cmd.Transaction!=null)
                {
                    cmd.Transaction = null;//清除事务
                }
                conn.Close();
            }
        }

5、在ImportDataFromExcel类中，编写保存“多个集合对象的方法”

  /// <summary>
        /// 通过学员集合添加
        /// </summary>
        /// <param name="list">学生集合</param>
        /// <returns></returns>
        public bool Import(List<Student> list)
        {
            //编写SQL语句
            StringBuilder sqlBuilder = new StringBuilder("INSERT INTO Students (StudentName, Gender, Birthday, StudentIdNo, CardNo, Age, PhoneNumber, StudentAddress, ClassId)");
            sqlBuilder.Append(" VALUES ('{0}', '{1}', '{2}', {3}, '{4}',  {5}, '{6}', '{7}', {8})");

            List<string> sqlList = new List<string>();//用来保存生成的多条SQL语句
            //解析对象
            foreach (Student objStudent in list)
            {
                string sql = string.Format(sqlBuilder.ToString(), objStudent.StudentName, objStudent.Gender,
                    objStudent.Birthday.ToString("yyyy-MM-dd"), objStudent.StudentIdNo, objStudent.CardNo,
                    objStudent.Age, objStudent.PhoneNumber, objStudent.StudentAddress,
                    objStudent.ClassId);
                //将解析的SQL语句添加到集合
                sqlList.Add(sql);
            }
            //将SQL语句集合提交到数据库
            try
            {
                return SQLHelper.UpdateByTran(sqlList);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

6、在UI中将导入的数据保存到数据库中

//保存到数据库
        private void btnSaveToDB_Click(object sender, EventArgs e)
        {
            //验证数据（保证List集合中有数据）
            if (stuList==null||stuList.Count==0)
            {
                MessageBox.Show("目前没有要导入的数据","导入提示");
                return;
            }
            //遍历集合（方法1：每查询一个对象，就提交一次到数据库；方法2：每遍历一次，就生成一条SQL语句，根据事务提交到数据库）
            try
            {
                if (objImportDataFromExcel.Import(this.stuList))
                {
                    MessageBox.Show("数据导入成功！","导入提示");
                    this.dgvStudentList.DataSource = null;
                    this.stuList.Clear();
                }
                else
                {
                    MessageBox.Show("数据导入失败","导入提示");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("数据导入失败!具体原因："   ex.Message);
            }
        }

这篇好文章是转载于：学新通技术网

C#学习笔记---通过Excel导入数据库

photoshop保存的图片太大微信发不了怎么办

《学习通》视频自动暂停处理方法

word里面弄一个表格后上面的标题会跑到下面怎么办

Android 11 保存文件到外部存储，并分享文件

photoshop扩展功能面板显示灰色怎么办

微信公众号没有声音提示怎么办

excel下划线不显示怎么办

excel打印预览压线压字怎么办

TikTok加速器哪个好免费的TK加速器推荐

怎样阻止微信小程序自动打开