.net實現一個簡單的通用查詢數據、導出Excel的網頁

来源:https://www.cnblogs.com/gdjlc/archive/2019/09/10/11498153.html

背景:臨時提供一個簡單的網頁,供其他人瀏覽資料庫(Oracel、MSSQL)的某些數據,並導出Excel。 ...


背景:
臨時提供一個簡單的網頁,供其他人瀏覽資料庫(Oracel、MSSQL)的某些數據,並導出Excel。
支持在配置文件中隨時添加或修改sql。

 

實現:
把sql語句等信息保存一個xml文件中,前端頁面提供一個下拉框,可以選擇不同類型的sql語句,查詢結果(暫沒分頁需要)直接綁定到GridView。

 

 

開發環境:VS2015
NuGet引入庫:NPOI、Oracle.ManagedDataAccess.Client

一、VS目錄

├─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 ---------配置資料庫連接信息

二、代碼
1、Web.config

  <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>
資料庫連接字元串

2、DataBase.cs

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[connectionName].ProviderName;
        provider = DbProviderFactories.GetFactory(providerName);
        cnn = provider.CreateConnection();
        cnn.ConnectionString = WebConfigurationManager.ConnectionStrings[connectionName].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[n].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[0];
    }
    /// <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[0];
    }
    /// <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
}
資料庫連接類

3、ExcelHelper.cs

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[column].ToString());
            }
        }

        MemoryStream ms = new MemoryStream();
        workbook.Write(ms);
        ms.Flush();

        return ms;
    }
}
導出Excel工具類

4、sql.xml 

<?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

5、SqlEntity.cs

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; }
}
實體類

6、SqlEntityList.cs

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內容轉實體

7、rpt.aspx

        <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>   
前端頁

8、rpt.aspx.cs

    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[0].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[0];
        String fileName = ddlType.SelectedItem.Text;
        ExcelHelper.ExportXlsx(dt, fileName);
    }
前端頁的後臺代碼

 


您的分享是我們最大的動力!

更多相關文章
  • 依賴註入主要是一種結構性的模式,註重的是類與類之間的結構,它要達到的目的就是設計原則中最少知道和合成復用的原則,減少內部依賴,履行單一職責,最終就是強解耦。依賴註入目前最好的實現就是依賴註入容器。 Unity是微軟Patterns & Practices團隊所開發的一個輕量級的,並且可擴展的依賴註入 ...
  • 場景 DevExpress的PanelControl常用進行窗體頁面的佈局。 一般是拖拽一個PannelControl,然後是再拖拽其他控制項。 如果是由代碼生成控制項並控制佈局的話,怎樣實現。 關註公眾號 霸道的程式猿 獲取編程相關電子書、教程推送與免費下載。 實現 比如說要在PanelContrl中 ...
  • 通過 abp(net core)+easyui+efcore實現倉儲管理系統——菜單-上 (十六)這篇文章,我們已經瞭解了ABP中的菜單相關的類及類的屬性與方法,接下我們通過實例來實現一個動態載入菜單的功能。動態菜單是我們在abp(net core)+easyui+efcore實現倉儲管理系統——領... ...
  • 場景 使用DevExpress的EditText控制項時,需要限制其輸入類型為數字。 正常來說是窗體上拖拽一個TextEdit,然後在設計視窗點擊小三角,選擇Change Mask 但是如果說TextEdit控制項不是拖拽上去而是由代碼生成的,那麼在代碼中怎樣設置只能輸入數字。 關註公眾號 霸道的程式猿 ...
  • 前提 入行已經7,8年了,一直想做一套漂亮點的自定義控制項,於是就有了本系列文章。 GitHub:https://github.com/kwwwvagaa/NetWinformControl 碼雲:https://gitee.com/kwwwvagaa/net_winform_custom_contr ...
一周排行
  • 首先給大家介紹一下序列化是用來乾什麼的,他為什麼出現 序列化這種技術說到底其實就是把臨時數據保存在電腦上。大家都知道對於程式而言對象是一種稍縱即逝的數據,不僅僅是程式重啟、電腦重啟,就連內部函數的變化也有可能導致對象的消失,但是總有一些對象是大家不想讓其隨意消失的並且想在下一次開啟程式的時候進行載入 ...
  • 一、前言 在.Net Framework框架有專門獲取webconfig配置的方法供我們使用,但是在.Net Core或者.Net Standard中沒有可以直接使用的方法來獲取配置文件信息,下麵就來實現獲取配置信息。 二、獲取配置信息的實現 在.Net Core中,他的配置信息的載體是一個json ...
  • 你一定看過這篇文章 《進擊的 Java ,雲原生時代的蛻變》, 本篇文章的靈感來自於這篇文章。北京時間9.24 就將正式發佈.NET Core 3.0, 所以寫下這篇文章讓大家全面認識.NET Core。.NET 生態系統是一個不斷變化的生態圈,我相信它正在朝著一個偉大的方向發展。正好 最近 Inf... ...
  • 參考文檔:Dapper one to many Table C Code pulic List GetPersons(){ var sql = @"SELECT 1 AS Id, 'Daniel Dennett' AS Name, 1942 AS Born, 1 AS CountryId, 'Uni ...
  • 非標設備多相機流水線模式緩存圖片(C/S客戶端,c 開發語言) ​ 本文所說流水線方式下存儲圖像是在軟體測量周期慢於圖片周期前提下講解的,如果軟體一直在等待圖片數據,邏輯就沒有那麼複雜。 1、非標設備項目,常規模式測量流程 常規模式下,相機採集圖像信號由上位機控制(無論軟觸發、硬觸發)。每個周期內的 ...
  • 簡單創建.NET Core WebApi:https://www.cnblogs.com/yanbigfeg/p/9197375.html 登陸驗證四種方式:https://www.cnblogs.com/zuowj/p/5123943.html 解決跨域的8種方法:https://blog.csd ...
  • 最近有個需求就是網頁表格裡面的數據導出到excel 於是從各位前輩的博客園搜了搜demo 大部分非為兩類 都是用的插件NPOI和Eppluse ,因此在這裡就介紹Eppluse 用法,還有就是在博客的時候 好多有留言說想看從資料庫裡面的數據進行導入 而不是寫死的,所以我就以我的案例給大家分享下用法( ...
  • 表達式樹練習實踐:C 值類型、引用類型、泛型、集合、調用函數 [TOC] 一,定義變數 C 表達式樹中,定義一個變數,使用 。 創建變數結點的方法有兩種, 兩種方式都是生成 類型 和 都具有兩個重載。他們創建一個 ParameterExpression節點,該節點可用於標識表達式樹中的參數或變數。 ...
  • 就像是.NET Framework WebApi與.NET Core WebApi一樣,.NET Framework MVC與.NET Core MVC的區別,也是框架的之間的區別。本系列先首先從.NET Framework MVC介紹,後面再去介紹.NET Core MVC 狹義MVC: MVC是 ...
  • 緩存的實現 我們不是做第三方比如Redis等的緩存實現,而是根據實際情況,基於C#上做一些環境變數的保存,方便項目使用。 1、系統全局變數 很多時候,在系統運行開始,需要對系統的運行參數進行保存,以便供全局使用。 代碼如下: 這裡使用一個靜態變數的Dictionary來進行保存,所有項目均可以直接獲 ...
x