Форум программистов [Powered by Invision Power Board]

Код

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.ProviderBase;
using System.Data.Sql;
using System.Drawing;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using Office;

public class MyExcel : Form
{
    [method: STAThread]
    public static int Main(string[] args)
    {
        Application.Run(new MyExcel());
        return 0;
    }

    MainMenu mainM;
    public DataGridView dgvTable = new DataGridView();
    public TextBox txtRequest = new TextBox();
    public Button btnRequest = new Button();
    public Button btnSeveZvit = new Button();

    DataSet myDS;

    string dbURL;
    string dbREQU;
    string xlsBlankURL;
    string xlsResultURL;

    public MyExcel()
    {
        CenterToScreen();
        this.Size = new Size(400, 400);

        InitializeComponent();
    }
    // Controls
    public void InitializeComponent()
    {
        FormBorderStyle = FormBorderStyle.FixedSingle;
        MainMenu_MethodConstr();
        DataGridView_MethodConstr();
        TextBox_MethodConstr();
        Button_MethodConstr();
    }
    public void MainMenu_MethodConstr()
    {
        mainM = new MainMenu();
        MenuItem miGeneral = mainM.MenuItems.Add("General");
        MenuItem miDataBase = miGeneral.MenuItems.Add("Data Base");
        miDataBase.MenuItems.Add(new MenuItem("Choose db", new EventHandler(this.ChooseDB_Click), Shortcut.CtrlO));
        MenuItem miRequest = miGeneral.MenuItems.Add("Request");
        miRequest.MenuItems.Add(new MenuItem("Do Request", new EventHandler(this.DoRequest_Click), Shortcut.CtrlD));
        miRequest.MenuItems.Add(new MenuItem("Save Request", new EventHandler(this.SaveRequest_Click), Shortcut.CtrlS));
        this.Menu = mainM;
    }
    public void DataGridView_MethodConstr()
    {
        dgvTable.Location = new Point(10, 10);
        dgvTable.Size = new Size(375, 200);
        dgvTable.ReadOnly = true;
        Controls.Add(dgvTable);
    }
    public void TextBox_MethodConstr()
    {
        txtRequest.Location = new Point(10, 220);
        txtRequest.Size = new Size(375, 25);
        txtRequest.TextAlign = HorizontalAlignment.Center;
        txtRequest.Text = "SELECT * FROM table1";
        Controls.Add(txtRequest);
    }
    public void Button_MethodConstr()
    {
        btnRequest.Location = new Point(10, 250);
        btnRequest.Size = new Size(100, 25);
        btnRequest.Text = "Do Request";
        btnRequest.FlatStyle = FlatStyle.Flat;
        btnRequest.Click += new EventHandler(btnRequest_Click);
        btnRequest.Enabled = false;
        Controls.Add(btnRequest);

        btnSeveZvit.Location = new Point(120, 250);
        btnSeveZvit.Size = new Size(100, 25);
        btnSeveZvit.Text = "Save Request";
        btnSeveZvit.FlatStyle = FlatStyle.Flat;
        btnSeveZvit.Click += new EventHandler(btnSeveZvit_Click);
        btnSeveZvit.Enabled = false;
        Controls.Add(btnSeveZvit);
    }
    // MainMenu Procesing
    private void ChooseDB_Click(object sender, EventArgs e)
    {
        OpenDB();
    }
    private void DoRequest_Click(object sender, EventArgs e)
    {
        DoRequest();
    }
    private void SaveRequest_Click(object sender, EventArgs e)
    {
        SaveRequest();
    }
    // Controls Provesung
    private void btnRequest_Click(object sender, EventArgs e)
    {
        DoRequest();
    }
    private void btnSeveZvit_Click(object sender, EventArgs e)
    {
        SaveRequest();
    }
    // DataBase
    public void OpenDB()
    {
        OpenFileDialog myOD = new OpenFileDialog();
        myOD.InitialDirectory = ".";
        myOD.Filter = "Access DB files (*.mdb)|*.mdb|All files(*.*)|*.*";
        myOD.FilterIndex = 1;
        myOD.RestoreDirectory = true;

        if (myOD.ShowDialog() == DialogResult.OK)
        {
            if (myOD.FileName.Contains(".mdb"))
            {
                dbURL = null;
                dbURL = myOD.FileName.ToString();
            }
        }
        btnRequest.Enabled = true;
    }
    // Request
    public void DoRequest()
    {
        dbREQU = txtRequest.Text;

        OleDbConnection cn = new OleDbConnection();
        cn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; data source = " + dbURL + "";
        cn.Open();

        string sqlSELECT = dbREQU;
        OleDbDataAdapter dAdapt = new OleDbDataAdapter(sqlSELECT, cn);

        myDS = new DataSet("baseDS");
        dAdapt.Fill(myDS, "baseDT");

        cn.Close();

        dgvTable.DataSource = myDS.Tables["baseDT"];
        btnSeveZvit.Enabled = true;
    }
    public void SaveRequest()
    {
        int toI = myDS.Tables["baseDT"].Rows.Count;
        int toJ = myDS.Tables["baseDT"].Columns.Count;

        string[,] strArr = new string[toI, toJ];
        
        DataTable dt = myDS.Tables["baseDT"];
        
        for (int i = 0; i <= toI-1; i++)
        {
            for (int j = 0; j <= toJ-1; j++)
            {
                strArr[i, j] = dt.Rows[i][j].ToString();
            }
        }

        int cellInside = 3;
        int cellOut = cellInside + toI - 1;

        OpenFileDialog ofd = new OpenFileDialog();
        ofd.InitialDirectory = ".";
        ofd.Filter = "Excel file BLANK (*.xls)|*.xls|All files(*.*)|*.*";
        ofd.FilterIndex = 1;
        ofd.RestoreDirectory = true;

        if (ofd.ShowDialog() == DialogResult.OK)
        {
            if (ofd.FileName.Contains(".xls"))
            {
                xlsBlankURL = null;
                xlsBlankURL = ofd.FileName.ToString();

                txtRequest.Text = xlsBlankURL;

                object optional = Missing.Value;
                Excel.Application app = new Excel.Application();
                Excel.Workbook wb1 = app.Workbooks.Open(xlsBlankURL, optional, optional, optional, optional, optional, optional, optional, optional, optional, optional, optional, optional);
                Excel.Worksheet ws1 = (Excel.Worksheet)wb1.Worksheets[1];
                Excel.Range rng;

                SaveFileDialog sfd = new SaveFileDialog();
                sfd.InitialDirectory = ".";
                sfd.Filter = "Excel file RESULT (*.xls)|*.xls|All files(*.*)|*.*";
                sfd.FilterIndex = 1;
                sfd.RestoreDirectory = true;

                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    if (sfd.FileName.Contains(".xls"))
                    {
                        xlsResultURL = null;
                        xlsResultURL = sfd.FileName.ToString();

                        rng = ws1.get_Range("A"+cellInside+"", "C"+cellOut+"");
                        
                        rng.Value = strArr;

                        wb1.SaveAs(xlsResultURL, optional, optional, optional, optional, optional, Excel.XlSaveAsAccessMode.xlShared, optional, optional, optional, optional);

                        txtRequest.Text = xlsResultURL;
                        app.Quit();
                    }
                }
            }
        }
    }
}