using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;

namespace Exportação
{
    public partial class Table : Form
    {
        string servidor;
        string db;
        string username;
        string password;
        string connectionString;
        string nomeTabela;

        public Table()
        {
            InitializeComponent();
        }

        private void LoadConf()
        {
            XmlDocument FXML = new XmlDocument();
            FXML.Load("conf.xml");

            foreach (XmlNode node in FXML.SelectNodes("//Configuracao"))
            {
                servidor = node.Attributes[0].Value.ToString();
                db = node.Attributes[1].Value.ToString();
                username = node.Attributes[2].Value.ToString();
                password = node.Attributes[3].Value.ToString();
            }

        }

        private void GetTables()
        {
            connectionString = @"server=" + servidor + ";" + " Initial Catalog="
            + db + "; User ID:" + username + "; Password=" + password;
            // connectionString = "Server=localhost\\SQLEXPRESS;Database=teste;Trusted_Connection=True;";
            connectionString = "Server=localhost\\SQLEXPRESS;Database=Base Dados Demo NAV;Trusted_Connection=True;";
            SqlConnection cnn = new SqlConnection(connectionString);

            cnn.Open();

            List<string> result = new List<string>();
            SqlCommand cmd = new SqlCommand("SELECT name FROM sys.Tables", cnn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                result.Add(reader["name"].ToString());

            int n = 0;
            foreach (string tableName in result)
            {
                n++;
                dgvTables.Rows.Add(n,tableName);
            }

            cnn.Close();
        }

        private void Table_Load(object sender, EventArgs e)
        {
            LoadConf();
            GetTables();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            int rowindex = dgvTables.CurrentCell.RowIndex;
            int columnindex = dgvTables.CurrentCell.ColumnIndex;
            nomeTabela = dgvTables.Rows[rowindex].Cells[columnindex].Value.ToString();

            SqlConnection cnn = new SqlConnection(connectionString);
                SqlDataAdapter sda = new SqlDataAdapter("Select * FROM " + nomeTabela,cnn);
                DataTable dt = new DataTable();
                sda.Fill(dt);
                expExcel(dt);
        }

            void expExcel(DataTable dt)
            {
                object MissingValue = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application
                {
                    Visible = false
                 };
                Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.ActiveSheet;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ws.Cells[1,i + 1] = dt.Columns[i].ColumnName;
                }


                ws.Name = nomeTabela;

                SFD.ShowDialog();
                wb.SaveAs(SFD.FileName,Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,MissingValue,MissingValue,MissingValue,MissingValue,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,MissingValue,MissingValue,MissingValue,MissingValue,MissingValue);
                wb.Close(true, MissingValue, MissingValue);
                app.Quit();

            }
        }
    }