一个C#写的数据查询导出工具

安装说明

环境要求：windows xp 、win 7、win 10

安装准备：

Oracle 客户端

Microsoft .NET Framework 4 （https://www.microsoft.com/zh-cn/download/confirmation.aspx?id=17718）

安装说明：运行安装包，即可安装

安装完成后在桌面会创建“数据查询导出工具”的查询工具。

程序初始化

1. 1. 配置数据库连接

首次运行，请先配置数据库连接

点击“数据库连接”后，会出现下面的窗体，要求输入维护工程师密码。（维护工程师密码默认是123456 ）

修改IP地址和数据库用户及密码

1. 1. 查询设置

点击“编辑”按钮可以修改查询语句，或者点击下方的“添加查询”按钮添加一个查询。

SQL语句中的[]表示一个条件，例如：

[名称=入院起始日期 类型=日期] 表示 下图中的“入院起始日期”

[名称=入院截止日期 类型=日期] 表示 下图中的“入院截止日期”

[名称=姓名] 表示 下图中的“姓名”

点击“预览”按钮，可以进行测试，点击“查询”按钮，会显示执行的SQL语句。

所有配置（数据库连接、查询、工程师密码）保存在安装目录的tools.db文件中。如果需要在多台计算机上使用相同的配置，可以复制这个文件。

使用说明

运行程序后，在首页中可以看到设置后的查询，点击左侧的“打开”按钮。

在打开的窗体中，输入条件，点击“查询”按钮，可以查询数据。

当查询到数据后，点击下方的导出按钮，可以将查询到数据导出成文件。

代码

调用init_control 函数，动态生成查询条件的控件，并自动布局。

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;

namespace DataTools
{
    public partial class OpenSelect : DevExpress.XtraEditors.XtraForm
    {
        string conn = "";
        public OpenSelect()
        {
            InitializeComponent();
            this.Icon = Icon.ExtractAssociatedIcon(Application.ExecutablePath);
            conn = DataConn.currconn.select_str("select v from sysconfig where keyname='conn' ");

        }

        private void OpenSelect_Load(object sender, EventArgs e)
        {

        }
        DataTable curr_dt = null;
        public void do_search(string sql) {
            memoEdit1.Text = sql;
            DevExpress.XtraSplashScreen.SplashScreenManager.ShowForm(typeof(WaitFormProc));
            try
            {
                string err = "";
                if (DBUtils.check_connstring(conn, out err))
                {
                    string err2 = "";
                    curr_dt = DBUtils.select(conn, sql,out err2);
                    gridControl1.DataSource = curr_dt;
                    if (err2 != "")
                        PubDevExpress.ShowMessage(err2);
                }
                else
                    PubDevExpress.ShowMessage(err);
            }
            catch (Exception ex)
            {
                PubDevExpress.ShowMessage(ex.Message);
            }
            DevExpress.XtraSplashScreen.SplashScreenManager.CloseForm();
        }
        public SqlStr sqlstr = new SqlStr();
        public void init_select(string name, string v)
        {
            this.Text = name;
            sqlstr.SetSql(v);
            if (sqlstr.paramlist.Count==0)
            {
                panelControl1.Visible = false;
                do_search ( sqlstr.GetSql());
                return;
            }
            else
            {
                init_control();
            }
        }
        public void init_control( )
        {
            PanelControl p = panelControl1;
            int x = 12;
            int y = 12;
            int lw = 100;
            int tw = 100;
            int spw = 10;
            int w = p.Width - 2;
            Dictionary<string, object> objectlist = new Dictionary<string, object>();
            for (int i=0;i<sqlstr.paramlist.Count;i++)
            {
                SqlStrParam sp = sqlstr.paramlist[i];
                string slable = "";
                if (!sp.option.TryGetValue("名称", out slable))
                    slable = "条件" + (i + 1).ToString();
                if (objectlist.ContainsKey(slable))
                {
                    sp.control = objectlist[slable];
                    continue;
                }
                string stype = "";
                if (!sp.option.TryGetValue("类型", out stype))
                    stype = "";
                labelControl_w.Text = slable;
                lw = labelControl_w.Width + 3;
                if ((x+  lw + tw)>w)
                {
                    x = 12;
                    y = y + 28;
                }
                LabelControl l = new LabelControl();
                l.Text = slable;
                l.Left = x;
                l.Top = y;
                l.Width = lw;
                l.Height = 25;
                p.Controls.Add(l);
                x = x + lw;              
               
                if (stype == "日期")
                {
                    DateEdit t = new DateEdit();
                    t.Properties.CalendarTimeProperties.DisplayFormat.FormatString = "yyyy-MM-dd";
                    t.Properties.CalendarTimeProperties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
                    t.Properties.CalendarTimeProperties.EditFormat.FormatString = "yyyy-MM-dd";
                    t.Properties.CalendarTimeProperties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;

                    t.Properties.DisplayFormat.FormatString = "yyyy-MM-dd";
                    t.Properties.DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
                    t.Properties.EditFormat.FormatString = "yyyy-MM-dd";
                    t.Properties.EditFormat.FormatType = DevExpress.Utils.FormatType.DateTime;

                    t.Left = x;
                    t.Top = y - 3;
                    t.Width = tw;
                    p.Controls.Add(t);
                    x = x + tw;
                    x = x + spw;
                    sp.control = t;
                }
                else
                {
                    TextEdit t = new TextEdit();
                    t.Left = x;
                    t.Top = y - 3;
                    t.Width = tw;
                    p.Controls.Add(t);
                    x = x + tw;
                    x = x + spw;
                    sp.control = t;
                }
                objectlist[slable] = sp.control;

            }
            if ((x + simpleButton_search.Width + 10) > w)
            {
                x = 12;
                y = y + 28;
            }
            simpleButton_search.Left = x;
            simpleButton_search.Top = y-4;
            p.Height = y +  28;
        }

