<%@ WebHandler Language="C#" Class="MESCommonBaseAMS" %>

using System;
using System.Collections;
using Aspose.Cells;
using System.Web;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using System.IO;
using System.Web.Script.Serialization;
using System.Data;
//using BizDataAccess;
using System.Data.SqlClient;//<a href="uploadInvoiceScan.ashx">uploadInvoiceScan.ashx</a>
//using DbCallData;
using BasicData;
//using LitJson;
using DataLinkMesWork;
using System.Configuration;

using System.Text.RegularExpressions;
#region  MESCommonBaseAMS
public class MESCommonBaseAMS : IHttpHandler
{


    public void ProcessRequest(HttpContext context)
    {



        context.Response.ContentType = "application/json";
        var data = context.Request;
        var stream = new StreamReader(data.InputStream).ReadToEnd();
        string InvoiceNum;
        //JsonData jsonData;
        string responseText = "NULL";
        byte[] bytes = null;
        string fileName = "test.xsl";



        try
        {
            JsonData jsonData=null;
            try
            {
                jsonData = JsonMapper.ToObject(stream);

            }
            catch(Exception err)
            { }
            int type=0;
            try
            {
                if(jsonData!=null)
                {
                    if(jsonData.ContainsKey("Type"))
                    {
                        type =  Int32.Parse( jsonData["Type"].ToString());
                    }else if(jsonData.ContainsKey("type"))
                    {
                        type =  Int32.Parse( jsonData["type"].ToString());
                    }
                }
            }
            catch
            {

            }
            jsonobj dataobj=null;
            try
            {
                dataobj = new JavaScriptSerializer().Deserialize<jsonobj>(stream);
                if(dataobj!=null)
                {
                    type =  Int32.Parse(dataobj.Type);
                }

            }
            catch(Exception err)
            {
                dataobj = null;
            }

            if(dataobj==null)
            {
                try
                {
                    InvoiceNum = HttpContext.Current.Request["param"];
                    if(InvoiceNum!=null)
                    {
                        jsonData = JsonMapper.ToObject(InvoiceNum);
                        if(jsonData.ContainsKey("Type"))
                        {
                            type =  Int32.Parse( jsonData["Type"].ToString());
                        }else if(jsonData.ContainsKey("type"))
                        {
                            type =  Int32.Parse( jsonData["type"].ToString());
                        }
                    }
                }
                catch
                {
                    jsonData = null;
                }

            }
            string fileExtension="";
            HttpFileCollection files;
            string suffix;
            string name;
            switch(type)
            {
                //大众项目
                case 21:
                    string name1;
                    List<SqlCallParameter> sqlCallParameterList;
                    GetSqlCallParameter(jsonData, out name1, out sqlCallParameterList);

                    ExePROCEDURE_Type21(context, name1, sqlCallParameterList);

                    break;
                case 2001:

                    //DataLink.LogJsonData(jsonData);

                    MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    break;
                case 2002:
                    //DataLink.LogJsonData(jsonData);

                    MESDownloadExcel.ExcelWebCall.ExcelFilePdf(jsonData,out bytes,out fileName,out fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();

                    break;

                case 2003:// 合成excel图片，下载
                    string[] dataimg;
                    if(context.Request.Form.Keys.Count>0)
                    {
                        dataimg = data.Form.GetValues(0);
                        MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref dataimg[0]);
                        if (bytes == null) return;
                        fileName = fileName + "."+dataimg[0];

                        HttpContext.Current.Response.ContentType = "application/octet-stream";
                        //HttpContext.Current.Response.ContentType = getContentType(extension);  
                        //通知浏览器下载文件而不是打开  
                        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                        HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                        HttpContext.Current.Response.BinaryWrite(bytes);
                        HttpContext.Current.Response.Flush();
                        HttpContext.Current.Response.End();
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix);
                        //}
                    }
                    break;
                case 2004:

                    //DataLink.LogJsonData(jsonData);
                    DataLinkMesWork.DataLink.ExePROCEDURE_Type2004(jsonData, out bytes, out fileName, out fileExtension);
                    //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
                    fileName = fileName + "."+fileExtension;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    break;

                case 15://上传文件
                    files = context.Request.Files;
                    if (files.Count > 0)
                    {

                        fileName = files[0].FileName;
                        bytes = new byte[files[0].InputStream.Length];
                        files[0].InputStream.Read(bytes, 0, bytes.Length);

                        suffix = fileName.Substring(fileName.LastIndexOf(".")+1);
                        name = fileName.Substring(0,fileName.LastIndexOf("."));

                        responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes);
                        context.Response.Write(responseText);
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes);
                        //}
                    }
                    else
                    {
                        fileName = "";
                        bytes = new byte[1];
                        suffix = "";
                        name = "";
                        responseText = DataLinkMesWork.DataLink.ExePROCEDURE_Type15(jsonData, name, suffix, bytes);
                        context.Response.Write(responseText);
                        //if(DataLinkMesWork.DataLink.isBakup=="1")
                        //{
                        //    DataLinkMesWork.DataLink.ExePROCEDURE_Type15_Bakup(jsonData, name, suffix, bytes);
                        //}
                    }

                    break;
                case 16://上传文件
                    DataLink.LogJsonData(jsonData);
                    DataLinkMesWork.DataLink.ExePROCEDURE_Type16(jsonData, out bytes, out fileName, out suffix);
                    if (bytes == null) return;
                    fileName = fileName + "."+suffix;

                    HttpContext.Current.Response.ContentType = "application/octet-stream";
                    //HttpContext.Current.Response.ContentType = getContentType(extension);  
                    //通知浏览器下载文件而不是打开  
                    HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
                    HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
                    HttpContext.Current.Response.BinaryWrite(bytes);
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.End();
                    //if(DataLinkMesWork.DataLink.isBakup=="1")
                    //{
                    //    DataLinkMesWork.DataLink.ExePROCEDURE_Type16_Bakup(jsonData, out bytes, out fileName, out suffix);
                    //}
                    break;
                default:

                    //DataLink.LogJsonData(jsonData);

                    responseText = DataLinkMesWork.DataLink.SqlWebCall(type,jsonData,dataobj);//.SqlWebCall(stream);

                    context.Response.Write(responseText);

