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

namespace 在datatable上进行数据操作
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            //SetStyle(ControlStyles.OptimizedDoubleBuffer, true);//设置双缓冲
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.eventbinding();//事件绑定
            this.initdatagirdview();//把数据库载入到datagridview控件
            ).ToString()+"行";//减去列头的一行
        }

        public void eventbinding()
        {
            this.dataGridView1.ContextMenuStrip = this.contextMenuStrip1;
            this.FormClosing+=Form1_FormClosing;
            toolStripProgressBar1.Alignment = ToolStripItemAlignment.Right;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            System.Environment.Exit();//保证结束所有相关的线程
        }

        public  void initdatagirdview()
        {
                    //连接数据库并把表读出
                    SqlConnection conn = new SqlConnection("server=MAGICIANLYX\\SQLEXPRESS;database=AdventureWorks;uid=magicianlyx;pwd=13113772339;");
                    string s = "select * from AdventureWorks.Person.vStateProvinceCountryRegion";
                    SqlDataAdapter sda = new SqlDataAdapter(s, conn);
                    DataSet ds = new DataSet();
                    conn.Open();
                    sda.Fill(ds);
                    conn.Close();

                    //把数据表和datagridview控件关联起来

                    DataTable dt = ds.Tables[];
                    this.dataGridView1.DataSource = dt;
                    this.datagridview_resize();//重新设置每列的宽度，使其填满窗体

            /*数据库操作
                    //删除数据表第一行
                    dt.Rows.RemoveAt(0);
                    //删除数据表第二行第一列
                    dt.Rows[1][1] = 1;
                    //在数据表第2行前插入一行空白
                    DataRow dr = dt.NewRow();
                    dt.Rows.InsertAt(dr, 1);
             * */
        }

        //重新计算即时每列的宽度
        public void datagridview_resize()
        {
            this.dataGridView1.Refresh();
            int width = (this.dataGridView1.Width - this.dataGridView1.TopLeftHeaderCell.Size.Width) / this.dataGridView1.Columns.Count;
            foreach (DataGridViewColumn dc in dataGridView1.Columns)
            {
                dc.Width = width;
            }
        }

        //重绘窗口宽度改变
        protected override void OnPaint(PaintEventArgs e)
        {
            this.datagridview_resize();
            base.OnPaint(e);
        }

        /// <summary>
        /// 保存为excel
        /// </summary>
        private void  保存为excelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.saveexcel();
        }

        public async void saveexcel()
        {
            if (File.Exists(@"C:\Users\Administrator\Documents\111.xls"))//当路径下存在文件时，删除，保证无异常
            {
                File.Delete(@"C:\Users\Administrator\Documents\111.xls");
            }

//——————————————————————————————核心代码部分

            TimeSpan t1 = new TimeSpan(DateTime.Now.Ticks);//保留实时时间，后期要计算时间差
            ;
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook excelworkbook = excel.Application.Workbooks.Add(true);
            excel.Visible = false;//是否显示excel文本

            try
            {
                //异步执行，保证前台窗口程序无卡顿
                await Task.Run(() =>
                {
                    ; i < this.dataGridView1.Columns.Count; i++)//复制表格的列头
                    {
                        excel.Cells[, i + ] = this.dataGridView1.Columns[i].HeaderText;//excel对象的行列索引是从1开始的
                        //datagridview的行列索引是从0开始的
                    }

                    ; i < ; i++)//减去列头的一行
                    {
                        ; j < this.dataGridView1.Columns.Count; j++)
                        {
                            ].Cells[j].Value==null)
                            {
                                excel.Cells[i + , j + ] ="'  ";//当表格的单元格为空时，保留行并跳过
                                break;
                            }
                            ].Cells[j].ValueType == typeof(string))
                            {
                                excel.Cells[i + , j + ] = ].Cells[j].Value.ToString();
                            }

                            else
                            {
                                excel.Cells[i + , j + ] = ].Cells[j].Value.ToString();
                            }

                        }

                       this.toolStripProgressBar1.Value++;//进度条前进

                    }
                });
            }
            catch (Exception ex)
            {
            }
            finally
            {
                //保存xls表格
                excelworkbook.SaveAs("111.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                //释放资源
               if(excelworkbook!=null)
                   excelworkbook.Close(Missing.Value, Missing.Value, Missing.Value);
               if (excel != null)
               {
                   excel.Workbooks.Close();
                   excel.Quit();
               }

//——————————————————————后续代码非核心代码

                //计算时间差
                TimeSpan t2 = new TimeSpan(DateTime.Now.Ticks);
                TimeSpan ts = t1.Subtract(t2).Duration();
                string hours = ts.Hours.ToString(), minutes = ts.Minutes.ToString(), seconds = ts.Seconds.ToString();
                )
                {
                    hours = " + ts.Hours.ToString();
                }
                )
                {
                    minutes = " + ts.Minutes.ToString();
                }
                )
                {
                    seconds = " + ts.Seconds.ToString();
                }

               if( MessageBox.Show("花费时间\n" + hours + ":" + minutes + ":" + seconds, "完成")==DialogResult.OK)
               {
                   ;
               }
            }

        }

    }
}