        private void simpleButton_search_Click(object sender, EventArgs e)
        {

            for (int i = 0; i < sqlstr.paramlist.Count; i++)
            {
                SqlStrParam sp = sqlstr.paramlist[i];
                if (sp.control is TextEdit)
                {
                    TextEdit t =  (TextEdit)(sp.control);
                    sp.v = t.Text.Trim();
                }
                if (sp.control is DateEdit)
                {
                    DateEdit t = (DateEdit)(sp.control);
                    sp.v = t.DateTime.ToString("yyyy-MM-dd");
                }
            }
            do_search(sqlstr.GetSql());
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            Close();
        }
        public void setsqlview(Boolean v)
        {
            groupControl1.Visible = v;
        } 
 
        private void simpleButton2_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
            sd.DefaultExt = "Xlsx";
            sd.Filter = "Excel文件|*.Xlsx";
            if (sd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                gridControl1.MainView.ExportToXlsx(sd.FileName);
                PubDevExpress.ShowMessage("文件已经保存在" + sd.FileName);
            }
        }

        private void simpleButton4_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
            sd.DefaultExt = "txt";
            sd.Filter = "txt文件|*.txt";
            if (sd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                gridControl1.MainView.ExportToText(sd.FileName);
                PubDevExpress.ShowMessage("文件已经保存在" + sd.FileName);
            }
        }

        private void simpleButton5_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
            sd.DefaultExt = "pdf";
            sd.Filter = "pdf文件|*.pdf";
            if (sd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                gridControl1.MainView.ExportToPdf(sd.FileName);
                PubDevExpress.ShowMessage("文件已经保存在" + sd.FileName);
            }
        }

        private void simpleButton3_Click(object sender, EventArgs e)
        {
            System.Windows.Forms.SaveFileDialog sd = new System.Windows.Forms.SaveFileDialog();
            sd.DefaultExt = "xls";
            sd.Filter = "Excel文件|*.xls";
            if (sd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                gridControl1.MainView.ExportToXls(sd.FileName);
                PubDevExpress.ShowMessage("文件已经保存在"+ sd.FileName);
            }
        }
    }
}

使用SqlStr类，生成查询sql

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DataTools
{
    public class SqlStr
    {
        private string _sql = "";
        public static string QuotedStr(string v)
        {
            return "'" + v.Replace("'", "''") + "'";
        }
        public string GetSql()
        {
            string s = "";
            foreach (SqlItem i in itemlist)
            {
               
                if (i.param != null)
                {
                    s = s + QuotedStr( i.param.v);
                }
                else
                {
                    s = s + i.txt;
                }
            }
            return s;
        }
        public void SetSql(string v)
        {
            _sql = v;
            decode();
        }
        public void decode()
        {
            paramlist.Clear();
            itemlist.Clear();
            SqlItem item = new SqlItem();
            itemlist.Add(item);
            foreach (char ch in _sql)
            {
                if (ch=='[')
                {
                    item = new SqlItem();
                    itemlist.Add(item);
                }
                item.txt = item.txt + ch;
                if (ch == ']')
                {
                    item = new SqlItem();
                    itemlist.Add(item);
                }
            }
            foreach (SqlItem i  in itemlist)
            {
                i.check();
                if (i.param!=null)
                {
                    paramlist.Add(i.param);
                }
            }
        }
        public List<SqlStrParam> paramlist = new List<SqlStrParam>();
        public List<SqlItem> itemlist = new List<SqlItem>();
    }
    public class SqlItem
    {
        public string txt = "";
        public string id = "";
        public SqlStrParam param = null;
        public void check()
        {
            if (txt.Length < 2)
                return;
            char ch1 = txt[0];
            char ch2 = txt[txt.Length - 1];
            if ((ch1 == '[') && (ch2 == ']'))
            {
                param = new SqlStrParam();
                string s = txt.Substring(1, txt.Length - 2);
                param.settxt(s);
            }
        }
    }
    public class SqlStrParam
    {
        public Dictionary<string, string> option = new Dictionary<string, string>();
        public string id = "";
        public string v = "";
        public object control;
        public void settxt(string v)
        {
            string[] ss=v.Split(new string[] { " ",  "\t" }, StringSplitOptions.RemoveEmptyEntries);
            
            foreach (string s in ss)
            {
                try
                {
                    string s1 = "";
                    string s2 = "";
                    int i = s.IndexOf('=');
                    if (i > 0)
                    {
                        s1 = s.Substring(0, i);
                        s2 = s.Substring(i + 1);
                    }
                    else
                    {
                        s1 = s;
                        s2 = "";
                    }
                    option[s1] = s2;
                }
                catch
                {
                }
               
            }
        }
    }
}