excel.cs

Excel导入MSSQL,MSSQL导出Excel使用方便,速度快,内带说明

            {
                bool IsInt = new Regex(@"^([-]|[0-9])[0-9]*$").IsMatch(strValue.ToString());
                if (IsInt)
                {
                    intValue = Convert.ToInt32(strValue);
                }
            }

            return intValue;
        }
        #endregion
     
        #region 判断给定的字符串(strNumber)是否是数值型
        /// <summary>
        /// 判断给定的字符串(strNumber)是否是数值型
        /// </summary>
        /// <param name="strNumber">要确认的字符串</param>
        /// <returns>是则返加true 不是则返回 false</returns>
        bool IsNumber(string strNumber)
        {
            return new Regex(@"^([0-9])[0-9]*(\.\w*)?$").IsMatch(strNumber);
            /*
            Regex objNotNumberPattern=new Regex("[^0-9.-]");
            Regex objTwoDotPattern=new Regex("[0-9]*[.][0-9]*[.][0-9]*");
            Regex objTwoMinusPattern=new Regex("[0-9]*[-][0-9]*[-][0-9]*");
            string strValidRealPattern="^([-]|[.]|[-.]|[0-9])[0-9]*[.]*[0-9]+$";
            string strValidIntegerPattern="^([-]|[0-9])[0-9]*$";
            Regex objNumberPattern =new Regex("(" + strValidRealPattern +")|(" + strValidIntegerPattern + ")");
            return !objNotNumberPattern.IsMatch(strNumber) &&
                !objTwoDotPattern.IsMatch(strNumber) &&
                !objTwoMinusPattern.IsMatch(strNumber) &&
                objNumberPattern.IsMatch(strNumber);
            */
        }
        #endregion

        #region 返回 URL 字符串的编码结果
        /// <summary>
        /// 返回 URL 字符串的编码结果
        /// </summary>
        /// <param name="str">字符串</param>
        /// <returns>编码结果</returns>
        string UrlEncode(string str)
        {
            return UrlEncode(str);
        }
        #endregion

        #region string型转换为float型
        /// <summary>
        /// string型转换为float型
        /// </summary>
        /// <param name="strValue">要转换的字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns>转换后的int类型结果</returns>
        Double StrToFloat(object strValue, Double defValue)
        {
            if ((strValue == null) || (strValue.ToString().Length <= 0))
            {
                return defValue;
            }

            Double intValue = defValue;
            if (strValue != null)
            {
                bool IsFloat = new Regex(@"^([-]|[0-9])[0-9]*(\.\w*)?$").IsMatch(strValue.ToString());
                if (IsFloat)
                {
                    intValue = Convert.ToDouble(strValue);
                }
            }
            return intValue;
        }
        #endregion

        #region string型转换为日期时间型
        /// <summary>
        /// string型转换为日期时间型
        /// </summary>
        /// <param name="strValue">要转换的字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns>转换后的日期时间类型结果</returns>
        System.DateTime StrToDateTime(object strValue)
        {
            if ((strValue == null) || (strValue.ToString() == string.Empty))
            {
                return System.DateTime.Now;
            }

            DateTime DateTimeValue = System.DateTime.Now;
            if (strValue != null)
            {
                string strRegex = @"^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578]
                                        )|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[4
                                        69])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\
                                        s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([1
                                        3579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((
                                        0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((
                                        0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9]
                                        )|(2[0-8]))))))"; //日期部分
                //strRegex += @"(\s(((0?[0-9])|([1-2][0-3]))\:([0-5]?[0-9])((\s)|(\:([0-5]?[0-9])))))?$"; //时间部分
                System.Text.RegularExpressions.RegexOptions options = ((System.Text.RegularExpressions.RegexOptions.IgnorePatternWhitespace | System.Text.RegularExpressions.RegexOptions.Multiline) | System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(strRegex, options);
                bool IsDateTime = reg.IsMatch(strValue.ToString());
                if (IsDateTime)
                {
                    DateTimeValue = Convert.ToDateTime(strValue);
                }
            }
            return DateTimeValue;
        }

        #endregion
        #region string型转换为布尔型
        /// <summary>
        /// string型转换为布尔型
        /// </summary>
        /// <param name="strValue">要转换的字符串</param>
        /// <param name="defValue">缺省值</param>
        /// <returns>转换后的布尔型类型结果</returns>
        System.Boolean StrToBool(object strValue, bool defaultValue)
        {
            if ((strValue == null) || (strValue.ToString() == string.Empty))
            {
                return defaultValue;
            }

            System.Boolean boolValue = defaultValue;
            if (strValue != null)
            {
                if (string.Compare(strValue.ToString(), "true", true) == 0)
                {
                    return true;
                }
                else if (string.Compare(strValue.ToString(), "false", true) == 0)
                {
                    return false;
                }
            }
            return boolValue;
        }
        #endregion

        #region  替换sql语句中的有问题符号
        /// <summary>
        /// 替换sql语句中的有问题符号
        /// </summary>
        string ChkSQL(string str)
        {
            if (str != null && str.ToString() != string.Empty)
            {
                str = str.Replace("&", "&#38;");
                str = str.Replace("'", "&#39;");
                str = str.Replace(">", "&gt;");
                str = str.Replace("<", "&lt;");
                str = str.Replace("=", "&#61;");
                str = str.Replace("?", "&#63;");
                str = str.Replace("%", "&#37;");
                str = str.Replace("@", "&#64;");
                str = str.Replace("+", "&#43;");
                str = str.Replace("-", "&#45;");
                str = str.Replace("*", "&#42;");
                str = str.Replace("/", "&#47;");
                str = str.Replace("[", "&#91;");
                str = str.Replace("]", "&#93;");
                str = str.Replace("(", "&#40;");
                str = str.Replace(")", "&#41;");
            }
            else
            {
                str = null;
            }
            return str;
        }
        #endregion

        #region 字符折分
        /// <summary>
        /// 字符折分
        /// </summary>
        /// <param name="strCon">要折分字符串</param>
        /// <param name="splitstr">分割符</param>
        /// <returns></returns>
        ArrayList SplitStr(string strChar, string splitstr)
        {
            ArrayList List = new ArrayList();
            if (strChar != null && strChar != "")
            {
                strChar = ReturnStr(strChar);
                if (strChar.IndexOf(splitstr) > -1)
                {
                    string strSep = splitstr;
                    char[] chrSep = strSep.ToCharArray();
                    string[] aryChar = strChar.Split(chrSep, strChar.Length);
                    for (int i = 0; i < aryChar.Length; i++)
                    {
                        List.Add(aryChar[i].ToString().Trim());
                    }
                }
                else
                {
                    List.Add(strChar);
                }
                return List;
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 出库字符分解
        /// <summary>
        /// 出库字符分解
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        string ReturnStr(string str)
        {
            if (str != null && str.ToString() != string.Empty)
            {
                str = str.Replace("&#38;", "&");
                str = str.Replace("&#39;", "'");
                str = str.Replace("&gt;", ">");
                str = str.Replace("&lt;", "<");
                str = str.Replace("&#61;", "=");
                str = str.Replace("&#63;", "?");
                str = str.Replace("&#37;", "%");
                str = str.Replace("&#64;", "@");
                str = str.Replace("&#43;", "+");
                str = str.Replace("&#45;", "-");
                str = str.Replace("&#42;", " *");
                str = str.Replace("&#47;", "/");
                str = str.Replace("&#91;", "[");
                str = str.Replace("&#93;", "]");
                str = str.Replace("&#40;", "(");
                str = str.Replace("&#41;", ")");
                str = str.Replace("&#123;", "{");
                str = str.Replace("&#125;", "}");
            }
            else
            {
                str = null;
            }
            return str;
        }
        #endregion

        #region 返回DataTable集
        /// <summary>
        /// 返回DataTable集
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="sql">SQL语句</param>
        /// <returns>DataSet</returns>
        List<DataTable> ExcelDataSet(string path, int numCol)
        {

            List<DataTable> dList = new List<DataTable>();
            try
            {
                List<DataTable> fwsn = (List<DataTable>)ALLWorkSheetName(path);
                dList = ClearDataTable(fwsn, numCol);
            }
            catch
            { }
            return dList;

        }
        #endregion

        #region 去除格式不同的DataTable
        List<DataTable> ClearDataTable(List<DataTable> dts, int numCol)
        {
            List<DataTable> tDt = new List<DataTable>();
            for (int i = 0; i < dts.Count; i++)
            {
                DataTable ds = dts[i];
                if (ds.Columns.Count == numCol)
                {
                    tDt.Add(ds);
                }
            }
            return tDt;
        }
        #endregion
    }
}