发布时间:2020-09-07编辑:佚名阅读(1555)
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; } } }
下载源码ReadExcel.zip
在打开Excel文件时可能会报未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”,解决方法参考未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序。
0人
0人
0人
0人