.net读取xml数据的方法_站点基地[zdbase.com]

详细内容

[关闭窗口]

.net读取xml数据的方法

发布日期：2011-11-02 点击：3546 字体：[大中小]

using System.Data;

using System.Data.OleDb;

/// <summary>
  /// 取得Excel中数据
  /// </summary>
  /// <param name="_path"></param>
  /// <returns></returns>
  public DataSet XlsToDs(string _path)
  {
   string my_StrConnection;
   string my_StrSelect;
   DataSet my_Ds = null;
   OleDbConnection my_conn = null;
   OleDbDataAdapter my_Adapter;
   OleDbCommandBuilder my_Builder;

   try
   {
    //string strSheetName = GetExcelWorkSheet(_path);
    string strSheetName = "SHEET1";

    //数据库连接
    my_StrConnection= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+_path+";Extended Properties=Excel 8.0;";
    //my_StrSelect="SELECT * FROM [SHEET1$]";
    my_StrSelect="SELECT * FROM [" + strSheetName + "$]";
    my_conn = new OleDbConnection(my_StrConnection);
    my_conn.Open();
    my_Adapter = new OleDbDataAdapter(my_StrSelect,my_conn);
    my_Builder=new OleDbCommandBuilder(my_Adapter);
    my_Ds=new DataSet();

    //填充数据集
    my_Adapter.Fill(my_Ds,"ExcelData");
    my_conn.Close();
    return my_Ds;
   }
   catch
   {
    if( my_conn != null )
    {
     my_conn.Close();
    }
    return null;
   }
  }

以上方法对excel2007以前版本有效，但对excel2007就力不从心了，下面给出可以读取所有版本的代码：

读取xlsx 用的是Microsoft.Ace.OleDb.12.0;

具体操作方法如下：

public static DataTable GetExcelToDataTableBySheet(string FileFullPath, string SheetName)

{

//string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件

string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + FileFullPath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

DataSet ds = new DataSet();

OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn); //("select * from [Sheet1$]", conn);

odda.Fill(ds, SheetName);

conn.Close();

return ds.Tables[0];

}

读取Excel文件时，可能一个文件中会有多个Sheet，因此获取Sheet的名称是非常有用的。

具体操作方法如下：

//根据Excel物理路径获取Excel文件中所有表名

public static String[] GetExcelSheetNames(string excelFile)
{
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            try
            {
                //string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + excelFile + ";Extended Properties='Excel 8.0; HDR=NO; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件

                string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + excelFile + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件
                objConn = new OleDbConnection(strConn);
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                String[] excelSheets = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
                return excelSheets;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
}