有些时候不想使用第三方组件来操作Excel，微软Office自带的库是一个不错的选择。可以在传统的WindowsFrom中使用，也可以在WPF中使用。
1. 首先的添加 Microsoft.Office.Interop.Excel 组件
2. WindowsForm 创建的项目
Excel内容如下：
实现读取Excel中内容，显示在UI DataGridView上。
完整代码如下：
using System;
using System.Data;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.IO;
namespace 使用微软数据引擎操作Excel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void btnRead_Click(object sender, EventArgs e)
{
string filePath = string.Empty;
string fileExt = string.Empty;
OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
{
filePath = file.FileName; //get the path of the file
fileExt = Path.GetExtension(filePath); //get the file extension
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
try
{
DataTable dtExcel = new DataTable();
dtExcel = ReadExcel(filePath, fileExt); //read excel file
dataGridView1.Visible = true;
dataGridView1.DataSource = dtExcel;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else
{
MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
}
}
}
public DataTable ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0)
{
//for below excel 2007
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";
// “HDR =Yes;” 表示第一行包含列名，而不是数据。“HDR =No;” 表明相反;
}
else
{
//for above excel 2007
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES';";
}
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return dtexcel;
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
3. 问题处理
未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。
将项目生成平台该为 X64即可
4. WPF项目一样的方式，添加引用Microsoft.Office.Interop.Excel 组件
UI设计代码如下：
<Window x:Class="使用微软数据引擎操作Excel_WPF.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:使用微软数据引擎操作Excel_WPF"
mc:Ignorable="d"
Title="MainWindow" Height="450" Width="800">
<Grid>
<DockPanel Margin="3">
<StackPanel DockPanel.Dock="Bottom">
<Button x:Name="btnReadExcel" Click="btnReadExcel_Click">Read Excel</Button>
</StackPanel>
<DataGrid x:Name="dgSource" DockPanel.Dock="Top" Margin="5"></DataGrid>
</DockPanel>
</Grid>
</Window>
后台完整带如下：
using System;
using System.Windows;
using System.Data;
using System.Data.OleDb;
using WForm = System.Windows.Forms;
namespace 使用微软数据引擎操作Excel_WPF
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
public DataTable ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0)
{
//for below excel 2007
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';";
// “HDR =Yes;” 表示第一行包含列名，而不是数据。“HDR =No;” 表明相反;
}
else
{
//for above excel 2007
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES';";
}
using (OleDbConnection con = new OleDbConnection(conn))
{
try
{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message);
}
}
return dtexcel;
}
private void btnReadExcel_Click(object sender, RoutedEventArgs e)
{
string filePath = string.Empty;
string fileExt = string.Empty;
WForm.OpenFileDialog file = new WForm.OpenFileDialog(); //open dialog to choose file
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
{
filePath = file.FileName; //get the path of the file
fileExt = System.IO.Path.GetExtension(filePath); //get the file extension
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
try
{
DataTable dtExcel = new DataTable();
dtExcel = ReadExcel(filePath, fileExt); //read excel file
dgSource.ItemsSource = dtExcel.DefaultView;
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message.ToString());
}
}
else
{
System.Windows.MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning"); //custom messageBox to show error
}
}
}
}
}
注意也要将项目生成平台该为 X64。
