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.SqlClient;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;
public class frmSqlServerDll : Form
{
/// <summary>
/// 必要なデザイナ変数です。
/// </summary>
private System.ComponentModel.IContainer components = null;
private System.Windows.Forms.DataGridView dgvData;
private System.Windows.Forms.Button btnSqlExecute;
private System.Windows.Forms.TextBox txtSQL;
private System.Windows.Forms.Button btnSqlGet;
private System.Windows.Forms.CheckBox cbxExcelSet;
private System.Windows.Forms.CheckBox cbxDown;
private System.Windows.Forms.CheckBox cbxUp;
private System.Windows.Forms.CheckBox cbxLeft;
private System.Windows.Forms.CheckBox cbxRight;
public string ConnectString;
public string SqlString;
public DataTable resultDt;
public SqlConnection Con;
public SqlCommand Command;
public Thread thread = null;
/// <summary>
/// デザイナ サポートに必要なメソッドです。このメソッドの内容を
/// コード エディタで変更しないでください。
/// </summary>
private void InitializeComponent()
{
this.dgvData = new System.Windows.Forms.DataGridView();
this.btnSqlExecute = new System.Windows.Forms.Button();
this.txtSQL = new System.Windows.Forms.TextBox();
this.btnSqlGet = new System.Windows.Forms.Button();
this.cbxExcelSet = new System.Windows.Forms.CheckBox();
this.cbxDown = new System.Windows.Forms.CheckBox();
this.cbxUp = new System.Windows.Forms.CheckBox();
this.cbxRight = new System.Windows.Forms.CheckBox();
this.cbxLeft = new System.Windows.Forms.CheckBox();
((System.ComponentModel.ISupportInitialize)(this.dgvData)).BeginInit();
this.SuspendLayout();
//
// dgvData
//
this.dgvData.AllowUserToAddRows = false;
this.dgvData.AllowUserToDeleteRows = false;
this.dgvData.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.dgvData.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dgvData.Location = new System.Drawing.Point(8, 115);
this.dgvData.Name = "dgvData";
this.dgvData.ReadOnly = true;
this.dgvData.RowTemplate.Height = 21;
this.dgvData.Size = new System.Drawing.Size(604, 314);
this.dgvData.TabIndex = 0;
this.dgvData.SelectionMode = DataGridViewSelectionMode.CellSelect;
this.dgvData.CellClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dgvData_CellClick);
//
// btnSqlExecute
//
this.btnSqlExecute.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.btnSqlExecute.Location = new System.Drawing.Point(506, 72);
this.btnSqlExecute.Name = "btnSqlExecute";
this.btnSqlExecute.Size = new System.Drawing.Size(107, 29);
this.btnSqlExecute.TabIndex = 1;
this.btnSqlExecute.Text = "ＳＱＬ実行";
this.btnSqlExecute.UseVisualStyleBackColor = true;
this.btnSqlExecute.Click += new System.EventHandler(this.btnSqlExecute_Click);
//
// txtSQL
//
this.txtSQL.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.txtSQL.Location = new System.Drawing.Point(10, 7);
this.txtSQL.Multiline = true;
this.txtSQL.Name = "txtSQL";
this.txtSQL.Size = new System.Drawing.Size(371, 102);
this.txtSQL.TabIndex = 2;
//
// btnSqlGet
//
this.btnSqlGet.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.btnSqlGet.Location = new System.Drawing.Point(393, 72);
this.btnSqlGet.Name = "btnSqlGet";
this.btnSqlGet.Size = new System.Drawing.Size(107, 30);
this.btnSqlGet.TabIndex = 3;
this.btnSqlGet.Text = "選択セルSQL取得";
this.btnSqlGet.UseVisualStyleBackColor = true;
this.btnSqlGet.Click += new System.EventHandler(this.btnSqlGet_Click);
//
// cbxExcelSet
//
this.cbxExcelSet.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.cbxExcelSet.AutoSize = true;
this.cbxExcelSet.Location = new System.Drawing.Point(387, 7);
this.cbxExcelSet.Name = "cbxExcelSet";
this.cbxExcelSet.Size = new System.Drawing.Size(102, 16);
this.cbxExcelSet.TabIndex = 4;
this.cbxExcelSet.Text = "選択セルにセット";
this.cbxExcelSet.UseVisualStyleBackColor = true;
this.cbxExcelSet.CheckedChanged += new System.EventHandler(this.cbxExcelSet_CheckedChanged);
//
// cbxDown
//
this.cbxDown.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.cbxDown.AutoSize = true;
this.cbxDown.Location = new System.Drawing.Point(401, 50);
this.cbxDown.Name = "cbxDown";
this.cbxDown.Size = new System.Drawing.Size(99, 16);
this.cbxDown.TabIndex = 5;
this.cbxDown.Text = "下のセルに移動";
this.cbxDown.UseVisualStyleBackColor = true;
this.cbxDown.CheckedChanged += new System.EventHandler(this.cbxDownCheckChanged);
//
// cbxUp
//
this.cbxUp.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.cbxUp.AutoSize = true;
this.cbxUp.Location = new System.Drawing.Point(401, 28);
this.cbxUp.Name = "cbxUp";
this.cbxUp.Size = new System.Drawing.Size(99, 16);
this.cbxUp.TabIndex = 6;
this.cbxUp.Text = "上のセルに移動";
this.cbxUp.UseVisualStyleBackColor = true;
this.cbxUp.CheckedChanged += new System.EventHandler(this.cbxUpCheckChanged);
//
// cbxRight
//
this.cbxRight.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.cbxRight.AutoSize = true;
this.cbxRight.Location = new System.Drawing.Point(506, 28);
this.cbxRight.Name = "cbxRight";
this.cbxRight.Size = new System.Drawing.Size(99, 16);
this.cbxRight.TabIndex = 7;
this.cbxRight.Text = "右のセルに移動";
this.cbxRight.UseVisualStyleBackColor = true;
this.cbxRight.CheckedChanged += new System.EventHandler(this.cbxRightCheckChanged);
//
// cbxLeft
//
this.cbxLeft.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.cbxLeft.AutoSize = true;
this.cbxLeft.Location = new System.Drawing.Point(506, 50);
this.cbxLeft.Name = "cbxLeft";
this.cbxLeft.Size = new System.Drawing.Size(99, 16);
this.cbxLeft.TabIndex = 8;
this.cbxLeft.Text = "左のセルに移動";
this.cbxLeft.UseVisualStyleBackColor = true;
this.cbxLeft.CheckedChanged += new System.EventHandler(this.cbxLeftCheckChanged);
//
// frmSqlserver
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(624, 437);
this.Controls.Add(this.cbxLeft);
this.Controls.Add(this.cbxRight);
this.Controls.Add(this.cbxUp);
this.Controls.Add(this.cbxDown);
this.Controls.Add(this.cbxExcelSet);
this.Controls.Add(this.btnSqlGet);
this.Controls.Add(this.txtSQL);
this.Controls.Add(this.btnSqlExecute);
this.Controls.Add(this.dgvData);
this.Name = "frmSqlserver";
this.Text = "DBToExcelJoinTool";
((System.ComponentModel.ISupportInitialize)(this.dgvData)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
public frmSqlServerDll(string Connect, string strSql)
{
InitializeComponent();
this.cbxDown.Enabled = false;
this.cbxUp.Enabled = false;
this.cbxLeft.Enabled = false;
this.cbxRight.Enabled = false;
ConnectString = Connect;
Con = new SqlConnection(Connect);
SqlString = strSql;
txtSQL.Text = strSql;
thread = new Thread(new ThreadStart(DataGet));
thread.Start();
thread.Join();
dgvData.DataSource = new DataView(resultDt);
}
private void btnSqlGet_Click(object sender, EventArgs e)
{
Excel.Application ExcelData = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
txtSQL.Text = ExcelData.ActiveCell.Text.ToString();
if (ExcelData != null)
{
Marshal.ReleaseComObject(ExcelData);
ExcelData = null;
}
}
private void DataGet()
{
if (SqlString != "")
{
resultDt = dtSqlExecute(SqlString);
}
}
private DataTable dtSqlExecute(string strSql)
{
DataTable rtnDatatable = new DataTable();
SqlDataReader rs;
DataRow dr;
try
{
Con.Open();
Command = new SqlCommand(strSql, Con);
rs = Command.ExecuteReader();
//フィールド名出力
for (int i = 0; i < rs.FieldCount; i++)
{
rtnDatatable.Columns.Add(rs.GetName(i));
}
//データ出力
while (rs.Read())
{
dr = rtnDatatable.NewRow();
for (int i = 0; i < rs.FieldCount; i++)
{
dr[i] = rs[i].ToString();
}
rtnDatatable.Rows.Add(dr);
}
Con.Close();
}
catch(Exception e)
{
rtnDatatable = null;
}
return rtnDatatable;
}
private void btnSqlExecute_Click(object sender, EventArgs e)
{
Con = new SqlConnection(ConnectString);
SqlString = txtSQL.Text;
thread = new Thread(new ThreadStart(DataGet));
thread.Start();
thread.Join();
dgvData.DataSource = new DataView(resultDt);
}
private void dgvData_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (this.cbxExcelSet.Checked == true)
{
if (e.ColumnIndex == -1)
{
return;
}
if (e.RowIndex == -1)
{
return;
}
Excel.Application ExcelData = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
ExcelData.ActiveCell.Value2 = dgvData[e.ColumnIndex, e.RowIndex].Value.ToString();
if (this.cbxDown.Checked == true)
{
int setRow;
if (ExcelData.Rows.Count != ExcelData.ActiveCell.Row)
{
setRow = ExcelData.ActiveCell.Row + 1;
}
else
{
setRow = ExcelData.ActiveCell.Row;
}
(ExcelData.Cells[setRow, ExcelData.ActiveCell.Column] as Excel.Range).Select();
}
if (this.cbxUp.Checked == true)
{
int setRow;
if (ExcelData.ActiveCell.Row != 1)
{
setRow = ExcelData.ActiveCell.Row - 1;
}
else
{
setRow = 1;
}
(ExcelData.Cells[setRow, ExcelData.ActiveCell.Column] as Excel.Range).Select();
}
if (this.cbxLeft.Checked == true)
{
int setColumn;
if (ExcelData.ActiveCell.Column != 1)
{
setColumn = ExcelData.ActiveCell.Column - 1;
}
else
{
setColumn = 1;
}
(ExcelData.Cells[ExcelData.ActiveCell.Row, setColumn] as Excel.Range).Select();
}
if (this.cbxRight.Checked == true)
{
int setColumn;
if (ExcelData.Columns.Count != ExcelData.ActiveCell.Column)
{
setColumn = ExcelData.ActiveCell.Column + 1;
}
else
{
setColumn = ExcelData.ActiveCell.Column;
}
(ExcelData.Cells[ExcelData.ActiveCell.Row, setColumn] as Excel.Range).Select();
}
if (ExcelData != null)
{
Marshal.ReleaseComObject(ExcelData);
ExcelData = null;
}
}
}
private void cbxExcelSet_CheckedChanged(object sender, EventArgs e)
{
if (this.cbxExcelSet.Checked == true)
{
this.cbxDown.Enabled = true;
this.cbxUp.Enabled = true;
this.cbxLeft.Enabled = true;
this.cbxRight.Enabled = true;
}
else
{
this.cbxDown.Enabled = false;
this.cbxUp.Enabled = false;
this.cbxLeft.Enabled = false;
this.cbxRight.Enabled = false;
}
}
private void cbxDownCheckChanged(object sender, EventArgs e)
{
if (this.cbxDown.Checked == true)
{
this.cbxUp.Checked = false;
this.cbxLeft.Checked = false;
this.cbxRight.Checked = false;
}
}
private void cbxUpCheckChanged(object sender, EventArgs e)
{
if (this.cbxUp.Checked == true)
{
this.cbxDown.Checked = false;
this.cbxLeft.Checked = false;
this.cbxRight.Checked = false;
}
}
private void cbxLeftCheckChanged(object sender, EventArgs e)
{
if (this.cbxLeft.Checked == true)
{
this.cbxDown.Checked = false;
this.cbxUp.Checked = false;
this.cbxRight.Checked = false;
}
}
private void cbxRightCheckChanged(object sender, EventArgs e)
{
if (this.cbxRight.Checked == true)
{
this.cbxDown.Checked = false;
this.cbxUp.Checked = false;
this.cbxLeft.Checked = false;
}
}
}
- 前ページ
- 次ページ