C#读取和写入Excel文档

    Excel是Microsoft Office中一款重要的软件，有强大的数据处理功能，用户可以通过Excel方便的操作和强大的公式以及图表处理现有的数据。
    在Excel文档中，数据是有明确的标识的，一般由其行名称和列名称进行标识。在C#中向Excel文档写入数据时，Microsoft Excel X Object Library也提供了这种支持。只需明确地给出所需添加的位置，即可向Excel文档的指定位置添加数据。

 先创建一个空白Excel文件： E:\TEMP\myTestExcel.xlsx，和一个待读取文件：  E:\TEMP\英超俱乐部.xls

其中，待读取文件内容如下图所示：

首先，添加如下引用：

具体代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ReadWriteExcel
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : System.Windows.Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        //从Excel读取内容
        private void btnReadExcel_Click(object sender, RoutedEventArgs e)
        {
            string readFilePathName = @"E:\TEMP\英超俱乐部.xls";
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + readFilePathName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            DataSet ds = new DataSet();

            string tableName = dt.Rows[0][2].ToString().Trim();//获取Excel的表名，默认值是sheet1
            //string strSql = "select * from [" + tableName + "]";
            string strSql = "select * from [" + tableName + "]";
            OleDbCommand cmd = new OleDbCommand(strSql, conn);
            
            OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
            da.Fill(ds);//填充数据

            //遍历DataSet并输出其中的内容
            foreach (DataRow col in ds.Tables[0].Rows)
            {
                Debug.WriteLine(col[1].ToString());//输出了第0列的所有单元格内容
            }
        }
       
        //向Excel写入内容
        private void btnWriteExcel_Click(object sender, RoutedEventArgs e)
        {
           Microsoft.Office.Interop.Excel.Application xapp = new Microsoft.Office.Interop.Excel.Application();
           string filePathName = @"E:\TEMP\myTestExcel.xlsx";
           Microsoft.Office.Interop.Excel._Workbook xbook=xapp.Workbooks.Open(filePathName,Missing.Value,
               Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
               Missing.Value, Missing.Value,Missing.Value,Missing.Value);
           Microsoft.Office.Interop.Excel.Worksheet xsheet = xbook.Sheets[1];
           Microsoft.Office.Interop.Excel.Range ran = xsheet.get_Range("F1", Missing.Value);
           ran.Value2 = "allen(test)";

           Microsoft.Office.Interop.Excel.Range rng2 = xsheet.get_Range("A4", Missing.Value);
           rng2.Value2 = "10";

           Microsoft.Office.Interop.Excel.Range rng3 = xsheet.get_Range("B4", Missing.Value);
           rng3.Value2 = "20";

           Microsoft.Office.Interop.Excel.Range rng4 = xsheet.get_Range("C4", Missing.Value);
           rng4.Value2 = "30";

           Microsoft.Office.Interop.Excel.Range rng5 = xsheet.get_Range("D4", Missing.Value);
           rng5.Value2 = "40";

           Microsoft.Office.Interop.Excel.Range rng6 = xsheet.get_Range("E4", Missing.Value);
           rng6.Value2 = "50";

           Microsoft.Office.Interop.Excel.Range rng7 = xsheet.get_Range("F4", Missing.Value);
           rng7.Value2 = "60";

           Microsoft.Office.Interop.Excel.Range rng8 = xsheet.get_Range("G4", Missing.Value);
           rng8.Value2 = "70";

           Microsoft.Office.Interop.Excel.Range rng9 = xsheet.get_Range("H4", Missing.Value);
           rng9.Value2 = "80";

           Microsoft.Office.Interop.Excel.Range rng10 = xsheet.get_Range("I4", Missing.Value);
           rng10.Value2 = "90";

           Microsoft.Office.Interop.Excel.Range rng11 = xsheet.get_Range("A5", Missing.Value);
           rng11.Value2 = DateTime.Now.ToLocalTime().ToString("yyyy-MM--dd HH:mm");

           xbook.Save();

           xbook.SaveAs(@"C:\test.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
               Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

           MessageBox.Show("写入成功");
        }

    }
}

读取效果：

写入效果：

完！