                    //if(DataLinkMesWork.DataLink.isBakup=="1")
                    //{
                    //    DataLinkMesWork.DataLink.SqlWebCall_Bakup(type,jsonData,dataobj);
                    //}
                    break;
            }



        }
        catch(Exception err)
        {
            context.Response.Write(responseText);
        }
    }
    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

    private void ExePROCEDURE_Type21(HttpContext context ,string nameFunction,List<SqlCallParameter> sqlCallParameterList)
    {



        switch (nameFunction)
        {
            //综合查询
            case "btnSearch":
                btnSearch_Click(context,sqlCallParameterList);

                break;
            //异常查询
            case "btnYiChang":
                btnYiChang_Click(context,sqlCallParameterList);

                break;
            //发动机详细信息
            case "CreatHtml":
                CreatHtml_Click(context,sqlCallParameterList);

                break;
            //导出OP
            case "btnDaoChuOP":
                btnDaoChuOP_Click(context,sqlCallParameterList);

                break;
            //导出单台
            case "btnDaoChuOne":
                btnDaoChuOne_Click(context,sqlCallParameterList);

                break;
            //导出全部
            case "btnDaoChuAll":
                btnDaoChuAll_Click(context,sqlCallParameterList);

                break;
        }

    }
    public static void GetSqlCallParameter(JsonData json,out string  name1,out List<SqlCallParameter> sqlCallParameterList)
    {
        SqlCallParameter thisParms = null;
        JsonData jsonData;
        JsonData jsonDataName;//存储过程名称  Sql
        JsonData jsonDataParam;//存储过程参数
        sqlCallParameterList = new List<SqlCallParameter>();
        name1 = "";
        try
        {
            {
                string type1;
                string param1;
                string userID1;
                string pagination1;
                bool hasReturn1;
                string modularID1;
                GetString_JsonData(json, out type1, out name1, out param1, out userID1, out pagination1, out hasReturn1, out modularID1);

                jsonDataName = name1;//存储过程名称
                jsonDataParam = param1; //存储过程参数 json格式 [{\"name\":\"name1\",\"value\":\"value1\"},{\"name\":\"name2\",\"value\":\"value1\"}]
                jsonData = JsonMapper.ToObject(jsonDataParam.ToString()); //转为JSON格式 
                if (jsonDataParam.ToString() == "" || jsonDataParam.ToString() == null)//执行没有参数的存储过程
                {
                    // isOK = SQLCommon.ExecuteInsertMesWork(jsonDataName.ToString(), ref param, connStringBakup, out err);
                }
                else//执行有参数的存储过程
                {
                    //thisParms = new []SqlParameter();
                    //thisParms = new SqlParameter[jsonData.Count];
                    for (int i = 0; i < jsonData.Count; i++)
                    {
                        string name = jsonData[i]["name"].ToString();// 参数名
                        string value = null;
                        try
                        {
                            //判断是否为数组，若为数组转为字符串格式
                            if (jsonData[i]["value"].IsArray == true)
                            {
                                //数组转字符串
                                value = jsonData[i]["value"].ToJson().ToString().Replace("[", "").Replace("]", "").Replace("\"", "");// 数组参数值转字符串
                                                                                                                                     // unicode解码
                                value = DeUnicode(value);
                            }
                            else//不是数组格式直接转字符串
                            {
                                value = jsonData[i]["value"].ToString();// 参数值
                            }
                        }
                        catch (Exception E)//value为null时。Tostring()会报错，需要手动赋值
                        {
                            value = null;
                        }
                        string output = null;// 参数是否为输出类型
                        string type = null;// 参数类型

                        {
                            thisParms = new SqlCallParameter(name, value);
                            if (jsonData[i].ContainsKey("output"))
                            {
                                if(jsonData[i]["output"]!=null)
                                {
                                    output = jsonData[i]["output"].ToString();// 参数是否为输出类型      
                                }
                            }
                            if (jsonData[i].ContainsKey("type"))
                            {
                                if(jsonData[i]["type"] != null)
                                {
                                    type = jsonData[i]["type"].ToString();// 参数类型
                                    object inputValue = null; ;
                                    inputValue = GetInputValue(type, output);
                                    thisParms = new SqlCallParameter(name, inputValue);
                                }
                            }

                            sqlCallParameterList.Add(thisParms);
                        }

                    }

                }
            }

        }
        catch (Exception e)
        {
        }
    }
    /// <summary>
    /// 返回输出参数类型
    /// </summary>
    /// <param name="type"></param>
    /// <param name="output"></param>
    /// <returns></returns>
    public static object GetInputValue(object type, object output)
    {
        object inputValue = null;
        switch (type.ToString())
        {
            case "int":
                inputValue = Convert.ToInt32(output);
                break;
            case "string":
                inputValue = output;
                break;
            case "boolean":
            case "bool":
                if (output.ToString() == "1" || output.ToString() == "true")
                {
                    inputValue = true;
                }
                else
                {
                    inputValue = false;
                }
                break;
            case "datetime":
                inputValue = Convert.ToDateTime(output);
                break;
            default:
                inputValue = output.ToString();
                break;
        }

        return inputValue;

    }
    /// <summary>
    /// Unicode解码
    /// </summary>
    /// <param name="str"></param>
    /// <returns></returns>
    public static string DeUnicode(string str)
    {
        //最直接的方法Regex.Unescape(str);
        Regex reg = new Regex(@"(?i)\\[uU]([0-9a-f]{4})");
        return reg.Replace(str, delegate (Match m) { return ((char)Convert.ToInt32(m.Groups[1].Value, 16)).ToString(); });
    }
    /// <summary>
    ///    "UserID": $("#UserID").val(),
    ///    "ModularID": $("#ModularID").val(), 子组件
    ///    "Type": $("#TYPE").val(),
    ///    "Name": $("#NAME").val(),
    ///    "Param": $("#PARAM1value").val(),
    ///    "Pagination": $("#Pagination").val(),
    ///    HasReturn: $("#HASRETURN").get(0).checked
    /// </summary>
    /// <param name="json"></param>
    /// <param name="type"></param>
    /// <param name="name"></param>
    /// <param name="param"></param>
    /// <param name="userID"></param>
    /// <param name="pagination"></param>
    /// <param name="hasReturn"></param>
    /// <param name="modularID">子组件</param>
    public static void GetString_JsonData(JsonData json, out string type,out string name,out string param,out string userID,out string pagination,out bool hasReturn,out string modularID)
    {
        type = null;
        name = null;
        param = "";
        userID = null;
        pagination=null;
        hasReturn=false;
        modularID = "";
        if (json == null) return;
        if (json.Keys.Contains("Type"))
        {
            if (json["Type"] != null)
                type = json["Type"].ToString();
        }
        else if (json.Keys.Contains("type"))
        {
            if (json["type"] != null)
                type = json["type"].ToString();
        }

        if (json.Keys.Contains("Name"))
        {
            if (json["Name"] != null)
                name = json["Name"].ToString();
        }
        else if (json.Keys.Contains("name"))
        {
            if (json["name"] != null)
                name = json["name"].ToString();
        }


        if (json.Keys.Contains("Param"))
        {
            if (json["Param"] != null)
                param = json["Param"].ToString();
        }
        else if (json.Keys.Contains("param"))
        {
            if (json["param"] != null)
                param = json["param"].ToString();
        }


        if (json.Keys.Contains("UserID"))
        {
            if (json["UserID"] != null)
                userID = json["UserID"].ToString();
        }
        else if (json.Keys.Contains("userID"))
        {
            if (json["userID"] != null)
                userID = json["userID"].ToString();
        }

        if (json.Keys.Contains("Pagination"))
        {
            if (json["Pagination"] != null)
                pagination = json["Pagination"].ToString();
        }
        else if (json.Keys.Contains("pagination"))
        {
            if (json["pagination"] != null)
                pagination = json["pagination"].ToString();
        }

        if (json.Keys.Contains("HasReturn"))
        {
            try
            {
                if (json["HasReturn"] != null)
                    hasReturn = Convert.ToBoolean(json["HasReturn"].ToString());
            }
            catch
            {

            }
        }
        else if (json.Keys.Contains("hasReturn"))
        {
            try
            {
                if (json["hasReturn"] != null)
                    hasReturn = Convert.ToBoolean(json["hasReturn"].ToString());
            }
            catch
            {

            }
        }


        if (json.Keys.Contains("ModularID"))
        {
            try
            {
                if (json["ModularID"] != null)
                    modularID = json["ModularID"].ToString();
            }
            catch
            {

            }
        }
        else if (json.Keys.Contains("modularID"))
        {
            try
            {
                if (json["modularID"] != null)
                    modularID = json["modularID"].ToString();
            }
            catch
            {

            }
        }
        //ModularID
    }

    /// <summary>
    /// 查询
    /// </summary>
    /// <returns></returns>
    private void btnSearch_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string szMsgBegin = "2021-11-02 00:00:00";
        string szMsgEnd = "2021-12-31 11:07:52";
        string m_sSortno = "";
        string m_sEngineNo = "";
        string m_sEngineNo1 = "";
        string m_sEngineNo2 = "";
        bool rbOK_Checked = false;
        bool rbFanXiu_Checked = false;
        bool rbNoOK_Checked = false;
        string szNote = "ZKG";
        string szComponent = "";

        int PageCurrent = 1;
        int PageSize = 10;
        int PageCount = 1;
        int ItemCount = 1;

        for (int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sSortno = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 4:
                    m_sEngineNo1 = sqlCallParameterList[i].value.ToString();
                    break;
                case 5:
                    m_sEngineNo2 = sqlCallParameterList[i].value.ToString();
                    break;
                case 6:
                    rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 7:
                    rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 8:
                    rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 9:
                    szNote = sqlCallParameterList[i].value.ToString();
                    break;
                case 10:
                    szComponent = sqlCallParameterList[i].value.ToString();
                    break;
                case 11:
                    PageCurrent = Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 12:
                    PageSize =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 13:
                    PageCount =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 14:
                    ItemCount =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
            }
        }



        string dt = new Form1().btnSearch_Click( szMsgBegin,  szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent,PageCurrent,PageSize,PageCount,ItemCount);
        //string result = DataLinkMesWork.DataLink.DataTableToJson(dt);
        context.Response.Write(dt);

    }
    /// <summary>
    /// 查询 导出
    /// </summary>
    /// <returns></returns>
    private void btnSearch_DaoChu_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string szMsgBegin = "2021-11-02 00:00:00";
        string szMsgEnd = "2021-12-31 11:07:52";
        string m_sSortno = "";
        string m_sEngineNo = "";
        string m_sEngineNo1 = "";
        string m_sEngineNo2 = "";
        bool rbOK_Checked = false;
        bool rbFanXiu_Checked = false;
        bool rbNoOK_Checked = false;
        string szNote = "ZKG";
        string szComponent = "";

        int PageCurrent = 1;
        int PageSize = 10;
        int PageCount = 1;
        int ItemCount = 1;

        for (int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sSortno = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 4:
                    m_sEngineNo1 = sqlCallParameterList[i].value.ToString();
                    break;
                case 5:
                    m_sEngineNo2 = sqlCallParameterList[i].value.ToString();
                    break;
                case 6:
                    rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 7:
                    rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 8:
                    rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 9:
                    szNote = sqlCallParameterList[i].value.ToString();
                    break;
                case 10:
                    szComponent = sqlCallParameterList[i].value.ToString();
                    break;
                case 11:
                    PageCurrent = Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 12:
                    PageSize =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 13:
                    PageCount =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
                case 14:
                    ItemCount =  Convert.ToInt32(sqlCallParameterList[i].value);
                    break;
            }
        }



        DataTable dt = new Form1().btnSearchDaoChu_Click( szMsgBegin,  szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent);
        string result = DataLinkMesWork.DataLink.DataTableToJson(dt);
        context.Response.Write(result);

    }
    /// <summary>
    /// 异常查询
    /// </summary>
    /// <returns></returns>
    private void btnYiChang_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string szMsgBegin = "2021-11-02 00:00:00";
        string szMsgEnd = "2021-12-31 11:07:52";

        string m_sSortno = "";
        string m_sEngineNo = "";
        string m_sEngineNo1 = "";
        string m_sEngineNo2 = "";
        for(int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sSortno = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 4:
                    m_sEngineNo1 = sqlCallParameterList[i].value.ToString();
                    break;
                case 5:
                    m_sEngineNo2 = sqlCallParameterList[i].value.ToString();
                    break;

            }
        }

        DataTable dt = new Form1().btnYiChang_Click( szMsgBegin,  szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2);
        string result = DataLinkMesWork.DataLink.DataTableToJson(dt);
        context.Response.Write(result);
    }
    /// <summary>
    /// 导出全部
    /// </summary>
    private void btnDaoChuAll_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string szMsgBegin = "2021-11-02 00:00:00";
        string szMsgEnd = "2021-12-31 11:07:52";


        string m_sSortno = "";
        string m_sEngineNo = "";
        string m_sEngineNo1 = "";
        string m_sEngineNo2 = "";
        bool rbOK_Checked = false;
        bool rbFanXiu_Checked = false;
        bool rbNoOK_Checked = false;
        string szNote = "ZKG";
        string szComponent = "";
        int PageCurrent = 25;
        int PageSize = 1000;
        int PageCount = 1;
        int ItemCount = 1;

        for(int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sSortno = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 4:
                    m_sEngineNo1 = sqlCallParameterList[i].value.ToString();
                    break;
                case 5:
                    m_sEngineNo2 = sqlCallParameterList[i].value.ToString();
                    break;
                case 6:
                    rbOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 7:
                    rbFanXiu_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 8:
                    rbNoOK_Checked = Convert.ToBoolean(sqlCallParameterList[i].value);
                    break;
                case 9:
                    szNote = sqlCallParameterList[i].value.ToString();
                    break;
                case 10:
                    szComponent = sqlCallParameterList[i].value.ToString();
                    break;
            }
        }
        MemoryStream ms;
        ms = new Form1().btnDaoChuAll_Click(szMsgBegin,  szMsgEnd, m_sSortno,m_sEngineNo,m_sEngineNo1, m_sEngineNo2,rbOK_Checked,rbFanXiu_Checked,rbNoOK_Checked,szNote,szComponent,PageCurrent,PageSize , PageCount,ItemCount);

        //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
        //fileName = fileName + "."+fileExtension;
        string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

        DownLoadFile(fileName, ms);


        // MemoryStream CreatHtml(string strEngNo,string strBeginTime,string strEndTime,string strEngine_Index)



    }

    /// <summary>
    /// 发动机详细信息
    /// </summary>
    private void CreatHtml_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string szMsgBegin="2021-12-18 22:26:37";
        string szMsgEnd="2021-12-19 00:28:58";
        string m_sEngineNo = "DMBNK0725";
        string strEngine_Index="2160615";
        for(int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    strEngine_Index = sqlCallParameterList[i].value.ToString();
                    break;

            }
        }
        MemoryStream ms;
        ms = new Form1().CreatHtml( szMsgBegin, szMsgEnd,m_sEngineNo,  strEngine_Index);

        string fileName = m_sEngineNo+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".html";

        DownLoadFile(fileName, ms);
    }
    /// <summary>
    /// 导出单台
    /// </summary>
    private void btnDaoChuOne_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        //发动机编号
        string m_sEngineNo;
        //生产时间
        string szMsgBegin;
        //完成时间
        string szMsgEnd;
        string strEngine_Index;
        //发动机类型号
        string strSortNo;

        m_sEngineNo = "DMBNK0725";
        szMsgBegin="2021-12-18 22:26:37";
        szMsgEnd="2021-12-19 00:28:58";
        strEngine_Index="2160615";
        strSortNo="5502";
        for(int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    m_sEngineNo = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    strEngine_Index = sqlCallParameterList[i].value.ToString();
                    break;

            }
        }
        MemoryStream ms;
        ms = new Form1().btnDaoChuOne_Click( szMsgBegin, szMsgEnd, m_sEngineNo,strEngine_Index, strSortNo);
        if (ms == null) return;
        //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
        //fileName = fileName + "."+fileExtension;
        string fileName = m_sEngineNo+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

        DownLoadFile(fileName, ms);


    }
    /// <summary>
    /// 导出OP
    /// </summary>
    private void btnDaoChuOP_Click(HttpContext context,List<SqlCallParameter> sqlCallParameterList)
    {
        string strOP;
        string szMsgBegin;
        string szMsgEnd;
        string strSortNo;
        strOP = "393";
        szMsgBegin = "2021-11-01 00:00:00";
        szMsgEnd = "2022-01-31 14:16:35";
        strSortNo = "";

        for(int i = 0; i < sqlCallParameterList.Count; i++)
        {
            switch (i)
            {
                case 0:
                    szMsgBegin = sqlCallParameterList[i].value.ToString();
                    break;
                case 1:
                    szMsgEnd = sqlCallParameterList[i].value.ToString();
                    break;
                case 2:
                    strOP = sqlCallParameterList[i].value.ToString();
                    break;
                case 3:
                    strSortNo = sqlCallParameterList[i].value.ToString();
                    break;

            }
        }


        MemoryStream ms;

        ms = new Form1().btnDaoChuOP_Click(szMsgBegin, szMsgEnd,strOP,  strSortNo);
        if (ms == null) return;
        //MESDownloadExcel.ExcelWebCall.ExcelFile(jsonData,out bytes,out fileName,ref fileExtension);
        //fileName = fileName + "."+fileExtension;
        string fileName = strOP+"_"+DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

        DownLoadFile(fileName, ms);

    }
    ///// <summary>
    ///// 导出OP
    ///// </summary>
    ///// <param name="strOP">工位号  请输入OP!</param>
    ///// <param name="szMsgBegin">开始时间</param>
    ///// <param name="szMsgEnd">结束时间</param>
    ///// <param name="strSortNo">发动机类型号</param>
    //public void btnDaoChuOP_Click(string strOP,string szMsgBegin ,string szMsgEnd,string strSortNo)



    private void DownLoadFile(string fileName,MemoryStream ms)
    {
        HttpContext.Current.Response.ContentType = "application/octet-stream";
        //HttpContext.Current.Response.ContentType = getContentType(extension);  
        //通知浏览器下载文件而不是打开  
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));
        HttpContext.Current.Response.AddHeader("Access-Control-Expose-Headers", "Content-Disposition");
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.End();
    }



}
#endregion
public class SqlCallParameter
{
    public string name { set; get; }
    public object value { set; get; }
    public SqlCallParameter(string name, object value)
    {
        this.name  = name;
        this.value = value;
    }
}
#region Form1
class Form1
{
    static string LineNum = "一线";

    //"0006"---"SELECT * from [0006-2015-10] union SELECT * from [0006-2015-11]"
    //Hashtable ht_CodeSQL = new Hashtable();

    //Hashtable ht_CodeSQLMonth = new Hashtable();

    //"0006"---"二维码扫描"
    // Hashtable ht_Code = new Hashtable();



    ///// <summary>
    ///// 开始日期到结束日期的月份
    ///// </summary>
    //List<string> List_DateMonth = new List<string>();

    ///// <summary>
    ///// 发动机所在的月份
    ///// </summary>
    //List<string> List_DateMonthOne = new List<string>();

    string strExcelPath;


    private ExcelCells ec;

    /// <summary>
    /// "0006"对应的列"二维码 索引"
    /// </summary>
    //DataTable dt_GroupDetail = new DataTable();

    string strTable2;

    //DataTable dt_code = new DataTable();

    /// <summary>
    /// 
    /// </summary>
    /// <param name="dateBegin_Value">开始时间</param>
    /// <param name="dateEnd_Value">结束时间</param>
    /// <param name="m_sSortno">生产类型码</param>
    /// <param name="m_sEngineNo"> 发动机号</param>
    /// <param name="m_sEngineNo1">号段查询</param>
    /// <param name="m_sEngineNo2">到</param>
    /// <param name="rbOK_Checked">合格</param>
    /// <param name="rbFanXiu_Checked">返修合格</param>
    /// <param name="rbNoOK_Checked">不合格</param>
    /// <param name="szNote">配件</param>
    /// <param name="szComponent">配件名称</param>
    /// <returns></returns>
    public string btnSearch_Click(string szMsgBegin,string szMsgEnd,

                    string m_sSortno,
                    string m_sEngineNo,
                    string m_sEngineNo1,
                    string m_sEngineNo2,
                    bool rbOK_Checked,
                    bool rbFanXiu_Checked,
                    bool rbNoOK_Checked,
                    string szNote,
                    string szComponent,
                    int PageCurrent,
        int PageSize,
        int PageCount,
        int ItemCount)

    {

        /// <summary>
        /// 查询数据
        /// </summary>
        string jsonStr = "";
        try
        {
            //string szMsgBegin = dateBegin.Text;
            //string szMsgEnd = dateEnd.Text;
            //string m_sSortno = txtSortNo.Text.Trim();
            //string m_sEngineNo = txtEngNo.Text.Trim();
            //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
            //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();
            DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin);
            DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd);


            TimeSpan ts = dateEnd_Value - dateBegin_Value;

            if (ts.TotalSeconds <= 0)
            {
                //Common.MsgInfoShow("结束时间必须大于开始时间!");
                return jsonStr;
            }
            try
            {
                //启动等待窗口
                //Thread TD = new Thread(ShowWaitingForm);
                //TD.Start();

                long l1 = DateTime.Now.Ticks/10000;


                DateTime date = dateBegin_Value;

                // 开始日期到结束日期的月份
                List<string> List_DateMonth = new List<string>();
                List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value);
                //初始化
                //List_DateMonth.Clear();

