C# 读取Excel文件

1.引入相应的命名空间：

using System;
using System.Collections.Generic;
using System.Text;

using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

 

2.读取Excel文件，将内容存储在DataSet中 

/// <summary>
        /// 读取Excel文件，将内容存储在DataSet中
        /// </summary>
        /// <param name="opnFileName">带路径的Excel文件名</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDataSet(string opnFileName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + opnFileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";
            OleDbConnection conn = new OleDbConnection(strConn);
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = new DataSet();
            strExcel = "select * from [sheet1$]";
            try
            {
                conn.Open();
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, "dtSource");
                return ds;
            }
            catch (Exception ex)
            {
                //MessageBox.Show("导入出错：" + ex, "错误信息");
                Console.WriteLine(ex);
                return ds;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

3.将读取出来的数据插入到数据库

/// <summary>
        /// 保存所有信息
        /// </summary>
        public static void SaveAll(DataSet ds)
        {
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Console.WriteLine("{0}/t{1}/t{2}", row[0], row[1], row[2]);

                UserInfo userinfo = new UserInfo();
                userinfo.UserName = (string)row[1];
                userinfo.UserPwd = (string)row[2];

                add(userinfo);
            }
        }


        private static void add(UserInfo userinfo)
        {
            string sqlCon = "Server=.;uid=sa;pwd=123;DataBase=accp"; //连接字符串
            SqlConnection con = new SqlConnection(sqlCon); //获取连接对象

            SqlCommand com = new SqlCommand("insert into userinfo values('" + userinfo.UserName + "','" + userinfo.UserPwd + "')", con);
            con.Open();
            int rows = com.ExecuteNonQuery();
            if (con != null)
            {
                con.Close();
            }
        }