.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);
    }
前端頁的後臺代碼

 


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

更多相關文章
  • 上次在 asp.net core 從單機到集群 一文中提到存儲還不支持分散式,並立了一個 flag > 基於 github 或者 開源中國的碼雲實現一個 storage 於是這兩天就來填坑了。。 ...
  • 一、static關鍵字 下麵我設計了一個房貸利率上浮類(用來計算房貸利率上浮多少): 上面例子的問題在於基準利率這個屬性是所有房貸利率上浮對象共用的屬性,而不是每個房貸利率上浮對象都擁有一個基準利率。所以要把基準利率這個屬性設置成共用的需要使用static關鍵字,第二版房貸利率上浮類: 靜態自動屬性 ...
  • 在java的spring中有自動註入功能,使得代碼變得更加簡潔靈活,所以想把這個功能移植到c#中,接下來逐步分析實現過程 1.使用自動註入場景分析 在asp.net mvc中,無論是什麼代碼邏輯分層,最終的表現層為Controller層,所以我們註入點就是在Controller中,這裡我們需要替換默 ...
  • EF 6及以前的版本是預設支持延遲載入(Lazy Loading)的,早期的EF Core中並不支持,必須使用Include方法來支持導航屬性的數據載入。 當然在 EF Core 2.1 及之後版本中已經引入了延遲載入功能,詳細實現原理可以查看官網( "傳送門" )。 下麵記錄一下,分別使用Incl ...
  • 按照目前的軟體開發發展趨勢中,不管是前後端分離還是提供數據服務,WebApi使用的越來越廣泛,而且.NET Core也是我們.NET開發人員未來發展的趨勢,所以說學會使用.NET Core Api是非常有必要的。 本人作為一個.NET菜鳥,正在慢慢的學習中,將學到的一步一步記錄下來。 一、創建項目 ...
  • 官網地址:https://framework7.io/docs/autocomplete.html#autocomplete-parameters 效果圖: <meta charset="UTF-8"><meta name="viewport" content="width=device-width ...
  • Http請求資源的過程可以看成一個管道:“Pipe”,並不是所有的請求都是合法的、安全的,其於功能、性能或安全方面的考慮,通常需要在這管道中裝配一些處理程式來篩選和加工這些請求。這些處理程式就是中間件。 中間件之間的調用順序就是添加中間件組件的順序,調用順序以於應用程式的安全性、性能、和功能至關重要 ...
  • 前提 入行已經7,8年了,一直想做一套漂亮點的自定義控制項,於是就有了本系列文章。 GitHub:https://github.com/kwwwvagaa/NetWinformControl 碼雲:https://gitee.com/kwwwvagaa/net_winform_custom_contr ...
一周排行
  • 該方式是直接對屏幕進行截圖操作UserControl chartContainPanel = new UserControl();Graphics graph = chartContainPanel.CreateGraphics();Size s = chartContainPanel.Size;B... ...
  • dotnetcore3.1 WPF 中使用依賴註入 Intro 在 ASP.NET Core 中預設就已經集成了依賴註入,最近把 "DbTool" 遷移到了 WPF dotnetcore 3.1, 在 WPF 中我們也希望能夠使用依賴註入,下麵來介紹一下如何在 WPF dotnetcore3.1 中 ...
  • 原來的C 程式都有Main的,現在用vs新建一個Wpf項目,啟動似乎變成App.xmal,前期項目中為了獲取啟動參數,很是折騰了一番: 1.先是修改App.xaml,添加StartUp事件 2.然後編輯Application_Startup,判斷e.Args數組 總感覺跟又臭又長的裹腳布一樣,不爽。 ...
  • 冒泡排序原理:(升序)通過當前位置數和後一個位置數進行比較 如果當前數比後一個數大 則交換位置, 完成後 比較基數的位置變成下一個數。直到數組末尾,當程式運行完第一遍 最大的數已經排序到最後一個位置了。次數可以減少迴圈數不用管最後一個數 降序排序同理 不過是把比較方式變成判斷當前數是否小於下一個數 ...
  • 一、前言 這方面的資料很多,重覆的寫沒必要,但是最近一直在學習身份驗證和授權相關東東,為了成體系還是寫一篇,主要是從概念上理解identity系統。 參考:https://www.cnblogs.com/r01cn/p/5179506.html 二、概述 幾乎所有系統都包含用戶、角色、許可權、登錄、註 ...
  • 首先我們使用最簡單的模板案例,裡面有一個Counter計數器,你可以在創建模板中找到。 首先需要設置運行調試方式為IIS Express。這意味著,MAC可能不能使用調試。 然後開啟運行而不調試(Ctrl+F5) 按Shift + Alt + D,會出現一個新的頁面。 如果你想用Chrome調試,復 ...
  • 實體映射時,遇到複雜類型,可選擇下述方法處理: NotMapped,跳過映射 在複雜類型上聲明 [Owned],但僅限該複雜類型是全部由簡單值類型組成的 自定義序列化方法 示例: IPInfo使用了owned,對IPEndPoint使用自定義序列化,對VersionInfo使用JSON序列化 @@@... ...
  • .NET Core 3 Web Api Cors fetch 一直 307 Temporary Redirect 繼上一篇 ".net core 3 web api jwt 一直 401" 為添加 所述的坑後, 本次為添加 ,又踩坑了。 自從 .NET Core 2.2 之後,CORS跨域配置代碼發 ...
  • 在前一章已經學習過WPF動畫的第一條規則——每個動畫依賴於一個依賴項屬性。然而,還有另一個限制。為了實現屬性的動態化(換句話說,使用基於時間的方式改變屬性的值),需要有支持相應數據類型的動畫類。例如,Button.Width屬性使用雙精度數據類型。為實現屬性的動態化,需要使用DoubleAnimat ...
  • WPF dotnet core 3.1 基於 `Microsoft.Extensions.Localization` 實現基本的多語言支持 ...
x