|
实用类：UtilityClass 包含如下方法
判断对象是否为空或NULL,如果是空或NULL返回true，否则返回false
验证手机号是否正确 13,15,18
验证邮箱
验证网址
MD5加密,返回32位的字符串
DES加密/解密方法
把字符串的第一个字符变为大写
判断一个字符串是否是数字
判断一个字符串是否是时间
判断一个字符串是否是decimal类型
生成随机数方法 小于9位
获取网站配置ConfigurationManager_AppSettings键值
检查某个文件是否存在于磁盘上，存在--true，不存在--false
在服务器上创建文件夹
转换相对路径为物理路径
两个值的百分比例
截取字符枚举值
获取指定长度的字符串
地址栏传值加密/解密
获得当前页面客户端的IP
格式化要显示的内容，主要用于在网页上显示由textarea产生的内容
判断当前访问是否来自浏览器软件
判断当前访问是否来自非IE浏览器软件
SQL操作类：DBAccess 包含如下方法
代码
public class DBAccess { private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); // ExecuteNonQuery public static int ExecuteNonQuery(string commandText) { return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText); } public static int ExecuteNonQuery(string commandText, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText, cmdParameters); } /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName"></param> /// <param name="cmdParameters"></param> /// <returns></returns> public static int ExecuteNonQueryProc(string procName,params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, procName, cmdParameters); } // ExecuteDataset public static DataSet ExecuteDataset(string commandText) { return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText); } public static DataSet ExecuteDataset(string commandText, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText, cmdParameters); } /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName"></param> /// <param name="cmdParameters"></param> /// <returns></returns> public static DataSet ExecuteDatasetProc(string procName, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteDataset(_connectionString, CommandType.StoredProcedure, procName, cmdParameters); } // ExecuteReader public static SqlDataReader ExecuteReader(string commandText) { return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText); } public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText, cmdParameters); } /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName"></param> /// <param name="cmdParameters"></param> /// <returns></returns> public static SqlDataReader ExecuteReaderProc(string procName, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, procName, cmdParameters); } // ExecuteScalar public static object ExecuteScalar(string commandText) { return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText); } public static object ExecuteScalar(string commandText, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText, cmdParameters); } /// <summary> /// 执行存储过程 /// </summary> /// <param name="procName"></param> /// <param name="cmdParameters"></param> /// <returns></returns> public static object ExecuteScalarProc(string procName, params SqlParameter[] cmdParameters) { return Sqlhelper.ExecuteScalar(_connectionString, CommandType.StoredProcedure, procName, cmdParameters); } } Json操作类：JsonHelper包含如下方法
代码
/* 添加引用 System.Runtime.Serialization 添加引用 System.ServiceModel.Web */ public static class JsonHelper { /// <summary> /// 格式化成Json字符串 /// </summary> /// <param name="obj">需要格式化的对象</param> /// <returns>Json字符串</returns> public static string ToJson(this object obj) { // 首先，当然是JSON序列化 DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType()); // 定义一个stream用来存发序列化之后的内容 Stream stream = new MemoryStream(); serializer.WriteObject(stream, obj); // 从头到尾将stream读取成一个字符串形式的数据，并且返回 stream.Position = 0; StreamReader streamReader = new StreamReader(stream); return streamReader.ReadToEnd(); } //DataSetToJson public static string ToJSON(DataSet dataSet, IDictionary<string, IDictionary<string, string>> details) { string json = string.Empty; if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0) { int i = 0, j = 0; json += "["; foreach (DataRow row in dataSet.Tables[0].Rows) { if (i == 0) { } else { json += ","; } j = 0; json += "{"; foreach (DataColumn column in dataSet.Tables[0].Columns) { if (j == 0) { } else { json += ","; } if (details != null && details.ContainsKey(column.ColumnName)) { IDictionary<string, string> dict = details[column.ColumnName] as IDictionary<string, string>; if (dict != null && dict.ContainsKey(row[column].ToString())) json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), dict[row[column].ToString()]); else json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString()); } else json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString()); j++; } json += "}"; i++; } json += "]"; } //json = "{\"result\":\"" + json + "\"}"; return json; } } 网页Messagebox：
代码
public class MessageBox : System.Web.UI.Page { public MessageBox() { // // TODO: 在此处添加构造函数逻辑 // } public static void Show(System.Web.UI.Page page, string msg) { page.ClientScript.RegisterStartupScript(page.GetType(), "message", "<script language='javascript' defer>alert('" + msg.ToString() + "');</script>"); } public static void ShowAndRedirect(System.Web.UI.Page page, string msg, string url) { StringBuilder Builder = new StringBuilder(); Builder.Append("<script language='javascript' defer>"); Builder.AppendFormat("alert('{0}');", msg); Builder.AppendFormat("self.location.href='{0}'", url); Builder.Append("</script>"); page.ClientScript.RegisterStartupScript(page.GetType(), "message", Builder.ToString()); } /// <summary> /// 控件点击 消息确认提示框 /// </summary> /// <param name="page">当前页面指针，一般为this</param> /// <param name="msg">提示信息</param> public static void ShowConfirm(System.Web.UI.WebControls.WebControl Control, string msg) { //Control.Attributes.Add("onClick","if (!window.confirm('"+msg+"')){return false;}"); Control.Attributes.Add("onclick", "return confirm('" + msg + "');"); } /// <summary> /// 信息提示 /// </summary> /// <param name="mess"></param> //public virtual void Alert(string mess) //{ // ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script language = javascript>alert(\"提示:" + mess.Replace("\r\n", "") + "\")</script>"); //} } EXCEL操作类，包含动态EXCEL导入导出方法：
代码
public class ExcelM:Page { /// <summary> /// 导出Excel Datatable版本 /// </summary> /// <param name="dt">导出的Datatable</param> /// <param name="ExcelName">导出EXCEL的名称 不需要要带有扩展名_xls</param> public static void ExportExcelDT(DataTable dt, string Title) { HttpResponse resp = System.Web.HttpContext.Current.Response; string ExcelName = Title + DateTime.Now.ToString("yyyyMMddHHmmss"); resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls"); string colHeaders = "", ls_item = ""; DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = dt.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 resp.Write("<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head><body><table border=1><tr style=\"background-color:#000088; color:White;border: Gray 1px solid;text-align:center\">"); for (i = 0; i < cl; i++) { colHeaders += "<th>" + dt.Columns[i].Caption.ToString() + "</th>"; } resp.Write(colHeaders + "</tr>"); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 ls_item = "<tr bgcolor=#ABCDC1>"; for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { ls_item += "<td>" + row[i].ToString() + "</td></tr>"; } else { ls_item += "<td>" + row[i].ToString() + "</td>"; } } resp.Write(ls_item); } resp.Write("</table></body></html>"); resp.End(); } public enum eControl { GridView,Repeater} /// <summary> /// 控件导出EXCEL /// </summary> /// <param name="dataControl">控件名称</param> /// <param name="dt">要导出的Datatable数据</param> /// <param name="title">名称</param> /// <param name="Control">控件类型 GridView or Repeater</param> public static void ExportExcelDataControl(object dataControl, ref DataTable dt, string title, eControl Control) { HttpResponse Response = System.Web.HttpContext.Current.Response; StringWriter objStringWriter = new StringWriter(); HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter); if (Control == eControl.GridView) { GridView gvList = (GridView)dataControl; gvList.DataSource = dt; gvList.DataBind(); gvList.RenderControl(objHtmlTextWriter); } if (Control == eControl.Repeater) { Repeater rpList = (Repeater)dataControl; rpList.DataSource = dt; rpList.DataBind(); rpList.RenderControl(objHtmlTextWriter); } string style = @"<html><head><meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" /><style> .text { mso-number-format:\@; } </style></head><body>"; string filename = title + DateTime.Now.ToString("yyyyMMddHHmmss"); Response.Clear(); Response.Buffer = true; Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Response.ContentType = "application/ms-excel"; Response.Write(style); Response.Write(objStringWriter.ToString()); Response.Write("</body></html>"); Response.End(); } /// <summary> /// Gridview重载函数 /// </summary> /// <param name="control"></param> public override void VerifyRenderingInServerForm(System.Web.UI.Control control) { } private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString(); /// <summary> /// EXCEL导入到数据库指定表 需配置XML文件 /// tableName 即将导入的表名 /// OutColumn EXCEL中对应的列名 默认第一行为列名 /// TableColumn 数据库表中对应的列名 /// CType 导入列的数据类型 以数据库中为准 /// Clong 导入列的长度 /// </summary> /// <param name="filePath">上传EXCEL的路径</param> /// <param name="erroMsg">错误信息</param> public static void ExcelToTable(string filePath,out string erroMsg) { try { erroMsg = ""; DataTable dtExcel = GetExcelFileData(filePath); //过滤dtExcel 中的空行 for (int i = 0; i < dtExcel.Rows.Count; i++) { DataRow dr=dtExcel.Rows[i]; if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count-1)) { bool isd = true; for (int j = 1; j < dtExcel.Columns.Count - 1; j++) { if (dr.IsNull(j)) continue; else { isd = false; break; } } if (isd) dtExcel.Rows[i].Delete(); } } List<string> listC = new List<string>(); List<string> tableC = new List<string>(); Dictionary<string,string> Det=new Dictionary<string,string>(); HttpServerUtility server = System.Web.HttpContext.Current.Server; //此处XML 为网站根目录下的XML string path = server.MapPath("ImportExcel.xml"); XElement xmldoc = XElement.Load(path); string tableName = xmldoc.FirstAttribute.Value; if (UtilityClass.IsNullOrEmpty(tableName)) { erroMsg = "tableName不能为空！"; return; } var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q; foreach (var q in qOutColumn) { listC.Add(q.Value.Trim()); } var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q; foreach (var q in qTableColumn) { tableC.Add(q.Value.Trim()); } if (listC.Count != tableC.Count) { erroMsg = "OutColumn同TableColumn不是一一对应！"; return; } for(int i = 0; i < listC.Count; i++) { if (listC[i] != dtExcel.Columns[i].ColumnName.Trim()) { erroMsg = "OutColumn[" + listC[i] + "]与实际导入列名[" + dtExcel.Columns[i].ColumnName.Trim() + "]不一致"; return; } } for (int i = 0; i < listC.Count; i++) { Det.Add(listC[i],tableC[i]); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
{ for (int i = 0; i < listC.Count; i++) { bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]])); } bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(dtExcel); } } catch (Exception ex) { throw ex; } }
/// <summary> /// 导入检测EXCEL之后的Datatable /// EXCEL导入到数据库指定表 需配置XML文件 /// tableName 即将导入的表名 /// OutColumn EXCEL中对应的列名 默认第一行为列名 /// TableColumn 数据库表中对应的列名 /// CType 导入列的数据类型 以数据库中为准 /// Clong 导入列的长度 /// </summary> /// <param name="dtExcel">传入Datatable</param> /// <param name="erroMsg">错误信息</param> /// <param name="isGLNullColumn">是否需要过滤空行</param> public static void ExcelToTable(DataTable dtExcel, out string erroMsg,bool isGLNullColumn) { try { erroMsg = ""; //过滤dtExcel 中的空行 if (isGLNullColumn) { for (int i = 0; i < dtExcel.Rows.Count; i++) { DataRow dr = dtExcel.Rows[i]; if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count - 1)) { bool isd = true; for (int j = 1; j < dtExcel.Columns.Count - 1; j++) { if (dr.IsNull(j)) continue; else { isd = false; break; } } if (isd) dtExcel.Rows[i].Delete(); } } } List<string> listC = new List<string>(); List<string> tableC = new List<string>(); Dictionary<string, string> Det = new Dictionary<string, string>(); HttpServerUtility server = System.Web.HttpContext.Current.Server; //此处XML 为网站根目录下的XML string path = server.MapPath("ImportExcel.xml"); XElement xmldoc = XElement.Load(path); string tableName = xmldoc.FirstAttribute.Value; if (UtilityClass.IsNullOrEmpty(tableName)) { erroMsg = "tableName不能为空！"; return; } var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q; foreach (var q in qOutColumn) { listC.Add(q.Value.Trim()); } var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q; foreach (var q in qTableColumn) { tableC.Add(q.Value.Trim()); } if (listC.Count != tableC.Count) { erroMsg = "OutColumn同TableColumn不是一一对应！"; return; } for (int i = 0; i < listC.Count; i++) { if (listC[i] != dtExcel.Columns[i].ColumnName.Trim()) { erroMsg = "OutColumn与实际导入列名不一致"; return; } } for (int i = 0; i < listC.Count; i++) { Det.Add(listC[i], tableC[i]); } using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
{ for (int i = 0; i < listC.Count; i++) { bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]])); } bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(dtExcel); } } catch (Exception ex) { throw ex; } }
/// <summary> /// 读取Excel /// </summary> /// <param name="filePath">EXCEL 路径</param> /// <returns></returns> public static DataTable GetExcelFileData(string filePath) { OleDbDataAdapter oleAdp = new OleDbDataAdapter(); OleDbConnection oleCon = new OleDbConnection(); string strCon = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; try { DataTable dt = new DataTable(); oleCon.ConnectionString = strCon; oleCon.Open(); DataTable table = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = table.Rows[0][2].ToString(); string sqlStr = "Select * From [" + sheetName + "]"; oleAdp = new OleDbDataAdapter(sqlStr, oleCon); oleAdp.Fill(dt); oleCon.Close(); return dt; } catch (Exception ex) { throw ex; } finally { oleAdp = null; oleCon = null; } } |
|
|
来自： weiledream > 《我的图书馆》
