请选择 进入手机版 | 继续访问电脑版

马上加入IBC程序猿 各种源码随意下,各种教程随便看! 注册 每日签到 加入编程讨论群

C#教程 ASP.NET教程 C#视频教程程序源码享受不尽 C#技术求助 ASP.NET技术求助

【源码下载】 社群合作 申请版主 程序开发 【远程协助】 每天乐一乐 每日签到 【承接外包项目】 面试-葵花宝典下载

官方一群:

官方二群:

.net实现一个简单的通用查询数据、导出Excel的网页

[复制链接]
查看2571 | 回复0 | 2019-9-12 16:22:27 | 显示全部楼层 |阅读模式

配景:
临时提供一个简单的网页,供其他人浏览数据库(Oracel、MSSQL)的某些数据,并导出Excel。
支持在设置文件中随时添加或修改sql。

实现:
把sql语句等信息生存一个xml文件中,前端页面提供一个下拉框,可以选择差别类型的sql语句,查询结果(暂没分页须要)直接绑定到GridView。

162335l8c8wuwt8cq88v11.png

开发环境:VS2015
NuGet引入库:NPOI、Oracle.ManagedDataAccess.Client

一、VS目录

  1. ├─App_Code
  2. │ DataBase.cs ---------数据库连接类
  3. │ ExcelHelper.cs ---------导出Excel工具类
  4. │ SqlEntity.cs ---------sql语句的实体类
  5. │ SqlEntityList.cs---------把sql.xml转化为实体类
  6. ├─App_Data
  7. │ sql.xml ---------sql语句
  8. │ rpt.aspx ---------前端查询页面
  9. │ rpt.aspx.cs ---------
  10. │ Web.config ---------设置数据库连接信息
复制代码

二、代码
1、Web.config

162335wh1ubh1lhh2mah1s.gif
162335mtfgximphpghfspp.gif
  1. <connectionStrings>
  2. <add name="OracleString" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=testdb)));Persist Security Info=True;User ID=dev;Password=123456" providerName="Oracle.ManagedDataAccess.Client"/>
  3. <add name="SqlServerString" connectionString="user id=sa; password=123456; database=test; server=localhost" providerName="System.Data.SqlClient"/>
  4. </connectionStrings>
复制代码
数据库连接字符串

2、DataBase.cs

