ASP.NET 之 常用类、方法的超级总结，并包含动态的EXCEL导入导出功能，奉上类库源码

         最近闲了，花点几天时间将项目中常用的一些类、方法做了一下总结，希望对大家有用。

 

         实用类：UtilityClass 包含如下方法

 

         判断对象是否为空或NULL,如果是空或NULL返回true，否则返回false

         验证手机号是否正确 13,15,18

         验证邮箱

         验证网址

         MD5加密,返回32位的字符串

         DES加密/解密方法

         把字符串的第一个字符变为大写

         判断一个字符串是否是数字

         判断一个字符串是否是时间

         判断一个字符串是否是decimal类型

         生成随机数方法 小于9位

         获取网站配置ConfigurationManager_AppSettings键值

         检查某个文件是否存在于磁盘上，存在--true，不存在--false

         在服务器上创建文件夹

         转换相对路径为物理路径

         两个值的百分比例

         截取字符枚举值

         获取指定长度的字符串

         地址栏传值加密/解密

         获得当前页面客户端的IP

         格式化要显示的内容，主要用于在网页上显示由textarea产生的内容

         判断当前访问是否来自浏览器软件

         判断当前访问是否来自非IE浏览器软件

 

     SQL操作类：DBAccess 包含如下方法  

     

 1 public class DBAccess
 2     {
 3         private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
 4 
 5         // ExecuteNonQuery
 6         public static int ExecuteNonQuery(string commandText)
 7         {
 8             return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText);
 9         }
