C#读Execl、Csv文件并导入SQLServer数据库

//读文件源代码；
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace lyzApplication2009
{
    class ReadExcel
    {
        private string FileName;
        private string FilsPath;
        /// <summary>
        /// 构造函数文件所在路径
        /// </summary>
        /// <param name="Path">文件路径（Execl文件为完整的路径）</param>
        public ReadExcel(string Path)//构造
        {
            FileName = Path;
            //FilsPath = Files;
        }
        /// <summary>
        /// 构造函数:文件所在路径
        /// </summary>
        /// <param name="Path">文件路径（为Csv文件所在的文件夹路径）</param>
        /// <param name="Files">读Csv文件时的文件名</param>
        public ReadExcel(string Path, string Files)//构造
        {
            FileName = Path;
            FilsPath = Files;
        }

        OleDbConnection OleCon = new OleDbConnection();
        OleDbCommand OleCmd = new OleDbCommand();
        OleDbDataAdapter OleDa = new OleDbDataAdapter();

        #region 读取Execl文件返回DataSet
        /// <summary>
        /// 读取Execl文件返回DataSet
        /// </summary>
        /// <returns>返回结果集</returns>
        public DataSet Execl()
        {
            DataSet Myds = new DataSet();
            OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+FileName+";Extended Properties=Excel 8.0;";
            OleCon.Open();
            OleCmd.CommandText="select * from [sheet1$]";
            OleCmd.Connection = OleCon;
            OleDa.SelectCommand = OleCmd;
            try
            {
                OleDa.Fill(Myds, "Execl");
                return Myds;
            }
            catch
            {
                return Myds;
            }
            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }

           
        }
        #endregion

        #region 读取Csv文件返回DataSet
        /// <summary>
        ///  读取Csv文件返回DataSet
        /// </summary>
        /// <returns>Csv内容</returns>
        public DataSet Csv()
        {
            DataSet CsvData = new DataSet();
            OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Text;FMT=Delimited;HDR=YES;'";
            OleCon.Open();
            OleCmd.Connection = OleCon;
            OleCmd.CommandText = "select * From " + FilsPath ;
            OleDa.SelectCommand=OleCmd;
            try
            {
                OleDa.Fill(CsvData, "Csv");
                return CsvData;
            }
            catch
            {
                return CsvData;
            }
            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }
        }
        #endregion
    }
}

//写入数据库源代码；此方法是源文件内容中的例必须和要导入的表的例数一至，且数据格式一至
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace lyzApplication2009
{
    /// <summary>
    /// 将数据写入数据库表中去
    /// </summary>
    class SqlBulkData
    {
        SqlConnection SqlCon = new SqlConnection();

        #region 将数据写数据库表中去（大容量）例与数据库表例一至
        /// <summary>
        /// 将数据写数据库表中去（大容量）例与数据库表例一至
        /// </summary>
        /// <param name="_Ds">数据源内容</param>
        /// <param name="_TableName">表名</param>
        /// <returns>返回是否成功！</returns>
        public bool BulkData(DataSet _Ds,string _TableName)
        {
            SqlCon.ConnectionString = PublicClass.ConnectString;
            SqlCon.Open();
            SqlBulkCopy SqlBulk = new SqlBulkCopy(SqlCon);
            SqlBulk.DestinationTableName = _TableName;
            try
            {
                SqlBulk.WriteToServer(_Ds.Tables[0], DataRowState.Unchanged);
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                SqlCon.Close();
                SqlCon.Dispose();
                SqlBulk.Close();
            }
        }
        #endregion
    }
}
< type="text/javascript"> < src="cache/hottags_forum_cache_jsonp.txt" type="text/javascript">

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/kbtjh/archive/2009/03/22/4014803.aspx