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 }
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 }
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 }
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 }
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 }
类库下载请点这里
暂时分享这么多，其中有些方法可以根据需要进行修改，同时期待诸位有所补充。
作者：Frederick Yang
出处：http://www.cnblogs.com/yangtongnet/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
出处：http://www.cnblogs.com/yangtongnet/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
分类: Asp.Net技术浅谈
清空回声
