using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

namespace isup
{
    public partial class dlg_dat : Form
    {
        public main t;
        public dlg_dat()
        {
            InitializeComponent();
        }

        public dlg_dat(main _t)
        {
            t = _t;
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                    Excel.Application ExcelApp = new Excel.Application();

                    Microsoft.Office.Interop.Excel.Workbook ExcelWorkBook;
                    Microsoft.Office.Interop.Excel.Worksheet ExcelWorkSheet;

                    ExcelWorkBook = ExcelApp.Workbooks.Add(System.Reflection.Missing.Value);
                    ExcelWorkSheet = (Excel.Worksheet)ExcelWorkBook.Worksheets.get_Item(1);


            
            

                    conn con = new conn();
                    SqlDataReader command_r;

                    using (SqlConnection connection = new SqlConnection(con.str_conn("www")))
                    {

                        SqlCommand command = new SqlCommand("ee_rep2 ", connection);
                        command.CommandType = CommandType.StoredProcedure;
                        SqlParameter dat1 = new SqlParameter("@s", SqlDbType.DateTime);
                        SqlParameter dat2 = new SqlParameter("@e", SqlDbType.DateTime);
                        dat1.Value = dateTimePicker1.Text;
                        dat2.Value = dateTimePicker2.Text;
                        command.Parameters.Add(dat1);
                        command.Parameters.Add(dat2);
                        connection.Open();
                        command_r = command.ExecuteReader();

                        int ColNum;
                        int RowNum=0;
                        ColNum = command_r.FieldCount;

                        while (command_r.Read())
                        {
                            RowNum++;
                        }

                        object[,] d = new object[RowNum, ColNum];
                


                        int i = 0;
                        command_r.Close();
                        command_r = command.ExecuteReader();
                        while (command_r.Read())
                        {
                            for (int j = 0; j < ColNum ; j++)
                            {
                                d[i, j] = command_r.GetSqlString(j).ToString();
                            }
                            i++;
                        }

                        int rows = d.GetUpperBound(0) + 1;
                        int cols = d.GetUpperBound(1) + 1;

                        Excel.Worksheet sheet = (Excel.Worksheet)ExcelApp.ActiveSheet;

                        object leftTop = ExcelWorkSheet.Cells[2, 2];
                        object rightBottom = ExcelWorkSheet.Cells[RowNum+1, ColNum+1];

                        Excel.Range range = ExcelWorkSheet.get_Range(leftTop, rightBottom);
                        range.Value2 = d;
                        setStyle(range);

                        ExcelApp.Visible = true;
                    }

                    Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void setStyle(Excel.Range range)
        {
            range.EntireColumn.AutoFit();
            range.EntireRow.AutoFit();
            object[] border = new object[] { Excel.XlBordersIndex.xlEdgeLeft, 
                                                Excel.XlBordersIndex.xlEdgeTop, 
                                                Excel.XlBordersIndex.xlEdgeBottom, 
                                                Excel.XlBordersIndex.xlEdgeRight,
                                                Excel.XlBordersIndex.xlInsideVertical,
                                                Excel.XlBordersIndex.xlInsideHorizontal};

            for (int i = 0; i < border.Length; i++)
            {
                range.Borders[(Excel.XlBordersIndex)border[i]].LineStyle = Excel.XlLineStyle.xlContinuous;
                range.Borders[(Excel.XlBordersIndex)border[i]].Weight = Excel.XlBorderWeight.xlThin;
                range.Borders[(Excel.XlBordersIndex)border[i]].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            }
        }

        private void dlg_dat_Load(object sender, EventArgs e)
        {
            dateTimePicker1.Value = DateTime.Now.AddDays(-7);
            dateTimePicker2.Value = DateTime.Now;

        }
    }
}