C#读取Excel

本文介绍2种C#读取Excel文件的方法。

第一种，通过OleDB的方式，与读取普通数据库类似。示例代码如下：
 public DataSet readExcelData(string sFile)
 {
  DataSet result = null;
  string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFile + ";" + "Extended Properties=Excel 8.0;";
  OleDbConnection conn = new OleDbConnection(strConn);
  try
  {
          conn.Open();
          string strExcel = "select * from [sheet1$]";
          OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
          result = new DataSet();
          myCommand.Fill(result, "table1");
  }
  finally
  {
   conn.Close();
  }
  return result;
 }
注：此方法，将Excel文件的第一个工作表（sheet1）的内容读入内存，并存储到DataSet对象的第一个数据表DataTable中，工作表的第一行作为数据表的列名。
其优点是读取方便，缺点是如果第一行中有特殊字符，列名将不能被正确转换，而代替以Fn，从而不能完整地读取Excel文件的内容。

第二种，利用VSTO(Micorosoft Visual Studio Tools for Office)对Excel直接读取.示例代码如下:
        using Excels=Microsoft.Office.Interop.Excel;
        ......
 public DataSet readExcelData(string sFile)
 {
            DataSet result = null;
            System.Data.DataTable dtResult = null;
            Excels.ApplicationClass app = new Excels.ApplicationClass();
            if (app !=null)
                try
                {
                    Excels.Workbook wb = app.Workbooks.Open(sFile, System.Type.Missing, false, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing,
                        System.Type.Missing, ",", true, System.Type.Missing, System.Type.Missing, true, System.Type.Missing, System.Type.Missing);
                    if (wb != null)
                    {
                        Excels.Worksheet sheet = wb.ActiveSheet as Excels.Worksheet;
                        if (sheet != null)
                        {
                            int r = 1;
                            Excels.Range rng = sheet.Cells[r, 1] as Excels.Range;
                            while (rng != null && rng.Value2 != null && rng.Value2.ToString() != "")
                            {
                                if (result == null)
                                {
                                    result = new DataSet();
                                    dtResult = result.Tables.Add();
                                    int y = 1;
                                    Excels.Range rng1 = sheet.Cells[r, 1] as Excels.Range;
                                    while (rng1 != null && rng1.Value2 != null && rng1.Value2.ToString() != "")
                                    {
                                        dtResult.Columns.Add("col" + y);
                                        rng1 = sheet.Cells[r, y++] as Excels.Range;
                                    }
                                }
                                string[] aryValue = new string[dtResult.Columns.Count];
                                for (int i = 0; i < aryValue.Length; i++)
                                {
                                    Excels.Range rng1 = sheet.Cells[r, i + 1] as Excels.Range;
                                    aryValue[i] = (rng1 == null || rng1.Value2 == null) ? "" : rng1.Value2.ToString();
                                }
                                dtResult.Rows.Add(aryValue);
                                rng = sheet.Cells[r++, 1] as Excels.Range;
                            }
                        }
                    }
                }
                finally
                {
                    app.Quit();
                }
            return result;
        }
注:该方法需要安装VSTO组件,一般随Microsoft Visual Studio开发环境一起提供，安装完成后，在新建项目中添加引用Microsoft.Office.Interop.Excel即可。示例代码中将Excel文件各行（包括第1行）内容读取到数据表中，数据表各列自动取名为coln。