使用微软Office组件读取Excel文件

有些时候不想使用第三方组件来操作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。