欢迎加入站点基地设计开发交流群:30720409
详细内容
.net读取xml数据的方法
发布日期:2011-11-02     点击:2921     字体:[ ]

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();
                }
            }
}

用户评论
昵称 
内容  *
验证码   
   
Copyright © 2010 zdbase.com All Rights Reserved. 苏ICP备15039389号-1 可人软件设计