利用Oledb操作Xlsx文件
来源:互联网 发布:mac如何修复磁盘权限 编辑:程序博客网 时间:2024/06/06 15:39
利用Oledb操作Xlsx文件
简介:
解决方案:
Form1.designer.cs:
namespace XlsxViaOledb{ partial class Form1 { /// <summary> /// Required designer variable. /// </summary> private System.ComponentModel.IContainer components = null; /// <summary> /// Clean up any resources being used. /// </summary> /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param> protected override void Dispose(bool disposing) { if (disposing && (components != null)) { components.Dispose(); } base.Dispose(disposing); } #region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.components = new System.ComponentModel.Container(); this.dataGridView1 = new System.Windows.Forms.DataGridView(); this.comboBox1 = new System.Windows.Forms.ComboBox(); this.label1 = new System.Windows.Forms.Label(); this.button1 = new System.Windows.Forms.Button(); this.toolTip1 = new System.Windows.Forms.ToolTip(this.components); ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit(); this.SuspendLayout(); // // dataGridView1 // this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize; this.dataGridView1.Location = new System.Drawing.Point(12, 34); this.dataGridView1.Name = "dataGridView1"; this.dataGridView1.ShowCellToolTips = false; this.dataGridView1.Size = new System.Drawing.Size(504, 233); this.dataGridView1.TabIndex = 0; this.dataGridView1.DataError += new System.Windows.Forms.DataGridViewDataErrorEventHandler(this.dataGridView1_DataError); this.dataGridView1.MouseDown += new System.Windows.Forms.MouseEventHandler(this.dataGridView1_MouseDown); // // comboBox1 // this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList; this.comboBox1.FormattingEnabled = true; this.comboBox1.Location = new System.Drawing.Point(130, 9); this.comboBox1.Name = "comboBox1"; this.comboBox1.Size = new System.Drawing.Size(130, 20); this.comboBox1.TabIndex = 1; // // label1 // this.label1.AutoSize = true; this.label1.Location = new System.Drawing.Point(12, 12); this.label1.Name = "label1"; this.label1.Size = new System.Drawing.Size(95, 12); this.label1.TabIndex = 2; this.label1.Text = "Select a sheet:"; // // button1 // this.button1.Location = new System.Drawing.Point(302, 8); this.button1.Name = "button1"; this.button1.Size = new System.Drawing.Size(75, 21); this.button1.TabIndex = 3; this.button1.Text = "Show"; this.button1.UseVisualStyleBackColor = true; this.button1.Click += new System.EventHandler(this.button1_Click); // // toolTip1 // this.toolTip1.IsBalloon = true; this.toolTip1.UseAnimation = false; this.toolTip1.UseFading = false; // // Form1 // this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(528, 278); this.Controls.Add(this.button1); this.Controls.Add(this.label1); this.Controls.Add(this.comboBox1); this.Controls.Add(this.dataGridView1); this.Name = "Form1"; this.Text = "Form1"; this.FormClosed += new System.Windows.Forms.FormClosedEventHandler(this.Form1_FormClosed); this.Load += new System.EventHandler(this.Form1_Load); ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit(); this.ResumeLayout(false); this.PerformLayout(); } #endregion private System.Windows.Forms.DataGridView dataGridView1; private System.Windows.Forms.ComboBox comboBox1; private System.Windows.Forms.Label label1; private System.Windows.Forms.Button button1; private System.Windows.Forms.ToolTip toolTip1; }}
Form.cs
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 System.Data.OleDb;namespace XlsxViaOledb{ public partial class Form1 : Form { OleDbConnection conn; OleDbDataAdapter adapter; DataTable dt; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { // connect to xls file // NOTE: it will be created if not exists try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + Application.StartupPath + "\\test.xlsx;" + "Extended Properties=Excel 12.0 Xml"); conn.Open(); } catch { try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.14.0;" + "Data Source=" + Application.StartupPath + "\\test.xlsx;" + "Extended Properties=Excel 14.0 Xml"); conn.Open(); } catch { try { conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.15.0;" + "Data Source=" + Application.StartupPath + "\\test.xlsx;" + "Extended Properties=Excel 15.0 Xml"); conn.Open(); } catch { } } } // create a sheet "Sheet1" if not exists // NOTE: no "id" field needed // WARNING: spaces in sheet's name are supported if names are in [] (automatically replace with _) // spaces in column names NOT supported with OleDbCommandBuilder! try { string cmdText = "CREATE TABLE [Sheet 1] (text_col MEMO, int_col INT)"; using (OleDbCommand cmd = new OleDbCommand(cmdText, conn)) { cmd.ExecuteNonQuery(); } } catch { } // get sheets list into combobox dt = conn.GetSchema("Tables"); for (int i = 0; i < dt.Rows.Count - 1; i++) { if (dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_TYPE")].ToString() == "TABLE" && !dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")].ToString().Contains("$")) { comboBox1.Items.Add(dt.Rows[i].ItemArray[dt.Columns.IndexOf("TABLE_NAME")]); } } } private void button1_Click(object sender, EventArgs e) { adapter = new OleDbDataAdapter("SELECT * FROM " + comboBox1.SelectedItem.ToString(), conn); new OleDbCommandBuilder(adapter); dt = new DataTable(); adapter.Fill(dt); dataGridView1.DataSource = dt; } private void Form1_FormClosed(object sender, FormClosedEventArgs e) { if (adapter == null) return; adapter.Update(dt); } // show tooltip (not intrusive MessageBox) when user trying to input letters into INT column cell private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e) { if (dt.Columns[e.ColumnIndex].DataType == typeof(Double)) { Rectangle rectColumn; rectColumn = dataGridView1.GetColumnDisplayRectangle(e.ColumnIndex, false); Rectangle rectRow; rectRow = dataGridView1.GetRowDisplayRectangle(e.RowIndex, false); toolTip1.ToolTipTitle = "This field is for integers and decimals only."; toolTip1.Show(" ", dataGridView1, rectColumn.Left, rectRow.Top + rectRow.Height); } } private void dataGridView1_MouseDown(object sender, MouseEventArgs e) { toolTip1.Hide(dataGridView1); } }}
利用Oledb操作XLS文件
0 0
- 利用Oledb操作Xlsx文件
- 利用Oledb操作XLS文件
- C# 利用 OLEDB 操作 Excel 文件
- C#利用OleDb操作Excel文件
- 利用NPOI组件读写XLSX文件
- libxl跨平台库 操作xlsx文件
- python操作xlsx文件的包openpyxl
- Python操作Excel新版本xlsx文件
- Asp.net/c#+OleDb操作excel文件
- C#操作Excel文件之OleDb
- Asp.net/c#+OleDb操作excel文件
- 在 Node.js 中利用 js-xlsx 处理 Excel 文件
- 利用ashx操作OleDb数据库返回json数据
- 导入xlsx文件提示:The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine
- 如何操作xlsx结尾文件,使其写入文本
- 不安装Office操作Excel文件(.xlsx)
- windows下python装openpyxl 操作 excel文件 xlsx
- .net中EXCEL,txt操作整理,xlsx文件加密
- selenium webdriver 学习总结-Selenium 控制测试流(五)-补充显示等待
- 基于Zigbee和树莓派的家居空气质量检测系统:硬件设计
- 第16周 阅读程序 ( 2)
- 文件系统之open
- Android酷炫实用的开源框架(UI框架)
- 利用Oledb操作Xlsx文件
- Window对象属性和方法
- Android中Application类用法
- XMPP和即时通讯内容学习(一)
- ViewController的生命周期分析和使用
- selenium webdriver 学习总结-JUnit4 入门(三)_补充Annotation
- PHP 求任意n个正负整数里面最大的连续和
- Log4j的配置与使用详解
- 通过WebService上传文件的原理