编写的c#读取Excel表中的內容，并把內容显示到UltraWebGrid上

在读取Excel表格之前，必须加载Excel的dll，然则在后台页面加载
using  Excel;
using  System.Data.OleDb;

定义一个DateTable存储Excel读取的数据
private   static  System.Data.DataTable dtFirSheet  =   new  System.Data.DataTable();

 

  /**/ /// <summary>
    /// 將Excel的内容讀到DataTable
    /// </summary>
    /// <param name="fileName">被讀的Excel文件</param>
    /// <returns></returns>
     private  System.Data.DataTable GetDataTable( string  fileName)
     ... {
        string excelPath = Server.MapPath(Request.ApplicationPath) + "/" + "Excel" + "/" + fileName;
        Excel.Application oExcel = new ApplicationClass();
        Excel.Workbooks oBooks = null;
        Excel.Workbook oBook = null;
        Excel.Sheets oSheets = null;
        Excel.Worksheet oSheetFir = null;

        oExcel.Visible = false;
        oExcel.DisplayAlerts = false;
        oBooks = oExcel.Workbooks;
        oBooks.Open(excelPath, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        oBook = oBooks.get_Item(1);
        oSheets = oBook.Worksheets;
        oSheetFir = (Excel.Worksheet)oSheets.get_Item(1);

        System.Data.DataTable dt = new System.Data.DataTable();
        string sheetNameFir = oSheetFir.Name;
        if (oExcel != null)
        ...{
            oExcel.ActiveWorkbook.Close(false, Type.Missing, Type.Missing);
        }
        oExcel.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheets);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheetFir);
        GC.Collect();
        OleDbConnection oleConn = new OleDbConnection();
        try
        ...{
            string strConn = "provider=Microsoft.Jet.OLEDB.4.0;data source='" + excelPath + "';Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"";
            oleConn.ConnectionString = strConn;
            oleConn.Open();
            string strSql = "select TOP 300 * from [" + sheetNameFir + "$]";
            OleDbDataAdapter oleDa = new OleDbDataAdapter(strSql, oleConn);
            oleDa.Fill(dt);
            oleConn.Close();
        }
        catch (System.Exception ex)
        ...{
            throw new Exception(ex.Message);
        }
        finally
        ...{
            oleConn.Close();
        }
        return dt;
    }

 

将DateTable中的数据存入Session中，可以传到任意需要的界面，如下：
  Session.Remove( " DtProj " );
        Session.Add( " DtProj " , dtFirSheet);

 

 定义Datatable接收传来的数据，并将此数据显示到UltraWebGrid上
DataTable dtGrid  =   new  DataTable();
                dtGrid  =  (DataTable)Session[ " DtProj " ];
                GetNewTable(dtGrid);
                UltAddGrid.DataSource  =  dtGrid.DefaultView;
                UltAddGrid.DataBind();
                SetGridFormat( " Default " );
                Session.Remove( " DtProj " );

GetNewTable(Datatable dtTem)事件如下
/**/ /// <summary>
    /// 描述：Datatable中無用的行被刪除
    /// </summary>
    /// <param name="dtTem"></param>
     private   void  GetNewTable(DataTable dtTem)
     ... {
        for (int rowNum = 0; rowNum < dtTem.Rows.Count; rowNum++)
        ...{
            if ((dtTem.Rows[rowNum][1].ToString().Trim() == "") || (dtTem.Rows[rowNum][0].ToString()== "序號"))
            ...{
                dtTem.Rows.RemoveAt(rowNum);
                dtTem.AcceptChanges();
                rowNum--;
            }
        }
    }

至此，读取并显示Excel的內容结束！