OLEDB对EXCEL进行CURD操作

http://www.cnblogs.com/linzheng/archive/2010/12/15/1907246.html

  
  

   
   

    
    
     
     

      
      
      
      
CRUD数据访问类基类 
      
      
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;


namespace myexcel
      
      
{
    public class DbExcel
    {
        
        /// <summary>
        /// 获取读取连接字符串
        /// </summary>
        /// <param name="phyfilepath"></param>
        /// <returns></returns>
        private static string GetOptionConnstr(string phyfilepath)
        {
            string endstr = phyfilepath.Substring(phyfilepath.IndexOf('.') + 1);
            if (endstr.ToLower() == "xlsx")
            {
                return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=no;\";Data Source=" + phyfilepath;
            }

            return "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=no;\";Data Source=" + phyfilepath;
        }

        /// <summary>
        /// 获取操作连接字符串
        /// </summary>
        /// <param name="phyfilepath"></param>
        /// <returns></returns>
        private static string GetReadConnStr(string phyfilepath)
        {
            string endstr = phyfilepath.Substring(phyfilepath.IndexOf('.') + 1);
            if (endstr.ToLower() == "xlsx")
            {
                return "Provider=Microsoft.ACE.OleDb.12.0;Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1\";Data Source="+phyfilepath; 
            }
   
                return "Provider=Microsoft.Jet." +
                "OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source="+phyfilepath;                
        }

        public static DataTable GetExcelDataTable(string phyfilepath)
        {
            OleDbConnection conn = null;
            string sheetName = "Sheet1";
            DataTable dataTable = null;
            
            try
            {
                using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))
                {
                    conn.Open();
                    DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                        foreach (DataRow dr in sheetNames.Rows)
                        {
                            sheetName = dr[2].ToString().Trim();
                            break;
                        }
                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
                    DataSet ds = new DataSet();
                    oada.Fill(ds, "InitData");

                    dataTable = ds.Tables["InitData"];
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return dataTable;
        }

        public static DataTable GetExcelSheetTable(string phyfilepath)
        {
            OleDbConnection conn = null;
            string sheetName = "Sheet1$";
            DataTable dataTable = null;

            try
            {
                using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))
                {
                    conn.Open();
                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
                    DataSet ds = new DataSet();
                    oada.Fill(ds, "InitData");

                    dataTable = ds.Tables["InitData"];
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
                //return null;
              
            }
            finally
            {
                conn.Close();
            }

            return dataTable;
        }

        public static DataTable GetExcelSheetTable(string phyfilepath,string SheetName)
        {
            OleDbConnection conn = null;
            string sheetName = SheetName;
            DataTable dataTable = null;

            try
            {
                using (conn = new OleDbConnection(GetReadConnStr(phyfilepath)))
                {
                    conn.Open();
                    DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                    OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
                    DataSet ds = new DataSet();
                    oada.Fill(ds, "InitData");

                    dataTable = ds.Tables["InitData"];
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return dataTable;
        }
        
        public static void ExcelColDataUpdate(string phyfilepath, string sheetName,string setvalue,string where)
        {
            OleDbConnection conn = null;
            try
            {
                using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "UPDATE ["+sheetName+"$] "+setvalue+" "+where;
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();

                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        
        
        }

        public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnNames, string values)
        {
            OleDbConnection conn = null;
            try
            {
                using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "insert into [" + sheetName + "$] (" + columnNames + ") values(" + values + ")";
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        
        }

        public static void ExcelVoucherDataInsert(string filePath, string sheetName, DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            if (dt == null || dt.Rows.Count == 0) return;
            try
            {
                using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath)))
                {
                    conn.Open();
                    foreach (DataRow row in dt.Rows)
                    {
                        OleDbCommand cmd = new OleDbCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "insert into [" + sheetName + "$] values('" + row["FName"].ToString() + "','" + row["FNo"].ToString() + "','" + row["FIName"].ToString() + "')";
                        cmd.Connection = conn;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
        }

        public static void  ExcelVoucherDataInsert(string filePath, string sheetName, string itemClass , string number , string name)
        {
            try
            {
                using (OleDbConnection conn = new OleDbConnection(GetOptionConnstr(filePath)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;

                    cmd.CommandText = "insert into [" + sheetName + "$] values('" + itemClass + "','" + number + "','" + name + "')";
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
        }
        

        public static void ExcelColDataInsert(string phyfilepath, string sheetName, string columnName, string[] values)
        {
            OleDbConnection conn = null;
            try
            {
                using (conn = new OleDbConnection(GetOptionConnstr(phyfilepath)))
                {
                    conn.Open();
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.CommandType = CommandType.Text;

                    foreach (string str in values)
                    {
                        cmd.CommandText = "insert into [" + sheetName + "$] (" + columnName + ") values(' " + str + "')";
                        cmd.Connection = conn;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new BaseDBException(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    
    }

}
      
      
复制代码
      
      
编写连接与操作excel文件的通用函数
      
      
代码 
      
      
protected void DoOleSql(string sql, string database)

  {   

  OleDbConnection conn = new OleDbConnection();

  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("\\") + database + "; Extended Properties='Excel 8.0;HDR=no;IMEX=0'";

  try

  {//打开连接

  conn.Open();

  }

  catch (Exception e)

  {

  Response.Write(e.ToString());

  }

  OleDbCommand olecommand = new OleDbCommand(sql, conn);   

  try

  {//执行语句

  olecommand.ExecuteNonQuery();

  }

  catch (Exception eee)

  {

  Response.Write(eee.ToString());

  conn.Close();

  }

  finally

  {

  conn.Close();//关闭数据库

  }

  conn.Close();

}
      
      
复制代码
      
      
 
      
      
 
      
      

注：1）使用 Excel 工作簿时，默认情况下，区域中的第一行是标题行（或字段名称）。如果第一个区域不包含标题，您可以在连接字符串的扩展属性中指定 HDR=NO。如果您在连接字符串中指定 HDR=NO，Jet OLE DB 提供程序将自动为您命名字段（F1 表示第一个字段，F2 表示第二个字段，依此类推）；2）IMEX=1将所有读入数据看作字符，其他值（0、2）请查阅相关帮助文档；3）如果出现“找不到可安装的isam”错误，一般是连接字符串错误

3、从excel文件读取数据

string sql = "select * from [sheet1$]";

DoOleSql(sql,"test.xls");

4、更新excel文件中的数据

string sql = "update [sheet1$] set FieldName1='333' where FieldName2='b3'";

DoOleSql(sql,"test.xls");

5、向excel文件插入数据

string sql = "insert into [sheet1$](FieldName1,FieldName2,…) values('a',’b’,…)";

DoOleSql(sql,"test.xls");

6、删除excel文件中的数据：不提倡使用这种方法

7、对于非标准结构的excel表格，可以指定excel中sheet的范围

1）读取数据：string sql = "select * from [sheet1$A3:F20]";

2）更新数据：string sql = "update [sheet1$A9:F15] set FieldName='333' where AnotherFieldName='b3'";

3）插入数据：string sql = "insert into [sheet1$A9:F15](FieldName1,FieldName2,…) values('a',’b’,…)";

4）删除数据：不提倡

注：1）代码根据需要可以自行修改；2）如果出现“操作必须使用一个可更新的查询”错误，可能sql语句中对excel文件中的“字段”引用有错误，或对 excel文件不具有“修改”权限；3）如果出现“不能扩充选定范围”错误，可能是对excel文件引用的“范围”有错误。