.net实现一个简单的通用查询数据、导出Excel的网页
<p>配景：<br />临时提供一个简单的网页，供其他人浏览数据库（Oracel、MSSQL）的某些数据，并导出Excel。<br />支持在设置文件中随时添加或修改sql。</p><p> </p>
<p>实现：<br />把sql语句等信息生存一个xml文件中，前端页面提供一个下拉框，可以选择差别类型的sql语句，查询结果（暂没分页须要）直接绑定到GridView。</p>
<p><div align="center"></div></p>
<p> </p>
<p> </p>
<p>开发环境：VS2015<br />NuGet引入库：NPOI、Oracle.ManagedDataAccess.Client</p>
<p>一、VS目录</p>
├─App_Code
│ DataBase.cs ---------数据库连接类
│ ExcelHelper.cs ---------导出Excel工具类
│ SqlEntity.cs ---------sql语句的实体类
│ SqlEntityList.cs---------把sql.xml转化为实体类
│
├─App_Data
│ sql.xml ---------sql语句
│
│rpt.aspx ---------前端查询页面
│rpt.aspx.cs---------
│Web.config ---------设置数据库连接信息
<p>二、代码<br />1、Web.config</p>
<div align="center"></div><div align="center"></div>
<connectionStrings>
<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"/>
<add name="SqlServerString" connectionString="user id=sa; password=123456; database=test; server=localhost" providerName="System.Data.SqlClient"/>
</connectionStrings>
数据库连接字符串
<p>2、DataBase.cs</p>
<div align="center"></div><div align="center"></div>
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;
/// <summary>
/// DataBase 的择要阐明
/// </summary>
public class DataBase
{
private DbConnection cnn;//抽象类型
private DbCommand cmd;//抽象类型
private DbProviderFactory provider;
private string providerName;
public DataBase(String connectionName)
{
providerName = WebConfigurationManager.ConnectionStrings.ProviderName;
provider = DbProviderFactories.GetFactory(providerName);
cnn = provider.CreateConnection();
cnn.ConnectionString = WebConfigurationManager.ConnectionStrings.ConnectionString;
cmd = provider.CreateCommand();
cmd.Connection = cnn;
}
#region 实行不带参数的SQL语句
/// <summary>
/// 实行SQL语句，返回影响的纪录数
/// </summary>
public int ExecuteSQL(string sql)
{
return ExecuteSQL(sql, null);
}
/// <summary>
/// 实行多条SQL语句，实现数据库事件。
/// </summary>
public int ExecuteSqlTran(List<string> sqlList)
{
int count = -1;
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
try
{
cmd.Transaction = tx;
for (int n = 0; n < sqlList.Count; n++)
{
string strsql = sqlList.ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count = cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (SqlException e)
{
tx.Rollback();
cnn.Close();
throw new Exception(e.Message);
}
return count;
}
/// <summary>
/// 实行一条盘算查询结果语句，返回查询结果（object）。
/// </summary>
public int ExecuteScalar(string sql)
{
return ExecuteScalar(sql, null);
}
/// <summary>
/// 实行查询语句，返回DataSet
/// </summary>
public DataSet GetDataSet(string sql)
{
return GetDataSet(sql, null);
}
/// <summary>
/// 实行查询语句，返回DataSet
/// </summary>
public DataTable GetDataTable(string sql)
{
return GetDataSet(sql).Tables;
}
/// <summary>
/// 实行查询语句，返回DataReader(使用该方法牢记要手工关闭DataReader和连接)
/// </summary>
public DbDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, null);
}
#endregion
#region 实行带参数的SQL语句
/// <summary>
/// 实行SQL语句，返回影响的纪录数
/// </summary>
public int ExecuteSQL(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 实行多条SQL语句，实现数据库事件。
/// </summary>
public int ExecuteSqlTran(Hashtable sqlList)
{
int count = -1;
cnn.Open();
DbTransaction tx = cnn.BeginTransaction();
try
{
cmd.Transaction = tx;
foreach (DictionaryEntry myDE in sqlList)
{
string cmdText = myDE.Key.ToString();
DbParameter[] cmdParms = (DbParameter[])myDE.Value;
CreateCommand(cmdText, cmdParms);
count = cmd.ExecuteNonQuery();
}
tx.Commit();
}
catch (SqlException e)
{
tx.Rollback();
cnn.Close();
throw new Exception(e.Message);
}
return count;
}
/// <summary>
/// 实行一条盘算查询结果语句，返回查询结果（object）。
/// </summary>
public int ExecuteScalar(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
object o = cmd.ExecuteScalar();
return int.Parse(o.ToString());
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 实行查询语句，返回DataSet
/// </summary>
public DataSet GetDataSet(string sql, params DbParameter[] cmdParms)
{
DataSet ds = new DataSet();
try
{
CreateCommand(sql, cmdParms);
DbDataAdapter adapter = provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
return ds;
}
/// <summary>
/// 实行查询语句，返回DataTable
/// </summary>
public DataTable GetDataTable(string sql, params DbParameter[] cmdParms)
{
return GetDataSet(sql, cmdParms).Tables;
}
/// <summary>
/// 实行查询语句，返回DataReader(使用该方法牢记要手工关闭DataReader和连接)
/// </summary>
public DbDataReader ExecuteReader(string sql, params DbParameter[] cmdParms)
{
try
{
CreateCommand(sql, cmdParms);
DbDataReader myReader = cmd.ExecuteReader();
return myReader;
}
catch (SqlException e)
{
cnn.Close();
throw new Exception(e.Message);
}
}
public DbParameter MakeParam(string ParamName, DbType DbType, Int32 Size, object Value)
{
DbParameter Param = cmd.CreateParameter();
Param.ParameterName = ParamName;
Param.DbType = DbType;
if (Size > 0)
Param.Size = Size;
if (Value != null)
Param.Value = Value;
return Param;
}
private DbCommand CreateCommand(string cmdText, DbParameter[] Prams)
{
return CreateCommand(CommandType.Text, cmdText, Prams);
}
private DbCommand CreateCommand(CommandType cmdType, string cmdText, DbParameter[] Prams)
{
if (cnn.State != ConnectionState.Open)
cnn.Open();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (Prams != null)
{
cmd.Parameters.Clear();
foreach (DbParameter Parameter in Prams)
cmd.Parameters.Add(Parameter);
}
return cmd;
}
public DataSet GetDataSetByProc(string ProcName, DbParameter[] Params)
{
cnn.Open();
DbCommand cmd = CreateCommand(CommandType.StoredProcedure, ProcName, Params);
DbDataAdapter adapter = provider.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);
cnn.Close();
return ds;
}
#endregion
}
数据库连接类
<p>3、ExcelHelper.cs</p>
<div align="center"></div><div align="center"></div>
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
/// <summary>
/// ExcelHelper 的择要阐明
/// </summary>
public class ExcelHelper
{
public static void ExportXlsx(DataTable dt, string strFileName)
{
HttpContext curContext = HttpContext.Current;
MemoryStream ms = ExportXlsx(dt);
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8) + ".xlsx");
curContext.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
curContext.Response.End();
}
private static MemoryStream ExportXlsx(DataTable dt)
{
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = null;
int headRowIndex = 0;
string sheetName = "Sheet1";
if (!string.IsNullOrEmpty(dt.TableName))
{
sheetName = dt.TableName;
}
sheet = workbook.CreateSheet(sheetName);
int rowIndex = 0;
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(headRowIndex);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dt.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
foreach (DataRow row in dt.Rows)
{
rowIndex++;
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dt.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row.ToString());
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
return ms;
}
}
导出Excel工具类
<p>4、sql.xml </p>
<div align="center"></div><div align="center"></div>
<?xml version="1.0" encoding="utf-8" ?>
<sql>
<item id="1" text="报表1" dbConnName="OracleString">
select * from tb
</item>
<item id="2" text="报表2" dbConnName="SqlServerString">
select * from tb
</item>
</sql>
生存sql语句等信息的xml
<p>5、SqlEntity.cs</p>
<div align="center"></div><div align="center"></div>
public class SqlEntity
{
public SqlEntity()
{
}
public int Id { get; set; }
public string text { get; set; }
public string sql { get; set; }
public string dbConnName { get; set; }
}
实体类
<p>6、SqlEntityList.cs</p>
<div align="center"></div><div align="center"></div>
public class SqlEntityList
{
public List<SqlEntity> GetXmlData(String xmlPath)
{
var list = new List<SqlEntity>();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlPath);
XmlNodeList xnl = xmlDoc.SelectSingleNode("sql").ChildNodes;
for (int i = 0; i < xnl.Count; i++)
{
int id = int.Parse(xnl.Item(i).Attributes["id"].Value);
string text = xnl.Item(i).Attributes["text"].Value;
string dbConnName = xnl.Item(i).Attributes["dbConnName"].Value;
string sql = xnl.Item(i).InnerText;
var model = new SqlEntity()
{
Id = id,
text = text,
dbConnName = dbConnName.ToLower(),
sql = sql
};
list.Add(model);
}
return list;
}
}
xml内容转实体
<p>7、rpt.aspx</p>
<div align="center"></div><div align="center"></div>
<div>
<asp:DropDownList ID="ddlType" DataTextField="text" DataValueField="id" runat="server">
</asp:DropDownList>
<asp:Button runat="server" ID="btnQuery" Text="查询" OnClick="btnQuery_Click"/>
<asp:Literal runat="server" ID="ltlInfo"></asp:Literal>
<asp:Button runat="server" ID="btnExport" Text="导出" OnClick="btnExport_Click" />
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" Width="100%"></asp:GridView>
前端页
<p>8、rpt.aspx.cs</p>
<div align="center"></div><div align="center"></div>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlType.DataSource = getSqlList();
ddlType.DataBind();
}
}
private List<SqlEntity> getSqlList()
{
String path = Server.MapPath("~/App_Data/sql.xml");
SqlEntityList sqlEntityList = new SqlEntityList();
List<SqlEntity> list = sqlEntityList.GetXmlData(path);
return list;
}
private DataSet getDataSet(int type)
{
DataSet ds = new DataSet();
List <SqlEntity> list = getSqlList();
var m = list.FirstOrDefault(t => t.Id == type);
DataBase db = new DataBase(m.dbConnName);
ds = db.GetDataSet(m.sql);
ltlInfo.Text = "纪录数:" + ds.Tables.Rows.Count.ToString();
return ds;
}
private void BindData(DataSet ds)
{
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void btnQuery_Click(object sender, EventArgs e)
{
int type = int.Parse(ddlType.SelectedValue);
DataSet ds = getDataSet(type);
BindData(ds);
}
protected void btnExport_Click(object sender, EventArgs e)
{
int type = int.Parse(ddlType.SelectedValue);
DataSet ds = getDataSet(type);
DataTable dt = ds.Tables;
String fileName = ddlType.SelectedItem.Text;
ExcelHelper.ExportXlsx(dt, fileName);
}
前端页的背景代码
<p> </p><br><br/><br/><br/><br/><br/>来源：<a href="https://www.cnblogs.com/gdjlc/p/11498153.html" target="_blank">https://www.cnblogs.com/gdjlc/p/11498153.html</a>
页:
[1]