Excel文件导入DataTable(2) -- 直接打开Excel文件

通过OleDb导入方便很多，但是有时候我们想直接操作Excel文件。使用Excel的Com组件。

首先导入Microsoft.Office.Interop.Excel，我的是Office2007，对应版本号为12.0.0.0

再添加命名空间：
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

其余部分代码如下：

public void ReadExcel(string FilePath)
{
    object Nothing = System.Reflection.Missing.Value;
             
    Excel.Application xlApp = null;
    Excel.Workbooks workbooks = null;
    Excel.Workbook workbook =  null;
    Excel.Sheets sheets  = null;
    Excel.Worksheet worksheet = null;
    Excel.Range range1 = null;
    Excel.Range range2 = null;
        
    try
    {
                  
        xlApp = new Excel.Application();
            
        if(xlApp == null)
        {
            MessageBox.Show("无法创建Excel对象，可能您未安装Excel");
            return;
        }
            
        workbooks=xlApp.Workbooks;
        workbook = workbooks.Add(FilePath);
        sheets = workbook.Sheets;
        worksheet = (Excel.Worksheet)sheets.get_Item(1);
                
        int rowCount = worksheet.UsedRange.Rows.Count;
        int colCount = worksheet.UsedRange.Columns.Count;
                
        if (rowCount <= 0)
            throw new Exception("文件中没有数据记录");
                
        DataTable dataTable = new DataTable();
            
        //此处开设二维数组，因为Range.Value2只能强制转换为二位数组
        //并且rang1强转时，对应二位数组编号从1,1开始！
        //即二维数组0行0列均为null。
        Object[,] tempObject2 = new object[2,colCount+1];
        range1 = worksheet.get_Range(worksheet.Cells[1,1],worksheet.Cells[1,colCount]);
        tempObject2 = (Object[,])range1.Value2;
            
        for(int i=1; i<=colCount; i++)
        {
            dataTable.Columns.Add(tempObject2[1,i].ToString(),System.Type.GetType("System.String"));        
        }                
                
        //此处如果继续使用range1，则必须首先使用Resize函数，重置rang1的大小！！
        Object[,] tempObject = new object[rowCount,colCount+1];
        range2=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[rowCount,colCount]);
        tempObject = (object[,])(range2.Value2);
                                        
        for(int j=1; j<rowCount-1; j++)
        {
            DataRow dr1 = dataTable.NewRow();
            for(int i=1; i<colCount+1; i++)
            {
                dr1[i-1]=tempObject[j,i].ToString();
            }                    
            dataTable.Rows.Add(dr1);
        }
                
        //自己创建一个DataGrid，将datatable作为数据源
        this.dataGrid1.DataSource = dataTable;
                
       }    
       finally
       {
           //结束Excel进程！有待进一步研究。
        if (xlApp != null)
           {
               if (workbooks != null)
               {
                 if (workbook != null)
                   {
                       if (worksheet != null)
                       {
                          if (range1 != null)
                           {
                               System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
                               range1 = null;
                           }
                           if (range2 != null)
                           {
                               System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
                               range2 = null;
                           }
                           System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                           worksheet = null;
                        }
                       workbook.Close(false,Nothing,Nothing); 
                       System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                       workbook = null;
                   }
                   workbooks.Close();
                   System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                   workbooks = null;
               }
               xlApp.Application.Workbooks.Close();
               xlApp.Quit();
               System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
               xlApp = null;
               GC.Collect();
        }
       }
}