using System;
using System.Data;
using System.Configuration;
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 Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Threading;
using System.Data.OleDb;
using System.IO;
/// <summary>
///DataPost 的摘要说明
/// </summary>
public class DataPost
{
	public DataPost()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}
    public void datauptofileyw(string SourceFileName, string SourceSheetName, string ObjectFileName, string ObjectSheetName)
    {
        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFileName + ";Extended Properties=Excel 8.0");
        olecon.Open();
        OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from [" + SourceSheetName + "$]", olecon);
        DataSet myds = new DataSet();
        oledbda.Fill(myds);

        object missing = Missing.Value;
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ObjectFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["" + ObjectSheetName + ""]);
        //worksheet.Move(workbook.Sheets[1], missing);
        try
        {
            object p_obj_name = (object)worksheet.Name;
            excel.DisplayAlerts = false;
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                worksheet.Cells[i + 3, 3] = myds.Tables[0].Rows[i][2].ToString();
                worksheet.Cells[i + 3, 9] = myds.Tables[0].Rows[i][5].ToString();
            }
            worksheet.Cells[3, 3] = "语文";
            worksheet.Cells[3, 9] = "语文";
            workbook.Save();
            System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            foreach (System.Diagnostics.Process p in excelProcess)
            {
                p.Kill();
            }
        }
        finally
        {
            oledbda.Dispose();
            olecon.Close();
        }
    }
    public void datauptofileshx(string SourceFileName, string SourceSheetName, string ObjectFileName, string ObjectSheetName)
    {
        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFileName + ";Extended Properties=Excel 8.0");
        olecon.Open();
        OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from [" + SourceSheetName + "$]", olecon);
        DataSet myds = new DataSet();
        oledbda.Fill(myds);

        object missing = Missing.Value;
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ObjectFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["" + ObjectSheetName + ""]);
        //worksheet.Move(workbook.Sheets[1], missing);
        try
        {
            object p_obj_name = (object)worksheet.Name;
            excel.DisplayAlerts = false;
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                worksheet.Cells[i + 3, 4] = myds.Tables[0].Rows[i][2].ToString();
                worksheet.Cells[i + 3, 10] = myds.Tables[0].Rows[i][5].ToString();
            }
            worksheet.Cells[3, 4] = "数学";
            worksheet.Cells[3, 10] = "数学";
            workbook.Save();
            System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            foreach (System.Diagnostics.Process p in excelProcess)
            {
                p.Kill();
            }
        }
        finally
        {
            oledbda.Dispose();
            olecon.Close();
        }
    }
    public void datauptofileyy(string SourceFileName, string SourceSheetName, string ObjectFileName, string ObjectSheetName)
    {
        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFileName + ";Extended Properties=Excel 8.0");
        olecon.Open();
        OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from [" + SourceSheetName + "$]", olecon);
        DataSet myds = new DataSet();
        oledbda.Fill(myds);

        object missing = Missing.Value;
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ObjectFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["" + ObjectSheetName + ""]);
        //worksheet.Move(workbook.Sheets[1], missing);
        try
        {
            object p_obj_name = (object)worksheet.Name;
            excel.DisplayAlerts = false;
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                worksheet.Cells[i + 3, 5] = myds.Tables[0].Rows[i][2].ToString();
                worksheet.Cells[i + 3, 11] = myds.Tables[0].Rows[i][5].ToString();
            }
            worksheet.Cells[3, 5] = "英语";
            worksheet.Cells[3, 11] = "英语";
            workbook.Save();
            System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            foreach (System.Diagnostics.Process p in excelProcess)
            {
                p.Kill();
            }
        }
        finally
        {
            oledbda.Dispose();
            olecon.Close();
        }
    }
    public void datauptofilekx(string SourceFileName, string SourceSheetName, string ObjectFileName, string ObjectSheetName)
    {
        OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + SourceFileName + ";Extended Properties=Excel 8.0");
        olecon.Open();
        OleDbDataAdapter oledbda = new OleDbDataAdapter("select * from [" + SourceSheetName + "$]", olecon);
        DataSet myds = new DataSet();
        oledbda.Fill(myds);

        object missing = Missing.Value;
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(ObjectFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets["" + ObjectSheetName + ""]);
        //worksheet.Move(workbook.Sheets[1], missing);
        try
        {
            object p_obj_name = (object)worksheet.Name;
            excel.DisplayAlerts = false;
            for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
            {
                worksheet.Cells[i + 3, 6] = myds.Tables[0].Rows[i][2].ToString();
                worksheet.Cells[i + 3, 12] = myds.Tables[0].Rows[i][5].ToString();
            }
            worksheet.Cells[3, 6] = "科学";
            worksheet.Cells[3, 12] = "科学";
            workbook.Save();
            System.Diagnostics.Process[] excelProcess = System.Diagnostics.Process.GetProcessesByName("EXCEL");
            foreach (System.Diagnostics.Process p in excelProcess)
            {
                p.Kill();
            }
        }
        finally
        {
            oledbda.Dispose();
            olecon.Close();
        }
    }
    public void CreateChineseTable(string path, string filename, string tablename, string classname)
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook newWordBook = excel.Application.Workbooks.Add(true);
        object missing = System.Reflection.Missing.Value;
        newWordBook.Worksheets.Add(missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Worksheets["sheet1"]);
        worksheet.Name = tablename;
        worksheet.Cells[1, 1] = "学校";
        worksheet.Cells[1, 2] = tablename;
        worksheet.Cells[1, 3] = "班级";
        worksheet.Cells[1, 4] = classname;
        worksheet.Cells[2, 1] = "考号";
        worksheet.Cells[2, 2] = "姓名";
        worksheet.Cells[2, 3] = "语文";
        worksheet.Cells[2, 4] = "考号";
        worksheet.Cells[2, 5] = "姓名";
        worksheet.Cells[2, 6] = "语文";
        for (int i = 1; i < 26; i++)
        {
            worksheet.Cells[i + 2, 1] = i;
            worksheet.Cells[i + 2, 4] = i + 25;
        }
        ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Sheets[1]).Delete();
        if (path.EndsWith("\\"))
        {
            newWordBook.SaveCopyAs(path + filename + ".xls");
        }
        else
        {
            newWordBook.SaveCopyAs(path + "\\" + filename + ".xls");
        }
        System.Diagnostics.Process[] excelPross = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        foreach (System.Diagnostics.Process p in excelPross)
        {
            p.Kill();
        }
    }
    public void CreateMathTable(string path, string filename, string tablename, string classname)
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook newWordBook = excel.Application.Workbooks.Add(true);
        object missing = System.Reflection.Missing.Value;
        newWordBook.Worksheets.Add(missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Worksheets["sheet1"]);
        worksheet.Name = tablename;
        worksheet.Cells[1, 1] = "学校";
        worksheet.Cells[1, 2] = tablename;
        worksheet.Cells[1, 3] = "班级";
        worksheet.Cells[1, 4] = classname;
        worksheet.Cells[2, 1] = "考号";
        worksheet.Cells[2, 2] = "姓名";
        worksheet.Cells[2, 3] = "数学";
        worksheet.Cells[2, 4] = "考号";
        worksheet.Cells[2, 5] = "姓名";
        worksheet.Cells[2, 6] = "数学";
        for (int i = 1; i < 26; i++)
        {
            worksheet.Cells[i + 2, 1] = i;
            worksheet.Cells[i + 2, 4] = i + 25;
        }
        ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Sheets[1]).Delete();
        if (path.EndsWith("\\"))
        {
            newWordBook.SaveCopyAs(path + filename + ".xls");

            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }
        else
        {
            newWordBook.SaveCopyAs(path + "\\" + filename + ".xls");
            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }

        //Microsoft.Office.Interop.Excel.Workbook workbookm = excel.Application.Workbooks.Open(Server.MapPath("./表文件/南兴渠小学.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        //((Microsoft.Office.Interop.Excel.Worksheet)workbookm.Sheets[2]).Delete();
        System.Diagnostics.Process[] excelPross = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        foreach (System.Diagnostics.Process p in excelPross)
        {
            p.Kill();
        }
    }
    public void CreateEnglishTable(string path, string filename, string tablename, string classname)
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook newWordBook = excel.Application.Workbooks.Add(true);
        object missing = System.Reflection.Missing.Value;
        newWordBook.Worksheets.Add(missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Worksheets["sheet1"]);
        worksheet.Name = tablename;
        worksheet.Cells[1, 1] = "学校";
        worksheet.Cells[1, 2] = tablename;
        worksheet.Cells[1, 3] = "班级";
        worksheet.Cells[1, 4] = classname;
        worksheet.Cells[2, 1] = "考号";
        worksheet.Cells[2, 2] = "姓名";
        worksheet.Cells[2, 3] = "英语";
        worksheet.Cells[2, 4] = "考号";
        worksheet.Cells[2, 5] = "姓名";
        worksheet.Cells[2, 6] = "英语";
        for (int i = 1; i < 26; i++)
        {
            worksheet.Cells[i + 2, 1] = i;
            worksheet.Cells[i + 2, 4] = i + 25;
        }
        ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Sheets[1]).Delete();
        if (path.EndsWith("\\"))
        {
            newWordBook.SaveCopyAs(path + filename + ".xls");

            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }
        else
        {
            newWordBook.SaveCopyAs(path + "\\" + filename + ".xls");
            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }

        //Microsoft.Office.Interop.Excel.Workbook workbookm = excel.Application.Workbooks.Open(Server.MapPath("./表文件/南兴渠小学.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        //((Microsoft.Office.Interop.Excel.Worksheet)workbookm.Sheets[2]).Delete();
        System.Diagnostics.Process[] excelPross = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        foreach (System.Diagnostics.Process p in excelPross)
        {
            p.Kill();
        }
    }
    public void CreateSienceTable(string path, string filename, string tablename, string classname)
    {
        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook newWordBook = excel.Application.Workbooks.Add(true);
        object missing = System.Reflection.Missing.Value;
        newWordBook.Worksheets.Add(missing, missing, missing, missing);
        Microsoft.Office.Interop.Excel.Worksheet worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Worksheets["sheet1"]);
        worksheet.Name = tablename;
        worksheet.Cells[1, 1] = "学校";
        worksheet.Cells[1, 2] = tablename;
        worksheet.Cells[1, 3] = "班级";
        worksheet.Cells[1, 4] = classname;
        worksheet.Cells[2, 1] = "考号";
        worksheet.Cells[2, 2] = "姓名";
        worksheet.Cells[2, 3] = "科学";
        worksheet.Cells[2, 4] = "考号";
        worksheet.Cells[2, 5] = "姓名";
        worksheet.Cells[2, 6] = "科学";
        for (int i = 1; i < 26; i++)
        {
            worksheet.Cells[i + 2, 1] = i;
            worksheet.Cells[i + 2, 4] = i + 25;
        }
        ((Microsoft.Office.Interop.Excel.Worksheet)newWordBook.Sheets[1]).Delete();
        if (path.EndsWith("\\"))
        {
            newWordBook.SaveCopyAs(path + filename + ".xls");

            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }
        else
        {
            newWordBook.SaveCopyAs(path + "\\" + filename + ".xls");
            //worksheet.Move(newWordBook.Sheets["南兴渠小学六、一班"], missing);
        }

        //Microsoft.Office.Interop.Excel.Workbook workbookm = excel.Application.Workbooks.Open(Server.MapPath("./表文件/南兴渠小学.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
        //((Microsoft.Office.Interop.Excel.Worksheet)workbookm.Sheets[2]).Delete();
        System.Diagnostics.Process[] excelPross = System.Diagnostics.Process.GetProcessesByName("EXCEL");
        foreach (System.Diagnostics.Process p in excelPross)
        {
            p.Kill();
        }
    }
}