10         public static int ExecuteNonQuery(string commandText, params SqlParameter[] cmdParameters)
11         {
12             return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.Text, commandText, cmdParameters);
13         }
14         /// <summary>
15         /// 执行存储过程 
16         /// </summary>
17         /// <param name="procName"></param>
18         /// <param name="cmdParameters"></param>
19         /// <returns></returns>
20         public static int ExecuteNonQueryProc(string procName,params SqlParameter[] cmdParameters)
21         {
22             return Sqlhelper.ExecuteNonQuery(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
23 
24         }
25 
26         // ExecuteDataset
27         public static DataSet ExecuteDataset(string commandText)
28         {
29             return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText);
30         }
31         public static DataSet ExecuteDataset(string commandText, params SqlParameter[] cmdParameters)
32         {
33             return Sqlhelper.ExecuteDataset(_connectionString, CommandType.Text, commandText, cmdParameters);
34         }
35         /// <summary>
36         /// 执行存储过程 
37         /// </summary>
38         /// <param name="procName"></param>
39         /// <param name="cmdParameters"></param>
40         /// <returns></returns>
41         public static DataSet ExecuteDatasetProc(string procName, params SqlParameter[] cmdParameters)
42         {
43             return Sqlhelper.ExecuteDataset(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
44         }
45 
46         // ExecuteReader
47         public static SqlDataReader ExecuteReader(string commandText)
48         {
49             return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText);
50         }
51         public static SqlDataReader ExecuteReader(string commandText, params SqlParameter[] cmdParameters)
52         {
53             return Sqlhelper.ExecuteReader(_connectionString, CommandType.Text, commandText, cmdParameters);
54         }
55         /// <summary>
56         /// 执行存储过程 
57         /// </summary>
58         /// <param name="procName"></param>
59         /// <param name="cmdParameters"></param>
60         /// <returns></returns>
61         public static SqlDataReader ExecuteReaderProc(string procName, params SqlParameter[] cmdParameters)
62         {
63             return Sqlhelper.ExecuteReader(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
64         }
65 
66         // ExecuteScalar
67         public static object ExecuteScalar(string commandText)
68         {
69             return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText);
70         }
71         public static object ExecuteScalar(string commandText, params SqlParameter[] cmdParameters)
72         {
73             return Sqlhelper.ExecuteScalar(_connectionString, CommandType.Text, commandText, cmdParameters);
74         }
75         /// <summary>
76         /// 执行存储过程 
77         /// </summary>
78         /// <param name="procName"></param>
79         /// <param name="cmdParameters"></param>
80         /// <returns></returns>
81         public static object ExecuteScalarProc(string procName, params SqlParameter[] cmdParameters)
82         {
83             return Sqlhelper.ExecuteScalar(_connectionString, CommandType.StoredProcedure, procName, cmdParameters);
84         }
85     }

 

   

        Json操作类：JsonHelper包含如下方法  

 

 1  /* 
 2            添加引用 System.Runtime.Serialization 
 3            添加引用 System.ServiceModel.Web
 4          */
 5     public static class JsonHelper
 6     {
 7         /// <summary>
 8         /// 格式化成Json字符串
 9         /// </summary>
10         /// <param name="obj">需要格式化的对象</param>
11         /// <returns>Json字符串</returns>
12         public static string ToJson(this object obj)
13         {
14             // 首先，当然是JSON序列化
15             DataContractJsonSerializer serializer = new DataContractJsonSerializer(obj.GetType());
16 
17             // 定义一个stream用来存发序列化之后的内容
18             Stream stream = new MemoryStream();
19             serializer.WriteObject(stream, obj);
20 
21             // 从头到尾将stream读取成一个字符串形式的数据，并且返回
22             stream.Position = 0;
23             StreamReader streamReader = new StreamReader(stream);
24             return streamReader.ReadToEnd();
25         }
26         //DataSetToJson
27         public static string ToJSON(DataSet dataSet, IDictionary<string, IDictionary<string, string>> details)
28         {
29             string json = string.Empty;
30             if (dataSet != null && dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0)
31             {
32                 int i = 0, j = 0;
33                 json += "[";
34                 foreach (DataRow row in dataSet.Tables[0].Rows)
35                 {
36                     if (i == 0) { } else { json += ","; }
37                     j = 0;
38                     json += "{";
39                     foreach (DataColumn column in dataSet.Tables[0].Columns)
40                     {
41                         if (j == 0) { } else { json += ","; }
42 
43                         if (details != null && details.ContainsKey(column.ColumnName))
44                         {
45                             IDictionary<string, string> dict = details[column.ColumnName] as IDictionary<string, string>;
46 
47                             if (dict != null && dict.ContainsKey(row[column].ToString()))
48                                 json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), dict[row[column].ToString()]);
49                             else
50                                 json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString());
51                         }
52                         else
53                             json += string.Format("'{0}':'{1}'", column.ColumnName.ToLower(), row[column].ToString());
54 
55                         j++;
56                     }
57                     json += "}";
58                     i++;
59                 }
60                 json += "]";
61             }
62 
63             //json = "{\"result\":\"" + json + "\"}";
64             return json;
65         }
66     }

 

 

       网页Messagebox： 

 

 1 public class MessageBox : System.Web.UI.Page
 2     {
 3         public MessageBox()
 4         {
 5             //
 6             // TODO: 在此处添加构造函数逻辑
 7             //
 8         }
 9         public static void Show(System.Web.UI.Page page, string msg)
10         {
11 
12             page.ClientScript.RegisterStartupScript(page.GetType(), "message", "<script language='javascript' defer>alert('" + msg.ToString() + "');</script>");
13 
14         }
15 
16         public static void ShowAndRedirect(System.Web.UI.Page page, string msg, string url)
17         {
18             StringBuilder Builder = new StringBuilder();
19 
20             Builder.Append("<script language='javascript' defer>");
21             Builder.AppendFormat("alert('{0}');", msg);
22             Builder.AppendFormat("self.location.href='{0}'", url);
23             Builder.Append("</script>");
24             page.ClientScript.RegisterStartupScript(page.GetType(), "message", Builder.ToString());
25 
26         }
27         /// <summary>
28         /// 控件点击 消息确认提示框
29         /// </summary>
30         /// <param name="page">当前页面指针，一般为this</param>
31         /// <param name="msg">提示信息</param>
32         public static void ShowConfirm(System.Web.UI.WebControls.WebControl Control, string msg)
33         {
34             //Control.Attributes.Add("onClick","if (!window.confirm('"+msg+"')){return false;}");
35             Control.Attributes.Add("onclick", "return confirm('" + msg + "');");
36         }
37         /// <summary>
38         /// 信息提示
39         /// </summary>
40         /// <param name="mess"></param>
41         //public virtual void Alert(string mess)
42         //{
43         //    ClientScript.RegisterStartupScript(this.GetType(), "Alert", "<script language = javascript>alert(\"提示:" + mess.Replace("\r\n", "") + "\")</script>");
44         //}
45     }

 

 

        EXCEL操作类，包含动态EXCEL导入导出方法：

 

  1 public class ExcelM:Page
  2     {
  3         /// <summary>
  4         /// 导出Excel Datatable版本
  5         /// </summary>
  6         /// <param name="dt">导出的Datatable</param>
  7         /// <param name="ExcelName">导出EXCEL的名称 不需要要带有扩展名_xls</param>
  8         public static void ExportExcelDT(DataTable dt, string Title)
  9         {
 10             HttpResponse resp = System.Web.HttpContext.Current.Response;
 11             string ExcelName = Title + DateTime.Now.ToString("yyyyMMddHHmmss");
 12             resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
 13             resp.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls");
 14             string colHeaders = "", ls_item = "";
 15             DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
 16             int i = 0;
 17             int cl = dt.Columns.Count;
 18             //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
 19             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\">");
 20             for (i = 0; i < cl; i++)
 21             {
 22                 colHeaders += "<th>" + dt.Columns[i].Caption.ToString() + "</th>";
 23             }
 24             resp.Write(colHeaders + "</tr>");
 25             //向HTTP输出流中写入取得的数据信息
 26             //逐行处理数据
 27             foreach (DataRow row in myRow)
 28             {
 29                 //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据  
 30                 ls_item = "<tr bgcolor=#ABCDC1>";
 31                 for (i = 0; i < cl; i++)
 32                 {
 33                     if (i == (cl - 1))//最后一列，加n
 34                     {
 35                         ls_item += "<td>" + row[i].ToString() + "</td></tr>";
 36                     }
 37                     else
 38                     {
 39                         ls_item += "<td>" + row[i].ToString() + "</td>";
 40                     }
 41                 }
 42                 resp.Write(ls_item);
 43             }
 44             resp.Write("</table></body></html>");
 45             resp.End();
 46         }
 47         public enum eControl { GridView,Repeater}
 48         /// <summary>
 49         /// 控件导出EXCEL 
 50         /// </summary>
 51         /// <param name="dataControl">控件名称</param>
 52         /// <param name="dt">要导出的Datatable数据</param>
 53         /// <param name="title">名称</param>
 54         /// <param name="Control">控件类型 GridView or Repeater</param>
 55         public static void ExportExcelDataControl(object dataControl, ref DataTable dt, string title, eControl Control)
 56         {
 57             HttpResponse Response = System.Web.HttpContext.Current.Response;
 58             StringWriter objStringWriter = new StringWriter();
 59             HtmlTextWriter objHtmlTextWriter = new HtmlTextWriter(objStringWriter);
 60 
 61             if (Control == eControl.GridView)
 62             {
 63                 GridView gvList = (GridView)dataControl;
 64                 gvList.DataSource = dt;
 65                 gvList.DataBind();
 66                 gvList.RenderControl(objHtmlTextWriter);
 67             }
 68             if (Control == eControl.Repeater)
 69             {
 70                 Repeater rpList = (Repeater)dataControl;
 71                 rpList.DataSource = dt;
 72                 rpList.DataBind();
 73                 rpList.RenderControl(objHtmlTextWriter);
 74             }
 75             string style = @"<html><head><meta http-equiv=""Content-Type"" content=""text/html; charset=utf-8"" /><style> .text { mso-number-format:\@; } </style></head><body>";
 76             string filename = title + DateTime.Now.ToString("yyyyMMddHHmmss");
 77             Response.Clear();
 78             Response.Buffer = true;
 79             Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
 80             Response.ContentType = "application/ms-excel";
 81             Response.Write(style);
 82             Response.Write(objStringWriter.ToString());
 83             Response.Write("</body></html>");
 84             Response.End();
 85         }
 86         /// <summary>
 87         /// Gridview重载函数
 88         /// </summary>
 89         /// <param name="control"></param>
 90         public override void VerifyRenderingInServerForm(System.Web.UI.Control control) { }
 91         private static readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ToString();
 92         /// <summary>
 93         /// EXCEL导入到数据库指定表 需配置XML文件
 94         /// tableName 即将导入的表名
 95         /// OutColumn EXCEL中对应的列名 默认第一行为列名
 96         /// TableColumn 数据库表中对应的列名
 97         /// CType 导入列的数据类型 以数据库中为准
 98         /// Clong 导入列的长度
 99         /// </summary>
