使用oledb访问excel文件

操作方法吧excel当成数据库就行了。sheet就是表！然后写sql 语句，create table ..insert into ,update ,select ..whaterver..

using  System;
using  System.Collections.Generic;
using  System.Linq;
using  System.Text;
using  System.Data.OleDb;
using  System.Data;
using  System.IO;
namespace  Utils
{
     public   class  OLEDBExcelHelper
    {
         public   static   void  ExcuteNonQuery( string  filePath,  string  sql)
        {
             using (OleDbConnection conn  =   new  OleDbConnection(GetConnectionString(filePath)));
            {
                OleDbCommand cmd  =  conn.CreateCommand();
                cmd.CommandText  =  sql;

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
        }
         public   static   void  BatchExcuteNonQuery( string  filePath, IList < string >  sqlList)
        {
             using  (OleDbConnection conn  =   new  OleDbConnection(GetConnectionString(filePath)))
            {
                OleDbCommand cmd  =  conn.CreateCommand();
                conn.Open();
                 foreach  (var sql  in  sqlList)
                {
                    cmd.CommandText  =  sql;
                    cmd.ExecuteNonQuery();
                }
            }
        }
         public   static   string  GetConnectionString( string  filePath)
        {
             if  (filePath.EndsWith( " .xls " ))
                 return   string .Format( " Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; "
                     +   " Extended Properties='Excel 8.0;HDR=Yes;IMEX=2'; " , filePath);
             if  (filePath.EndsWith( " .xlsx " ))
                 return   string .Format( " Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; "
                     +   " Extended Properties='Excel 12.0 Xml;HDR=YES'; " , filePath);
             throw   new  Exception( " wrong file type! " );
        }
         public   static  DataTable ExcuteSelect( string  filePath,  string  sql)
        {
             using (OleDbConnection conn  =   new  OleDbConnection(GetConnectionString(filePath)));
            {
                OleDbDataAdapter ad  =   new  OleDbDataAdapter(sql, conn);
                DataTable table  =   new  DataTable();
                conn.Open();
                ad.Fill(table);
                 return  table;
            }
        }
    }
}

 

转载于:https://www.cnblogs.com/xhan/archive/2009/06/17/1504998.html