using System;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// exceltoaccess 的摘要说明
/// </summary>
public class exceltoaccess
{
	public exceltoaccess()
	{
		//
		// TODO: 在此处添加构造函数逻辑
		//
	}
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("~\\App_Data\\ZY.mdb");

    public void gettable(string conn, DropDownList DropDownList1)
    {
        OleDbConnection connect = new OleDbConnection(conn);
        connect.Open();
        DataTable schemaTable = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        for (int i = 0; i <= schemaTable.Rows.Count - 1; i++)
        {
            if (schemaTable.Rows[i].ItemArray[3].ToString() == "TABLE")
            { DropDownList1.Items.Add(schemaTable.Rows[i].ItemArray[2].ToString()); }
        }
       
    }
    public DataTable getds(string conn, string Sql)
    {
        OleDbDataAdapter mycommand = new OleDbDataAdapter(Sql, conn);
        DataSet ds = new DataSet();
        mycommand.Fill(ds, "tablename");
        DataTable myTable = ds.Tables["tablename"];
        return myTable;
    }
    /// <summary>
    /// 能创建ACCESS表
    /// </summary>
    /// <param name="tablename"></param>
    /// <param name="conn"></param>
    /// <param name="myTable"></param>
    public void createtable(string tablename, string conn, DataTable myTable)
    {
          //DataTable myTable = getds(conn, Sql,tablename);
         OleDbConnection thisconnection1 = new OleDbConnection(connectionString);
        thisconnection1.Open();

            string strSql = "create table " + tablename + "(";
            foreach (System.Data.DataColumn c in myTable.Columns)
            {
                strSql += c.ColumnName + " VARCHAR,";
            }
            strSql = strSql.Substring(0, strSql.Length - 1) + ")";


            OleDbCommand mycommand1 = new OleDbCommand(strSql, thisconnection1);
            mycommand1.ExecuteNonQuery();
thisconnection1.Close();
    } 

    /// <summary>
    /// 从EXCEL文件中导入数据到ACCESS文件
    /// </summary>
    /// <param name="excelFile"></param>
    /// <param name="sheetName"></param>
    /// <param name="connectionString"></param>

    public void toaccess(string tablename, string conn, DataTable myTable)
    {
        
        //DataTable myTable = getds(conn, Sql,tablename);
        OleDbConnection thisconnection1 = new OleDbConnection(connectionString);
        thisconnection1.Open();
         int count = myTable.Rows.Count;
        for (int i = 0; i < count; i++)
        {
            string strSql1 = "insert into " + tablename + "(";
            foreach (System.Data.DataColumn c in myTable.Columns)
            {
                strSql1 += c.ColumnName + ",";
            }
            strSql1 = strSql1.Substring(0, strSql1.Length - 1) + ") values ('";
            for (int j = 0; j < myTable.Columns.Count; j++)
                strSql1 += myTable.Rows[i][j].ToString() + "','";
            strSql1 = strSql1.Substring(0, strSql1.Length - 2) + ")";

            OleDbCommand mycommand2 = new OleDbCommand(strSql1, thisconnection1);
            mycommand2.ExecuteNonQuery();
        }
        thisconnection1.Close();
    }
    /// <summary>
    /// 从ACCESS文件中导出数据到EXCEL文件
    /// </summary>
    /// <param name="filename">EXCEL文件名</param>
    /// <param name="sql">SQL语句以得到要导出的记录</param>
    /// <param name="connectionString">连接字符串</param>
    public void toexcel(string filename,string sql,string connectionString)
    {
        //StringWriter sw = new StringWriter();
        //OleDbConnection con = new OleDbConnection(connectionString);
        //OleDbDataAdapter sda = new OleDbDataAdapter();
        //sda.SelectCommand = new OleDbCommand(sql, con);
        //DataTable dt = new DataTable();
        //ExportExcel(dt, sw);
        ////attachment 参数表示作为附件下载，可以改成 online在线打开
        ////filename=FileFlow.xls 指定输出文件的名称，注意其扩展名和指定文件类型相符，可以为:.doc .xls.txt.htm
        ////Response.ContentType指定文件类型 可以为application/ms-excel、
        ////application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档
        //HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);//遠程下載
        //HttpContext.Current.Response.ContentType = "application/ms-excel";//或为"text/plain"
        //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//或为Encoding.UTF8;Response.Charset = "utf-8"; 
        //HttpContext.Current.Response.Write(sw);

        //HttpContext.Current.Response.End();
       // string conn1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("~\\App_Data\\xngs.mdb");
        OleDbConnection con = new OleDbConnection(connectionString);
        OleDbDataAdapter sda = new OleDbDataAdapter();
        sda.SelectCommand = new OleDbCommand(sql, con);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        StringWriter sw = new StringWriter();
        // StreamWriter sw = new StreamWriter("d:\\test.xls",false,System.Text.Encoding.Default); //本地保存
        ExportExcel(dt, sw);
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + filename);//遠程下載
        HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        HttpContext.Current.Response.Write(sw);
        HttpContext.Current.Response.End();
    }
      public void ExportExcel(DataTable dt, StringWriter w)
    {
        try
        {
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                w.Write(dt.Columns[i]);
                w.Write('\t');
            }
            w.Write("\r\n");

            object[] values = new object[dt.Columns.Count];
            foreach (DataRow dr in dt.Rows)
            {
                values = dr.ItemArray;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    w.Write(values[i]);
                    w.Write('\t'); //修改语句
                }
                w.Write("\r\n"); //修改语句
            }

            // w.Flush();
            w.Close();
        }
        catch
        {
            w.Close();
        }
    }
}