using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;

public partial class 页面集_生成表页面 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //BindListBox();
            BindDropDownList();
        }
    }
    protected void btmCreateTable_Click(object sender, EventArgs e)
    {
        DataPost nt = new DataPost();
        for (int i = 0; i < ddlSheetName.Items.Count; i++)
        {
            ddlSheetName.SelectedIndex = i;
            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "六年级语文", ddlSheetName.SelectedItem.Text, "六年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "六年级数学", ddlSheetName.SelectedItem.Text, "六年级");
            nt.CreateEnglishTable(Server.MapPath("../英语"), ddlSheetName.SelectedItem.Text + "六年级英语", ddlSheetName.SelectedItem.Text, "六年级");
            nt.CreateSienceTable(Server.MapPath("../科学"), ddlSheetName.SelectedItem.Text + "六年级科学", ddlSheetName.SelectedItem.Text, "六年级");

            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "五年级语文", ddlSheetName.SelectedItem.Text, "五年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "五年级数学", ddlSheetName.SelectedItem.Text, "五年级");
            nt.CreateEnglishTable(Server.MapPath("../英语"), ddlSheetName.SelectedItem.Text + "五年级英语", ddlSheetName.SelectedItem.Text, "五年级");
            nt.CreateSienceTable(Server.MapPath("../科学"), ddlSheetName.SelectedItem.Text + "五年级科学", ddlSheetName.SelectedItem.Text, "五年级");

            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "四年级语文", ddlSheetName.SelectedItem.Text, "四年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "四年级数学", ddlSheetName.SelectedItem.Text, "四年级");
            nt.CreateEnglishTable(Server.MapPath("../英语"), ddlSheetName.SelectedItem.Text + "四年级英语", ddlSheetName.SelectedItem.Text, "四年级");
            nt.CreateSienceTable(Server.MapPath("../科学"), ddlSheetName.SelectedItem.Text + "四年级科学", ddlSheetName.SelectedItem.Text, "四年级");

            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "三年级语文", ddlSheetName.SelectedItem.Text, "三年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "三年级数学", ddlSheetName.SelectedItem.Text, "三年级");
            nt.CreateEnglishTable(Server.MapPath("../英语"), ddlSheetName.SelectedItem.Text + "三年级英语", ddlSheetName.SelectedItem.Text, "三年级");
            nt.CreateSienceTable(Server.MapPath("../科学"), ddlSheetName.SelectedItem.Text + "三年级科学", ddlSheetName.SelectedItem.Text, "三年级");

            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "二年级语文", ddlSheetName.SelectedItem.Text, "二年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "二年级数学", ddlSheetName.SelectedItem.Text, "二年级");

            nt.CreateChineseTable(Server.MapPath("../语文"), ddlSheetName.SelectedItem.Text + "一年级语文", ddlSheetName.SelectedItem.Text, "一年级");
            nt.CreateMathTable(Server.MapPath("../数学"), ddlSheetName.SelectedItem.Text + "一年级数学", ddlSheetName.SelectedItem.Text, "一年级");
        }
        Response.Write("<script>alert('Excel文件成功生成！');</script>");
        ddlSheetName.SelectedIndex = 0;
    }
    //public void BindListBox()
    //{
    //    LBClass.Items.Clear(); //清空下拉列表项
    //    //连接Excel数据库
    //    OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../汇总表/六年级成绩汇总表.xls") + ";Extended Properties=Excel 8.0");
    //    olecon.Open();//打开数据库连接
    //    System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
    //    DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
    //    while (DTReader.Read())//循环读取
    //    {
    //        LBClass.Items.Add(DTReader["Table_Name"].ToString().Replace('$', ' ').Trim());//将工作表名添加到下拉列表中
    //        //if (LBClass.SelectedValue == "按校汇总表" || LBClass.SelectedValue == "按班汇总表")
    //        //{
    //        //    LBClass.Items.Remove(LBClass.SelectedValue);
    //        //}
    //        //if (ddlSheetName.SelectedValue == "按班汇总表")
    //        //{
    //        //    ddlSheetName.Items.Remove(ddlSheetName.SelectedItem);
    //        //}
    //    }
    //    int count = LBClass.Items.Count;
    //    //int index = 0;
    //    for (int i = 0; i < count; i++)
    //    {
    //        //ListItem Item = LBClass.Items[index];
    //        if (LBClass.SelectedValue == "按校汇总表" || LBClass.SelectedValue == "按班汇总表")
    //        {
    //            LBClass.Items.Remove(LBClass.SelectedItem);
    //        }
    //        //index++;
    //    }
    //    DTable = null;//清空表对象
    //    DTReader = null;//清空表读取对象
    //    olecon.Close();//关闭数据库连接       
    //    LBClass.SelectedIndex = 0;//设置下拉列表默认选项为第一项 
    //}
    private void BindDropDownList()//对下拉列表进行数据绑定
    {
        ddlSheetName.Items.Clear();//清空下拉列表项
        //连接Excel数据库
        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("../汇总表/六年级成绩汇总表.xls") + ";Extended Properties=Excel 8.0");
        olecon.Open();//打开数据库连接
        System.Data.DataTable DTable = olecon.GetSchema("Tables");//实例化表对象
        DataTableReader DTReader = new DataTableReader(DTable);//实例化表读取对象
        while (DTReader.Read())//循环读取
        {
            ddlSheetName.Items.Add(DTReader["Table_Name"].ToString().Replace('$', ' ').Trim());//将工作表名添加到下拉列表中
            if (ddlSheetName.SelectedValue == "按校汇总表" || ddlSheetName.SelectedValue == "按班汇总表")
            {
                ddlSheetName.Items.Remove(ddlSheetName.SelectedItem);
            }            
        }
        DTable = null;//清空表对象
        DTReader = null;//清空表读取对象
        olecon.Close();//关闭数据库连接       
        ddlSheetName.SelectedIndex = 0;//设置下拉列表默认选项为第一项 
    }
}