                //while (true)
                //{
                //    if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM"))
                //    {
                //        string strDateMonth = date.ToString("yyyy-MM");
                //        List_DateMonth.Add(strDateMonth);

                //        break;
                //    }
                //    else
                //    {
                //        string strDateMonth = date.ToString("yyyy-MM");
                //        List_DateMonth.Add(strDateMonth);

                //        date = date.AddMonths(1);
                //    }
                //}
                Hashtable ht_Code;
                Hashtable  ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out ht_Code);

                //string szMsgBegin = dateBegin.Text;
                //string szMsgEnd = dateEnd.Text;
                //string m_sSortno = txtSortNo.Text.Trim();
                //string m_sEngineNo = txtEngNo.Text.Trim();
                //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
                //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();

                string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin +
                                     "') and convert(datetime, '" + szMsgEnd + "') ";

                if (!string.IsNullOrEmpty(m_sSortno))
                {
                    sCondiction += " and sortno='" + m_sSortno + "' ";
                }

                if (!string.IsNullOrEmpty(m_sEngineNo))
                {
                    sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' ";
                }

                //2015-03-23-zjh-发动机号码段查询
                if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2))
                {
                    sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' ";

                    sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' ";
                }

                if (rbOK_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '合格' ";
                    sCondiction += " and sortnodesc = '合格' ";
                }
                else if (rbFanXiu_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '返修合格' ";
                    sCondiction += " and sortnodesc = '返修合格' ";
                }
                else if (rbNoOK_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '不合格' ";
                    sCondiction += " and sortnodesc = '不合格' ";
                }

                //string szNote = cboPeiJian.Text.Trim();
                //string szComponent = txtPeiJian.Text.Trim();
                if (!string.IsNullOrEmpty(szComponent))
                {
                    //sCondiction += " and flowdata.engine_index in (select engine_index from DMCTable, Component where Component.componentvalue_id = dmctable.value_id and StatusTeil='" + szNote + "' and dmc like '%" + szComponent + "%') ";

                    // and closedate between convert(datetime, '2015-10-29 00:00:00') and convert(datetime, '2015-11-01 01:32:32')  and flowdata.engine_index in (select engine_index from ( select * from [0006-2015-10]  UNION  select * from [0006-2015-11]  where 索引='ZKG' and [二维码] like '%bg%')as tb)

                    //sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" +
                    //               ht_CodeSQL["0006"].ToString() + " where 索引='" + szNote + "' and [二维码] like '%" +
                    //               szComponent + "%')as tb) ";

                    sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + ht_CodeSQL["0006"].ToString() + ")as tb where 索引='" + szNote + "' and [二维码] like '%" + szComponent + "%' )";
                }

                //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,qatable.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index " + sCondiction;
                string strSqlStart = " SELECT TOP  " + PageSize + " * FROM (";

                string strSqlEnd = " ) as A WHERE 编号 > "+ PageSize + " * ( "+PageCurrent+"-1) "+
                        " DECLARE	@PageCount int,@ItemCount int "
                +"select @PageCount ="+PageCount + " ,@ItemCount = count(*)  from flowdata where 1=1 " + sCondiction
                +" SELECT	@PageCount as N'PageCount',@ItemCount as N'ItemCount'";

                string strSQL = "  select rank() over(order by closedate desc, sortno) as 编号,engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where 1=1 " + sCondiction;

                //dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                string sql = strSqlStart + strSQL + strSqlEnd;
                //dt = ExecuteDataTable(sql);
                DataSet ds;
                ExecuteDataset(sql, out ds);
                string result = JsonHelper.DataTableToJson(ds.Tables[0]);
                string resultOutput = JsonHelper.DataTableToJson(ds.Tables[1]);
                jsonStr = "{\"result\":" + result + "," + "\"output\":" + resultOutput + "}";
                //dataGridView1.DataSource = dt;

                //dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列

                ////Common.BindGrid(dataGridView1);

                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    //2015-03-17-查询后显示合格率等
                //    string strHeGeLv;

                //    float iHeGe = 0, iFanXiu = 0, BuHeGe = 0, iHeGeLv = 0, iFanXiuLv = 0, BuHeGeLv = 0;

                //    for (int i = 0; i < dt.Rows.Count; i++)
                //    {
                //        string strMark = dt.Rows[i]["状态"].ToString();

                //        if (strMark == "合格")
                //        {
                //            iHeGe++;
                //        }
                //        else if (strMark == "返修合格")
                //        {
                //            iFanXiu++;
                //        }
                //        else if (strMark == "不合格")
                //        {
                //            BuHeGe++;
                //        }
                //    }

                //    string tmpStr1;
                //    string tmpStr2;
                //    string tmpStr3;

                //    int nCount = dt.Rows.Count;

                //    iHeGeLv = iHeGe/nCount*100;
                //    tmpStr1 = iHeGeLv.ToString("0.00");

                //    iFanXiuLv = iFanXiu/nCount*100;
                //    tmpStr2 = iFanXiuLv.ToString("0.00");

                //    BuHeGeLv = BuHeGe/nCount*100;
                //    tmpStr3 = BuHeGeLv.ToString("0.00");

                //    strHeGeLv = "合格率:" + tmpStr1 + "%   返修合格率:" + tmpStr2 + "%   不合格率:" + tmpStr3 + "%";
                //    lblHeGeLv.Text = strHeGeLv;
                //    long l22 = DateTime.Now.Ticks/10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22);

                //    ////2018-06-14-zjh-默认选中第一行,防止数据量大时,点击末尾的行时软件会死
                //    //dataGridView1.Rows[0].Selected = true;
                //}
                //else
                //{
                //    lblHeGeLv.Text = ""; //空数据时初始化

                //    long l2 = DateTime.Now.Ticks/10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2);


                //    //MessageBox.Show("此时间段无数据.", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information);


                //}

                //waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后，
                //焦点不在等待窗口上，造成自动关闭失效。
                //waitingFrm.Close();
                //TD.Abort(); //终止线程
                //TD.Join();
            }
            catch (Exception ex)
            {
                //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
            }

        }
        catch (Exception ex)
        {
            //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }
        return jsonStr;
    }
    public DataTable btnSearchDaoChu_Click(string szMsgBegin,string szMsgEnd,

                    string m_sSortno,
                    string m_sEngineNo,
                    string m_sEngineNo1,
                    string m_sEngineNo2,
                    bool rbOK_Checked,
                    bool rbFanXiu_Checked,
                    bool rbNoOK_Checked,
                    string szNote,
                    string szComponent)

    {

        /// <summary>
        /// 查询数据
        /// </summary>
        DataTable dt = new DataTable();
        try
        {
            //string szMsgBegin = dateBegin.Text;
            //string szMsgEnd = dateEnd.Text;
            //string m_sSortno = txtSortNo.Text.Trim();
            //string m_sEngineNo = txtEngNo.Text.Trim();
            //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
            //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();
            DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin);
            DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd);


            TimeSpan ts = dateEnd_Value - dateBegin_Value;

            if (ts.TotalSeconds <= 0)
            {
                //Common.MsgInfoShow("结束时间必须大于开始时间!");
                return dt;
            }
            try
            {
                //启动等待窗口
                //Thread TD = new Thread(ShowWaitingForm);
                //TD.Start();

                long l1 = DateTime.Now.Ticks/10000;


                DateTime date = dateBegin_Value;

                // 开始日期到结束日期的月份
                List<string> List_DateMonth = new List<string>();
                List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value);
                //初始化
                //List_DateMonth.Clear();

                //while (true)
                //{
                //    if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM"))
                //    {
                //        string strDateMonth = date.ToString("yyyy-MM");
                //        List_DateMonth.Add(strDateMonth);

                //        break;
                //    }
                //    else
                //    {
                //        string strDateMonth = date.ToString("yyyy-MM");
                //        List_DateMonth.Add(strDateMonth);

                //        date = date.AddMonths(1);
                //    }
                //}
                Hashtable ht_Code;
                Hashtable  ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out ht_Code);

                //string szMsgBegin = dateBegin.Text;
                //string szMsgEnd = dateEnd.Text;
                //string m_sSortno = txtSortNo.Text.Trim();
                //string m_sEngineNo = txtEngNo.Text.Trim();
                //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
                //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();

                string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin +
                                     "') and convert(datetime, '" + szMsgEnd + "') ";

                if (!string.IsNullOrEmpty(m_sSortno))
                {
                    sCondiction += " and sortno='" + m_sSortno + "' ";
                }

                if (!string.IsNullOrEmpty(m_sEngineNo))
                {
                    sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' ";
                }

                //2015-03-23-zjh-发动机号码段查询
                if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2))
                {
                    sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' ";

                    sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' ";
                }

                if (rbOK_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '合格' ";
                    sCondiction += " and sortnodesc = '合格' ";
                }
                else if (rbFanXiu_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '返修合格' ";
                    sCondiction += " and sortnodesc = '返修合格' ";
                }
                else if (rbNoOK_Checked)
                {
                    //2015-01-26-zjh-改为查询历史数据库
                    //sCondiction += " and qatable.mark = '不合格' ";
                    sCondiction += " and sortnodesc = '不合格' ";
                }

                //string szNote = cboPeiJian.Text.Trim();
                //string szComponent = txtPeiJian.Text.Trim();
                if (!string.IsNullOrEmpty(szComponent))
                {
                    //sCondiction += " and flowdata.engine_index in (select engine_index from DMCTable, Component where Component.componentvalue_id = dmctable.value_id and StatusTeil='" + szNote + "' and dmc like '%" + szComponent + "%') ";

                    // and closedate between convert(datetime, '2015-10-29 00:00:00') and convert(datetime, '2015-11-01 01:32:32')  and flowdata.engine_index in (select engine_index from ( select * from [0006-2015-10]  UNION  select * from [0006-2015-11]  where 索引='ZKG' and [二维码] like '%bg%')as tb)

                    //sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" +
                    //               ht_CodeSQL["0006"].ToString() + " where 索引='" + szNote + "' and [二维码] like '%" +
                    //               szComponent + "%')as tb) ";

                    sCondiction += " and flowdata.engine_index in (select distinct engine_index from (" + ht_CodeSQL["0006"].ToString() + ")as tb where 索引='" + szNote + "' and [二维码] like '%" + szComponent + "%' )";
                }

                //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,qatable.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index " + sCondiction;

                string strSQL = "  select rank() over(order by closedate desc, sortno) as 编号,engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where 1=1 " + sCondiction;

                //dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                dt = ExecuteDataTable(strSQL);

                //dataGridView1.DataSource = dt;

                //dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列

                ////Common.BindGrid(dataGridView1);

                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    //2015-03-17-查询后显示合格率等
                //    string strHeGeLv;

                //    float iHeGe = 0, iFanXiu = 0, BuHeGe = 0, iHeGeLv = 0, iFanXiuLv = 0, BuHeGeLv = 0;

                //    for (int i = 0; i < dt.Rows.Count; i++)
                //    {
                //        string strMark = dt.Rows[i]["状态"].ToString();

                //        if (strMark == "合格")
                //        {
                //            iHeGe++;
                //        }
                //        else if (strMark == "返修合格")
                //        {
                //            iFanXiu++;
                //        }
                //        else if (strMark == "不合格")
                //        {
                //            BuHeGe++;
                //        }
                //    }

                //    string tmpStr1;
                //    string tmpStr2;
                //    string tmpStr3;

                //    int nCount = dt.Rows.Count;

                //    iHeGeLv = iHeGe/nCount*100;
                //    tmpStr1 = iHeGeLv.ToString("0.00");

                //    iFanXiuLv = iFanXiu/nCount*100;
                //    tmpStr2 = iFanXiuLv.ToString("0.00");

                //    BuHeGeLv = BuHeGe/nCount*100;
                //    tmpStr3 = BuHeGeLv.ToString("0.00");

                //    strHeGeLv = "合格率:" + tmpStr1 + "%   返修合格率:" + tmpStr2 + "%   不合格率:" + tmpStr3 + "%";
                //    lblHeGeLv.Text = strHeGeLv;
                //    long l22 = DateTime.Now.Ticks/10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22);

                //    ////2018-06-14-zjh-默认选中第一行,防止数据量大时,点击末尾的行时软件会死
                //    //dataGridView1.Rows[0].Selected = true;
                //}
                //else
                //{
                //    lblHeGeLv.Text = ""; //空数据时初始化

                //    long l2 = DateTime.Now.Ticks/10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2);


                //    //MessageBox.Show("此时间段无数据.", "结果", MessageBoxButtons.OK, MessageBoxIcon.Information);


                //}

                //waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后，
                //焦点不在等待窗口上，造成自动关闭失效。
                //waitingFrm.Close();
                //TD.Abort(); //终止线程
                //TD.Join();
            }
            catch (Exception ex)
            {
                //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
            }

        }
        catch (Exception ex)
        {
            //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }
        return dt;
    }

    private List<string>  GetList_DateMonth(DateTime dateBegin_Value ,DateTime dateEnd_Value)
    {
        // 开始日期到结束日期的月份
        List<string> List_DateMonth = new List<string>();
        //初始化
        List_DateMonth.Clear();
        DateTime date = dateBegin_Value;
        while (true)
        {
            if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM"))
            {
                string strDateMonth = date.ToString("yyyy-MM");
                List_DateMonth.Add(strDateMonth);

                break;
            }
            else
            {
                string strDateMonth = date.ToString("yyyy-MM");
                List_DateMonth.Add(strDateMonth);

                date = date.AddMonths(1);
            }
        }

        return List_DateMonth;
    }

    /// <summary>
    /// 导出全部
    /// </summary>
    /// <param name="dateBegin_Value"></param>
    /// <param name="dateEnd_Value"></param>
    /// <param name="m_sSortno"></param>
    /// <param name="m_sEngineNo"></param>
    /// <param name="m_sEngineNo1"></param>
    /// <param name="m_sEngineNo2"></param>
    /// <param name="rbOK_Checked"></param>
    /// <param name="rbFanXiu_Checked"></param>
    /// <param name="rbNoOK_Checked"></param>
    /// <param name="szNote"></param>
    /// <param name="szComponent"></param>
    public MemoryStream btnDaoChuAll_Click(string szMsgBegin,string szMsgEnd,

                      string m_sSortno,
                      string m_sEngineNo,
                      string m_sEngineNo1,
                      string m_sEngineNo2,
                      bool rbOK_Checked,
                      bool rbFanXiu_Checked,
                      bool rbNoOK_Checked,
                      string szNote,
                      string szComponent,
                    int PageCurrent,
        int PageSize,
        int PageCount,
        int ItemCount)
    {
        MemoryStream ms = null;

        DataTable  dt = btnSearchDaoChu_Click(szMsgBegin, szMsgEnd,

             m_sSortno,
             m_sEngineNo,
             m_sEngineNo1,
             m_sEngineNo2,
             rbOK_Checked,
             rbFanXiu_Checked,
             rbNoOK_Checked,
             szNote,
             szComponent);

        try
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                ms = DataTableExport(dt);

                //Workbook workbook = new Workbook();
                ////workbook.Worksheets.Add("<" + strEngNo + ">");//sheet2名称
                //Worksheet sheet1 = workbook.Worksheets[0];
                ////sheet1.Name = "<" + strEngNo + ">质量数据";//sheet1名称
                //// Worksheet sheet2 = workbook.Worksheets[1];
                //Cells cells = sheet1.Cells;

                //cells.ImportDataTable(dt, true, "0");

                ////先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错
                //string strTempPath = ConfigurationManager.AppSettings.Get("TempPath");
                //string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath;
                //if (!Directory.Exists(szTempFile))
                //{
                //    Directory.CreateDirectory(szTempFile);
                //}

                //ec = new ExcelCells();
                //string fileName = ec.OutputExcel(dt, "Temp");





                //ec = new ExcelCells();

                //bool blHaveCol = true;//Excel中显示表头
                //ec.ImportDataColumn(dt, 0, 0, blHaveCol);

                ////gridDesktop1.ImportExcelFile(ec.GetExcelStream());//此方法只支持带批注的Excel 97-2003,但Excel 2007不支持

                ////ImportExcelFile()方法对带批注的只支持Excel 97-2003,对Excel 2007直接加载内存流数据会报错,必须保存成相应Excel 97-2003,2007格式的中间文件再用ImportExcelFile方法打开才行
                ////如果不存在文件夹则新建
                //string strFilePath = AppDomain.CurrentDomain.BaseDirectory + "temp";
                //if (!Directory.Exists(strFilePath))
                //{
                //    Directory.CreateDirectory(strFilePath);
                //}

                //string fileName = strFilePath + "temp" + ec.workbook.FileName.Substring(ec.workbook.FileName.ToLower().LastIndexOf(".xls"));//根据Excel的版本存为xls或xlsx类型,否则报错
                //ec.workbook.Save(fileName);
                ////Old
                ////gridDesktop1.ImportExcelFile(fileName);

                //2013-10-15:直接打开Excel
                //System.Diagnostics.Process process = new System.Diagnostics.Process();
                //process.StartInfo.FileName = fileName;
                //process.Start();

                ////string localFilePath, fileNameExt, newFileName, FilePath;
                //SaveFileDialog sfd = new SaveFileDialog();
                ////设置文件类型
                ////sfd.Filter = "Excel 2003文件（*.xls）|*.xls|Excel 2007文件（*.xlsx）|*.xlsx";
                //string strHouZhui = ec.workbook.FileFormat.ToString();
                //sfd.Filter = "自动选择（*.xls）和（*.xlsx）文件|*." + strHouZhui;

                ////设置默认文件类型显示顺序 
                ////sfd.FilterIndex = 2;//Excel 2007文件（*.xlsx）

                ////保存对话框是否记忆上次打开的目录 
                //sfd.RestoreDirectory = true;

                ////点了保存按钮进入 
                //if (sfd.ShowDialog() == DialogResult.OK)
                //{
                //    //string localFilePath = sfd.FileName.ToString(); //获得文件路径 
                //    //string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径

                //    //获取文件路径，不带文件名 
                //    //FilePath = localFilePath.Substring(0, localFilePath.LastIndexOf("\\"));

                //    //给文件名前加上时间 
                //    //newFileName = DateTime.Now.ToString("yyyyMMdd") + fileNameExt;

                //    //在文件名里加字符 
                //    //saveFileDialog1.FileName.Insert(1,"dameng");

                //    //System.IO.FileStream fs = (System.IO.FileStream)sfd.OpenFile();//输出文件

                //    ////fs输出带文字或图片的文件，就看需求了 

                //    this.Cursor = Cursors.WaitCursor;//鼠标忙

                //    //网格内容保存到一个的Excel文件
                //    gridDesktop1.ExportExcelFile(sfd.FileName);
                //    Common.MsgInfoShow("导出成功！");
                //    ////格内容保存到MS Excel 2007的xlsx文件格式
                //    //gridDesktop1.ExportExcelFile("D:\\book1.xlsx", FileFormatType.Excel2007Xlsx);
                //}
            }
            else
            {
                //MessageBox.Show("记录为空,无法导出.", "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

            }
        }
        catch (Exception ex)
        {
            // Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }

        return ms;
    }
    /// <summary>
    /// 导出单台
    /// <param name="strBeginTime">生产时间</param>
    /// <param name="strEndTime">完成时间</param>
    /// <param name="strEngNo">发动机编号</param>
    /// <param name="strEngine_Index">engine_index</param>
    /// <param name="strSortNo">发动机类型号</param>
    public MemoryStream btnDaoChuOne_Click(string strBeginTime,string strEndTime,string strEngNo,string strEngine_Index,string strSortNo)
    {
        MemoryStream ms = null;

        try
        {
            //if (dataGridView1.RowSel < 0)
            //{
            //    Common.MsgInfoShow("请选择一行记录!");
            //    return;
            //}

            //int x = dataGridView1.RowSel;

            //if (dataGridView1.Rows.Count > 0)
            {
                //先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错
                //string strTempPath = ConfigurationManager.AppSettings.Get("TempPath");
                //string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath;
                //if (!Directory.Exists(szTempFile))
                //{
                //    Directory.CreateDirectory(szTempFile);
                //}

                //string strEngNo = dataGridView1.Rows[x]["发动机编号"].ToString();
                //string strBeginTime = dataGridView1.Rows[x]["生产时间"].ToString();
                //string strEndTime = dataGridView1.Rows[x]["完成时间"].ToString();
                //string strEngine_Index = dataGridView1.Rows[x]["engine_index"].ToString();
                //string strSortNo = dataGridView1.Rows[x]["发动机类型号"].ToString();

                Workbook workbook = new Workbook();
                workbook.Worksheets.Add("<" + strEngNo + ">");//sheet2名称
                Worksheet sheet1 = workbook.Worksheets[0];
                sheet1.Name = "<" + strEngNo + ">质量数据";//sheet1名称
                Worksheet sheet2 = workbook.Worksheets[1];

                sheet1.Cells[0, 0].PutValue("生产时间");
                sheet1.Cells[0, 1].PutValue(strBeginTime);
                sheet1.Cells[0, 2].PutValue("完成时间");
                sheet1.Cells[0, 3].PutValue(strEndTime);
                sheet1.Cells[0, 4].PutValue("发动机类型号");
                sheet1.Cells[0, 5].PutValue(strSortNo);

                sheet2.Cells[0, 0].PutValue("生产时间");
                sheet2.Cells[0, 1].PutValue(strBeginTime);
                sheet2.Cells[0, 2].PutValue("完成时间");
                sheet2.Cells[0, 3].PutValue(strEndTime);

                int iSheet1Row = 2;

                string strSQL;

                //sheet1-new
                //2019-04-01-zjh-按双击网页的样式导出Excel
                DataSet ds = new DataSet();//存储相应op号对应的DataTable
                int iTableCount = 0;//table名称,加1
                Hashtable htOpType = new Hashtable();//存储相应op号对应的类型代码
                DataTable dtTableName = new DataTable();//op按从小到大排序后,取DataSet中的DataTable数据
                dtTableName.Columns.Add("op", typeof(int));
                dtTableName.Columns.Add("TableName", typeof(string));

                DateTime dateBegin_Value;
                DateTime dateEnd_Value;
                //dateBegin_Value = Convert.ToDateTime(strBeginTime);
                //dateEnd_Value = Convert.ToDateTime(strEndTime);
                Hashtable ht_Code = new Hashtable();

                Hashtable  ht_CodeSQL = CheckTable( strBeginTime, strEndTime,out ht_Code);

                foreach (DictionaryEntry de in ht_CodeSQL)
                {
                    strSQL = "select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( " + ht_CodeSQL[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index= '" + strEngine_Index + "' order by Engine.opnumber asc";

                    //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                    DataTable dtCode = ExecuteDataTable(strSQL);

                    if (dtCode != null && dtCode.Rows.Count > 0)
                    {
                        string strOpRows1 = dtCode.Rows[0]["OP号"].ToString();//首行op号

                        DataTable dtOp = dtCode.Clone();

                        for (int i = 0; i < dtCode.Rows.Count; i++)
                        {
                            string strOpRowsN = dtCode.Rows[i]["OP号"].ToString();//第二行后的op号

                            if (!htOpType.Contains(strOpRowsN))
                            {
                                htOpType.Add(strOpRowsN, de.Key);//op:2-----Type0006:二维码扫描,op:11-----Type0004:扳手
                            }

                            if (i == 0)//处理第一行
                            {
                                iTableCount++;
                                dtOp.TableName = "tb" + iTableCount.ToString();
                                ds.Tables.Add(dtOp);//第一次直接添加

                                //添加op和TableName
                                DataRow dr = dtTableName.NewRow();
                                dr["op"] = Convert.ToInt32(strOpRowsN);
                                dr["TableName"] = dtOp.TableName;
                                dtTableName.Rows.Add(dr);
                            }
                            else
                            {
                                if (strOpRows1 != strOpRowsN)//下一行和上一行的op不相同,数据插入到新建的DataTable中
                                {
                                    ds.Tables.Remove(dtOp.TableName);//把第一次的移除后再添加,否则添加相同名称的DataTable名称报错
                                    ds.Tables.Add(dtOp.Copy());
                                    dtOp.Rows.Clear();//清除数据

                                    strOpRows1 = strOpRowsN;//覆盖后,下一个循环重新判断
                                    iTableCount++;

                                    dtOp.TableName = "tb" + iTableCount.ToString();
                                    ds.Tables.Add(dtOp);

                                    //添加op和TableName
                                    DataRow dr = dtTableName.NewRow();
                                    dr["op"] = Convert.ToInt32(strOpRowsN);
                                    dr["TableName"] = dtOp.TableName;
                                    dtTableName.Rows.Add(dr);
                                }
                            }
                            dtOp.Rows.Add(dtCode.Rows[i].ItemArray);//复制数据行
                        }
                    }
                }

                DataView dv = dtTableName.DefaultView;
                dv.Sort = "op asc";
                dtTableName = dv.ToTable();

                for (int i = 0; i < dtTableName.Rows.Count; i++)
                {
                    DataTable dtCode = ds.Tables[dtTableName.Rows[i]["TableName"].ToString()];

                    string strCode = htOpType[dtTableName.Rows[i]["op"].ToString()].ToString();//0004,0006

                    //strHtml.Append("<table  class='component'>\n");
                    //strHtml.Append("类型:" + ht_Code[strCode].ToString() + "<br>\n");
                    sheet1.Cells[iSheet1Row, 0].PutValue("类型");
                    sheet1.Cells[iSheet1Row, 1].PutValue(ht_Code[strCode].ToString());
                    iSheet1Row++;

                    //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引
                    //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引
                    dtCode.Columns.Remove("engine_index");
                    dtCode.Columns.Remove("opnumber");
                    dtCode.Columns.Remove("optype");

                    DataTable dtsheet1 = dtCode.Copy();

                    //2016-03-30-zjh-自定义显示列名
                    strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

                    DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);
                    //去掉GroupDetail表description="skip"的列
                    for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++)
                    {
                        DataRow[] drs = dt_GroupDetail.Select("code='" + strCode + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'");
                        if (drs != null && drs.Length > 0)
                        {
                            if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip")
                            {
                                dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName);
                            }
                        }
                    }

                    if (dtsheet1 != null && dtsheet1.Rows.Count > 0)
                    {
                        //2016-03-28-zjh-带通道号的列,按通道号从小到大排序
                        if (dtsheet1.Columns[2].ColumnName == "轴号")
                        {
                            DataTable dtsheetCopy = new DataTable();

                            foreach (DataColumn col in dtsheet1.Columns)
                            {
                                if (col.ColumnName == "轴号")
                                {
                                    dtsheetCopy.Columns.Add("轴号", typeof(Int32));
                                }
                                else
                                {
                                    dtsheetCopy.Columns.Add(col.ColumnName);
                                }
                            }

                            //填充数据
                            foreach (DataRow row in dtsheet1.Rows)
                            {
                                //dtsheetCopy.Rows.Add(row.ItemArray);
                                dtsheetCopy.ImportRow(row);
                            }

                            DataView dvTest = dtsheetCopy.DefaultView;
                            dvTest.Sort = "轴号 asc";//按轴号排序
                            dtsheet1 = dvTest.ToTable();
                        }
                    }

                    //strHtml.Append("</table><br>\n");
                    bool blHaveCol = true;//是否显示表头
                    int firstRow = iSheet1Row;
                    int firstColumn = 0;
                    bool blInsertRow = false;
                    Cells cells = sheet1.Cells;
                    cells.ImportDataTable(dtsheet1, blHaveCol, firstRow, firstColumn, dtsheet1.Rows.Count, dtsheet1.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss");

                    iSheet1Row += dtsheet1.Rows.Count + 2;//加2:空一行
                }





                //sheet2
                //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,qatable.mark as 综合 from engine inner join qatable on engine.engine_index = qatable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";
                //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,flowdata.sortnodesc as 综合 from engine inner join flowdata on engine.engine_index = flowdata.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";
                strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,OP_QATable.opmark as 综合 from engine inner join OP_QATable on engine.opnumber=OP_QATable.opnumber and engine.engine_index=OP_QATable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";

                //DataTable dtsheet2 = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                DataTable dtsheet2 = ExecuteDataTable(strSQL);

                dv = dtsheet2.DefaultView;
                dv.Sort = "op号 asc";
                dtsheet2 = dv.ToTable();

                if (dtsheet2 != null && dtsheet2.Rows.Count > 0)
                {
                    bool blHaveCol = true;//是否显示表头
                    int firstRow = 1;
                    int firstColumn = 0;
                    bool blInsertRow = false;
                    Cells cells = sheet2.Cells;
                    cells.ImportDataTable(dtsheet2, blHaveCol, firstRow, firstColumn, dtsheet2.Rows.Count, dtsheet2.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss");
                }

                //string FileName = "Temp-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";//.xls
                //string returnStr = AppDomain.CurrentDomain.BaseDirectory + "Temp\\" + FileName;

                ms = workbook.SaveToStream();

                //workbook.Save(returnStr, SaveFormat.Xlsx);

                ////2013-10-15:直接打开Excel
                //System.Diagnostics.Process process = new System.Diagnostics.Process();
                //process.StartInfo.FileName = returnStr;
                //process.Start();
            }
            //else
            //{
            //    Common.MsgInfoShow("请选择一行记录!");
            //}
        }
        catch (Exception ex)
        {
            //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }

        return ms;
    }

    /// <summary>
    /// 导出OP
    /// </summary>
    /// <param name="strOP">工位号  请输入OP!</param>
    /// <param name="szMsgBegin">开始时间</param>
    /// <param name="szMsgEnd">结束时间</param>
    /// <param name="strSortNo">发动机类型号</param>
    public MemoryStream btnDaoChuOP_Click(string szMsgBegin ,string szMsgEnd,string strOP,string strSortNo)
    {
        MemoryStream ms = null;
        try
        {

            //string strOP = txtOP.Text.Trim();
            //if (string.IsNullOrEmpty(strOP))
            //{
            //    Common.MsgInfoShow("请输入OP!");
            //    return;
            //}

            ////启动等待窗口
            //Thread TD = new Thread(ShowWaitingForm);
            //TD.Start();

            //先创建文件夹,否则生成Excel方法:OutputExcel(),workbook.Save()报错
            string strTempPath = ConfigurationManager.AppSettings.Get("TempPath");
            string szTempFile = AppDomain.CurrentDomain.BaseDirectory + strTempPath;
            if (!Directory.Exists(szTempFile))
            {
                Directory.CreateDirectory(szTempFile);
            }

            Workbook workbook = new Workbook();
            Worksheet sheet1 = workbook.Worksheets[0];
            sheet1.Name = "质量数据";//sheet1名称

            int iSheet1Row = 0;

            string strSQL;

            //string szMsgBegin = dateBegin.Text;
            //string szMsgEnd = dateEnd.Text;
            //DateTime            dateBegin_Value = Convert.ToDateTime(szMsgBegin);
            //DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd);
            Hashtable ht_Code;
            Hashtable  ht_CodeSQL = CheckTable( szMsgBegin, szMsgEnd,out  ht_Code);
            //是否打开Excel文件
            bool isOpen = false;


            foreach (DictionaryEntry de in ht_CodeSQL)
            {
                //select flowdata.kb + flowdata.engineno as 发动机号,flowdata.date as 生产时间,flowdata.closedate as 完成时间,Engine.station as 工位,Engine.opnumber as OP号,flowdata.workload as 托盘号,tbCode.* from ( select * from [0006-2015-10]  UNION  select * from [0006-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber inner join flowdata on tbCode.engine_index = flowdata.engine_index where FlowData.closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') and tbCode.opnumber=5
                strSQL = "select flowdata.kb + flowdata.engineno as 发动机号,flowdata.date as 生产时间,flowdata.closedate as 完成时间,Engine.station as 工位,Engine.opnumber as OP号,flowdata.workload as 托盘号,tbCode.* from ( " + ht_CodeSQL[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber inner join flowdata on tbCode.engine_index = flowdata.engine_index where FlowData.closedate between convert(datetime, '" + szMsgBegin + "') and convert(datetime, '" + szMsgEnd + "') and tbCode.opnumber='" + strOP + "'";

                //string strSortNo = txtSortNo.Text.Trim();
                if (!string.IsNullOrEmpty(strSortNo))
                {
                    strSQL += " and flowdata.[sortno]='" + strSortNo + "'";
                }

                //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                DataTable dtCode = ExecuteDataTable(strSQL);

                if (dtCode != null && dtCode.Rows.Count > 0)
                {
                    isOpen = true;
                    sheet1.Cells[iSheet1Row, 0].PutValue("类型");
                    sheet1.Cells[iSheet1Row, 1].PutValue(ht_Code[de.Key].ToString());

                    iSheet1Row++;

                    //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引
                    //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引
                    dtCode.Columns.Remove("engine_index");
                    dtCode.Columns.Remove("opnumber");
                    dtCode.Columns.Remove("optype");

                    DataTable dtsheet1 = dtCode.Copy();
                    //2016-03-30-zjh-自定义显示列名
                    strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

                    DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);
                    //去掉GroupDetail表description="skip"的列
                    for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++)
                    {
                        DataRow[] drs = dt_GroupDetail.Select("code='" + de.Key.ToString() + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'");
                        if (drs != null && drs.Length > 0)
                        {
                            if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip")
                            {
                                dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName);
                            }
                        }
                    }
                    bool blHaveCol = true;//是否显示表头
                    int firstRow = iSheet1Row;
                    int firstColumn = 0;
                    bool blInsertRow = false;
                    Cells cells = sheet1.Cells;

                    cells.ImportDataTable(dtsheet1, blHaveCol, firstRow, firstColumn, dtsheet1.Rows.Count, dtsheet1.Columns.Count, blInsertRow, "yyyy-MM-dd HH:mm:ss");

                    iSheet1Row += dtsheet1.Rows.Count + 2;//加2:空一行
                }
            }
            ms = workbook.SaveToStream();

            //关闭稍等窗口
            //waitingFrm.Close();
            //TD.Abort(); //终止线程
            //TD.Join();
            //if (isOpen)
            //{
            //    string FileName = "Temp-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; //.xls
            //    string returnStr = AppDomain.CurrentDomain.BaseDirectory + "Temp\\" + FileName;
            //    workbook.Save(returnStr, SaveFormat.Xlsx);

            //    //2013-10-15:直接打开Excel
            //    System.Diagnostics.Process process = new System.Diagnostics.Process();
            //    process.StartInfo.FileName = returnStr;
            //    process.Start();
            //}
        }
        catch (Exception ex)
        {
            //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }

        return ms;
    }



    /// <summary>
    /// 异常
    /// </summary>
    /// <param name="dateBegin_Value"></param>
    /// <param name="dateEnd_Value"></param>
    /// <param name="strEndTime"></param>
    /// <param name="m_sSortno"></param>
    /// <param name="m_sEngineNo"></param>
    /// <param name="m_sEngineNo1"></param>
    /// <param name="m_sEngineNo2"></param>
    /// <returns></returns>
    public DataTable btnYiChang_Click( string szMsgBegin,string szMsgEnd,
                              string m_sSortno,
                      string m_sEngineNo,
                      string m_sEngineNo1,
                      string m_sEngineNo2)
    {

        //string m_sSortno = txtSortNo.Text.Trim();
        //string m_sEngineNo = txtEngNo.Text.Trim();
        //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
        //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();
        DataTable dt = new DataTable();
        try
        {
            DateTime dateBegin_Value;
            DateTime dateEnd_Value;
            dateBegin_Value = Convert.ToDateTime(szMsgBegin);
            dateEnd_Value = Convert.ToDateTime(szMsgEnd);

            //string szMsgBegin = dateBegin_Value.ToString();
            //string szMsgEnd = dateEnd_Value.ToString();
            //TimeSpan ts = dateEnd.Value - dateBegin.Value;

            //if (ts.TotalSeconds <= 0)
            //{
            //    Common.MsgInfoShow("结束时间必须大于开始时间!");
            //    return;
            //}
            try
            {
                ////启动等待窗口
                //Thread TD = new Thread(ShowWaitingForm);
                //TD.Start();

                long l1 = DateTime.Now.Ticks / 10000;


                DateTime date = dateBegin_Value;
                // 开始日期到结束日期的月份
                List<string> List_DateMonth = new List<string>();
                //初始化
                List_DateMonth.Clear();

                while (true)
                {
                    if (date.ToString("yyyy-MM") == dateEnd_Value.ToString("yyyy-MM"))
                    {
                        string strDateMonth = date.ToString("yyyy-MM");
                        List_DateMonth.Add(strDateMonth);

                        break;
                    }
                    else
                    {
                        string strDateMonth = date.ToString("yyyy-MM");
                        List_DateMonth.Add(strDateMonth);

                        date = date.AddMonths(1);
                    }
                }

                //2018-06-07-zjh
                strTable2 = "";
                Hashtable ht_Code;
                CheckTable( szMsgBegin, szMsgEnd,out  ht_Code);

                //string szMsgBegin = dateBegin.Text;
                //string szMsgEnd = dateEnd.Text;
                //string m_sSortno = txtSortNo.Text.Trim();
                //string m_sEngineNo = txtEngNo.Text.Trim();
                //string m_sEngineNo1 = txtEngNoBegin.Text.Trim();
                //string m_sEngineNo2 = txtEngNoEnd.Text.Trim();

                string sCondiction = " and closedate between convert(datetime, '" + szMsgBegin +
                                     "') and convert(datetime, '" + szMsgEnd + "') ";

                if (!string.IsNullOrEmpty(m_sSortno))
                {
                    sCondiction += " and sortno='" + m_sSortno + "' ";
                }

                if (!string.IsNullOrEmpty(m_sEngineNo))
                {
                    sCondiction += " and kb+engineno like '%" + m_sEngineNo + "%' ";
                }

                //2015-03-23-zjh-发动机号码段查询
                if (!string.IsNullOrEmpty(m_sEngineNo1) && !string.IsNullOrEmpty(m_sEngineNo2))
                {
                    sCondiction += " and kb+engineno >= '" + m_sEngineNo1 + "' ";

                    sCondiction += " and kb+engineno <= '" + m_sEngineNo2 + "' ";
                }

                //string strSQL = "select rank() over(order by closedate desc, sortno) as 编号,flowdata.engine_index, qatable.mark as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata,qatable where qatable.engine_index = flowdata.engine_index and flowdata.engine_index not in(select [engine_index] from( ";

                //如果速度慢,不加载qatable表
                StringBuilder strSQL = new StringBuilder("");
                strSQL.Append("select rank() over(order by closedate desc, sortno) as 编号,flowdata.engine_index, sortnodesc as 状态,sortno as 发动机类型号, kb + engineno as 发动机编号,[workload] as 托盘号,convert(char, date,120) as 生产时间 ,convert(char, closedate,120) as 完成时间 from flowdata where  flowdata.engine_index not in(select [engine_index] from( ");

                strSQL.Append(strTable2 + " )t1 where ( ");

                //opnumber=39 or opnumber=40 or opnumber=41 or opnumber=54 or opnumber=55 or opnumber=135 or opnumber=225 or opnumber=258 or opnumber=382 or opnumber=60 or opnumber=84
                string strOpNumber = LineNum == "一线" ? ConfigurationManager.AppSettings.Get("OpNumber1") : ConfigurationManager.AppSettings.Get("OpNumber2");

                string[] strListOpNumber = strOpNumber.Split(',');
                for(int i=0;i<strListOpNumber.Length;i++)
                {
                    if (i == strListOpNumber.Length - 1)
                    {
                        strSQL.Append(" opnumber=" + strListOpNumber[i] + " ");
                    }
                    else
                    {
                        strSQL.Append(" opnumber=" + strListOpNumber[i] + " or ");
                    }
                }

                string stDataLen = LineNum == "一线" ? ConfigurationManager.AppSettings.Get("DataLen1") : ConfigurationManager.AppSettings.Get("DataLen2");

                strSQL.Append(" ) group by engine_index having count(engine_index)=" + stDataLen + " ) ");
                //strSQL += " ) group by engine_index having count(engine_index)=46 ) and closedate between convert(datetime, '" + dateBegin.Value.ToString() + "') and convert(datetime, '" + dateEnd.Value.ToString() + "') ";

                strSQL.Append(sCondiction);

                //dt = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                dt = ExecuteDataTable(strSQL.ToString());

                //dataGridView1.DataSource = dt;

                //dataGridView1.Cols["engine_index"].Visible = false;//隐藏engine_index列

                ////Common.BindGrid(dataGridView1);

                //if (dt != null && dt.Rows.Count > 0)
                //{
                //    long l22 = DateTime.Now.Ticks / 10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 {0} 条记录.耗时:{1}ms", dt.Rows.Count, l22);
                //}
                //else
                //{
                //    long l2 = DateTime.Now.Ticks / 10000 - l1;

                //    toolStripStatusLabel1.Text = string.Format("查询结果:共 0 条记录.耗时:{0}ms", l2);
                //}

                ////waitingFrm.Activate(); //这个操作主要是为了防止在数据加载完成后，
                ////焦点不在等待窗口上，造成自动关闭失效。
                //waitingFrm.Close();
                //TD.Abort(); //终止线程
                //TD.Join();
            }
            catch (Exception ex)
            {
                // Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
            }
        }
        catch (Exception ex)
        {
            //Common.MsgInfoShow(ex.Source + ":" + ex.ToString());
        }
        return dt;
    }




    private string css =
               "<style>* {font-size: 12pt;padding: 0;font-family:\"tahoma\";background-color:whitesmoke;}h3 {font:\"tahoma\";font-size:15pt;}table{border:3px solid lightgray;border-collapse:collapse;padding:20;}.component{}.componenthead, .componentdesc{font-weight:700;word-break:break-all;text-align: center;}.componentdesc td{background-color: lightgray;}.componenthead td{padding: 5;background-color: lightgray;color: gray;}.componenthead ~ tr td{border: 1px solid lightgray;padding: 4;padding-left: 10;}.engine{margin-top:30px;width:100%;}.enginehead td{background-color:skyblue;padding:10;border:1px solid lightgray;}.enginehead ~ tr td{border:1px solid lightgray;padding:5;}.row2 td{background-color: #e3e3e3;}.componenthead td:after{content: \"[\" attr(cname) \"]\";}.componenthead ~ tr:hover td {background-color: gold;}</style>";
    /// <summary>
    /// 输出发动机详细信息
    /// </summary>
    /// <param name="strBeginTime">生产时间</param>
    /// <param name="strEndTime">完成时间</param>
    /// <param name="strEngNo">发动机编号</param>
    /// <param name="strEngine_Index">engine_index</param>
    /// <returns>文件</returns>
    public MemoryStream CreatHtml(string szMsgBegin,string szMsgEnd,string m_sEngineNo,string strEngine_Index)
    {
        //int x = dataGridView1.RowSel;
        Hashtable ht_Code;
        CheckTable(szMsgBegin, szMsgEnd, out ht_Code);
        StringBuilder strHtml = new StringBuilder("");
        //string strEngNo = dataGridView1.Rows[x]["发动机编号"].ToString().Trim();
        //string strBeginTime = dataGridView1.Rows[x]["生产时间"].ToString().Trim();
        //string strEndTime = dataGridView1.Rows[x]["完成时间"].ToString().Trim();
        //string strEngine_Index = dataGridView1.Rows[x]["engine_index"].ToString().Trim();

        strHtml.Append("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">\n");
        strHtml.Append("<html>\n");
        strHtml.Append("<head>" + css + " <meta http-equiv=Content-Type content=\"text/html;charset=utf-8\"> <link rel=\"stylesheet\" href=\"../report2.css\"></head>\n");
        strHtml.Append("<body>\n");
        strHtml.Append("<h3>发动机编号:" + m_sEngineNo + " 启动时间:" + szMsgBegin + " 下线时间:" + szMsgEnd + "</h3>\n");

        //第一部分:质量数据
        strHtml.Append("<center><h3>质量数据</h3></center>\n");
        strHtml.Append("<p></p>\n");

        string strSQL;

        DataSet ds = new DataSet();//存储相应op号对应的DataTable
        int iTableCount = 0;//table名称,加1
        Hashtable htOpType = new Hashtable();//存储相应op号对应的类型代码

        DataTable dtTableName = new DataTable();//op按从小到大排序后,取DataSet中的DataTable数据
        dtTableName.Columns.Add("op", typeof(int));
        dtTableName.Columns.Add("TableName", typeof(string));

        /// <summary>
        /// 发动机所在的月份
        /// </summary>
        List<string> List_DateMonthOne = new List<string>();
        //初始化
        List_DateMonthOne.Clear();

        string strDateMonth = Convert.ToDateTime(szMsgEnd).ToString("yyyy-MM");
        //string strDateMonth = Convert.ToDateTime(dataGridView1.Rows[x]["完成时间"].ToString()).ToString("yyyy-MM");
        List_DateMonthOne.Add(strDateMonth);

        Hashtable ht_CodeSQLMonth = CheckTableMonth(List_DateMonthOne);
        //sheet1
        foreach (DictionaryEntry de in ht_CodeSQLMonth)
        {
            //select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( select * from [0006-2015-10]  UNION  select * from [0006-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index=2376
            //select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( select * from [0007-2015-10]  UNION  select * from [0007-2015-11] ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index=2376
            strSQL = "select engine.station as 工位,engine.opnumber as OP号,tbCode.* from ( " + ht_CodeSQLMonth[de.Key].ToString() + " ) as tbCode inner join engine on engine.engine_index = tbCode.engine_index and Engine.opnumber=tbCode.opnumber where engine.engine_index= '" + strEngine_Index + "' order by Engine.opnumber asc";

            //DataTable dtCode = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            DataTable dtCode = ExecuteDataTable(strSQL);

            if (dtCode != null && dtCode.Rows.Count > 0)
            {
                string strOpRows1 = dtCode.Rows[0]["OP号"].ToString();//首行op号

                DataTable dtOp = dtCode.Clone();

                for (int i = 0; i < dtCode.Rows.Count; i++)
                {
                    string strOpRowsN = dtCode.Rows[i]["OP号"].ToString();//第二行后的op号

                    if (!htOpType.Contains(strOpRowsN))
                    {
                        htOpType.Add(strOpRowsN, de.Key);//op:2-----Type0006:二维码扫描,op:11-----Type0004:扳手
                    }

                    if (i == 0)//处理第一行
                    {
                        iTableCount++;
                        dtOp.TableName = "tb" + iTableCount.ToString();
                        ds.Tables.Add(dtOp);//第一次直接添加

                        //添加op和TableName
                        DataRow dr = dtTableName.NewRow();
                        dr["op"] = Convert.ToInt32(strOpRowsN);
                        dr["TableName"] = dtOp.TableName;
                        dtTableName.Rows.Add(dr);
                    }
                    else
                    {
                        if (strOpRows1 != strOpRowsN)//下一行和上一行的op不相同,数据插入到新建的DataTable中
                        {
                            ds.Tables.Remove(dtOp.TableName);//把第一次的移除后再添加,否则添加相同名称的DataTable名称报错
                            ds.Tables.Add(dtOp.Copy());
                            dtOp.Rows.Clear();//清除数据

                            strOpRows1 = strOpRowsN;//覆盖后,下一个循环重新判断
                            iTableCount++;

                            dtOp.TableName = "tb" + iTableCount.ToString();
                            ds.Tables.Add(dtOp);

                            //添加op和TableName
                            DataRow dr = dtTableName.NewRow();
                            dr["op"] = Convert.ToInt32(strOpRowsN);
                            dr["TableName"] = dtOp.TableName;
                            dtTableName.Rows.Add(dr);
                        }
                    }
                    dtOp.Rows.Add(dtCode.Rows[i].ItemArray);//复制数据行
                }


            }
        }

        DataView dv = dtTableName.DefaultView;
        dv.Sort = "op asc";
        dtTableName = dv.ToTable();

        for (int i = 0; i < dtTableName.Rows.Count; i++)
        {
            DataTable dtCode = ds.Tables[dtTableName.Rows[i]["TableName"].ToString()];
            strHtml.Append("<table  class='component'>\n");

            string strCode = htOpType[dtTableName.Rows[i]["op"].ToString()].ToString();//0004,0006
            strHtml.Append("类型:" + ht_Code[strCode].ToString() + "<br>\n");

            //dtCode原来列:工位 OP号 engine_index opnumber optype 二维码 索引
            //dtsheet1修改列,去掉3，4，5列后:工位 OP号 二维码 索引
            dtCode.Columns.Remove("engine_index");
            dtCode.Columns.Remove("opnumber");
            dtCode.Columns.Remove("optype");

            DataTable dtsheet1 = dtCode.Copy();
            //2016-03-30-zjh-自定义显示列名
            strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

            DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);
            //去掉GroupDetail表description="skip"的列
            for (int iCol = 0; iCol < dtCode.Columns.Count; iCol++)
            {
                DataRow[] drs = dt_GroupDetail.Select("code='" + strCode + "' and column_name='" + dtCode.Columns[iCol].ColumnName + "'");
                if (drs != null && drs.Length > 0)
                {
                    if (drs[0]["GroupDetaildescription"].ToString().Trim().ToLower() == "skip")
                    {
                        dtsheet1.Columns.Remove(dtCode.Columns[iCol].ColumnName);
                    }
                }
            }

            strHtml.Append("<tr  class='componenthead'>\n");
            for (int j = 0; j < dtsheet1.Columns.Count; j++)
            {
                strHtml.Append("<td cname='" + dtsheet1.Columns[j].ColumnName + "'></td>\n");
            }
            strHtml.Append("</tr>\n");

            if (dtsheet1 != null && dtsheet1.Rows.Count > 0)
            {
                //2016-03-28-zjh-带通道号的列,按通道号从小到大排序
                if (dtsheet1.Columns[2].ColumnName == "轴号")
                {
                    //DataTable dtsheetCopy = UpdateDataTable(dtsheet1, "轴号", "System.Int32");
                    //DataTable dtsheetCopy = dtsheet1.Clone();

                    ////修改列类型后,dtsheetCopy.ImportRow(row);“报错:"无法设置列轴号" "。此值违反了此列的MaxLength限制”,改为直接新建表后新增列
                    //dtsheetCopy.Columns[2].DataType = typeof(Int32);//Type.GetType("System.Int32");//无数据时才能修改表结构

                    DataTable dtsheetCopy = new DataTable();

                    foreach (DataColumn col in dtsheet1.Columns)
                    {
                        if (col.ColumnName == "轴号")
                        {
                            dtsheetCopy.Columns.Add("轴号", typeof(Int32));

                            //修改列类型后,dtsheetCopy.ImportRow(row);“报错:"无法设置列轴号" "。此值违反了此列的MaxLength限制”,改为直接新建表后新增列
                            //col.DataType = typeof(Int32);
                        }
                        else
                        {
                            dtsheetCopy.Columns.Add(col.ColumnName);
                        }
                    }

                    //填充数据
                    foreach (DataRow row in dtsheet1.Rows)
                    {
                        //dtsheetCopy.Rows.Add(row.ItemArray);
                        dtsheetCopy.ImportRow(row);
                    }

                    DataView dvTest = dtsheetCopy.DefaultView;
                    dvTest.Sort = "轴号 asc";//按轴号排序
                    dtsheet1 = dvTest.ToTable();
                }

                int nCounter = 0;
                for (int j = 0; j < dtsheet1.Rows.Count; j++)
                {
                    nCounter++;
                    if (nCounter % 2 == 1)
                    {
                        strHtml.Append("<tr class='row1'>\n");
                    }
                    else
                    {
                        strHtml.Append("<tr  class='row2'>\n");
                    }

                    for (int k = 0; k < dtsheet1.Columns.Count; k++)
                    {
                        strHtml.Append("<td>" + dtsheet1.Rows[j][k].ToString() + "</td>\n");
                    }

                    strHtml.Append("</tr>\n");
                }
            }

            strHtml.Append("</table><br>\n");
        }

        //第二部分:操作
        strHtml.Append("<center><h3>操作</h3></center>\n<table class='engine'>\n<tr class='enginehead'>\n<td>操作号</td>\n<td>操作号名称</td>\n<td>操作类型</td>\n<td>描述</td>\n<td>工作</td>\n<td>合格</td>\n<td>返修</td>\n<td>综合</td>\n</tr>\n");

        int nCounter2 = 0;

        //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,case Engine.isqaok when 1 then '合格' when 0 then '不合格' end as 综合 from engine inner join qatable on engine.engine_index = qatable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";
        //strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,case Engine.isqaok when 1 then '合格' when 0 then '不合格' end as 综合 from engine left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";
        strSQL = "select engine.opnumber as OP号,[opNumber].description as OP号名称,engine.optype as 操作类型,[optypeinfo].name as 描述,case Engine.isworked when 1 then 'OK' when 0 then 'NOK' end as 工作,case Engine.isqaok when 1 then 'OK' when 0 then 'NOK' end as 合格,case Engine.isrepaireok when 1 then 'OK' when 0 then '-' end as 返修,OP_QATable.opmark as 综合 from engine inner join OP_QATable on engine.opnumber=OP_QATable.opnumber and engine.engine_index=OP_QATable.engine_index left join [opNumber] on engine.opnumber=[opnumber].id left join [optypeinfo] on engine.optype=[optypeinfo].id where engine.engine_index= '" + strEngine_Index + "'";

        //DataTable dtsheet2 = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        DataTable dtsheet2 = ExecuteDataTable(strSQL);

        dv = dtsheet2.DefaultView;
        dv.Sort = "op号 asc";
        dtsheet2 = dv.ToTable();

        if (dtsheet2 != null && dtsheet2.Rows.Count > 0)
        {
            for (int i = 0; i < dtsheet2.Rows.Count; i++)
            {
                nCounter2++;
                if (nCounter2 % 2 == 1)
                {
                    strHtml.Append("<tr class='row1'>\n");
                }
                else
                {
                    strHtml.Append("<tr  class='row2'>\n");
                }

                for (int j = 0; j < dtsheet2.Columns.Count; j++)
                {
                    if (dtsheet2.Columns[j].ColumnName == "综合")
                    {
                        //zjh-判断综合列的背景色，不合格为红色，合格并返修为黄色，其他为无色
                        string strbgColor = "";
                        if (dtsheet2.Rows[i]["合格"].ToString() == "NOK")
                        {
                            strbgColor = "style='background-color:red'";
                        }
                        else if (dtsheet2.Rows[i]["合格"].ToString() == "OK" && dtsheet2.Rows[i]["返修"].ToString() == "OK")
                        {
                            strbgColor = "style='background-color:yellow'";
                        }
                        strHtml.Append("<td " + strbgColor + ">" + dtsheet2.Rows[i][j].ToString() + "</td>\n");
                    }
                    else
                    {
                        strHtml.Append("<td>" + dtsheet2.Rows[i][j].ToString() + "</td>\n");
                    }
                }

                strHtml.Append("</tr>\n");
            }
        }

        strHtml.Append("</table>\n");
        strHtml.Append("</body>\n");
        strHtml.Append("</html>\n");


        //string str = "Testing 1-2-3";             //convert string 2 stream            
        byte[] array = Encoding.UTF8.GetBytes(strHtml.ToString());
        MemoryStream ms = new MemoryStream(array);             //convert stream 2 string
        return ms;
        //    StreamReader reader = new StreamReader(stream);

        //string strFileName = AppDomain.CurrentDomain.BaseDirectory + "Report\\" + strEngNo + ".html";
        ////如果存在,则先删除文件
        //if (File.Exists(strFileName))
        //{
        //    File.Delete(strFileName);
        //}
        //FileInfo fileinfo = new FileInfo(strFileName);
        //using (FileStream fs = fileinfo.OpenWrite())
        //{
        //    //StreamWriter sw = new StreamWriter(fs);//乱码
        //    StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);//Encoding.GetEncoding("gb2312")
        //                                                                      //sw.BaseStream.Seek(0, SeekOrigin.End);
        //    sw.Write(strHtml);
        //    sw.Flush();
        //    sw.Close();
        //}


        //return strFileName;
    }

    private Hashtable  CheckTable(string szMsgBegin,string szMsgEnd,out Hashtable ht_Code)
    {
        //IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[0007-2015-10]') AND type in (N'U'))
        //select '1'
        //else
        //CREATE TABLE [0007-2015-10](
        //    [engine_index] [int] NOT NULL,
        //    [opnumber] [int] NOT NULL,
        //    [optype] [int] NOT NULL,
        //    [设定上限] [nvarchar](50) NULL,
        //    [设定下限] [nvarchar](50) NULL,
        //    [实际] [nvarchar](50) NULL,
        //    [Einzel I.O.] [nvarchar](50) NULL,
        //    [程序] [nvarchar](50) NULL,
        //    [通道] [nvarchar](50) NULL
        //) ON [PRIMARY]
        // 开始日期到结束日期的月份
        DateTime dateBegin_Value = Convert.ToDateTime(szMsgBegin);
        DateTime dateEnd_Value = Convert.ToDateTime(szMsgEnd);
        ht_Code = new Hashtable();
        List<string> List_DateMonth = new List<string>();
        List_DateMonth = GetList_DateMonth(dateBegin_Value, dateEnd_Value);
        //IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[0007-2015-10]') AND type in (N'U')) select '1' else CREATE TABLE [0007-2015-10]([engine_index] [int] NOT NULL,[opnumber] [int] NOT NULL,[optype] [int] NOT NULL,[设定上限] [nvarchar](50) NULL,[设定下限] [nvarchar](50) NULL,[实际] [nvarchar](50) NULL,[Einzel I.O.] [nvarchar](50) NULL,[程序] [nvarchar](50) NULL,[通道] [nvarchar](50) NULL) ON [PRIMARY]
        Hashtable ht_CodeSQL = new Hashtable();
        string strSQL = "select [code],[description] from [Group]";

        //dt_code = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        DataTable dt_code = new DataTable();

        dt_code = ExecuteDataTable(strSQL);
        for (int i = 0; i < List_DateMonth.Count; i++)
        {
            string strDate = List_DateMonth[i];//DateTime.Now.ToString("yyyy-MM");

            string strCheckTable = "";



            //2016-03-30-zjh-自定义显示列名
            strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

            //dt_GroupDetail = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);

            if (dt_code != null && dt_code.Rows.Count > 0)
            {
                for (int j = 0; j < dt_code.Rows.Count; j++)
                {
                    string strTable = "[" + dt_code.Rows[j]["code"].ToString() + "-" + strDate + "]";//[0007-2015-10]

                    strCheckTable += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'" + strTable + "') AND type in (N'U')) select '1' else CREATE TABLE " + strTable + "([engine_index] [int] NOT NULL,[opnumber] [int] NOT NULL,[optype] [int] NOT NULL,";

                    DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[j]["code"].ToString() + "'", "column_index asc");

                    if (drs != null && drs.Length > 0)
                    {
                        for (int m = 0; m < drs.Length; m++)
                        {
                            strCheckTable += "[" + drs[m]["column_name"] + "] [nvarchar](50) NULL,";//[设定上限] [nvarchar](50) NULL,
                        }
                        strCheckTable += ") ON [PRIMARY] ";
                    }
                }
            }

            if (!string.IsNullOrEmpty(strCheckTable))
            {
                ExecuteNonQuery(strCheckTable);

                //Utility.ExectueTranNonQuery(strCheckTable);//2016-03-08-zjh-权限用一线的数据库
            }
        }

        if (dt_code != null && dt_code.Rows.Count > 0)
        {
            //初始化
            ht_CodeSQL.Clear();
            //2016-03-30-zjh-自定义显示列名
            strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

            DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);
            for (int i = 0; i < dt_code.Rows.Count; i++)
            {
                //2016-03-30-zjh-自定义显示列名
                string strColName = "";
                DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[i]["code"].ToString() + "'", "column_index asc");//column_index asc将来GroupDetail表需要增加排序列
                if (drs != null && drs.Length > 0)
                {
                    for (int m = 0; m < drs.Length; m++)
                    {
                        strColName += ",[" + drs[m]["column_name"].ToString() + "] as '" + drs[m]["show_name"].ToString() + "'";
                    }
                }

                string strValue = "";

                string strValue2 = "";

                for (int j = 0; j < List_DateMonth.Count; j++)
                {
                    string strTable = "[" + dt_code.Rows[i]["code"].ToString() + "-" + List_DateMonth[j] + "]";//[0007-2015-10]

                    //strValue += " select * from " + strTable + " ";

                    //2016-03-30-zjh-自定义显示列名
                    strValue += " select engine_index,opnumber,optype" + strColName + " from " + strTable + " ";

                    strValue2 += " select engine_index,opnumber from " + strTable + " ";

                    if (j != List_DateMonth.Count - 1)//最后一条记录不加UNION,其他的加UNION
                    {
                        strValue += " UNION ";//UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
                        strValue2 += " UNION ALL ";
                    }
                }
                if (!ht_CodeSQL.Contains(dt_code.Rows[i]["code"].ToString()))
                {
                    //strValue = "(" + strValue + ") as [" + dt_code.Rows[i]["code"].ToString() + "]";
                    ht_CodeSQL.Add(dt_code.Rows[i]["code"].ToString(), strValue);

                    //2018-06-07-zjh
                    if (i == dt_code.Rows.Count - 1)
                    {
                        strTable2 += strValue2;
                    }
                    else
                    {
                        strTable2 += strValue2 + " UNION ALL ";
                    }
                }
                if (!ht_Code.Contains(dt_code.Rows[i]["code"].ToString()))
                {
                    ht_Code.Add(dt_code.Rows[i]["code"].ToString(), dt_code.Rows[i]["description"].ToString());
                }
            }
        }


        return ht_CodeSQL;
    }

    /// <summary>
    /// 检查[Group]表中的[code]月份,只建发动机所在月份的表，如：[0007-2015-10],[0008-2015-10],[0009-2015-10],[0010-2015-10]
    /// </summary>
    private Hashtable CheckTableMonth(List<string> List_DateMonthOne)
    {
        Hashtable ht_CodeSQLMonth = new Hashtable();

        string strSQL = "select [code],[description] from [Group]";

        //dt_code = DataHelper.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
        DataTable dt_code = new DataTable();

        dt_code = ExecuteDataTable(strSQL);

        if (dt_code != null && dt_code.Rows.Count > 0)
        {
            //初始化
            ht_CodeSQLMonth.Clear();
            //2016-03-30-zjh-自定义显示列名
            strSQL = "SELECT t1.[groupid],t1.[code],t1.[description],t2.[column_index],t2.[column_name],t2.[description] as GroupDetaildescription,t2.show_name FROM [Group] t1 inner join [GroupDetail] t2 on t1.groupid=t2.groupid";

            DataTable  dt_GroupDetail = ExecuteDataTable(strSQL);
            for (int i = 0; i < dt_code.Rows.Count; i++)
            {
                //2016-03-30-zjh-自定义显示列名
                string strColName = "";
                DataRow[] drs = dt_GroupDetail.Select("code='" + dt_code.Rows[i]["code"].ToString() + "'", "column_index asc");//column_index asc将来GroupDetail表需要增加排序列
                if (drs != null && drs.Length > 0)
                {
                    for (int m = 0; m < drs.Length; m++)
                    {
                        strColName += ",[" + drs[m]["column_name"].ToString() + "] as '" + drs[m]["show_name"].ToString() + "'";
                    }
                }

                string strValue = "";

                string strValue2 = "";

                for (int j = 0; j < List_DateMonthOne.Count; j++)
                {
                    string strTable = "[" + dt_code.Rows[i]["code"].ToString() + "-" + List_DateMonthOne[j] + "]";//[0007-2015-10]

                    //strValue += " select * from " + strTable + " ";

                    //2016-03-30-zjh-自定义显示列名
                    strValue += " select engine_index,opnumber,optype" + strColName + " from " + strTable + " ";

                    strValue2 += " select engine_index,opnumber from " + strTable + " ";

                    if (j != List_DateMonthOne.Count - 1)//最后一条记录不加UNION,其他的加UNION
                    {
                        strValue += " UNION ";//UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
                        strValue2 += " UNION ALL ";
                    }
                }
                if (!ht_CodeSQLMonth.Contains(dt_code.Rows[i]["code"].ToString()))
                {
                    //strValue = "(" + strValue + ") as [" + dt_code.Rows[i]["code"].ToString() + "]";
                    ht_CodeSQLMonth.Add(dt_code.Rows[i]["code"].ToString(), strValue);
                }
            }
        }
        return ht_CodeSQLMonth;
    }
    /// <summary>
    /// 将DataTable数据转换成Excel数据流
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    public  MemoryStream DataTableExport(DataTable data)
    {
        MemoryStream ms = null;
        try
        {
            Workbook book = new Workbook();
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;

            int Colnum = data.Columns.Count;//表格列数 
            int Rownum = data.Rows.Count;//表格行数 
                                         //生成行 列名行 
            for (int i = 0; i < Colnum; i++)
            {
                cells[0, i].PutValue(data.Columns[i].ColumnName);
            }
            //生成数据行 
            for (int i = 0; i < Rownum; i++)
            {
                for (int k = 0; k < Colnum; k++)
                {
                    cells[1 + i, k].PutValue(data.Rows[i][k].ToString());
                }
            }
            ms =   book.SaveToStream();


            GC.Collect();
        }
        catch (Exception e)
        {
            //logger.Error("生成excel出错：" + e.Message);
        }
        return ms;
    }

    public static DataTable ExecuteDataTable(string sqlZiDingYi)
    {
        string connStr = LineNum == "一线" ? System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString") : System.Configuration.ConfigurationManager.AppSettings.Get("connectionStringL2");

        SqlConnection conn = new SqlConnection(connStr);
        try
        {
            conn.Open();
            SqlCommand comm = new SqlCommand(sqlZiDingYi, conn);
            int iTimeOut = Convert.ToInt32( System.Configuration.ConfigurationManager.AppSettings.Get("TimeOut"));
            comm.CommandTimeout = iTimeOut;//600秒=10分钟:迁移数据量比较大,超时时间设置为10分钟
            DataTable dt = new DataTable();
            dt.Load(comm.ExecuteReader());
            conn.Close();
            return dt;
        }
        catch (Exception ex)
        {
            conn.Close();
            //MessageBox.Show(ex.Source + ":" + ex.Message);
            return new DataTable();
        }

    }

    public static bool ExecuteDataset(string sql, out DataSet ds)
    {
        string errorMessage;
        string connectionString = LineNum == "一线" ? System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString") : System.Configuration.ConfigurationManager.AppSettings.Get("connectionStringL2");

        bool result = false;
        errorMessage = "";
        ds = new DataSet();
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                {
                    dsCommand.SelectCommand = new SqlCommand(sql, conn);
                    dsCommand.Fill(ds);
                }
                result = true;
            }
            catch (Exception e)
            {
                errorMessage = e.ToString();
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
        return result;
    }

    public static void ExecuteNonQuery(string sqlText)
    {
        string connectionString =  System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionString");

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            try
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;

                    cmd.CommandText = sqlText;
                    sqlText = cmd.CommandText;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
        }
    }

}