162336r8qb4ksid3sa33w3.gif
162336x36gfspnf0x60tps.gif
  1. using System;
  2. using System.Collections;
  3. using System.Collections.Generic;
  4. using System.Data;
  5. using System.Data.Common;
  6. using System.Data.SqlClient;
  7. using System.Linq;
  8. using System.Web;
  9. using System.Web.Configuration;
  10. /// <summary>
  11. /// DataBase 的择要阐明
  12. /// </summary>
  13. public class DataBase
  14. {
  15. private DbConnection cnn;//抽象类型
  16. private DbCommand cmd;//抽象类型
  17. private DbProviderFactory provider;
  18. private string providerName;
  19. public DataBase(String connectionName)
  20. {
  21. providerName = WebConfigurationManager.ConnectionStrings[connectionName].ProviderName;
  22. provider = DbProviderFactories.GetFactory(providerName);
  23. cnn = provider.CreateConnection();
  24. cnn.ConnectionString = WebConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
  25. cmd = provider.CreateCommand();
  26. cmd.Connection = cnn;
  27. }
  28. #region 实行不带参数的SQL语句
  29. /// <summary>
  30. /// 实行SQL语句,返回影响的纪录数
  31. /// </summary>
  32. public int ExecuteSQL(string sql)
  33. {
  34. return ExecuteSQL(sql, null);
  35. }
  36. /// <summary>
  37. /// 实行多条SQL语句,实现数据库事件。
  38. /// </summary>
  39. public int ExecuteSqlTran(List<string> sqlList)
  40. {
  41. int count = -1;
  42. cnn.Open();
  43. DbTransaction tx = cnn.BeginTransaction();
  44. try
  45. {
  46. cmd.Transaction = tx;
  47. for (int n = 0; n < sqlList.Count; n++)
  48. {
  49. string strsql = sqlList[n].ToString();
  50. if (strsql.Trim().Length > 1)
  51. {
  52. cmd.CommandText = strsql;
  53. count = cmd.ExecuteNonQuery();
  54. }
  55. }
  56. tx.Commit();
  57. }
  58. catch (SqlException e)
  59. {
  60. tx.Rollback();
  61. cnn.Close();
  62. throw new Exception(e.Message);
  63. }
  64. return count;
  65. }
  66. /// <summary>
  67. /// 实行一条盘算查询结果语句,返回查询结果(object)。
  68. /// </summary>
  69. public int ExecuteScalar(string sql)
  70. {
  71. return ExecuteScalar(sql, null);
  72. }
  73. /// <summary>
  74. /// 实行查询语句,返回DataSet
  75. /// </summary>
  76. public DataSet GetDataSet(string sql)
  77. {
  78. return GetDataSet(sql, null);
  79. }
  80. /// <summary>
  81. /// 实行查询语句,返回DataSet
  82. /// </summary>
  83. public DataTable GetDataTable(string sql)
  84. {
  85. return GetDataSet(sql).Tables[0];
  86. }
  87. /// <summary>
  88. /// 实行查询语句,返回DataReader(使用该方法牢记要手工关闭DataReader和连接)
  89. /// </summary>
  90. public DbDataReader ExecuteReader(string sql)
  91. {
  92. return ExecuteReader(sql, null);
  93. }
  94. #endregion
  95. #region 实行带参数的SQL语句
  96. /// <summary>
  97. /// 实行SQL语句,返回影响的纪录数
  98. /// </summary>
  99. public int ExecuteSQL(string sql, params DbParameter[] cmdParms)
  100. {
  101. try
  102. {
  103. CreateCommand(sql, cmdParms);
  104. int rows = cmd.ExecuteNonQuery();
  105. cmd.Parameters.Clear();
  106. return rows;
  107. }
  108. catch (SqlException e)
  109. {
  110. cnn.Close();
  111. throw new Exception(e.Message);
  112. }
  113. }
  114. /// <summary>
  115. /// 实行多条SQL语句,实现数据库事件。
  116. /// </summary>
  117. public int ExecuteSqlTran(Hashtable sqlList)
  118. {
  119. int count = -1;
  120. cnn.Open();
  121. DbTransaction tx = cnn.BeginTransaction();
  122. try
  123. {
  124. cmd.Transaction = tx;
  125. foreach (DictionaryEntry myDE in sqlList)
  126. {
  127. string cmdText = myDE.Key.ToString();
  128. DbParameter[] cmdParms = (DbParameter[])myDE.Value;
  129. CreateCommand(cmdText, cmdParms);
  130. count = cmd.ExecuteNonQuery();
  131. }
  132. tx.Commit();
  133. }
  134. catch (SqlException e)
  135. {
  136. tx.Rollback();
  137. cnn.Close();
  138. throw new Exception(e.Message);
  139. }
  140. return count;
  141. }
  142. /// <summary>
  143. /// 实行一条盘算查询结果语句,返回查询结果(object)。
  144. /// </summary>
  145. public int ExecuteScalar(string sql, params DbParameter[] cmdParms)
  146. {
  147. try
  148. {
  149. CreateCommand(sql, cmdParms);
  150. object o = cmd.ExecuteScalar();
  151. return int.Parse(o.ToString());
  152. }
  153. catch (SqlException e)
  154. {
  155. cnn.Close();
  156. throw new Exception(e.Message);
  157. }
  158. }
  159. /// <summary>
  160. /// 实行查询语句,返回DataSet
  161. /// </summary>
  162. public DataSet GetDataSet(string sql, params DbParameter[] cmdParms)
  163. {
  164. DataSet ds = new DataSet();
  165. try
  166. {
  167. CreateCommand(sql, cmdParms);
  168. DbDataAdapter adapter = provider.CreateDataAdapter();
  169. adapter.SelectCommand = cmd;
  170. adapter.Fill(ds);
  171. }
  172. catch (SqlException e)
  173. {
  174. cnn.Close();
  175. throw new Exception(e.Message);
  176. }
  177. return ds;
  178. }
  179. /// <summary>
  180. /// 实行查询语句,返回DataTable
  181. /// </summary>
  182. public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
  183. {
  184. return GetDataSet(sql, cmdParms).Tables[0];
  185. }
  186. /// <summary>
  187. /// 实行查询语句,返回DataReader(使用该方法牢记要手工关闭DataReader和连接)
  188. /// </summary>
  189. public DbDataReader ExecuteReader(string sql, params DbParameter[] cmdParms)
  190. {
  191. try
  192. {
  193. CreateCommand(sql, cmdParms);
  194. DbDataReader myReader = cmd.ExecuteReader();
  195. return myReader;
  196. }
  197. catch (SqlException e)
  198. {
  199. cnn.Close();
  200. throw new Exception(e.Message);
  201. }
  202. }
  203. public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, object Value)
  204. {
  205. DbParameter Param = cmd.CreateParameter();
  206. Param.ParameterName = ParamName;
  207. Param.DbType = DbType;
  208. if (Size > 0)
  209. Param.Size = Size;
  210. if (Value != null)
  211. Param.Value = Value;
  212. return Param;
  213. }
  214. private DbCommand CreateCommand(string cmdText, DbParameter[] Prams)
  215. {
  216. return CreateCommand(CommandType.Text, cmdText, Prams);
  217. }
  218. private DbCommand CreateCommand(CommandType cmdType, string cmdText, DbParameter[] Prams)
  219. {
  220. if (cnn.State != ConnectionState.Open)
  221. cnn.Open();
  222. cmd.CommandType = cmdType;
  223. cmd.CommandText = cmdText;
  224. if (Prams != null)
  225. {
  226. cmd.Parameters.Clear();
  227. foreach (DbParameter Parameter in Prams)
  228. cmd.Parameters.Add(Parameter);
  229. }
  230. return cmd;
  231. }
  232. public DataSet GetDataSetByProc(string ProcName, DbParameter[] Params)
  233. {
  234. cnn.Open();
  235. DbCommand cmd = CreateCommand(CommandType.StoredProcedure, ProcName, Params);
  236. DbDataAdapter adapter = provider.CreateDataAdapter();
  237. adapter.SelectCommand = cmd;
  238. DataSet ds = new DataSet();
  239. adapter.Fill(ds);
  240. cnn.Close();
  241. return ds;
  242. }
  243. #endregion
  244. }
