dataexport.cs

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

            strb.Append("( ");
            for (int i = 0; i < m_dataBaseDT.Columns.Count; i++)
            {
                strb.Append(string.Format("{0} {1}", m_dataBaseDT.Columns[i].ColumnName, m_dataBaseDT.Columns[i].DataType.Name));
                if (i < m_dataBaseDT.Columns.Count - 1)
                    strb.Append(",");
                else
                    strb.Append(")");
            }
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileFullName + ";Extended Properties=\"Excel 8.0\";";
           
            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = strb.ToString();
            try
            {
                objConn.Open();
                objCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //raiseShowMessageEvent("在Excel中创建表失败，错误信息：" + e.Message);
                MessageBox.Show("在Excel中创建表失败，错误信息！" + ex.Message, "提示：");
                return;
            }

            #endregion 结束 生成创建表的脚本 , 创建表

            #region 生成插入数据脚本 ,循环插入数据

            strb.Remove(0, strb.Length);
            strb.Append("INSERT INTO ");
            strb.Append(ExcelTableName);
            strb.Append("( ");
            for (int i = 0; i < m_dataBaseDT.Columns.Count; i++)
            {
                if (i < m_dataBaseDT.Columns.Count - 1)
                {
                    strb.Append(m_dataBaseDT.Columns[i].ColumnName + ",");
                }
                else
                {
                    strb.Append(m_dataBaseDT.Columns[i].ColumnName + ") values (");
                }
            }
            for (int i = 0; i < m_dataBaseDT.Columns.Count; i++)
            {
                if (i < m_dataBaseDT.Columns.Count - 1)
                    strb.Append("@" + m_dataBaseDT.Columns[i].ToString() + ",");
                else
                    strb.Append("@" + m_dataBaseDT.Columns[i].ToString() + ")");
            }

            //建立插入动作的Command
            objCmd.CommandText = strb.ToString();
            OleDbParameterCollection param = objCmd.Parameters;
            for (int i = 0; i < m_dataBaseDT.Columns.Count; i++)
            {
                param.Add(new OleDbParameter("@" + m_dataBaseDT.Columns[i].ColumnName, OleDbType.VarChar));
            }
            //遍历DataTable将数据插入新建的Excel文件中
            int row_count = 0;
            foreach (DataRow row in m_dataBaseDT.Rows)
            {
                row_count++;
                for (int i = 0; i < param.Count; i++)
                {
                    param[i].Value = row[i];
                }
                try
                {
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出数据Excel出错！" + ex.Message, "提示：");
                    return;
                }
            }
            objConn.Close();
            #endregion 结束 生成插入数据脚本 ,循环插入数据

            MessageBox.Show("数据已成功导出Excel！", "提示：");
        }

        private void CnnectionSetBT_Click(object sender, EventArgs e)
        {
            ConnectionSet cs = new ConnectionSet();
            cs.ShowDialog();
        }

        // 移动按钮显示
        private void MoveButtonShow()
        {
            if (this.MatchingDG.Rows.Count == 0)
            {
                this.DBUpMoveBT.Enabled = false;
                this.DBDownMoveBT.Enabled = false;
                this.EDUpMoveBT.Enabled = false;
                this.EDDownMoveBT.Enabled = false;
                return;
            }
            if (this.MatchingDG.CurrentCell.ColumnIndex == 0)
            {
                this.EDUpMoveBT.Enabled = false;
                this.EDDownMoveBT.Enabled = false;

                if (this.MatchingDG.CurrentCell.RowIndex == 0)
                {
                    this.DBUpMoveBT.Enabled = false;
                    this.DBDownMoveBT.Enabled = true;
                }
                else if (this.MatchingDG.CurrentCell.RowIndex == this.MatchingDG.Rows.Count - 1)
                {
                    this.DBUpMoveBT.Enabled = true;
                    this.DBDownMoveBT.Enabled = false;
                }
                else
                {
                    this.DBUpMoveBT.Enabled = true;
                    this.DBDownMoveBT.Enabled = true;
                }
            }
            else
            {
                this.DBUpMoveBT.Enabled = false;
                this.DBDownMoveBT.Enabled = false;

                if (this.MatchingDG.CurrentCell.RowIndex == 0)
                {
                    this.EDUpMoveBT.Enabled = false;
                    this.EDDownMoveBT.Enabled = true;
                }
                else if (this.MatchingDG.CurrentCell.RowIndex == this.MatchingDG.Rows.Count - 1)
                {
                    this.EDUpMoveBT.Enabled = true;
                    this.EDDownMoveBT.Enabled = true;
                }
                else
                {
                    this.EDUpMoveBT.Enabled = true;
                    this.EDDownMoveBT.Enabled = true;
                }
            }
        }

        static public void ChangeCnnectionSet(string serverName, string userName, string passward)
        {
            m_serverName = serverName;
            m_userName = userName;
            m_passward = passward;
        }

        // 检索网格，把NULL网格 赋值为空字符串
        private void setNullStr(DataGridView dgv)
        {            
            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                for (int j = 0; j < dgv.Columns.Count; j++)
                {
                    if (dgv.Columns[j].Name != "DataBaseExport")
                    {
                        if (dgv.Rows[i].Cells[j].Value == null)
                        {
                            dgv.Rows[i].Cells[j].Value = "";
                        }
                    }
                }
            }
        }

        private void RemoveNullRow(DataGridView dgv)
        {
            while (true)
            {
                if (this.MatchingDG.Rows[this.MatchingDG.Rows.Count - 1].Cells["DataBase"].Value.ToString().Trim() == "" && this.MatchingDG.Rows[this.MatchingDG.Rows.Count - 1].Cells["ExteriorData"].Value.ToString().Trim() == "")
                {
                    this.MatchingDG.Rows.RemoveAt(this.MatchingDG.Rows.Count - 1);
                }
                else
                {
                    break;
                }
            }
        }

        private void MatchingDG_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }

            if (this.MatchingDG.Columns[e.ColumnIndex].Name == "DataBaseCondition")
            {
                DataExportCondition dec = new DataExportCondition(this, e.RowIndex);
                dec.ShowDialog();
            }
        }

        private void IsNewTableCB_CheckedChanged(object sender, EventArgs e)
        {
            if (this.IsNewTableCB.Checked == true)
            {
                this.OpenExteriorDataBT.Visible = false;
            }
            else
            {
                this.OpenExteriorDataBT.Visible = true;
            }
        }
    }
}