#endregion

#region ExcelCells
public class ExcelCells
{
    public Workbook workbook;
    public Worksheet worksheet;
    public Cells cells;

    /// <summary>
    /// 初始化操作类
    /// </summary>        
    public void CreateStaticReport()
    {
        //Instantiate a new workbook
        workbook = new Workbook();
        //workbook.Worksheets[0].Cells.SetRowHeightPixel(1, 50);
    }

    /// <summary>
    /// 打开报表模板
    /// </summary>
    /// <param name="path">模板路径</param>
    public void OpenReport(MemoryStream stream)
    {
        //Open template
        //打开模板
        //string path = System.Web.HttpContext.Current.Server.MapPath("~");
        //path = path.Substring(0, path.LastIndexOf("\\"));
        //path += @"\designer\Workbooks\HelloWorld.xls";
        try
        {
            // workbook.Open(path);
            //workbook.Open(stream);
            workbook = new Workbook(stream);//此处为新方法,Open是旧方法
        }
        catch (System.Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 打开报表模板
    /// </summary>
    /// <param name="fileName">文件全路径名</param>
    public void OpenReport(string fileName)
    {
        try
        {
            //workbook.Open(fileName);
            workbook = new Workbook(fileName);//此处为新方法,Open是旧方法
        }
        catch (System.Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 获取工作簿中的工作表
    /// </summary>
    /// <param name="sheetNumber">工作簿的位置</param>
    public void GetWorksheet(int sheetNumber)
    {
        //获得工作簿中的第一个工作表
        worksheet = workbook.Worksheets[sheetNumber];

        ////清空批注信息
        //worksheet.ClearComments();

        ////设置所有列为自适应列宽
        //worksheet.AutoFitColumns(); 

        ////指定行设置为自适应行宽  
        //worksheet.AutoFitRow(0, 0, 0);

        //获得工作表中的Sheet
        cells = worksheet.Cells;
    }

    public string GetEecelCellsValue(string cellsName)
    {
        return cells[cellsName].StringValue;
    }

    /// <summary>
    /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
    /// </summary>
    /// <param name="inputString">以前缀字符查询,如'$'</param>
    /// <returns></returns>
    public List<string> GetExcelSQLListString(string inputString)
    {
        List<string> SQLListString = new List<string>();

        string str = string.Empty;

        //读取Excel的批注
        for (int i = 0; i < worksheet.Comments.Count; i++)
        {
            //str = string.Format("{0}${1}${2}", cells[0, 0].Worksheet.Comments[i].Note, cells[0, 0].Worksheet.Comments[i].Row, cells[0, 0].Worksheet.Comments[i].Column);
            if (!worksheet.Comments[i].Note.Contains(inputString))
            {
                continue;
            }
            str = string.Format("{0}${1}${2}", worksheet.Comments[i].Note, worksheet.Comments[i].Row, worksheet.Comments[i].Column);
            SQLListString.Add(str);
        }

        //清空批注信息
        worksheet.ClearComments();




        //单元格直接填充方式如:connectionString$SELECT top 2 * FROM wl
        ////Find the cell ending with the input string
        //Aspose.Cells.Cell cell = cells.FindStringContains(inputString, null);

        //if (cell != null)
        //{
        //    str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);

        //    //单元格里有公式的不做sql解析,直接调用相应公式
        //    if (cell.Formula == null)
        //    {
        //        SQLListString.Add(str);
        //    }

        //    //  [10/23/2011 Andy]
        //    //删除sql所在的位置内容,以防止填充错位
        //    //ClearCells(cell.Row, cell.Column);
        //}

        //while (cell != null)
        //{
        //    cell = cells.FindStringContains(inputString, cell);
        //    if (cell != null)
        //    {
        //        str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);

        //        //单元格里有公式的不做sql解析,直接调用相应公式
        //        if (cell.Formula == null)
        //        {
        //            SQLListString.Add(str);
        //        }

        //        //  [10/23/2011 Andy]
        //        //删除sql所在的位置内容,以防止填充错位
        //        //ClearCells(cell.Row, cell.Column);
        //    }
        //}

        return SQLListString;
    }

    /// <summary>
    /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
    /// </summary>
    /// <param name="inputString">以前缀字符查询,如'$'</param>
    /// <param name="SheetName">Excel工作单名称</param>
    /// <returns></returns>
    public List<string> GetExcelSQLListString(string inputString,string SheetName)
    {

        List<string> SQLListString = new List<string>();

        string str = string.Empty;

        //Find the cell ending with the input string
        Aspose.Cells.Cell cell = workbook.Worksheets[SheetName].Cells.FindStringContains(inputString, null);


        if (cell != null)
        {
            str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);
            SQLListString.Add(str);


            //  [10/23/2011 Andy]
            //删除sql所在的位置内容,以防止填充错位
            //ClearCells(cell.Row, cell.Column);
        }


        while (cell != null)
        {
            cell = cells.FindStringContains(inputString, cell);
            if (cell != null)
            {
                str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);
                SQLListString.Add(str);

                //  [10/23/2011 Andy]
                //删除sql所在的位置内容,以防止填充错位
                //ClearCells(cell.Row, cell.Column);
            }
        }

        return SQLListString;
    }

    /// <summary>
    /// 消除单元格内容
    /// </summary>
    private void ClearCells(int row, int column)
    {
        //删除起始单元格至结束单元格数据
        cells.ClearContents(row, column, row, column);

        cells.InsertRows(1, 2);
    }

    /// <summary>
    /// 返回内容数据流.
    /// </summary>
    /// <returns></returns>
    public Stream GetExcelStream()
    {
        Stream stream = workbook.SaveToStream();
        return stream;
    }

    /// 用于获取Excel文件中SQL语句.有SQL语句和起始位置(即所在的行和列)
    /// </summary>
    public Cell GetExcelSQLString()
    {
        string str = string.Empty;

        //Find the cell ending with the input string
        Aspose.Cells.Cell cell = cells.FindStringContains("$", null);

        if (cell != null)
        {
            str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);

        }

        return cell;
    }

    public Cell GetExcelSQLNextString(Cell previousCell)
    {
        string str = string.Empty;

        //Find the cell ending with the input string
        Aspose.Cells.Cell cell = cells.FindStringContains("$", previousCell);

        if (cell != null)
        {
            str = string.Format("{0}${1}${2}", cell.StringValue, cell.Row, cell.Column);

        }

        return cell;
    }

    /// <summary>
    /// 将DataTable写到指定的位置中.(Excel中不显示表头)
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="firstRow"></param>
    /// <param name="firstColumn"></param>
    public void ImportDataColumn(DataTable dt, int firstRow, int firstColumn)
    {
        int j = cells.ImportDataTable(dt, false, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, false);
    }

    /// <summary>
    /// 将DataTable写到指定的位置中.(Excel中不显示表头)-是否插入行(insertRows)
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="firstRow"></param>
    /// <param name="firstColumn"></param>
    public void ImportDataColumn(bool blinsertRows, DataTable dt, int firstRow, int firstColumn)
    {
        int j = cells.ImportDataTable(dt, false, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, blinsertRows);
    }

    /// <summary>
    /// 将DataTable写到指定的位置中.(Excel中是否显示表头)
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="firstRow"></param>
    /// <param name="firstColumn"></param>
    /// <param name="blHaveCol">Excel中是否显示表头</param>
    public void ImportDataColumn(DataTable dt, int firstRow, int firstColumn,bool blHaveCol)
    {
        int j = cells.ImportDataTable(dt, blHaveCol, firstRow, firstColumn, dt.Rows.Count, dt.Columns.Count, false, "yyyy-MM-dd HH:mm:ss");
    }

    public void ImportDataColumn(DataTable dt, string startCell)
    {
        cells.ImportDataTable(dt, false, startCell);
    }

    public DataTable ExportDataColumn(int firstRow,int firstCol,int totalRows,int totalCols)
    {
        return cells.ExportDataTable(firstRow, firstCol, totalRows, totalCols);
    }

    /// <summary>
    /// 向单元格中写入数据
    /// </summary>
    /// <param name="cellsName">单元格名称,如:B1</param>
    /// <param name="stringValue">数值</param>
    public void PutCellValue(string cellsName, string stringValue)
    {
        cells[cellsName].PutValue(stringValue);
    }

    /// <summary>
    /// 向单元格中写入数据:x-行,y-列
    /// </summary>
    /// <param name="x">行</param>
    /// <param name="y">列</param>
    /// <param name="stringValue">数值</param>
    public void PutCellValue(int x, int y, string stringValue)
    {
        cells[x, y].PutValue(stringValue);
    }

    /// <summary>
    /// 单元格公式计算:x-行,y-列
    /// </summary>
    /// <param name="x">行</param>
    /// <param name="y">列</param>
    /// <param name="formulaValue">公式</param>
    public void FormulaValue(int x, int y, string formulaValue)
    {
        cells[x, y].Formula = formulaValue;
    }

    /// <summary>
    /// 重新计算表格中的公式
    /// </summary>
    public void ReCalcOnOpen()
    {
        workbook.CalculateFormula();
    }

    /// <summary>
    /// 设置单元格背景颜色
    /// </summary>
    /// <param name="x"></param>
    /// <param name="y"></param>
    /// <param name="colorValue"></param>
    public void PutCellColor(int x, int y, string colorValue)
    {
        Style stype = cells[x,y].GetStyle();
        stype.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(colorValue);                stype.Pattern = BackgroundType.Solid;
        stype.Font.IsBold = true;
        cells[x, y].SetStyle(stype);
    }


    public void PutCellColor(string cellName, string colorValue)
    {
        Style stype = cells[cellName].GetStyle();
        stype.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(colorValue);
        stype.Pattern = BackgroundType.Solid;
        stype.Font.IsBold = true;
        cells[cellName].SetStyle(stype);
    }


    public void other()
    {
        //Get the first worksheet in the workbook


        //worksheet.Name = "222";

        //Get the cells collection in the sheet


        //Put a string value into the cell using its name
        //写入使用其名称的单元格字符串值
        cells["A1"].PutValue("Cell Value");

        //put a string value into the cell using its name
        cells["A2"].PutValue("Hello World");

        //Put an boolean value into the cell using its name
        cells["A3"].PutValue(true);

        //Put an int value into the cell using its name
        cells["A4"].PutValue(100);

        //Put an double value into the cell using its name
        cells["A5"].PutValue(2856.5);

        //Put an string value that can be converted to other data type if appropriate
        cells["A6"].PutValue((123.6).ToString(), true);

        //Put an object value into the cell using its name
        object obj = "Aspose";
        cells["A7"].PutValue(obj);

        //Put an datetime value into the cell
        DateTime dt = DateTime.Now;
        cells["A8"].PutValue(dt);
        //cells["A9"].Style.Number = 14;
        cells[1].PutValue(38843);
        //Put a string value into the cell using its row and column
        cells[0, 1].PutValue("Cell Value Type");


        for (int i = 1; i < 100; i++)
        {
            for (int j = 1; j < 100; j++)
            {
                cells[i, j].PutValue("ddlld" + i.ToString());
                //ee.SetCellValue(1, i, j, "ddlld" + i.ToString());
            }
        }
        //for (int i = 1; i < 8; i++)
        //{
        //    switch (cells[i, 0].Type)
        //    {
        //        //Cell value is boolean
        //        case CellValueType.IsBool:
        //            cells[i, 1].PutValue("IsBool");
        //            break;
        //        //Cell value is datetime
        //        case CellValueType.IsDateTime:
        //            cells[i, 1].PutValue("IsDateTime");
        //            break;
        //        //Blank cell
        //        case CellValueType.IsNull:
        //            cells[i, 1].PutValue("IsNull");
        //            break;
        //        //Cell value is numeric
        //        case CellValueType.IsNumeric:
        //            cells[i, 1].PutValue("IsNumeric");
        //            break;
        //        //Cell value is string
        //        case CellValueType.IsString:
        //            cells[i, 1].PutValue("IsString");
        //            break;
        //        //Cell value type is unknown
        //        case CellValueType.IsUnknown:
        //            cells[i, 1].PutValue("IsUnknown");
        //            break;
        //    }
        //}

        //Save the excel file

        // End response to avoid unneeded html after xls
        //Response.End();
    }


    public void SaveAs(string path, SaveFormat saveFormat)
    {
        //workbookNew.Worksheets[sheetNumber] = worksheet;
        //Stream stream =(Stream) worksheet;
        //workbook.Worksheets.RemoveAt(sheetNumber);

        try
        {
            //worksheet.AutoFitColumns();
            //worksheet.AutoFitColumn(5);

            //将所有字符串型数据转换为数值型数据
            //cells.ConvertStringToNumericValue();

            //获取或设置是否显示网格
            worksheet.IsGridlinesVisible = true;

            //设置当前光标停留的单元格
            worksheet.Move(0);

            workbook.Save(path,saveFormat);

        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.Message.ToString());
        }

    }


