excel helper

    #region Public static functions

        /// <summary>
        /// Excute Data Reader
        /// </summary>
        /// <param name="filePath">Excel Path</param>
        /// <param name="queryString">SQL Query String</param>
        /// <returns>Data Reader</returns>
        public static OleDbDataReader ExecuteReader(string filePath, string queryString)
        {
            OleDbCommand cmd = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(ExcelConnection(filePath));
            try
            {
                PrepareCommand(cmd, conn, queryString);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                Exception ex1 = new Exception("Exception Occured when read excel data", ex);
                throw ex1;
            }
        }

        public static OleDbDataReader ExecuteReader(OleDbConnection conn, string queryString)
        {
            OleDbCommand cmd = new OleDbCommand();
            try
            {
                PrepareCommand(cmd, conn, queryString);
                OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                Exception ex1 = new Exception("Exception Occurd when read excel data", ex);
                throw ex1;
            }
        }


        /// <summary>
        /// Execute SQL Query String
        /// </summary>
        /// <param name="conn">Connection</param>
        /// <param name="queryString">SQL Query String</param>
        public static void ExecuteNonQuery(OleDbConnection conn, string queryString)
        {
            OleDbCommand cmd = new OleDbCommand();
            try
            {
                PrepareCommand(cmd, conn, queryString);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                conn.Close();
                Exception ex1 = new Exception("Exception Occurd when read excel data", ex);
                throw ex1;
            }
        }

        /// <summary>
        /// Create a connection
        /// </summary>
        /// <param name="filePath">Excel Path</param>
        /// <returns>Connection</returns>
        public static OleDbConnection CreateConn(string filePath)
        {
            return new OleDbConnection(ExcelConnection(filePath));
        }

        #endregion

        #region Private static functions

        /// <summary>
        /// Preparet Command
        /// </summary>
        /// <param name="cmd">OleDb Command Object</param>
        /// <param name="conn">Connection</param>
        /// <param name="queryString">SQL Query String</param>
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, string queryString)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = queryString;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 300;
        }

        /// <summary>
        /// Excel Connection String
        /// </summary>
        /// <param name="filePath">Excel Path</param>
        /// <returns>Connection String</returns>
        private static string ExcelConnection(string filePath)
        {
            //default for newly version.
            return ExcelConnection(filePath, false);
        }

        private static string ExcelConnection(string filePath, bool isOlderVersion)
        {
            string strConn = string.Empty;
            if (isOlderVersion)
            {
                //for excel 2000, 2003
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0;HDr=No";
            }
            else
            {
                //for 2007 //HDR=Yes/No Header Define Row.
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties='Excel 12.0;HDR=No;'";
            }

            return strConn;
        }

        /// <summary>
        /// transfer block data a time
        /// </summary>
        /// <param name="excelFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="connectionString"></param>
        public static void TransferData(string excelFile, string sheetName)
        {
            DataSet ds = new DataSet();
            try
            {
                //获取全部数据
                OleDbConnection conn = ExcelHelper.CreateConn(excelFile);
                conn.Open();
                string selectExcelQeury = string.Empty;
                string excelTableName = GetExcelFirstSheetName(excelFile);
                OleDbDataAdapter oleAdapter = null;
                
                selectExcelQeury = string.Format("select * from [{0}]", excelTableName);
                oleAdapter = new OleDbDataAdapter(selectExcelQeury, conn);
                oleAdapter.Fill(ds, sheetName);

                conn.Close();

                //如果目标表不存在则创建
                //string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                //foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
                //{
                //    strSql += string.Format("[{0}] varchar(255),", column.ColumnName);
                //}
                //strSql = strSql.Trim(',') + ")";

                ///get excel table fields Info.
                //System.Data.DataTable tableColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, excelTableName, null });
                //string ColumnName = string.Empty;
                //foreach (System.Data.DataRow drowColumns in tableColumns.Rows)
                //{
                //    ColumnName = drowColumns["Column_Name"].ToString();
                //    Console.WriteLine(" "+ColumnName);
                //}

                //using (SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
                //{
                //    sqlconn.Open();
                //    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                //    command.CommandText = strSql;
                //    command.ExecuteNonQuery();
                //    sqlconn.Close();
                //}

                //用bcp导入数据
                using (SqlBulkCopy bcp = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
                {
                    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    bcp.BatchSize = 100;//each time transfered rows
                    bcp.NotifyAfter = 100;//进度提示的行数
                    bcp.DestinationTableName = sheetName;//target table name
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.Message);
                throw ex;
            }
        }

        private static string GetExcelFirstSheetName(string filePath)
        {
            string firstSheetName = string.Empty;
            if (!string.IsNullOrEmpty(filePath))
            {
                OleDbConnection conn = ExcelHelper.CreateConn(filePath);
                conn.Open();

                DataTable dataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                firstSheetName = dataTable.Rows[0]["table_Name"].ToString();

                conn.Close();
            }

            return firstSheetName;
        }

        private static void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
        {
            string str = e.RowsCopied.ToString();
        }

        /// <summary>
        /// export control content to excel
        /// </summary>
        /// <param name="control">control with content</param>
        /// <param name="page">on the page</param>
        /// <param name="fileName">export file name</param>
        public static void ImportToExcel(System.Web.UI.Control control, System.Web.UI.Page page, string fileName)
        {
            string defaultFileName = "import excel.xls";
            if (string.IsNullOrEmpty(fileName))
            {
                fileName = defaultFileName;
            }
            page.Response.AppendHeader("Content-Disposition", "attachment;filename="+fileName);
            page.Response.ContentType = "application/ms-excel";
            page.Response.Charset = "UTF-8";
            page.Response.ContentEncoding = System.Text.Encoding.UTF8;

            //初始化HtmlWriter
            System.IO.StringWriter writer = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(writer);

            control.RenderControl(htmlWriter);

            //输出
            page.Response.Write(writer.ToString());
            page.Response.End();
        }