using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Read
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 连接字符串
        /// </summary>
        static string strConn = "";
        /// <summary>
        /// 搜索弹出选择Excel框，选择Excel，获取所有工作表。
        /// 将工作表名赋值给comboBox1，显示所有工作表，用户可以筛选工作表读取数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void label5_Click(object sender, EventArgs e)
        { 
            //清空comboBox1中现有的工作表
            comboBoxSheet.Items.Clear();   
            //打开一个文件选择框
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.Filter = "Excel文件(*.xls)|*";
            try
            {
                //选中文件
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    //获取选中文件的路径
                    this.textBoxFilePath.Text = ofd.FileName;                 
                    //获取文件后缀名 
                    if (System.IO.Path.GetExtension(ofd.FileName).ToLower() == ".xls")
                    {
                        //如果是07以下（.xls）的版本的Excel文件就使用这条连接字符串
                        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ofd.FileName + ";Extended Properties=Excel 8.0;";
                    }
                    else
                    {  
                        //如果是07以上(.xlsx)的版本的Excel文件就使用这条连接字符串
                        strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + ofd.FileName + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此連接可以操作.xls與.xlsx文件
                    }
                    if (System.IO.Path.GetExtension(ofd.FileName).ToLower().Contains( ".xls"))
                    {
                        //打开Excel的连接，设置连接对象
                        OleDbConnection conn = new OleDbConnection(strConn);
                        conn.Open();
                        DataTable sheetNames = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        conn.Close();
                        int i = 0;
                        //遍历Excel文件获取Excel工作表，并将所有工作表名称加载到comboBox控件中
                        foreach (DataRow dr in sheetNames.Rows)
                        {
                            if (i == 0)
                            {
                                this.comboBoxSheet.Text = dr[2].ToString();
                            }
                            //添加工作表名称
                            comboBoxSheet.Items.Add(dr[2]);
                            i++;
                        }
                    }
                    else
                    {
                        MessageBox.Show("excel 格式不正确！");
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导入文件时出错,文件可能正被打开\r\n"+ex.Message.ToString(), "提示");
            }
        }

        /// <summary>
        /// 查看所有列
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void label3_Click(object sender, EventArgs e)
        {
            //设置鼠标指针为等待状态
            this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
            try
            {
                //清空所有节点
                this.treeView1.Nodes.Clear();
                OleDbConnection conn = new OleDbConnection(strConn);
                //当前选中的工作表前几行数据，获取数据列
                OleDbDataAdapter oada = new OleDbDataAdapter("select top 5 * from [" + comboBoxSheet.Text + "]", strConn);
                DataTable ds = new DataTable();
                oada.Fill(ds);
                //将列加载到树节点上
                for (int i = 0; i < ds.Columns.Count; i++)
                { 
                    TreeNode node = new TreeNode();
                    node.Name = i.ToString();
                    node.Text = ds.Columns[i].ColumnName.ToString();
                    this.treeView1.Nodes.Add(node);
                }
                conn.Close();                 
            }
            catch (Exception ex)
            {
                
            }
            //设置鼠标指针状态为默认状态
            this.Cursor = System.Windows.Forms.Cursors.Default;           

        } 
        /// <summary>
        /// 删除选择项目
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            //遍历树节点
            for (int i = treeView1.Nodes.Count - 1; i >= 0; i--)
            {
                TreeNode node = new TreeNode();
                node = treeView1.Nodes[i];
                if (node.Checked)
                {
                    //删除已经勾选的节点，倒序删除，防止异常
                    treeView1.Nodes.Remove(treeView1.Nodes[i]);
                }
            }
        }
        /// <summary>
        /// 检索数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void label1_Click(object sender, EventArgs e)
        {
            try
            {
                //存储需要查询数据的列
                string strName = "";
                //遍历列名，每个列名用逗号隔开
                for (int i = 0; i < treeView1.Nodes.Count; i++)
                {
                    TreeNode n = new TreeNode();
                    n = treeView1.Nodes[i];
                    strName = strName + "[" + n.Text + "],";
                }
                strName = strName.Substring(0, strName.Length - 1); 
                //获取用户没有删掉留下来的列，读取这些列的数据
                //建立Excel连接
                OleDbConnection conn = new OleDbConnection(strConn);
                //读取数据
                OleDbDataAdapter oada = new OleDbDataAdapter("select  " + strName + " from [" + comboBoxSheet.Text + "]", strConn);
                DataTable dt = new DataTable();
                //填入DataTable
                oada.Fill(dt);
                conn.Close();
                //显示这些列的数据
                dataGridView1.DataSource = dt;
            }
            catch (Exception ex)
            {
                 
            }
        }
        private void label5_MouseEnter(object sender, EventArgs e)
        {
            this.Cursor = Cursors.Hand;
            ((Label)sender).BackColor = Color.LightGray;
        }
        private void label5_MouseLeave(object sender, EventArgs e)
        {
            this.Cursor = Cursors.Default;
            ((Label)sender).BackColor = Control.DefaultBackColor;
        } 
    }
}