100         /// <param name="filePath">上传EXCEL的路径</param>
101         /// <param name="erroMsg">错误信息</param>
102         public static void ExcelToTable(string filePath,out string erroMsg)
103         {
104             try
105             {
106                 erroMsg = "";
107                 DataTable dtExcel = GetExcelFileData(filePath);
108                 //过滤dtExcel 中的空行
109                 for (int i = 0; i < dtExcel.Rows.Count; i++)
110                 {
111                     DataRow dr=dtExcel.Rows[i];
112                     if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count-1))
113                     {
114                         bool isd = true;
115                         for (int j = 1; j < dtExcel.Columns.Count - 1; j++)
116                         {
117                             if (dr.IsNull(j))
118                                 continue;
119                             else
120                             {
121                                 isd = false;
122                                 break;
123                             }
124                         }
125                         if (isd)
126                             dtExcel.Rows[i].Delete();
127                     }
128                 }
129                 List<string> listC = new List<string>();
130                 List<string> tableC = new List<string>();     
131                 Dictionary<string,string> Det=new Dictionary<string,string>();
132                 HttpServerUtility server = System.Web.HttpContext.Current.Server;
133                 //此处XML 为网站根目录下的XML
134                 string path = server.MapPath("ImportExcel.xml");
135                 XElement xmldoc = XElement.Load(path);
136                 string tableName = xmldoc.FirstAttribute.Value;
137                 if (UtilityClass.IsNullOrEmpty(tableName))
138                 {
139                     erroMsg = "tableName不能为空！";
140                     return;
141                 }
142                 var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q;
143                 foreach (var q in qOutColumn)
144                 {
145                     listC.Add(q.Value.Trim());
146                 }
147                 var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q;
148                 foreach (var q in qTableColumn)
149                 {
150                     tableC.Add(q.Value.Trim());
151                 }
152                 if (listC.Count != tableC.Count)
153                 {
154                     erroMsg = "OutColumn同TableColumn不是一一对应！";
155                     return;
156                 }
157                 for(int i = 0; i < listC.Count; i++)
158                 {
159                     if (listC[i] != dtExcel.Columns[i].ColumnName.Trim())
160                     {
161                         erroMsg = "OutColumn[" + listC[i] + "]与实际导入列名[" + dtExcel.Columns[i].ColumnName.Trim() + "]不一致";
162                         return;
163                     }
164                 }
165                 for (int i = 0; i < listC.Count; i++)
166                 {
167                     Det.Add(listC[i],tableC[i]);
168                 }
169 
170                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
171                 {
172                     for (int i = 0; i < listC.Count; i++)
173                     {
174                         bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]]));
175                     }
176                     bulkCopy.DestinationTableName = tableName;
177                     bulkCopy.WriteToServer(dtExcel);
178                 }
179             }
180             catch (Exception ex)
181             {
182                 throw ex;
183             }
184 
185         }
186         /// <summary>
187         /// 导入检测EXCEL之后的Datatable
188         /// EXCEL导入到数据库指定表 需配置XML文件
189         /// tableName 即将导入的表名
190         /// OutColumn EXCEL中对应的列名 默认第一行为列名
191         /// TableColumn 数据库表中对应的列名
192         /// CType 导入列的数据类型 以数据库中为准
193         /// Clong 导入列的长度
194         /// </summary>
195         /// <param name="dtExcel">传入Datatable</param>
196         /// <param name="erroMsg">错误信息</param>
197         /// <param name="isGLNullColumn">是否需要过滤空行</param>
198         public static void ExcelToTable(DataTable dtExcel, out string erroMsg,bool isGLNullColumn)
199         {
200             try
201             {
202                 erroMsg = "";
203                 //过滤dtExcel 中的空行
204                 if (isGLNullColumn)
205                 {
206                     for (int i = 0; i < dtExcel.Rows.Count; i++)
207                     {
208                         DataRow dr = dtExcel.Rows[i];
209                         if (dr.IsNull(0) && dr.IsNull(dtExcel.Columns.Count - 1))
210                         {
211                             bool isd = true;
212                             for (int j = 1; j < dtExcel.Columns.Count - 1; j++)
213                             {
214                                 if (dr.IsNull(j))
215                                     continue;
216                                 else
217                                 {
218                                     isd = false;
219                                     break;
220                                 }
221                             }
222                             if (isd)
223                                 dtExcel.Rows[i].Delete();
224                         }
225                     }
226                 }
227                 List<string> listC = new List<string>();
228                 List<string> tableC = new List<string>();
229                 Dictionary<string, string> Det = new Dictionary<string, string>();
230                 HttpServerUtility server = System.Web.HttpContext.Current.Server;
231                 //此处XML 为网站根目录下的XML
232                 string path = server.MapPath("ImportExcel.xml");
233                 XElement xmldoc = XElement.Load(path);
234                 string tableName = xmldoc.FirstAttribute.Value;
235                 if (UtilityClass.IsNullOrEmpty(tableName))
236                 {
237                     erroMsg = "tableName不能为空！";
238                     return;
239                 }
240                 var qOutColumn = from q in xmldoc.Descendants("OutColumn") select q;
241                 foreach (var q in qOutColumn)
242                 {
243                     listC.Add(q.Value.Trim());
244                 }
245                 var qTableColumn = from q in xmldoc.Descendants("TableColumn") select q;
246                 foreach (var q in qTableColumn)
247                 {
248                     tableC.Add(q.Value.Trim());
249                 }
250                 if (listC.Count != tableC.Count)
251                 {
252                     erroMsg = "OutColumn同TableColumn不是一一对应！";
253                     return;
254                 }
255                 for (int i = 0; i < listC.Count; i++)
256                 {
257                     if (listC[i] != dtExcel.Columns[i].ColumnName.Trim())
258                     {
259                         erroMsg = "OutColumn与实际导入列名不一致";
260                         return;
261                     }
262                 }
263                 for (int i = 0; i < listC.Count; i++)
264                 {
265                     Det.Add(listC[i], tableC[i]);
266                 }
267 
268                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(_connectionString))
269                 {
270                     for (int i = 0; i < listC.Count; i++)
271                     {
272                         bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(listC[i], Det[listC[i]]));
273                     }
274                     bulkCopy.DestinationTableName = tableName;
275                     bulkCopy.WriteToServer(dtExcel);
276                 }
277             }
278             catch (Exception ex)
279             {
280                 throw ex;
281             }
282 
283         }
284         /// <summary>
285         /// 读取Excel
286         /// </summary>
287         /// <param name="filePath">EXCEL 路径</param>
288         /// <returns></returns>
289         public static DataTable GetExcelFileData(string filePath)
290         {
291             OleDbDataAdapter oleAdp = new OleDbDataAdapter();
292             OleDbConnection oleCon = new OleDbConnection();
293             string strCon = "Provider=Microsoft.Jet.oleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
294             try
295             {
296                 DataTable dt = new DataTable();
297                 oleCon.ConnectionString = strCon;
298                 oleCon.Open();
299                 DataTable table = oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
300                 string sheetName = table.Rows[0][2].ToString();
301                 string sqlStr = "Select * From [" + sheetName + "]";
302                 oleAdp = new OleDbDataAdapter(sqlStr, oleCon);
303                 oleAdp.Fill(dt);
304                 oleCon.Close();
305                 return dt;
306             }
307             catch (Exception ex)
308             {
309                 throw ex;
310             }
311             finally
312             {
313                 oleAdp = null;
314                 oleCon = null;
315             }
316         }
317     }

 

 

          类库下载请点这里

          

          暂时分享这么多，其中有些方法可以根据需要进行修改，同时期待诸位有所补充。