    /// <summary>
    /// 保存文件
    /// </summary>
    /// <param name="path">保存路径</param>
    /// <param name="saveFormat">文件格式</param>
    /// <param name="saveSheetIndex">保存文件的序号,从1开始</param>
    public void SaveAs(string path, SaveFormat saveFormat, int saveSheetIndex)
    {
        try
        {
            //获取或设置是否显示网格
            worksheet.IsGridlinesVisible = false;
            //设置当前光标停留的单元格
            worksheet.Move(0);

            int j = workbook.Worksheets.Count;
            if (saveSheetIndex<1)
            {
                saveSheetIndex = 1;
            }

            ArrayList al = new ArrayList();
            for (int i = 0; i < j; i++)
            {
                al.Add(workbook.Worksheets[i].Name);
            }

            al.Remove(al[saveSheetIndex-1]);

            foreach (String str in al)
            {
                workbook.Worksheets.RemoveAt(str);
            }

            workbook.Save(path, saveFormat);

        }
        catch (System.Exception e)
        {
            Console.WriteLine(e.Message.ToString());
        }

    }
    public void ClearCell(string InputString)
    {
        Aspose.Cells.Cell cell = cells.FindStringContains(InputString, null);


        if (cell != null)
        {
            cell.PutValue("");
        }


        while (cell != null)
        {
            cell = cells.FindStringContains(InputString, cell);
            if (cell != null)
            {
                cell.PutValue("");
            }
        }
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="dt">要导出的dataTable数据</param>
    /// <returns>创建时间：130915 wzp</returns>
    public string OutputExcel(DataTable dt, string fileName)
    {
        string returnStr = string.Empty;
        Workbook book = new Workbook();
        Worksheet sheet = book.Worksheets[0];

        int i = 0;
        foreach (DataColumn dc in dt.Columns)
        {
            sheet.Cells[0, i].PutValue(dc.ColumnName);
            i++;
        }
        for (int r = 0; r < dt.Rows.Count; r++)
        {
            for (int c = 0; c < dt.Columns.Count; c++)
            {
                sheet.Cells[r + 1, c].PutValue(dt.Rows[r][c].ToString());
            }
        }
        string FileName = fileName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";//.xls
                                                                                                      //string FilePath = System.Web.HttpContext.Current.Server.MapPath("~/") + "DownLoad\\" + FileName;
        returnStr = AppDomain.CurrentDomain.BaseDirectory +"Temp\\" + FileName;
        book.Save(returnStr, SaveFormat.Xlsx);
        return returnStr;
    }



}

#endregion