using UnityEngine;
using System.Collections;
using System; 
using System.Data; 
using System.Data.Odbc;

public class EXCELREADER 
{
    // our odbc connector 
    OdbcConnection odbcCon = null;

    /// <summary>
    /// 
    /// </summary>
    /// <param name="filepath"></param>
    public EXCELREADER(string filepath)
    {
        string connect = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=" + filepath + ";";

        Debug.Log(connect);

        odbcCon = new OdbcConnection(connect);

    }

    public void Open()
    {

        odbcCon.Open(); 

    }

    public void Close()
    {
        odbcCon.Close();
    }

    OdbcCommand odbcCmd;

    public void GetSheet(String sheetname)
    {
        string query = "SELECT * FROM [" + sheetname + "$]";

        if (odbcCmd != null)
            odbcCmd = null;

        odbcCmd = new OdbcCommand(query, odbcCon);
    }

    public DataTable GetDataFrom()
    {
        // table to hold the data 
        DataTable dtYourData = new DataTable("YourData");
        // lets use a datareader to fill that table! 
        OdbcDataReader rData = odbcCmd.ExecuteReader();
        // now lets blast that into the table by sheer man power! 
        dtYourData.Load(rData);

        //// Use a DataTable object's DataColumnCollection.
        //DataColumnCollection columns = dtYourData.Columns;

        //// Print the ColumnName and DataType for each column.
        //foreach (DataColumn column in columns)
        //{
        //    Debug.Log(column.ColumnName);
        //    Debug.Log(column.DataType);
        //}

        // close that reader! 
        rData.Close();

        return dtYourData;
    }

    public void readXLS( string filetoread)
    {
       
	    // Must be saved as excel 2003 workbook, not 2007, mono issue really
	    string con = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq="+filetoread+";";
	    Debug.Log(con);
        //string yourQuery = "SELECT * FROM [Sheet1$]";
        string yourQuery = "SELECT * FROM [ºìÂÞº£°¶_Õ½¶·µØͼ1_MAP$]"; 
	    // our odbc connector 
	    OdbcConnection oCon = new OdbcConnection(con); 
	    // our command object 
	    OdbcCommand oCmd = new OdbcCommand(yourQuery, oCon);
	    // table to hold the data 
	    DataTable dtYourData = new DataTable("YourData"); 
	    // open the connection 
	    oCon.Open(); 
	    // lets use a datareader to fill that table! 
	    OdbcDataReader rData = oCmd.ExecuteReader(); 
	    // now lets blast that into the table by sheer man power! 
	    dtYourData.Load(rData); 
	    // close that reader! 
	    rData.Close(); 
	    // close your connection to the spreadsheet! 
	    oCon.Close(); 
	    // wow look at us go now! we are on a roll!!!!! 
	    // lets now see if our table has the spreadsheet data in it, shall we? 

        Debug.Log(dtYourData.Rows.Count + "  " + dtYourData.Rows.Count);

	    if(dtYourData.Rows.Count > 0) 
	    { 
		    // do something with the data here 
		    // but how do I do this you ask??? good question! 
		    for (int i = 0; i < dtYourData.Rows.Count; i++) 
		    { 
			    // for giggles, lets see the column name then the data for that column! 
                //Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString());

                Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString() + "  |  " + dtYourData.Columns[1].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[1].ColumnName].ToString() + "  |  " + dtYourData.Columns[2].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[2].ColumnName].ToString());
            } 
	    } 
    }
}