using System;
using System.Collections;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;

namespace ExcelObject{
    internal class ExcelHelper{
        private static FileStream oStream; //当前文件流 
        private static StreamWriter oWriter; //当前输出流 
        private static string strCurFileName; //使用组合函数输出Excel文件时使用的文件名 

 
        public static string GetExcelConnectionStr(string fileName){
            return "provider=Microsoft.Jet.OLEDB.4.0"+";data source="+fileName
                +";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"";
        }

        public static string GetTextConnectionStr(string fileName){
            string strPath=Path.GetDirectoryName(fileName);
            return "Provider=Microsoft.Jet.OLEDB.4.0"+";Data Source="+strPath
                +";Extended Properties=\"text;HDR=Yes;IMEX=1;\""; //FMT=Delimited; 
        }

        public static DataSet GetDBFromTextFile(string fn){
            string fileName=Path.GetFileName(fn);
            string strSql="SELECT * From "+fileName;
            string connStr=GetTextConnectionStr(fn);
            var ds=new DataSet("DataSet1");
            var oAdap=new OleDbDataAdapter(
                strSql,
                connStr);
            oAdap.Fill(ds);
            return ds;
        }

       
        public static DataSet GetDBFromExcel(string fileName,string strSql){
            var ds=new DataSet("DataSet1");
            string connStr=GetExcelConnectionStr(fileName);
            var adap=new OleDbDataAdapter(
                strSql,
                connStr);
            adap.Fill(ds);
            return ds;
        }

        
        public static DataSet GetDBFromExcel(string fileName){
            var ds=new DataSet("DataSet1");
            int num=1;
            while(true){
                try{
                    string strSql="SELECT * FROM [Sheet"+n