快速导入Excel

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Data.Odbc;

namespace QZMDB.Function
{

    /// <summary>
    /// Excel文件的读取
    /// </summary>
    public class ExcelLoadFunction
    {
        public static string ConStr = Properties.Settings.Default.QZMDBConnectionString;
        DataSet ds = new DataSet();
        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public int LoadExcel(string fileName)
        {
            //  return BlacklistQuChong(fileName); ////如果需要对黑名单进行去重处理则放行此代码段.注掉"ds-return0;"之间代码段
            ds = CSV_Getds(fileName);
            if (ds.Tables[0].Rows.Count != 0)
            {

                try
                {

                    using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString))
                    {
                        sbc.DestinationTableName = "tbBlackList";//对应的数据库表名
                        sbc.ColumnMappings.Clear();
                        sbc.ColumnMappings.Add("黑名单号码", "mobile");//关联ds.tables[0]中的列和数据库中的列明
                        sbc.BatchSize = 1000;  //设置每次放入条数
                        sbc.WriteToServer(ds.Tables[0]);
                    }
                    return ds.Tables[0].Rows.Count;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    return 0;
                }

            }
            return 0;

        }
        /// <summary>
        /// 黑名单导入(带去重处理)
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        private int BlacklistQuChong(string fileName)
        {
            try
            {
                SqlConnection sConn = new SqlConnection(ConStr);
                string[] name = GetTablesFromOleDb(fileName);
                string a = name[0].Replace("'", "");
                string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                                    "data source=" + fileName;
                sConn.Open();
                OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + a + "]", OLEDBConnStr);
                DataTable sourceDataTable = new DataTable();
                oda.Fill(sourceDataTable);
                oda.Dispose();

                int result = 0;
                foreach (DataRow item in sourceDataTable.Rows)
                {
                    string ids = Convert.ToString(item["黑名单号码"]);
                    if (delete(ids))
                    {
                        string sql = "insert into tbBlackList values(@mobile)";
                        SqlParameter[] param = new SqlParameter[]
                   {
                       new SqlParameter("@mobile",ids),
                   };
                        result += SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, sql, param);
                    }
                }

                sConn.Close();
                return result;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }
        }


        public static string[] GetTablesFromOleDb(string path)
        {

            string[] result = null;
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                                    "data source=" + path;
            OleDbConnection conn = null;
            DataTable tblSchema = null;


            // 初始化连接，并打开
            conn = new OleDbConnection(connStr);
            try
            {
                conn.Open();
                tblSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                result = new string[tblSchema.Rows.Count];
                for (int i = 0; i < tblSchema.Rows.Count; i++)
                {
                    result[i] = tblSchema.Rows[i][2].ToString();
                }
            }
            catch (Exception)
            {
                return null;
            }

            finally
            {
                // 关闭连接
                conn.Close();
            }
            return result;
        }
        /*
         * 此处代码段进行了修改
         * 原有:先判断是否存在数据行有删除后添加
         * 更改:如果有重复的数据行则保持不执行添加操作
         * 时间:20110711
         * 备注;详情参见注掉的代码片段
         * 修改人:Roni
         * **/
        private bool delete(string ids)
        {
            SqlConnection sConn = new SqlConnection(ConStr);

            string select = "select * from tbBlackList where mobile='" + ids + "'";

            using (SqlDataReader reader = SqlHelper.SqlHelper.ExecuteReader(ConStr, CommandType.Text, select, null))
            {

                if (reader.HasRows)
                {
                    //string s = "delete from tbBlackList where mobile='" + ids + "'";
                    //SqlHelper.SqlHelper.ExecuteNonQuery(sConn, System.Data.CommandType.Text, s, null);
                    return false;
                }

            }
            sConn.Close();
            return true;
        }

        internal static bool CreateExcelByCondition(string filePath, string ExcelName)
        {
            throw new NotImplementedException();
        }
        /// <summary>
        /// 查找手机号码是否重复
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        private bool deleteByMobile(string ids)
        {
            SqlConnection sConn = new SqlConnection(ConStr);

            string select = "select * from tbDialResultHis where mobile='" + ids + "'";

            using (SqlDataReader reader = SqlHelper.SqlHelper.ExecuteReader(ConStr, CommandType.Text, select, null))
            {

                if (reader.HasRows)
                {
                    return false;
                }

            }
            sConn.Close();
            return true;
        }



        /// <summary>
        /// Excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public int LoadExcelByFileName(string fileName, int projectId, int productID, string rev_file_name, string input_date)
        {
            ds = CSV_Getds(fileName);
    
            if (ds.Tables[0].Rows.Count != 0)
            {
                try
                {
                    if (!ds.Tables[0].Columns.Contains("项目编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建
                    }
                    if (!ds.Tables[0].Columns.Contains("产品编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int)));

                    }
                    if (!ds.Tables[0].Columns.Contains("导入时间"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime)));

                    }
                    if (!ds.Tables[0].Columns.Contains("导入文件名"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string)));

                    }

                    //if (!ds.Tables[0].Columns.Contains("手机1"))
                    //{
                    //    ds.Tables[0].Columns.Add(new DataColumn("手机1", typeof(string)));

                    //}
                    string errmsg = "";
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                         
                        dr["项目编号"] = projectId;
                        dr["产品编号"] = productID;
                        dr["导入时间"] = input_date;
                        dr["导入文件名"] = rev_file_name;
                        if (dr["手机"].ToString().Length > 11)
                        {
                            errmsg += dr["手机"].ToString()+" 手机长度超过11位\r\n";
                        }
                       
                    }

                    if(errmsg.Length!=0)
                    {
                        MessageBox.Show(errmsg);
                        return 0;
                    }
                    using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString))
                    {
                        sbc.DestinationTableName = "tbDialResultHis";//对应的数据库表名
                        sbc.ColumnMappings.Clear();
                        sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明
                        sbc.ColumnMappings.Add("产品编号", "product_id");
                        sbc.ColumnMappings.Add("导入时间", "input_dt");
                        sbc.ColumnMappings.Add("导入文件名", "rev_file_name");


                        sbc.ColumnMappings.Add("手机", "mobile");
                         sbc.ColumnMappings.Add("姓名", "name");
                         sbc.ColumnMappings.Add("性别", "sex");
                            sbc.ColumnMappings.Add("年龄", "age");
                         sbc.ColumnMappings.Add("营销是否成功", "marketing_result");
                         sbc.ColumnMappings.Add("已接通结果", "dial_result");
                         sbc.ColumnMappings.Add("备注", "dial_remark");
                         sbc.ColumnMappings.Add("电话经理", "dial_user");
                        sbc.BatchSize = 1000;  //设置每次放入条数
                        sbc.WriteToServer(ds.Tables[0]);
                    }
                    return ds.Tables[0].Rows.Count;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    return 0;
                }

            }
            return 0;

        }
        /// <summary>
        /// 拨打清单导入
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="projectId"></param>
        /// <param name="productID"></param>
        /// <param name="rev_file_name"></param>
        /// <param name="input_date"></param>
        /// <returns></returns>
        public int LoadExcelByFileName_CallList(string fileName, int projectId, int productID, string rev_file_name, string input_date)
        {
               ds = CSV_Getds(fileName);
                if (ds.Tables[0].Rows.Count != 0)
                {
                    try
                    {
                        if (!ds.Tables[0].Columns.Contains("项目编号"))
                        {
                            ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建
                        }
                        if (!ds.Tables[0].Columns.Contains("产品编号"))
                        {
                            ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int)));

                        }
                        if (!ds.Tables[0].Columns.Contains("导入时间"))
                        {
                            ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime)));

                        }
                        if (!ds.Tables[0].Columns.Contains("导入文件名"))
                        {
                            ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string)));

                        }
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            dr["项目编号"] = projectId;
                            dr["产品编号"] = productID;
                            dr["导入时间"] = input_date;
                            dr["导入文件名"] = rev_file_name;
                        }

                        using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString))
                        {
                            sbc.DestinationTableName = "tbDialHis";//对应的数据库表名
                            sbc.ColumnMappings.Clear();
                            sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明
                            sbc.ColumnMappings.Add("产品编号", "product_id");
                            sbc.ColumnMappings.Add("导入时间", "input_dt");
                            //sbc.ColumnMappings.Add("导入文件名", "rev_file_name");


                            sbc.ColumnMappings.Add("被叫", "mobile");
                            sbc.ColumnMappings.Add("呼出时间", "dial_dt");
                            sbc.ColumnMappings.Add("通话时长(秒)", "dial_duration");
                              sbc.BatchSize = 1000;  //设置每次放入条数
                            sbc.WriteToServer(ds.Tables[0]);
                        }
                        return ds.Tables[0].Rows.Count;
                    }
                    catch (Exception ex)
                    {
                        System.Windows.Forms.MessageBox.Show(ex.Message);
                        return 0;
                    }

                }
                return 0;

            
        }

        /// <summary>
        /// QC数据导入
        /// </summary>
        /// <param name="projectID"></param>
        /// <param name="productID"></param>
        /// <param name="filename"></param>
        /// <param name="time"></param>
        /// <returns></returns>
        internal int LoadExcelByFileName_QCData(int projectID, int productID, string filename, string time)
        {    
             ds = CSV_Getds(filename);
            if (ds.Tables[0].Rows.Count != 0)
            {
                try
                {
                    if (!ds.Tables[0].Columns.Contains("项目编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建
                    }
                    if (!ds.Tables[0].Columns.Contains("产品编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int)));

                    }
                    if (!ds.Tables[0].Columns.Contains("导入时间"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("导入时间", typeof(DateTime)));

                    }
                    if (!ds.Tables[0].Columns.Contains("导入文件名"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("导入文件名", typeof(string)));

                    }
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        dr["项目编号"] = projectID;
                        dr["产品编号"] = productID;
                        dr["导入时间"] = time;
                        dr["导入文件名"] = filename;
                    }

                    using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString))
                    {
                        sbc.DestinationTableName = "tbQCHis";//对应的数据库表名
                        sbc.ColumnMappings.Clear();
                        sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明
                        sbc.ColumnMappings.Add("产品编号", "product_id");
                        sbc.ColumnMappings.Add("导入时间", "input_dt");
                        sbc.ColumnMappings.Add("导入文件名", "rev_file_name");


                        sbc.ColumnMappings.Add("用户号码", "mobile");
                        sbc.ColumnMappings.Add("开通时间", "qc_order_dt");
                        sbc.BatchSize = 1000;  //设置每次放入条数
                        sbc.WriteToServer(ds.Tables[0]);
                    }
                    return ds.Tables[0].Rows.Count;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    return 0;
                }

            }
            return 0;

        }

        /// <summary>
        /// 电信反馈表
        /// </summary>
        /// <param name="FilePath"></param>
        /// <param name="projectID"></param>
        /// <param name="productID"></param>
        /// <param name="input_date"></param>
        /// <returns></returns>
        public int LoadExcelByFileName_tbFeedBack(string FilePath, int projectID, int productID, string input_date)
        {
            ds = CSV_Getds(FilePath);
            if (ds.Tables[0].Rows.Count != 0)
            {
                try
                {
                    if (!ds.Tables[0].Columns.Contains("项目编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("项目编号", typeof(int))); //临时表datatable中没有此列则创建
                    }
                    if (!ds.Tables[0].Columns.Contains("产品编号"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("产品编号", typeof(int)));

                    }
                    if (!ds.Tables[0].Columns.Contains("电信反馈时间"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("电信反馈时间", typeof(DateTime)));

                    }
                    if (!ds.Tables[0].Columns.Contains("文件导入日期"))
                    {
                        ds.Tables[0].Columns.Add(new DataColumn("文件导入日期", typeof(DateTime)));

                    }
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        dr["项目编号"] = projectID;
                        dr["产品编号"] = productID;
                        dr["电信反馈时间"] = input_date;
                        dr["文件导入日期"] = input_date;
                    }

                    using (SqlBulkCopy sbc = new SqlBulkCopy(Properties.Settings.Default.QZMDBConnectionString))
                    {
                        sbc.DestinationTableName = "tbFeedBack";//对应的数据库表名
                        sbc.ColumnMappings.Clear();
                        sbc.ColumnMappings.Add("项目编号", "project_id");//关联ds.tables[0]中的列和数据库中的列明
                        sbc.ColumnMappings.Add("产品编号", "product_id");
                        sbc.ColumnMappings.Add("电信反馈时间", "fb_dt");
                        sbc.ColumnMappings.Add("文件导入日期", "input_dt");


                        sbc.ColumnMappings.Add("手机号码", "mobile");
                        sbc.ColumnMappings.Add("在网情况", "fb_online");
                        sbc.ColumnMappings.Add("订购是否成功", "fb_order_result");
                        sbc.ColumnMappings.Add("离网日期", "fb_leave_dt");
                        sbc.BatchSize = 1000;  //设置每次放入条数
                        sbc.WriteToServer(ds.Tables[0]);
                    }
                    return ds.Tables[0].Rows.Count;
                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show(ex.Message);
                    return 0;
                }

            }
            return 0;
        }
        public DataSet CSV_Getds(string filePath)
        {

            try
            {
                string[] name = GetTablesFromOleDb(filePath);
                string a = name[0].Replace("'", "");
                string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                    "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\";" + // 指定扩展属性为 Microsoft Excel 8.0 (97) 9.0 (2000) 10.0 (2002)，并且第一行作为数据返回，且以文本方式读取
                                    "data source=" + filePath;
                OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + a + "]", OLEDBConnStr);
                DataSet sourceDataTable = new DataSet();
                oda.Fill(sourceDataTable);
                oda.Dispose();
                return sourceDataTable;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return null;
            }

        }

    }

    }