C# 读取Excel文件 .

2009-03-15 10:25 166人阅读 评论(0) 收藏 举报

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

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中 

       /// 读取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>
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();
    }
}