dataexport.cs

数据集导出到EXCEL文件接口程序

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

using System.Data.OleDb;
using System.Data.OracleClient;
using System.Configuration;

namespace DataExport
{
    public partial class DataExport : Form
    {
        #region 变量

        static public string m_serverName;
        static public string m_userName;
        static public string m_passward;
        // 数据库表名
        private string m_dataTableName;
        private DataTable m_dataBaseDT;

        #endregion 结束 变量

        public DataExport()
        {
            InitializeComponent();

            m_serverName = ConfigurationManager.AppSettings["ServerName"];
            m_userName = ConfigurationManager.AppSettings["UserName"];
            m_passward = ConfigurationManager.AppSettings["Passward"];
            this.DataBaseTableTB.Text = "ldvideo_preset";

            this.IsNewTableCB.Visible = false;
            this.OpenExteriorDataBT.Visible = false;
            this.DBDownMoveBT.Visible = false;
            this.DBUpMoveBT.Visible = false;
            this.EDDownMoveBT.Visible = false;
            this.EDUpMoveBT.Visible = false;
            this.MatchingDG.Columns["ExteriorData"].Visible = false;

        }

        private void GetPathBT_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                this.ExteriorDataTB.Text = ofd.FileName;
            }
        }

        private void OpenDataBaseBT_Click(object sender, EventArgs e)
        {
            if (this.DataBaseTableTB.Text == "")
            {
                MessageBox.Show("请您先输入表名！", "提示：");
                return;
            }
            m_dataTableName = this.DataBaseTableTB.Text;

            // 清空历史数据
            foreach (DataGridViewRow dgvr in this.MatchingDG.Rows)
            {
                dgvr.Cells[0].Value = "";
            }

            try
            {
                #region 访问数据库 获取表结构

                DataSet myDataSet = new DataSet();
                OracleConnection myConnection = new OracleConnection("server= " + m_serverName + ";user id= " + m_userName + ";Password= " + m_passward + "");
                myConnection.Open();
                OracleCommand myCommand = new OracleCommand();
                myCommand.Connection = myConnection;
                myCommand.CommandText = "select * from " + m_dataTableName + " where rownum <=0";
                OracleDataAdapter myAdapter = new OracleDataAdapter(myCommand);
                // 把Dataset绑定数据表
                myAdapter.Fill(myDataSet, m_dataTableName);

                myConnection.Close();
                #endregion 结束 访问数据库 获取表结构

                m_dataBaseDT = myDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show("打开数据库表失败：" + ex.Message, "提示：");
                return;
            }

            // 把表列信息输入到匹配网格
            if (this.MatchingDG.Rows.Count < this.m_dataBaseDT.Columns.Count)
            {
                this.MatchingDG.Rows.Add(this.m_dataBaseDT.Columns.Count - this.MatchingDG.Rows.Count);
                this.setNullStr(MatchingDG);
            }
            for (int i = 0; i < this.m_dataBaseDT.Columns.Count; i++)
            {
                this.MatchingDG.Rows[i].Cells["DataBase"].Value = this.m_dataBaseDT.Columns[i].ColumnName;
                string str = this.m_dataBaseDT.Columns[i].DataType.ToString();
                str = str.Substring(str.IndexOf(".") + 1, str.Length - str.IndexOf(".") - 1);
                this.MatchingDG.Rows[i].Cells["DataBaseType"].Value = str;
                this.MatchingDG.Rows[i].Cells["DataBaseExport"].Value = true;
            }

            //this.RemoveNullRow(MatchingDG);
            //this.MoveButtonShow();
        }

        private void ExecuteBT_Click(object sender, EventArgs e)
        {
            if (m_dataBaseDT == null)
            {
                MessageBox.Show("请您先打开数据库表！", "提示：");
                return;
            }

            if (this.ExteriorDataTB.Text == "")
            {
                MessageBox.Show("外部数据路径不能为空！", "提示：");
                return;
            }
            if (this.ExteriorTableNameBT.Text.Trim() == "")
            {
                MessageBox.Show("外部数据表名不能为空！", "提示：");
                return;
            }
            // EXL文件路径
            string fileFullName = this.ExteriorDataTB.Text;
            // EXL表名
            string ExcelTableName = this.ExteriorTableNameBT.Text.Trim();

            #region 生成查询字符串，获取数据库表数据

            string exportWord = "";
            // 得到要导出的字段
            for(int i =0;i< this.MatchingDG.Rows.Count;i++)
            {
                if(Convert.ToBoolean( this.MatchingDG.Rows[i].Cells["DataBaseExport"].Value))
                {
                    exportWord+=this.MatchingDG.Rows[i].Cells["DataBase"].Value.ToString();
                    exportWord+= ",";
                }
            }
            if (exportWord == "")
            {
                MessageBox.Show("没有要导出的数据字段，请选择！","提示：");
                return;
            }
            exportWord = exportWord.Substring(0, exportWord.Length - 1);

            // 添加约束条件
            string condition = "";
            for (int i = 0; i < this.MatchingDG.Rows.Count; i++)
            {
                if (Convert.ToBoolean(this.MatchingDG.Rows[i].Cells["DataBaseCondition"].Value.ToString() != ""))
                {
                    condition += this.MatchingDG.Rows[i].Cells["DataBaseCondition"].Value.ToString();
                    condition += " and ";
                }
            }
            if (condition != "")
            {
                condition = condition.Substring(0, condition.Length - 5);
                condition = " where " + condition;
            }

            // 得到要导出数据的查询语句
            string newDataStr = "select " + exportWord + " from " + m_dataTableName + "" + condition + "";
            try
            {
                #region 访问数据库 获取表结构

                DataSet myDataSet = new DataSet();
                OracleConnection myConnection = new OracleConnection("server= " + m_serverName + ";user id= " + m_userName + ";Password= " + m_passward + "");
                myConnection.Open();
                OracleCommand myCommand = new OracleCommand();
                myCommand.Connection = myConnection;
                myCommand.CommandText = newDataStr;
                OracleDataAdapter myAdapter = new OracleDataAdapter(myCommand);
                // 把Dataset绑定数据表
                myAdapter.Fill(myDataSet, m_dataTableName);
                myConnection.Close();

                #endregion 结束 访问数据库 获取表结构

                m_dataBaseDT = myDataSet.Tables[0];
            }
            catch (Exception ex)
            {
                MessageBox.Show("打开数据库表失败：" + ex.Message, "提示：");
                return;
            }

            if (m_dataBaseDT.Rows.Count == 0)
            {
                MessageBox.Show("你的选择集中没有数据！", "提示：");
                return;
            }

            #endregion 结束 生成查询字符串，获取数据库表数据



            #region 生成创建表的脚本 , 创建表

            StringBuilder strb = new StringBuilder(100);
            strb.Append("CREATE TABLE ");
            strb.Append(ExcelTableName);