這次要將讀取到的Office資料透過DataGridView去顯示，以前有寫過類似的東西，知道必須透過OLE DB去取得資料
什麼是OLE(Object Linking and Embedding)？OLE就是可以在檔案內嵌入不同的檔案
舉例：Word內可嵌入Excel、圖片、PDF等等的檔案
OLE DB是為了擴充不支援SQL語法的程式，而透過COM元件去實做出來的
程式內容會因為openToolStripMenuItem點擊事件『openToolStripMenuItem_Click』而觸發
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel 2003|*.xls|Excel 2007|*.xlsx";
if (dialog.ShowDialog() == DialogResult.OK)
{
//File Path
string path = dialog.FileName;
string oleStr = GetOleStr(ref path);
try
{
using (OleDbConnection ole = new OleDbConnection(oleStr))
{
//OLE SQL
const string sql = @"SELECT * FROM [tableName$]";
using (OleDbCommand cmd = new OleDbCommand(sql, ole))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
DataTable table = new DataTable();
adapter.Fill(table);
dataGridView1.DataSource = table;
}
}
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
}
}
透過OpenFileDialog去取得到檔案位址
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel 2003|*.xls|Excel 2007|*.xlsx";
if (dialog.ShowDialog() == DialogResult.OK)
{
.....
}
並將檔案位址透過『GetOleStr』方法去選擇使用OLE DB4.0或是OLE DB12.0語法，OLE DB4.0是給2003使用，OLE DB12.0是給2007使用
//File Path
string path = dialog.FileName;
string oleStr = GetOleStr(ref path);
private string GetOleStr(ref string path)
{
//isXls = true then file is Excel 2003
bool isXls = path.EndsWith(".xls");
string oleStr = string.Empty;
if (isXls)
{
//2003 OLE
oleStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", path);
}
else
{
//2007 OLE
oleStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", path);
}
return oleStr;
}
『GetOleStr』方法內透過path.EndsWith方法去知道最後檔名為『Excel 2003的xls』或是『Excel 2007的xlsx』，並且回傳一個OLE DB語法
HDR全文為『HeaDer Row』
- 如果值為Yes，則代表第一行是標題
- 如果值為No，則代表第一行是資料
IMEX全文為『IMport EXport mode』
- 值為0，則只能匯出使用
- 值為1，則只能匯入使用
- 值為2，可以匯出也可以匯入
接著透過OleDbConnection去做連接
using (OleDbConnection ole = new OleDbConnection(oleStr))
{
//OLE SQL
const string sql = @"SELECT * FROM [tableName$]";
using (OleDbCommand cmd = new OleDbCommand(sql, ole))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
DataTable table = new DataTable();
adapter.Fill(table);
dataGridView1.DataSource = table;
}
}
}
使用SQL語法選擇資料表，注意語法必須為"SELECT * FROM [資料表欄位$]"，否則會無法取得資料
const string sql = @"SELECT * FROM [tableName$]";
用OleDbDataAdapter將資料注入到DataTable或DataSet，兩者有差異，有機會之後會寫一篇比較文
DataTable table = new DataTable(); adapter.Fill(table); dataGridView1.DataSource = table;
資料注入後就可以看得到結果啦
Designer.cs
namespace OpenExcelForm
{
partial class OpenExcelForm
{
/// <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.dataGridView1 = new System.Windows.Forms.DataGridView();
this.menuStrip1 = new System.Windows.Forms.MenuStrip();
this.fileToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();
this.openToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();
this.exitToolStripMenuItem = new System.Windows.Forms.ToolStripMenuItem();
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
this.menuStrip1.SuspendLayout();
this.SuspendLayout();
//
// dataGridView1
//
this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.dataGridView1.Location = new System.Drawing.Point(12, 27);
this.dataGridView1.Name = "dataGridView1";
this.dataGridView1.RowTemplate.Height = 24;
this.dataGridView1.Size = new System.Drawing.Size(682, 310);
this.dataGridView1.TabIndex = 1;
//
// menuStrip1
//
this.menuStrip1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
this.fileToolStripMenuItem});
this.menuStrip1.Location = new System.Drawing.Point(0, 0);
this.menuStrip1.Name = "menuStrip1";
this.menuStrip1.Size = new System.Drawing.Size(706, 24);
this.menuStrip1.TabIndex = 2;
this.menuStrip1.Text = "menuStrip1";
//
// fileToolStripMenuItem
//
this.fileToolStripMenuItem.DropDownItems.AddRange(new System.Windows.Forms.ToolStripItem[] {
this.openToolStripMenuItem,
this.exitToolStripMenuItem});
this.fileToolStripMenuItem.Name = "fileToolStripMenuItem";
this.fileToolStripMenuItem.Size = new System.Drawing.Size(39, 20);
this.fileToolStripMenuItem.Text = "File";
//
// openToolStripMenuItem
//
this.openToolStripMenuItem.Name = "openToolStripMenuItem";
this.openToolStripMenuItem.Size = new System.Drawing.Size(152, 22);
this.openToolStripMenuItem.Text = "Open";
this.openToolStripMenuItem.Click += new System.EventHandler(this.openToolStripMenuItem_Click);
//
// exitToolStripMenuItem
//
this.exitToolStripMenuItem.Name = "exitToolStripMenuItem";
this.exitToolStripMenuItem.Size = new System.Drawing.Size(152, 22);
this.exitToolStripMenuItem.Text = "Exit";
this.exitToolStripMenuItem.Click += new System.EventHandler(this.exitToolStripMenuItem_Click);
//
// OpenExcelForm
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(706, 348);
this.Controls.Add(this.dataGridView1);
this.Controls.Add(this.menuStrip1);
this.MainMenuStrip = this.menuStrip1;
this.Name = "OpenExcelForm";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
this.menuStrip1.ResumeLayout(false);
this.menuStrip1.PerformLayout();
this.ResumeLayout(false);
this.PerformLayout();
}
#endregion
private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.MenuStrip menuStrip1;
private System.Windows.Forms.ToolStripMenuItem fileToolStripMenuItem;
private System.Windows.Forms.ToolStripMenuItem openToolStripMenuItem;
private System.Windows.Forms.ToolStripMenuItem exitToolStripMenuItem;
}
}
OpenExcelForm.cs
using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace OpenExcelForm
{
public partial class OpenExcelForm : Form
{
public OpenExcelForm()
{
InitializeComponent();
}
private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog dialog = new OpenFileDialog();
dialog.Filter = "Excel 2003|*.xls|Excel 2007|*.xlsx";
if (dialog.ShowDialog() == DialogResult.OK)
{
//File Path
string path = dialog.FileName;
string oleStr = GetOleStr(ref path);
try
{
using (OleDbConnection ole = new OleDbConnection(oleStr))
{
//OLE SQL
const string sql = @"SELECT * FROM [tableName$]";
using (OleDbCommand cmd = new OleDbCommand(sql, ole))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
{
DataTable table = new DataTable();
adapter.Fill(table);
dataGridView1.DataSource = table;
}
}
}
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
}
}
}
private string GetOleStr(ref string path)
{
//isXls = true then file is Excel 2003
bool isXls = path.EndsWith(".xls");
string oleStr = string.Empty;
if (isXls)
{
//2003 OLE
oleStr = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", path);
}
else
{
//2007 OLE
oleStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'", path);
}
return oleStr;
}
}
}
參考資料：
http://seanlu1688.pixnet.net/blog/post/47385718
http://social.msdn.microsoft.com/Forums/zh-TW/a9416a68-92f8-4f62-a3e0-59a18f025dd6/-microsoft-jet-ole-db-providerexcel?forum=232
http://www.blueshop.com.tw/board/FUM200410061527123KL/BRD20130508180117XNG.html
http://en.wikipedia.org/wiki/Object_Linking_and_Embedding
http://blog.csdn.net/ecnuzhangsq/article/details/5907689