using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace BizUpdate.Implement
{
    public class ExcelOperator
    {
        /// <summary>
        /// 连接Excel
        /// </summary>
        /// <param name="filePath">数据库地址</param>
        public static OleDbConnection ExcelConnection(string filePath)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";
            OleDbConnection odconn = new OleDbConnection(string.Format(connectionString, filePath));
            return odconn;
        }
        /// <summary>
        /// 从Excel中读取数据
        /// </summary>
        /// <param name="filePath">Excel文件</param>
        /// <param name="selectQuery">SQL</param>
        /// <returns></returns>
        public static DataTable GetDataFromExcel(string filePath, string selectQuery)
        {
            OleDbConnection oconn = ExcelConnection(filePath);
            try
            {
                if (oconn.State == ConnectionState.Closed)
                { oconn.Open(); }
                OleDbCommand odbc = oconn.CreateCommand();
                odbc.CommandType = CommandType.Text;
                odbc.CommandText = selectQuery;
                OleDbDataAdapter da = new OleDbDataAdapter(odbc);
                DataSet ds = new DataSet();
                da.Fill(ds);

                if (oconn.State != ConnectionState.Closed)
                {
                    oconn.Close();
                }
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    foreach (DataColumn dc in ds.Tables[0].Columns)
                    {
                        if (dc.DataType == typeof(string))
                        {
                            if (dr[dc] == DBNull.Value)
                            {
                                dr[dc] = "";
                            }
                        }
                    }
                }
                return ds.Tables[0];
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (oconn.State != ConnectionState.Closed)
                {
                    oconn.Close();
                }
            }
        }
        /// <summary>
        /// DataTabl写入到Excel
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="selectQuery"></param>
        /// <returns></returns>
        public static bool WriteDataToExcel(string filePath, DataTable dt, string TableName)
        {
            OleDbConnection oconn = ExcelConnection(filePath);
            try
            {
                dt.TableName = TableName;
                if (oconn.State == ConnectionState.Closed)
                { oconn.Open(); }
                OleDbCommand odbc = oconn.CreateCommand();
                odbc.CommandType = CommandType.Text;
                if(dt !=null && dt.Rows.Count > 0)
                {
                    string creSql = string.Format("CREATE TABLE {0} (",dt.TableName);
                    for(int i = 0;i<dt.Columns.Count;i++)
                    {
                        if(i==dt.Columns.Count -1)
                        {
                           creSql += dt.Columns[i].ColumnName + " VARCHAR)";
                        }
                        else
                        {
                            creSql += dt.Columns[i].ColumnName + " VARCHAR,";
                        }
                    }
                    odbc.CommandText = creSql;
                    odbc.ExecuteNonQuery();
                    foreach (DataRow row in dt.Rows)
                    {
                        string inSql = string.Format("insert into {0} values(", dt.TableName);
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (i == dt.Columns.Count - 1)
                            {
                                inSql += "‘" + row[i].ToString() + "‘)";
                            }
                            else
                            {
                                inSql += "‘" + row[i].ToString() + "‘,";
                            }
                        }
                        odbc.CommandText = inSql;
                        odbc.ExecuteNonQuery();
                    }
                }

                if (oconn.State != ConnectionState.Closed)
                {
                    oconn.Close();
                }
                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw new Exception("写"+dt.TableName + "失败"+ex.Message);
            }
            finally
            {
                if (oconn.State != ConnectionState.Closed)
                {
                    oconn.Close();
                }
            }
        }
    }
}