复制代码
数据库连接类

3、ExcelHelper.cs

162336paczyd8r901pj0nj.gif
162336tp00izoj1kk190n2.gif
  1. using NPOI.SS.UserModel;
  2. using NPOI.XSSF.UserModel;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.IO;
  7. using System.Linq;
  8. using System.Text;
  9. using System.Web;
  10. /// <summary>
  11. /// ExcelHelper 的择要阐明
  12. /// </summary>
  13. public class ExcelHelper
  14. {
  15. public static void ExportXlsx(DataTable dt, string strFileName)
  16. {
  17. HttpContext curContext = HttpContext.Current;
  18. MemoryStream ms = ExportXlsx(dt);
  19. curContext.Response.AppendHeader("Content-Disposition",
  20. "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
  21. curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
  22. curContext.Response.ContentEncoding = Encoding.UTF8;
  23. curContext.Response.BinaryWrite(ms.ToArray());
  24. ms.Close();
  25. ms.Dispose();
  26. curContext.Response.End();
  27. }
  28. private static MemoryStream ExportXlsx(DataTable dt)
  29. {
  30. XSSFWorkbook workbook = new XSSFWorkbook();
  31. ISheet sheet = null;
  32. int headRowIndex = 0;
  33. string sheetName = "Sheet1";
  34. if (!string.IsNullOrEmpty(dt.TableName))
  35. {
  36. sheetName = dt.TableName;
  37. }
  38. sheet = workbook.CreateSheet(sheetName);
  39. int rowIndex = 0;
  40. XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
  41. ICellStyle headStyle = workbook.CreateCellStyle();
  42. headStyle.Alignment = HorizontalAlignment.Center;
  43. IFont font = workbook.CreateFont();
  44. font.FontHeightInPoints = 10;
  45. font.Boldweight = 700;
  46. headStyle.SetFont(font);
  47. foreach (DataColumn column in dt.Columns)
  48. {
  49. headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  50. headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
  51. }
  52. foreach (DataRow row in dt.Rows)
  53. {
  54. rowIndex++;
  55. XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
  56. foreach (DataColumn column in dt.Columns)
  57. {
  58. dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
  59. }
  60. }
  61. MemoryStream ms = new MemoryStream();
  62. workbook.Write(ms);
  63. ms.Flush();
  64. return ms;
  65. }
  66. }
复制代码
导出Excel工具类

4、sql.xml

162336nmd4sdydinbk33kv.gif
162336zd85s19g1bscgyp0.gif
  1. <?xml version="1.0" encoding="utf-8" ?>
  2. <sql>
  3. <item id="1" text="报表1" dbConnName="OracleString">
  4. select * from tb
  5. </item>
  6. <item id="2" text="报表2" dbConnName="SqlServerString">
  7. select * from tb
  8. </item>
  9. </sql>
复制代码
生存sql语句等信息的xml

5、SqlEntity.cs

162336lmqqqfn7wm7qp8kk.gif
162336gb9xp48f0xs8biib.gif
  1. public class SqlEntity
  2. {
  3. public SqlEntity()
  4. {
  5. }
  6. public int Id { get; set; }
  7. public string text { get; set; }
  8. public string sql { get; set; }
  9. public string dbConnName { get; set; }
  10. }
复制代码
实体类

6、SqlEntityList.cs

162336aomcb7qyq36nwjb2.gif
162337ifx0w2xxwuo0f08z.gif
  1. public class SqlEntityList
  2. {
  3. public List<SqlEntity> GetXmlData(String xmlPath)
  4. {
  5. var list = new List<SqlEntity>();
  6. XmlDocument xmlDoc = new XmlDocument();
  7. xmlDoc.Load(xmlPath);
  8. XmlNodeList xnl = xmlDoc.SelectSingleNode("sql").ChildNodes;
  9. for (int i = 0; i < xnl.Count; i++)
  10. {
  11. int id = int.Parse(xnl.Item(i).Attributes["id"].Value);
  12. string text = xnl.Item(i).Attributes["text"].Value;
  13. string dbConnName = xnl.Item(i).Attributes["dbConnName"].Value;
  14. string sql = xnl.Item(i).InnerText;
  15. var model = new SqlEntity()
  16. {
  17. Id = id,
  18. text = text,
  19. dbConnName = dbConnName.ToLower(),
  20. sql = sql
  21. };
  22. list.Add(model);
  23. }
  24. return list;
  25. }
  26. }
复制代码
xml内容转实体

7、rpt.aspx

162337ala98akxkd8hxkxz.gif
162337fu399p49js3ad63t.gif
  1. <div>
  2. <asp:DropDownList ID="ddlType" DataTextField="text" DataValueField="id" runat="server">
  3. </asp:DropDownList>
  4. <asp:Button runat="server" ID="btnQuery" Text="查询" OnClick="btnQuery_Click"/>
  5. <asp:Literal runat="server" ID="ltlInfo"></asp:Literal>
  6. <asp:Button runat="server" ID="btnExport" Text="导出" OnClick="btnExport_Click" />
  7. </div>
  8. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" Width="100%"></asp:GridView>
复制代码
前端页

8、rpt.aspx.cs

162337qa8aozdmm0agihwg.gif
162337mqqyynh6t2nhyml2.gif
  1. protected void Page_Load(object sender, EventArgs e)
  2. {
  3. if (!IsPostBack)
  4. {
  5. ddlType.DataSource = getSqlList();
  6. ddlType.DataBind();
  7. }
  8. }
  9. private List<SqlEntity> getSqlList()
  10. {
  11. String path = Server.MapPath("~/App_Data/sql.xml");
  12. SqlEntityList sqlEntityList = new SqlEntityList();
  13. List<SqlEntity> list = sqlEntityList.GetXmlData(path);
  14. return list;
  15. }
  16. private DataSet getDataSet(int type)
  17. {
  18. DataSet ds = new DataSet();
  19. List <SqlEntity> list = getSqlList();
  20. var m = list.FirstOrDefault(t => t.Id == type);
  21. DataBase db = new DataBase(m.dbConnName);
  22. ds = db.GetDataSet(m.sql);
  23. ltlInfo.Text = "纪录数:" + ds.Tables[0].Rows.Count.ToString();
  24. return ds;
  25. }
  26. private void BindData(DataSet ds)
  27. {
  28. GridView1.DataSource = ds;
  29. GridView1.DataBind();
  30. }
  31. protected void btnQuery_Click(object sender, EventArgs e)
  32. {
  33. int type = int.Parse(ddlType.SelectedValue);
  34. DataSet ds = getDataSet(type);
  35. BindData(ds);
  36. }
  37. protected void btnExport_Click(object sender, EventArgs e)
  38. {
  39. int type = int.Parse(ddlType.SelectedValue);
  40. DataSet ds = getDataSet(type);
  41. DataTable dt = ds.Tables[0];
  42. String fileName = ddlType.SelectedItem.Text;
  43. ExcelHelper.ExportXlsx(dt, fileName);
  44. }
复制代码
前端页的背景代码







来源:https://www.cnblogs.com/gdjlc/p/11498153.html
C#论坛 www.ibcibc.com IBC编程社区
C#
C#论坛
IBC编程社区
*滑块验证:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则